import excel spreadsheet without empty rows (causing nulls)

[Mitchell_Collen via AccessMonster.com]

Hi.

I am trying to import an excel file into access. The import goes fine or so I
thought. When I opened the table it had all my excel data plus over 9000 null
valued records.So instead of getting a record set number of 200 records, I
got over 9200. I have re imported the table after deleting empty rows (<-it
felt pointless because there was nothing that I was deleting, I just figured
there was something hidden) in the spreadsheet but it did the same thing. Is
there something that attaches to the spreadsheet that causes null records?

Please let me know if you know what has happened.
Thanks, Misty

 

[Roger Carlson]

I contend with this problem every month. I have to import data from an
external system that comes in an Excel spreadsheet. I've tried a number of
approaches, but the simplest I've come up with is to simply delete the null
records after the fact.

Create and save a Delete query something like this:

DELETE * FROM YourTable
WHERE YourField IS NULL;

Of course, you would replace YourTable with the actual name of the table and
YourField with a field in the table which would NOT have a null in it
ordinarily.

Then just run your delete query after you import your table.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Klatuu]

There was something in at least one cell in the last row imported that the
importer saw as data. It could be as simple as someone selected a cell and
hit the space bar.

 

[gllincoln]

Hi Dave,

Exactly, and it's constantly a problem - involves both rows and columns.

If it's a one of process, then if it breaks, I copy the real data range to a
blank new worksheet and import that.

When it's a frequent flyer and I need to get automated - I have resorted to
using an Excel VBA macro to build a csv, sorting the sheet on a required
column desc, testing for a null in the required column - and ending the
export at that row.

In psuedo code -

'if you trust the header to not have any blanks
'you can test for the first blank and get the
'EndofRow dynamically - then your macro
'will work for other sheets, just provide
'the required col - could snag that via an
'inputbox question - converting the
'col name to corresponding numeric index
'or translate the column header label
'to the index if you really want to

EndofRow = 25 '(however many columns of data we have)
RequiredCol = 15 '(or whatever)
r=1
Do while len(Sheets(1).cells(r, RequiredCol).value)>0

for c = 1 to EndOfRow

' we build a row of csv data
'one cell going across the columns at a time
' and write it to file
'can fix nulls or coerce/format in a given col
'by testing the value of c
' and then doing whatever

next c
r=r+1
loop
' close file

' msgbox count of records exported,
' that count will be equal to r-2
' extra points for decent error handling
' set focus to A1 of the sheet
'get out

Gordon

 

[Mitchell_Collen via AccessMonster.com]

Thanks Gordon!
-Misty