Importing Excel File - Getting 7 extra blank rows each import

[eking002]

Hello everyone.



Have been using Saved Imports to import data from an Excel sheet.

For some reason, and after some database and excel changes, we noticed that 8 records are being imported. 1 as expected, and 7 blank rows. Each time we import.

We have an autonumber ID feild. So ID 1 = the expected row data, ID 2,3,4,5,6,7,8 all have blank/null/defualt data.

The ID numbers are not being duplicated and are sequential, and the rows themselves do not contain any of the import data.


The database changes occured outside of my control, and am unaware of many of the new specifics. I am just looking for some thoughts/ideas on what could be causing it and what needs to be done to fix/prevent this. The Google hasn't been of much help on this.

[JoeM]

Do you have access to the Excel file?
If so, go to that sheet and hit CTRL-END. Does it go to a row 7 past the last row with data?
They may have formatted extra rows in Excel, or cleared some data in those rows without deleting those rows, so Excel & Access thinks the data goes for 7 more rows.
If you can delete those 7 rows in Excel and save the file, that may fix it.

Other options include:
- deleting the blank rows out of your Access table after import
- importing the Excel data to a temporary table in Access, then using an Append query to just move the rows with data to your final destination table
- making at least one of the fields in your Access table required; then when it tries to import a blank row, that whole record should fail and not be imported into your table

[eking002]

Interesting. It takes me to cell AR9.

Not sure how/why, but I imagine there is some invisible data in this cell.
Will give it a try.

Thanks a bunch.

[eking002]

More interesting....

Deleted the rows, CTRLEND still takes me to AR9.
Cleared contents of AR9 and it now takes me to AR58....

So the import process is working fine I guess, Excel is foobar it seems.

Does this mean I will get 58 blank rows now? 64999 blank rows?
ControlEnd has to 'stop' somewhere, how do I know where it ends, is clear or not?




EDIT:

Made a new tab, copied Row 1&2, Control End takes me to the logical endpoint of the last column in Row2.

Sheesh. Thanks again for the help.

[JoeM]

Glad you got it worked out.
Sometimes when you delete rows in Excel, you need to then re-save file before it will recognize the end row with CTRL-END.