|
Home > Archive > MS Access Database with External Data > April 2006 > Importing to Access from Excel adds tons of blank records
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Importing to Access from Excel adds tons of blank records
|
|
|
| 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??
| |
| david epsom dot com dot au 2006-03-30, 3:27 am |
| 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)
"Jeff" <Jeff@discussions.microsoft.com> wrote in message
news:45C6E87A-13EF-41CA-B07C- 4931BF0B2FB8@microso
ft.com...
>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??
| |
| John Nurick 2006-03-30, 3:27 am |
| 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.
On Wed, 29 Mar 2006 14:56:04 -0800, Jeff
<Jeff@discussions.microsoft.com> wrote:
>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??
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
| |
| Taffy B 2006-04-02, 7:29 am |
| 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
"Jeff" wrote:
> 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??
| |
|
| Also, there is a registry setting that may affect this behaviour:
HKLM\microsoft\jet\4
. 0\engines\excel\appe
ndblankrows
(david)
"david epsom dot com dot au" < david@epsomdotcomdot
au> wrote in message
news:eq%23I737UGHA.5288@TK2MSFTNGP14.phx.gbl...
> 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)
>
> "Jeff" <Jeff@discussions.microsoft.com> wrote in message
> news:45C6E87A-13EF-41CA-B07C- 4931BF0B2FB8@microso
ft.com...
creates[color=darkre
d]
>
>
|
|
|
|
|