A very simple query function that takes in a path for a source CSV file and a SQL statement as a string (I'm also transposing the data from the VBA function),
Public Function RunQuery(FilePath As String, SQLStatement As String)
Dim Conn As New ADODB.Connection
Dim RecSet As New ADODB.Recordset
With Conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & FilePath & ";" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited;IMEX=1"""
End With
Conn.Open
RecSet.Open SQLStatement, Conn
RecSet.MoveFirst
RunQuery = RecSet.GetRows()
Conn.Close
Set RecSet = Nothing
Set Conn = Nothing
End FunctionThis code works intermittently against a CSV files, some data is retrieved correctly and some is not. An example are these two CSV files - Small and Large. The following SQL query works perfectly on the Small file, but returns #VALUE on the Large file,
SELECT birthYear FROM [File].
It's definitely not a data limit/size issue as the Full file only contains 1800 rows. Incidentally if I wrap up the logic into a Sub rather than a UDF then it works perfectly without any errors,
Public Sub RunQuerySub()
Dim Conn As New ADODB.Connection
Dim RecSet As New ADODB.Recordset
Dim FilePath As String
FilePath = ActiveSheet.Range("Path")
With Conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & FilePath & ";" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited;IMEX=1"""
End With
Dim SQLStatement As String
SQLStatement = ActiveSheet.Range("SQL")
Conn.Open
RecSet.Open SQLStatement, Conn
ActiveSheet.Cells(1, 8).CopyFromRecordset RecSet
Conn.Close
Set RecSet = Nothing
Set Conn = Nothing
End SubI am very confused, and would appreciate any pointers. I have also posted this question on Stackoverflow, but no answers yet unfortunately.