Text file import for date format DD MMM YYYY results in error

[peskywinnets]

Hello,

So I have a text file that I need to regularly import, where the first column of data in the text file has a Date in the following format...

21 Dec 2015

...I've tried messing about with all the paramaters in the import specifications, but no matter what I do, the final imported Date data results in #Num! in the table

Any top tips how I can workaround this to get the date to show as it should?

 

[sneuberg]

Try CDate

http://www.techonthenet.com/access/functions/datatype/cdate.php

 

[peskywinnets]

Thanks but I don't have the 'Access chops' to know how to use that.

Are you saying to first import it & then use VBA code to change the badly imported date in the resulting table so that it shows correctly? If so, how do I get the code to change/cycle through all the rows?!!!

 

[Frothingslosh]

Basically, you load your data into a 'staging' table as text. Then you create an append query to copy it to your permanent table. Instead of just copying the field (let's call it DataDate) over as-is, you replace the field name in the query grid with:

ConvertedDate: CDate(DataDate)

Once the append query runs successfully, you delete your data from the staging table.
(Note - there are other ways to do this, but this is probably the easiest. You'll need to compact the database regularly, though, as this WILL cause bloating due to the way Access handles record deletion.)

 

[peskywinnets]

Many thanks...that worked a treat....but wow, what a faff, just to get a date showing right!

 

[gemma-the-husky]

well the reason is that "21 Dec 2015" is just a bit of text, until you tell access it is to be treated as a date.

try changing the date to something that is not a date, such as "32 Dec 2015" to see what happens. With reliable data, it probably won't ever be a problem but your import code may need to be resilient in case of errors.

So much programming is defensive, guarding against things like this.

 

[peskywinnets]

well the reason is that "21 Dec 2015" is just a bit of text, until you tell access it is to be treated as a date.

I guess what I was meaning was that the import specification ought to be a bit more flexible, because dates are quite often written as DD MMM YYYY, but to have to import it as text first then have to run CDate() on it, just seems like an extra step that could have been embodied into the access text import specification.

 

[Frothingslosh]

The formats in the import process are pretty restricted, that's all. Access is quite capable of recognizing non-standard dates, as the CDate thing shows, and you can use VBA to work around pretty much anything.

Hell, the dates I have to import often come in the format ccyymm or ccyyddmm, neither of which are automatically picked up, either.

Cleaning the data is just part of the job of running databases, and sometimes it takes extra steps.

 

[Galaxiom]

One thing to watch out for is Windows' (not just Access/VBA) willingness to accept dates that are invalid in the Regional Date format so long as they are valid in some recognised format.

One of my favourites is revealed in expressions like:
IsDate("29/2/15")

2015 is not a leap year but this date tests as valid. To see what it does, type this in the Immediate Window:
? CDate("29/2/15")