Error on importing MS Excel file

[Lou_Reed]

When I tried to import an Excel into Access into I got the following error. See attached


pdf file.

It says that the primary key filed cannot contain a null value.

My Excel file right now just consists of headers and nothing else. All fields are empty, of course the primary filed contains a null value.

Sometimes it does not seem to care. It says to cancel the import and deal with the primary field having null values. But if I go on, it seems to not mind at least it has not complained yet.

What is going on here?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[PLangley]

I have found that if you had data in a row in the spreadsheet and you delete the data, but not the row, when you import into Access it treats the row as a data row with no values. This would create the error of a null value in a primary key if you defined one. To fix this, open the spreadsheet and highlight a dozen or so rows beneath your header row and right-click | Delete. Otherwise, don't be concerned over the error in that instance. Access excludes the row from import, but imports all other rows that do not have issues. Look in Access tables after the import and you'll probably see a table named Production Tracker_ImportErrors. It will show all rows or values not imported due to errors.



Best regards,
Pat Langley

[Jen0dorf]

Hi

I've done a lot of importing excel into access. So here are my thoughts

Is the table you are importing into in a relationship with another table? If so make sure you are not populating a field that is linked the the other access table using a foreign key.

Is the formatting of your access table the same as your excel spreadsheet ie text or number make sure they are the same either alter them in excell or when importing into access

If your access table field names are the same as column names in excel and in row a make sure you tick the box first row contains header names.

HTH

Ian

[Lou_Reed]

It says, in any import Excel Sreadsheet to MS Access Access, instructions to check a box on the dialogue box window when the first row of an Excel spreadsheet that you about to import is really column titles etc. That is fine and that is the way it should be and it works.

However, what if the lines take up more than just one row? What if the titles take up several rows say up to four rows. What do we do then?

Any help appreciated. Thanks in advance.

Respectfully,

Lou_Reed

[John_G]

However, what if the lines take up more than just one row? What if the titles take up several rows say up to four rows. What do we do then?

If by that you mean multiple rows of text within the same spreadsheet cell, then it should import with no issues. If your spreadsheet has merged cells (columns or rows), then weird things can and do happen. Let us know if that is the case.

[Lou_Reed]

I understand what you say, but the button says first row, not many rows for header titles, not first rows. How does it distinguish between so many rows and one row?

Respectfully,

Lou Reed

[John_G]

Sorry - I misunderstood you question. I have had that issue, and what I usually did was to let Access import the additional "header" rows as data, and then just deleted them in Access after the import. As I recall it didn't seem to adversely affect the (real) data, and Access doesn't use header rows anyway, other than to create field names.