CSV import to Access 2007 - problem with dates

M

Mike

I have several CSV files that need to be imported on a regular basis
to an Access 2007 application. I have been unable to import the dates
with the date data type. I have been importing them as text. I would
like to know if there is a way to convert them to dates on import or
if I will need to write a query to convert them once they are
imported? Here is a small sample:

locationid bocDate tot_pre_forv_amt tot_curr_forv_amt
day_cust_trans_qty
12966 6/29/2008 16056253.24 16065265.33 1218
17022 6/29/2008 2790246.84 2795972.09 888
17690 6/29/2008 3486209.19 3493413.69 939
18950 6/29/2008 17245565.26 17253991.53 992

Thanks,

Mike
 
P

pietlinden

I have several CSV files that need to be imported on a regular basis
to an Access 2007 application. I have been unable to import the dates
with the date data type. I have been importing them as text. I would
like to know if there is a way to convert them to dates on import or
if I will need to write a query to convert them once they are
imported? Here is a small sample:

locationid bocDate tot_pre_forv_amt tot_curr_forv_amt
day_cust_trans_qty
12966 6/29/2008 16056253.24 16065265.33 1218
17022 6/29/2008 2790246.84 2795972.09 888
17690 6/29/2008 3486209.19 3493413.69 939
18950 6/29/2008 17245565.26 17253991.53 992

Thanks,

Mike
Click to expand...

use an update query and use CDate(bocDate)
 
B

Benjy

The easiest way is to use an import specification. Access 2007 will handle it
fine, as long as the date data contains a separator - in your sample I can
see that a slash is used so it should be fine. You will have a problem is
your date does not contain a separator (eg 06292008) - Access 2007 cannot
currently handle that, although earlier versions all do it OK.

Import specs are very easy to use, just click on the Advanced button after
selecting the file (File>Get External Data>Import) and follow your nose.

I also replied to a thread titled "import text field 20080704 (4th of july
2008) as date access 03" which goes into a bit more detail.

Cheers

Benjy
 
Top