Error when Importing – Access 2003

Topic

New Reply

I am trying to import an excel spreadsheet. The data looks good. I have checked several times but I must be missing something. It goes through the entire import dialog box but when I click on Finished, I get a blanket statement about there was an error and the file was not imported. Nothing else. Not description of any problems…

So, what could be the problem???

Reply | Quote

Replies

This can occur if there is a column that contains mostly number values but a few text values, or if there is a column with an invalid field name in the top row.

Reply | Quote

Hans,
I have checked the data. It looks good and headings are appropriate. I can link it and it works.
There are a few cells without data…could this be an issue?

Reply | Quote

There is one thing I noticed, when I link all the fields do not come it. I am not sure why…this might be the reason that the import is not working.
The 2 columns contain dollar amounts that were exported from another database to excel then the spreadsheet was modified and now they want it back into the database. I know…confusing but this person likes excel better and well….it works.

Reply | Quote

Are you importing into a new table or an existing one? For some strange reason Access will happily let things through on a first import into a new table, but if you clear that table and try to import into it again the data will be rejected!

Things to watch for:
Column headings ending in invalid characters such as ‘.’. Access will drop these when creating a field first time but will reject subsequent imports because the column heading doesn’t match the field heading
Data type mismatch – Access identifies a field as numeric using the first few rows but later data includes text
Cells containing zero length strings if Allow Zero Length is set to No in the Access table (mysteriously the first time you import Access will bring in the data happily, but if you check the field properties you find Allow Zero Length is set to No. Subsequent imports fail precisely because this property is set to No). Genuinely empty cells will import OK and become Nulls.

Reply | Quote

I have had Access to bomb out in the past however it would bring in some data and then give me a table with the errors such as the data types change. I am getting nothing except a dialog box telling me that the file did not import.

I am creating a new table.
I have checked the data and even re-formatted the columns.
I have removed a few columns.

It is real puzzling…

Reply | Quote

Have you try to remove some (all) rows to see if you can link or import ?
Anny chance to see the Excel file or is it confidential data ?

Reply | Quote

I have been playing around with the file since 7:30AM EST….I could have entered the information by now…

I have deleted fields, rows…etc. It bothers me that I can not find the problem.

The information is confidential. I can try and micky mouse some of the data and see if anyone gets the same problem.

Give me a few and I will attach a file.

Reply | Quote

Here is a copy of the spreadsheet.

Reply | Quote

Access has a problem with at least one of the field names. If I change the column headings in row 1 to F1, F2, …, F19, the sheet imports into Access without a hitch.

Reply | Quote

Here is a copy of the spreadsheet.

Four field have a space as first letter. That’s the problem
Fields : AMT CHG, AMT PD, Totchgs and Totpd
Remove the space and you will be able to import

Reply | Quote

To be more precise: several of the column headers (those in columns H, I, J and K) have a leading space. This causes Access to choke. If you remove the leading spaces from these column headers, the import will be OK.

(Added: Sorry, Francois, didn’t see your reply)

Reply | Quote

Thanks….I feel like an idiot!
You guys are great!

Reply | Quote

One trick I’ve used when encountering this problem is to link to the Excel file so it is treated as a table, and then create an append query to append the data to an existing table where the data types are all set to Text. Most likely causes are currency fields with funny data, or date fields that Excel has converted to “Errors” because it thinks the date value is invalid.

Reply | Quote