CSV text into date format

  • Thread starter Thread starter Stacey
  • Start date Start date
S

Stacey

I have a file which I will be uploading into Access on a regular basis. The
date field is formatted to text which I would like to format into a date
field upon importing into Access. I would also like to rearrange the date
into a regular format, it reads as such 082101 and I would like it to read
01/21/08. Any help is greatly appreciated. Thanks in advance!

Stacey.
 
Stacey said:
I have a file which I will be uploading into Access on a regular basis.
The
date field is formatted to text which I would like to format into a date
field upon importing into Access. I would also like to rearrange the date
into a regular format, it reads as such 082101 and I would like it to read
01/21/08. Any help is greatly appreciated. Thanks in advance!

Stacey.
Click to expand...


Do I understand correctly that the format used in the CSV file is YYDDMM,
that is to say, a two-digit year followed by a two-digit day followed by a
two-digit month? If so, the following formula would convert it to a date ...

DateSerial(CInt(Left$([TestText],2)),CInt(Right$([TestText],2)),CInt(Mid$([TestText],3,2)))

If you are using the built-in import feature, then the simplest solution
might be to import the data "as is" into a temporary holding table, then use
an append query incorporating the above formula to transfer the data from
the temporary table to its permanent home.

You can display the date in whatever format you choose by setting the Format
property of columns in tables or queries or text boxes in forms and reports.
 
Brendan, I did exactly as you indicated and it worked out perfectly. Thanks!!

Brendan Reynolds said:
Stacey said:
I have a file which I will be uploading into Access on a regular basis.
The
date field is formatted to text which I would like to format into a date
field upon importing into Access. I would also like to rearrange the date
into a regular format, it reads as such 082101 and I would like it to read
01/21/08. Any help is greatly appreciated. Thanks in advance!

Stacey.
Click to expand...


Do I understand correctly that the format used in the CSV file is YYDDMM,
that is to say, a two-digit year followed by a two-digit day followed by a
two-digit month? If so, the following formula would convert it to a date ...

DateSerial(CInt(Left$([TestText],2)),CInt(Right$([TestText],2)),CInt(Mid$([TestText],3,2)))

If you are using the built-in import feature, then the simplest solution
might be to import the data "as is" into a temporary holding table, then use
an append query incorporating the above formula to transfer the data from
the temporary table to its permanent home.

You can display the date in whatever format you choose by setting the Format
property of columns in tables or queries or text boxes in forms and reports.
Click to expand...
 
Back
Top