Import errors w/dates

[Guest]

I am trying to import a large (over 14K records) Excel worksheet into an
Access database. The Excel worksheet was originally generated from an Oracle
database. My problem is that when I import it, I get data conversion errors
for two of the date fields. All the dates that I typed in myself in Excel
are imported, but all the dates that were populated by the original Oracle
query are now blank. I have tried to formatting the date fields as Dates in
Excel before importing them. I have saved the table format, changed the data
type to text, and import the data into an existing table, and none of it has
worked. Access won't let me change the data type when I import it. It would
be ok if it were formatted as text because I'm not going to run queries based
on the dates, but I still need for them to be there.

Any suggestions are welcome.

 

[Jeff Boyce]

I'm not entirely clear on what is and isn't working.

Can you create an Access table with a text field and import the date data
into that text field?

Once you have a "copy" of the Excel data, you can create as many queries as
it takes to parse the (probably "flat") Excel data into well-normalized
Access tables. In the process, you can use functions that convert (like
CDate()) to coerce the values you imported into their proper data types.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I can create an Access table and make all the field text, but when I try and
paste the data into table everything come in fine except for those date
fields. They are just blank. I've imported a lot of data in my time, and
I've tried every workaround that I can think of, but nothing's worked.

 

[Jeff Boyce]

Aha! The key word might be "paste"...

If you are using "cut & paste", try something else. Instead, open Access,
use File | Get External Data | Import ...

Point the data from the Excel file to the table.

Regards

Jeff Boyce
Microsoft Office/Access MVP