Difficulty importing excel data

[chris-evans]

I am working with Access & Excel 2000 and am trying to import some data to a table from a spreadsheet. I have other files that have imported fine but there is one file that I am having difficulty with.

Access keeps saying that there was an error importing the file and that the data was not imported. I have tried copying and pasting the data into different files, reformatting the data but nothing seems to work.

Does anyone have any ideas what could be causing this problem? Any guidance would be greatly appreciated.

Thanks

Chris

 

[Dugantrain]

Without actually seeing the data, my guess would be that you're having issues with Data Types. What Access does with Excel is make guesses of the data types of each field by looking at the first 10 or so rows of data. If you had a field where the first 10 rows were numbers, but it was actually supposed to be a text field, then any text in that column will be rejected. A brutish way to work-around this is to add a row between the column heading row and the first data row, write an "X" in each field, and then just import everything as text into a temporary import table. Once it's in the database, you can delete your "X" row, and then write an Append Query to Append the import table records to your final table, using handy query tools like Iif statements to validate your data and conversion functions (CDate, CStr, etc.) to get your data in the format that it should be in.