import text file yyyymmdd into date field

[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.

 

[Jeff Boyce]

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/

 

[Albert D. Kallal]

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.

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....

 

[MGFoster]

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.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Do not check Leading Zeros. Make sure the import specification's data
type of the field is Date/Time.

I tested this on Access 2003 and it worked OK - imported as dates.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSXTgKoechKqOuFEgEQKhUgCfc1ykkCilKA0vgQ9+un9al+pjSGUAn3G4
nhLnB4CUe+c5B1kFnwRuEigt
=kiRx
-----END PGP SIGNATURE-----

 

[Song Su]

No wonder! I used Access 2003 before and now I use Access 2007. Thank you
for the info.