Importing to Access from Excel adds tons of blank records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am importing a flat file / table of data from Excel 2003 into Access 2003.
The table is about 7,000 rows long and I double checked, all the rows below
the data are completely blank, no formatting no nothing.
When I import this table into Access, it imports all the data but creates a
total of 32,000 records with records 7001 throu 32,000 being blank??
 
Yes, you can have blank rows in tables or spreadsheets.

If you don't want to import the blank rows, either
delete them from the spreadsheet, or specify a range
in your import specification.

(david)
 
Hi Jeff,

Probably this will fix it:

Select all the rows below your data and delete them (delete the actual
rows, don't just clear their contents). The total number of rows in the
worksheet won't change, but Excel will no longer think that the empty
rows contain data.
 
Hi Jeff,
Further to the previous answers to your problem, the easiest way would be to
"Name" the range in the spreadsheet - select/highlight all the relevant
cells, click in the "Name Box" directly above Column A (normally shows the
reference of the cell you are curerently in), type a relevant name and press
the enter key to finish the naming process. When importing the data from
within Access, on the first page of the Import Wizard choose the second
option to "Show Named Ranges" instead of "Show Worksheets" and continue
working through the Wizard - this will just bring in the correct amount of
data without any blank or empty rows.

Taffy B
 
Also, there is a registry setting that may affect this behaviour:

HKLM\microsoft\jet\4.0\engines\excel\appendblankrows

(david)
 
Back
Top