Sending SQL Queries to CSV file on Excel VBA doesn't work for Date Columns-CodePudding

I am trying to get some data from a csv file having more than than 3 million rows. I've set an ADODB connection to my csv file and everything works fine except for querying date columns.

Here is how I set the connection

Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset

With conn
   .Provider = "Microsoft.ACE.OLEDB.16.0"
   .ConnectionString = "Data Source=" & fileDirPath & ";Extended Properties='text'"
End With

conn.Open

The query I send is

SELECT * FROM my_csv.csv WHERE myDate < 2030-12-01

Although all the dates in myDate column are smaller than 2030-12-01, recordset returns an empty result. It's not spesific for that date, any query with myDate column returns empty. Thanks a lot in advance for your support.

My schema.ini file looks like

[my_csv.csv]
DecimalSymbol=.
Format=CSVDelimited
ColNameHeader=True
DateTimeFormat=YYYY-MM-DD
DecimalSymbol=,
Col1=org_id Text 
Col2=app_id Text 
Col3=myDate Date(YYYY-MM-DD)
Col4=valid Text

Here is how my data is formatted

cpt_id,rmn_id,myDate,valid
1285230,652671575,2019-03-12,true
1956123,1384198347,2019-03-15,true
2123640,1901167025,2019-02-21,true
2231241,1021153291,2019-03-02,true
7349946,1462824644,2019-03-07,true
7349946,4661867294,2019-03-16,true
1956123,3611518899,2019-03-01,true

CodePudding user response：

To me, it seems that the date specified in the query needs quotes around it to prevent the data from being a subtraction problem. Try this:

SELECT * FROM my_csv.csv WHERE myDate < '2030-12-01'

I haven't worked much with the ACE database driver, if it is like the older JET, it may require pound signs (#) for date literals, so if the above does not work, try this:

SELECT * FROM my_csv.csv WHERE myDate < #2030-12-01#

CodePudding user response：

You have to use the date literals in the format #MM/dd/yyyy HH:mm:ss#. The SQL statement

 "SELECT * FROM my_csv.csv WHERE myDate < #12/01/2030#"

should work here.