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

Question

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

I can't recreate errors. The uncommented out strSQL for csv runs fine as do Excel versions on my end. However, the query that you claim does work is a head-scratcher: `Select * FROM [Attendance#csv]`? How does a direct query on a csv work? — Parfait, Aug 08 '16 at 01:56
Oh. With the commented out db= that pointed towards the csv, which made it work. I think, in structuring this question, with cut/paste of the attempts, I've tangled the presentation. I want to end up with a LEFT JOIN of two external databases, the LEFT JOIN which is commented out. The balance of the code were attempts to isolate what linguistic twists caused the errors. I will re-edit the question, and leave the code that failed, top-to-bottom, if I may indulge your patience; I'll comment in when I've done that... — John, Aug 08 '16 at 11:56
Hmmm. Thinking about this....the answer may be that I am referencing the FullPath [Folder + FileName] in the external reference, rather than the Folder. Will look at this tomorrow. This may explain everything. Note the difference between the standalone commented-out Set dB, which references the folder, and the reference to strPathXXX, which I know to be a FullPath [folder plus fileName.] — John, Aug 09 '16 at 01:17
Did you put this comment in the wrong place? Text files need a directory as database but Excel files needs the specific file as database. — Parfait, Aug 09 '16 at 01:44

score 0 · Answer 1 · answered Aug 09 '16 at 00:35

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)

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

1 Answers1

Linked