import text file yyyymmdd into date field

  • Thread starter Thread starter Song Su
  • Start date Start date
S

Song Su

I have text file comlumn 20090120 (yyyymmdd). how to import into Access with
date/time as 01/20/2009? I use import wizard, advanced import specification,
pick YMD as date order without date delimiter, check Four digit years and
check Leadning Zeros in Dates. The import results are all #Num! in each
record.
 
The text string "20090120" is not a date.

If you want Access to store a date/time value in a date/time data-type
field, you'll need to convert the string into an actual date/time value.
One approach might be to import it as text, then use a query to "parse" it
into the proper datatype.

If the CDate() function doesn't do it, you could always use the DateSerial()
function (along with Left(), Right(), and Mid()).

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Song Su said:
I have text file comlumn 20090120 (yyyymmdd). how to import into Access
with date/time as 01/20/2009? I use import wizard, advanced import
specification, pick YMD as date order without date delimiter, check Four
digit years and check Leadning Zeros in Dates. The import results are all
#Num! in each record.
Click to expand...

Access 2003 can import this data, if you using 2007, it is broken. You have
to import the data into a text column, and then run an update query like:


update MyTablename set RealDateCollum =
dateserial(left(textDateCollum,4),mid(textDateCollum,5,2),mid(textDateCollum,7,2))

so, import the column as a text column, and then process as above. A bit
messy, but that is about the only workaround I can think of right now...


What version of access are you using? If you are using a2007, you have to do
this in two steps as per above but at least that gets your data in without
re-typing!

I have already reported this bug....
 
Back
Top