importing xls with dates

[aa]

I am trying to import an xls file into Access 2000
the xls has a colum containing dates in format DD.MM.YYYY

Yet the colume keeps getting imported as text.
During importation, the Access' Import Spreadsheet Wizard shows Field
Options including Data Type.
For this date field it shows DataType "text" which is gray and cannot be
changed
But the data in the filed are transformed into anothe Date format "DD Month
YYYY" which indicated that Access recognised it as date, yet formated as
text.

I attempted to change this field fromat from text to date in the Design view
of the imported table, but message popped up saying that error happens
during conversion and the data of that field will be lost - and it got lost
I did it twice same result.
How shal I get round it?
PS. I removed the first line of the excel sprreadsheet which contained the
names of the fields and saved it. Yet on an attempt to import this changed
spreadsheet the Access' Import Spreadsheet Wizard still shows the
spreadsheet with the first line of filed names - looks like it cached it.
How do I clean the cache?

 

[Guest]

Hi AA,

You have a couple of options:

1.) Fix the dates in Excel first, before attempting to import. To do this,
you can insert a new column and use the Substitute function. The following
example converts 01.07.2007 in cell A2 to 01/07/2007:

=SUBSTITUTE(A2,".","/")

This will still import as a text field, but you can convert it to a
date/time data type.

2.) Import the data into a text field named TextDate. Then use the following
Update query to update the values in a field set as date/time and named
ActualDate:

UPDATE TableName
SET ActualDate = CDate(Replace([TextDate],".","/"));

where TableName is the name of your table.

3.) You can also link to the Excel file, and then run an append query to
append the records to the target table. Using the Replace function is very
easy (available in Access 2000 and later). However, you could also use CDate
(Convert to Date) in combination with the appropriate Left & Mid functions.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[aa]

Thanks, Tom