?Type conversion failure when importing excel to access

[Jaylin]

I tried to import an excel table to access, and receive import error report
regarding "Type Conversion failure". The column field in error holds "date
of birth". The error records has no entry ("null" value).

I would much appreciate some advice to resolve the problem

 

[Klatuu]

What is the format of the cells in Excel?
Are there rows in that column in Excel that have no data?
Are you importing into an existing table or a new table?
Each of the above are important considerations when importing from Excel.
Post back with more detail and perhaps we can make a recommendation.

 

[Jaylin]

Thanks a million for your kind assistance and advice

1)The cells are in dd/mm/yy format
2)There are rows in the column that has no data, and they result in the
"type conversion failure"
3)I m importing into a new table

 

[Klatuu]

Just about like I suspected. I would recommend the following:
Rather than import the spreadsheet, link to it. Then create a table with
the format you need for the fields. Create an append query that will read
the Excel table into the table you created. So do this in the following
order:

Delete the data in the Access Table
Link to the Excel sheet
Run the append query to copy the data in
Delete the link to the Excel sheet.

 

[Jaylin]

Thanks a zillion Access Genius. I followed your guidance and import the data
successfully.

 

[Klatuu]

Glad to help

Thanks a zillion Access Genius. I followed your guidance and import the data
successfully.

 

[Sandy]

Your link suggestion worked for the most point, some fields come in with
#NUM! Some I can correct by changing the SS field to convert to number but
others I'm not sure why.

Any clue

 

[Klatuu]

Are all the rows in the column #NUM or just some of them? What is happening
here, I can't be sure of. Have you opened the spreadsheet by itself to see
if there is a value or it is #NUM in the spreadsheet.

In Excel, the #NUM indicates either an error in a formula or a number that
is too large or too small for Excel to display.

 

[Sandy]

No just some, the spreadsheet did have error I fixed them which fixed some of
the table items but not all.
The table is linked, how do I remove the link?
If I use an append query will I need to delete the data and run the query
everytime I open the database?

 

[Sandy]

no just certain cells. there are 4 columns that it happens in but not every
cell shows the error. I keep looking at the spread sheet to see what is
different but I can't find anything.