Importing Excel Spreadsheet with empty rows

[JoeyB_99]

I am using Access 2007 and we are importing an Excel spreadsheet that is received from our customer on a regular basis.

When I open the Access table I see over 9,900 empty rows at the start. Following this is the actual data from the spreadsheet.

Why is this? What is wrong here? Is there an import setting somewhere that would eliminate all these blank rows?

 

[John Big Booty]

Are you importing the spread sheets directly into your live data tables?

If so I would suggest importing your data into a temporary table, where you can massage the data, a little, prior to transferring it to your live data tables.

The example posted here might be of assistance.

 

[Pat Hartman]

Your client needs to be instructed as to how to "clear" data if they are going to reuse a spreadsheet. Users have a tendency to select the cells and press delete thinking that this deletes the data. It doesn't. It simply empties the cells but Excel remembers there was data there. To actually delete the rows, they need to select the rows, right click, and choose delete rows.

If you use an append query, you can set the validation rules to require some data values and so empty rows will be discarded. Otherwise, just delete them after importing.

 

[gemma-the-husky]

even if there are empty rows, you can just import the spreadsheet, and then find a candidate "empty" column to use to tidy the data up

so

docmd.transferspreadsheet
docmd.runsql "delete * from tmptable where somecolumn is null"

etc etc

which is all why it makes sense to import first to a temporary table, and then run validation tests etc on the temporary table.