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

Question

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

single quotes around the date literal

Joel Coehoorn
– Joel Coehoorn

2022-04-15 13:46:28 +00:00
Commented Apr 15, 2022 at 13:46 — Joel Coehoorn
– Joel Coehoorn, Commented Apr 15, 2022 at 13:46

Gove · Accepted Answer · 2022-04-15 14:33:59Z

0

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#

edited Apr 15, 2022 at 14:33

answered Apr 15, 2022 at 14:23

Gove

1,82411 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Oğuzhan Orhan Over a year ago

Pound signs are what I was looking for. Thanks a lot!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related