Invalid Date Format

[Fherrera]

Hi, this may a bit OT ..

But while trying to import a CSV file I get an error as the wizard cannot distinguish the date. Format looks like this:

TAG,DATE,VALUE
UNIT1_A5272019,6/29/1993 8:48 AM,3
UNIT1_A5272019,6/30/1993 12:00 AM,3

ie. MM/DD/YYYY HH:MM AM/PM

I tried the advanced tab during the wizard to get it to work, but it doesn't help.

Does anyone have other suggestions... Besides declaring it as a string that is?

Thanks

Frank

 

[Tranman]

Try cutting your CSV file down to just one record and importing that. If that works, take a close look at your data, and try to find one or more records where the date is missing or corrupted.

Tranman

 

[Fherrera]

Well, in the _importerrors table it creates in addition to the original it tells me that every row did not successfully have a date (or the date was not added for every row).

I tried it with a single entry and the same occurred.

I guess I have to convert the date entries of my 1.3 Gigz worth of CSV files into a "proper" Access date format...

Might you know a way to do this? I was thinking VBscript and hoping it was similar to VBA ...

Thanks.

Frank

 

[Fherrera]

I tried a few different variations and it seems it's the AM at the end of the date that causes the problem...

Looking at the date Access converts it too, I also tried adding a :00 at the end of the time for seconds but it still wouldn't read it...

TAG,DATE,VALUE
UNIT1_A5272019,06/29/1993 08:48:00 AM,3
UNIT1_A5272019,06/29/1993,3
UNIT1_A5272019,06/29/1993 08:48,3
UNIT1_A5272019,6/29/1993 8:48,3
UNIT1_A5272019,6/29/1993 20:48,3

So of those 5, only the first one was not converted properly (dates).

So, I need to go through the 1.3 gigz of csv files and convert the entries with PM to 12+the time and just get rid of the AM entries...

Anyone have a suggestion as to how this might be carried out...

Thanks

Frank

 

[Tranman]

You can load your values as text, then use the CDate function to convert them.

Just define the date column as text and import the file.

Then create an update query that converts the date field to: Str(CDate([<your date field name]))

Then open the table in design view and change the data type of your date field to Date/Time.

I verified this and it worked fine on the two sample records in your original post.

Tranman

 

[Fherrera]

Sounds good I'll give it a shot. Thanks Tranman!