CSV text into date format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stacey - 23 Jan 2008 01:29 GMT

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.

Reply to this Message

Brendan Reynolds - 23 Jan 2008 11:26 GMT

>I have a file which I will be uploading into Access on a regular basis.
>The
[quoted text clipped - 4 lines]
>
> Stacey.

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.

Signature

Brendan Reynolds

Reply to this Message

Stacey - 23 Jan 2008 15:34 GMT

Brendan, I did exactly as you indicated and it worked out perfectly. Thanks!!

> >I have a file which I will be uploading into Access on a regular basis.
> >The
[quoted text clipped - 18 lines]
> 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.

Reply to this Message