Import of Data

[Brian]

Sorry for placing this question here but the Import/Export
Data newsgroup was not available.

I am importing a number of csv and txt files. All is
working exactly as planned except for one of the fields
that holds a date, in the format 01/Apr/2003. Don't ask
me why the source is like that; was designed that way
before I started and unfortunately I cannot change it.
When the import finishes, this field comes back as a 'type
conversion' error, even though the import recognizes it as
a Date/Time field.

Is there a trick to getting around this? For input files
where the date is the same, I can simply make the change
pre/post import, without a problem. However, when there
are a number of different dates, this would be very
cumbersome. Many files have over 800K records and are for
an entire month. Would have to do 30 or 31 Find and
Replaces if I don't have to.

Any help would be appreciated. Thanks.

 

[Dennis Schmidt]

Hi Brian,

My name is Dennis Schmidt. Thank you for using the Microsoft Newsgroups.

Create your table structure first and specify that this field is text.
Then import your data and append it to the table. This will get the values
into the table.

You can then use an update query to change the format of the data (using
functions like Left(), Right() and Mid()). Once the data has been
corrected, you will be able to change the data type of the field back to
Date.

I hope this helps! If you have additional questions on this topic, please
reply to this posting.

Need quick answers to questions like these? The Microsoft Knowledge Base
provides a wealth of information that you can use to troubleshoot a problem
or answer a question! It's located at
http://support.microsoft.com/support/c.asp?M=F>.

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.

Regards,
Dennis Schmidt
Microsoft Support