Help with Identifying import errors (2003)

Topic

New Reply

I am importing some data into Access but keep getting errors, l cannot work out what

Reply | Quote

Replies

Hi again!

The error is possibly on row 243 of the spreadsheet, athough this is approximate. You must have a whole lot of columns in your spreadsheet to get 215 as the error column. Possibly there are cells in the spreadsheet that once held data, but it has now been deleted. Excel and Access often have a dispute over the content of these cells. I suggest you try copying your data into a nice new workbook, only copying those columns and rows that have visible data that you want to keep, and try the import again.

Reply | Quote

I tried to delete row 243 but still got an error. I also tried to copy into a new workbook but still got an error.
Any other suggestions, shall l send you a zipped copy of the file?
Is there any other way you know of l can track down the errors in Excel?

Reply | Quote

Justin

This is is not a problem with Access per se but with your Excel import. You have data in excel spreadsheet that should not be there. You example graphic show F215 to F226 which is column HG to HR of the spreadsheet.

1) Do you have filed names in HG1 through to HR1 or is just empty fields?
2) Which columns in your worksheet have data stored in them. Highlight all the columns that do not have data in them, and then press the delete button. This will clear the cells of any erroneous data
3) After 2, try the import again

Errors like this are caused by erroneous characters like char(10) and char (13) creeping in.

A friendly word of advise. You are having a plethora of problems recently that you have posted about file imports to Access. It really is not that difficult. Could you not go on a short course with your friend jj1234 to get this concept, I am sure there quite a few courses near the University…just a thought.

Reply | Quote

Thanks for your help.

I will remove all duplicate field names and delete all columns with data then try an import again.
In reponse to your points:

1. HG1 thru HR1 is empty fields.
2. I will delete all columns with no data them

You mention erroneous chars like char (10) and char (13). Can you give me an example of what you mean.
Perhaps a screenshot?

Reply | Quote

On removing duplicate field names l get an error, see attachment. Is there something else which is wrong with the spreadsheet?

Reply | Quote

The data now imports correctly , as l changed all the fields in the spreadsheet to text fomat, thanks once again for your help.
This thread can now be closed.

Reply | Quote

Remember that if you have a header row (as seems extremely likely), then the row with the problem may be 246 not 245. I’ve also known Excel to be misleading with these row numbers, and the problem could be a few above or below the stated row.

Jerry mentioned char(10) and char(13). The ascii character set includes characters that are not always visible on screen, such as carriage returns and, obviously, spaces. For more information, try the help file or Microsoft’s KnowledgeBase.

I’ll make this my last reply to this post, I don’t think there’s anything useful I can add. Keep trying, there’s always a solution, always something new to learn.

Reply | Quote