Importing data to MS Access when the date is in the format dd-mmm-yyyy

Question

I have a database which I use to keep my business accounts, it's that time of year again when I need to bring the accounts up to date and what I do is download my bank statement as a csv from the bank's website and then I have a linked table in my access database to that csv so that the next step can be an append query to append the transactions in the csv statement to the transactions table in my database, and then all I have to do is give each transaction a category.

Since last time I imported my statements, my bank has changed the format of the date from dd/mm/yyyy to dd-mmm-yyyy and this is no longer readable by access, resulting in the date column just being full of #num errors.

I tried creating a new linked table (previously I just downloaded the latest csv then updated the linked table to point to it), assuming that when creating it I would be able to tell it what format the date should be in, but there doesn't seem to be an option.

I could be missing it, but as far as I can tell dates in linked tables must be in the format dd/mm/yyyy or else Access simply won't be able to read them. Even if people have no solution to the issue, I'd at least appreciate just knowing if this is true or not so that I'm not wasting time looking for a feature that doesn't exist.

Answer 1

Consider using a schema.ini file to be saved in same directory as CSV specifying the date/time format.

Save below in a text file named schema.ini. Adjust file names accirdingly. You can even specify data types by columns. All lines are optional.

[MyData.csv]
ColNameHeader=True
Format=Delimited(,)
MaxScanRows=0
DateTimeFormat=dd-mm-yyyy
Col1="EmployeeID" Integer
Col2="Amount" Double
Col3="Date" DateTime
Col4="Details" Text
...