Forum Discussion
Date errors when importing csv file to Access -
Hello, everyone,
I encounter the following error when importing csv file to access. Can someone advise me how to make Access read the data correctly?
I exported a csv file including a date column from Salesforce Marketing Cloud (default is American date format - MM/DD/YYYY HH:MM:SS. For instance, 9/23/2019 3:14:30 PM)
When I import the csv file to Access, Access will return me errors for all the dates in the date column. Unfortunately I can not find why it wont work. I would appreciaite if someone can offer a simple solution, becuase I need to import 10x different files everyday.
I attached the file and hope that helps. Thank you in advance.
best regards, Di
Blood4Iron Hello , check the attachment if this is what you are after
These are the first 1000 lines of your csv....for this i used my custom CSV importer which is in a stage that i cannot decide if i am going to release it to public or use it for small freelancing projects.
If you don't get other helpful replies contact me and will sort it out.
JT
Hi, tsgiannis
there are other thousands of lines in the file. I only uploaded the first 1000 because i thought it makes my case easier to understand.
Please image there are one million rows in the file and share a solution if applicable.
br, Di
Blood4Iron Hi BI
The way you are importing your .csv is the usual way...probably your problem is due to Access disregarding the comma "," as delimiter and want to match agains system delimiter....for example my system delimiter is ";"
To test it...just open your .csv with any text editor and do a replacement of comma to something else like ";" or "|" for example
Then redo the import process...select the new delimiter...it should finish just fine.
My code doesn't care about system delimiters and such ...it just accepts the delimiter you want to use and works by iterating the .csv line by line (thus its a bit slower)...on the other hand i have complete control on what to import and and how i want this imported....for example i could import numbers as text or format text to date...numbers to date...and so on.
Now for such a vast amount of lines (.csv) maybe you should consider full automation ...like pointing a directory...iterating the .csvs ...importing them....and then whatever manipulation you want....but this is beyond the scope of a simple question
JT
Blood4Iron The import usually looks at the Excel cell types for the first few rows. Looks like although your values in EventDate look like a date, they are formatted as General. I would try formatting them using the custom m/d/yyyy h:mm option in Excel. Then make sure on your import mapping they are mapping to the type Date with Time
