使用vba和SQL从导入CSV（分隔）到DAO数据库中的错误 [英] errors importing CSV (delimited) into DAO database using vba and SQL from

问题描述

我因使用VBA代码的SQL介质导入CSV文件而受阻。我使用第三个EXCEL宏/电子表格，分析了两个文件的LEFT JOIN，一个作为XLXS，另一个作为CSV。

I am stymied by an SQL mediated import of a CSV file using VBA code. I am using a Third EXCEL macro/spreadsheet, to analyze a LEFT JOIN of 2 files, one as an XLXS and the other as a CSV.

我怀疑问题可能是如何使用SQL命令，对于FROM引用到Excel文件。我使用Excel VBA，2010，14数据库访问引擎。

I suspect that part of the problem may be how the SQL command is used, for a FROM reference to an excel file. I am using Excel VBA, 2010, The 14 Database Access Engine.

我想结束一个SQL语句从一个外部逗号分隔的CSV文件

I want to end with an SQL statement that pulls from an external comma delimited CSV file

我预计在一个独立的宏启用excel文件中用这个伪代码来标记宏：

I anticipate heading the macro with this pseudo code, in a stand-alone macro enabled excel file:

dbEngine = CreateObject(DAO.engine ... )
set DB = dbEngine.OpenDatabase(theNormalExternalExcellFile,....)

对于SQL语句，在伪代码中，我想要：

For the SQL statement, in pseudo-code, I want this:

SELECT fields 
    FROM [Table$]   ' a normal external excel file
    LEFT JOIN [an external CSV, comma delimited file]
    ON...
    GROUP...

我可以在一个简单的SQL语句中成功导入XLXS或CSV，将外部文件引用放在SQL的FROM子句中，我得到两个错误之一，这取决于我如何使用代码：无效的文件路径或FROM子句中的错误。路径是-not-invalid。

I can successfully import an XLXS, or the CSV, independently, in a simple SQL statement, yet when I place the outside file references within an SQL's FROM clause, I get one of two errors, depending on how I play with the code: an Invalid File Path, or an error in the FROM Clause. The path is -not- invalid.

在记录集指令下面显示错误。

The error is shown, below, where it occurs, at the recordset instruction.

我还提供了替代SQL字符串，我用它来测试错误在代码中的位置。

I also provide alternative SQL strings, which I had played with to test where in the code the error is generated.

'the Seating Chart
strPathSource = ThisWorkbook.Worksheets("Logic").Range("rngPathSource")
'strFileNameSource = ThisWorkbook.Worksheets("Logic").Range("rngFileNameSource")
'strFileNameSourceWOExt = Left(strFileNameSource, Len(strFileNameSource) - 4)

'the attendance
strPathAttendance = ThisWorkbook.Worksheets("Logic").Range("rngPathAttendance")
strFileNameAttendance = ThisWorkbook.Worksheets("Logic").Range("rngFileNameAttendance")
strFolderAttendance = ThisWorkbook.Worksheets("Logic").Range("rngFolderAttendance")
strFileNameAttendanceWOExt = Left(strFileNameAttendance, Len(strFileNameAttendance) - 4)

Set dbE = CreateObject("Dao.DBEngine.120")
Set db = dbe.OpenDatabase(strPathSource, True, False, "Excel 12.0;HDR=Yes")

''Set db = DAO.OpenDatabase(strFolderAttendance, True, False, "text;HDR=Yes;FMT=Delimited(,)")

'[Master$] is a tab on the spreadsheet at strPathSource
'[Attendance#csv]
'  This reference to the table at strPathAttendance which otherwise works: [Attendance#csv]
'     when not inside the FROM clause

strSQL = _
    "SELECT tM.Job, Count(tA.Name) AS CountOfName" _
    & " FROM [Master$] tM" _
    & " LEFT JOIN" _
    & " (SELECT * FROM [text;HDR=Yes;FMT=Delimited(,);Database='" _
        & strPathAttendance & "'].[" & strFileNameAttendanceWOExt & "#csv]) tA" _
    & " ON (tM.GivenName = tA.GivenName) AND (tM.SurName = tA.SurName)" _
    & " GROUP BY tM.Job" _
    & " ORDER BY tM.Job, Count(tA.Name)"

'Debug.Print strSQL
' This is the reported value for the string, strSQL, particularly the FROM clause:
' SELECT tM.Job, Count(tA.Name) AS CountOfName FROM [Master$] tM LEFT JOIN
'  (SELECT * FROM
'     [text;HDR=Yes;FMT=Delimited(,);Database=T:\Solutions Team Shared Folder\Seats -
'     Attendance\Attendance.csv].[Attendance#csv]) tA
'        ON (tM.GivenName = tA.GivenName) AND (tM.SurName = tA.SurName)
'        GROUP BY tM.Job ORDER BY tM.Job, Count(tA.Name)
'' putting a single or double quote, around the database path, does not change the error

Set rstR = db.OpenRecordset(strSQL)
'Error:
'  'T:\...\...\Attendance.csv' is not a valid path.  Make sure that
'  the path name is spelled correctly and that you are connected to the server
'  on which the file resides.

' ALT SQL strings, to test what's going on.
'strSQL = _
'   "Select * FROM [Attendance#csv]"

'strSQL = _
'   "Select * FROM (Select * FROM [Excel 12.0;HDR=Yes;Database=" & strPathSource & "].[Master$])"

'strSQL = _
'   "SELECT * FROM [text;HDR=Yes;FMT=Delimited(,);Database=" _
'   & strPathAttendance & "].[" & strFileNameAttendanceWOExt & "#csv]"

'strSQL = _
'   "Select * FROM [Excel 12.0;HDR=Yes;Database=" & strPathSource & "].[Master$]"

推荐答案

文本文件与Jet / ACE SQL，数据库参数需要引用目录路径而不是任何特定的文本文件。然后，周期限定符将指定单个文件。

When connected to text files with Jet/ACE SQL, the database parameter needs to reference the directory path not any specific text file. The period qualifier will then specify the individual file.

因此，只需从 strPathAttendance （不带引号）中删除文件名和扩展名。所以查询应该如下所示：

Therefore, simply remove the file name and extension from strPathAttendance (without quotes). So query should look like the below:

SELECT tM.Job, Count(tA.Name) AS CountOfName 
FROM [Master$] tM 
LEFT JOIN
  (SELECT * FROM
    [text;HDR=Yes;FMT=Delimited(,);Database=T:\Solutions Team Shared Folder\Seats -
    Attendance].[Attendance#csv]) tA
ON (tM.GivenName = tA.GivenName) AND (tM.SurName = tA.SurName)
GROUP BY tM.Job 
ORDER BY tM.Job, Count(tA.Name)

这篇关于使用vba和SQL从导入CSV（分隔）到DAO数据库中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！