CODEWITHSUNDEEP
excelvbacsvdaodata-access-object
John
John

Reputation: 237

errors importing CSV (delimited) into DAO database using vba and SQL from

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.

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.

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

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,....)

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...

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.

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$]"

Upvotes: 0

Views: 1116

Answers (1)

Parfait
Parfait

Reputation: 107587

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.

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)

Upvotes: 0

Related Questions