Solved Importing from Excel into a table (1 Viewer)

[SwampyNZ]

Hi

I have a table that has a PK that is an auto number (Contact_ID)

I exported the table, added data from another source that I need to bring into the table, and tried to reimport it. I populated the Contact_ID starting with the next sequential number.

I get an error message that says "Null value in AutoNumber field" for the last record in the spreadsheet (row 94). I have checked, it is the same format as all of the other rows.

I tried deleting that row, and I end up with the same result on row 93. It says there is an error on that line only, but it does not insert the other 90+ lines.

The format of the field in Access is a Long Integer, General number. In Excel it is configured to Number.

 

[The_Doc_Man]

You COULD try to link the spreadsheet as though it were a table, do a JOIN of the original table (that still has the original PK values) with the spreadsheet, and do an update from the spreadsheet to the (modified) original table that now contains the fields it needs to accommodate the extra data. Since it would be a JOIN, a row with a null value won't participate.

 

[SwampyNZ]

Thanks, I have not done that before, but happy to have a go!

 

[arnelgp]

make sure to check First the Excel worksheet for Null values and duplicates before Inserting to your table..

 

[SwampyNZ]

Thanks. There are none. It's actually the last record that reports the issue.

 

[ebs17]

Import from Excel - how exactly does this take place? There are many variations, and if you want to talk about the same process, it would have to be clearly described.

A possible problem: With a standard import using TransferSpreadsheet, the entire table is used. The whole table is UsedRange in the worksheet. Especially with manual post-processing, this UsedRange can be larger than the actual lines of content. So you end up in the position of importing a few lines of nothing.

 

[SwampyNZ]

Import from Excel - how exactly does this take place? There are many variations, and if you want to talk about the same process, it would have to be clearly described.

A possible problem: With a standard import using TransferSpreadsheet, the entire table is used. The whole table is UsedRange in the worksheet. Especially with manual post-processing, this UsedRange can be larger than the actual lines of content. So you end up in the position of importing a few lines of nothing.

Hi,

I have defined a range (import_range), and reference the range. The error message refers to line 94, which in the excel spreadsheet is the last line with data. However, it is using row 1 as a header, which mean rows of data it would actually be the next line, but that is not included in input_range.

 

[SwampyNZ]

Problem solved, it was not actually the autonumber field. It was several other fields that were set to mandatory that were not in the input sheet.

Weird message!!!

Thanks for the help all.