Importing Many Many blank records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill Sturdevant - 02 Aug 2004 03:36 GMT

I have a routine that imports data from an Excel
spreadsheet which has relatively fiew records. When the
export runs, I get something like 65,000+ records, 99.999%
of which have null values.

I know I can quickly delete the null records, but the
export process takes a while because of them. Is there
any way I can do the export and avoid importing those
records?

Reply to this Message

John Nurick - 02 Aug 2004 07:17 GMT

Hi Bill,

If you specify a worksheet when importing from , the import routine
attempts to import the entire UsedRange of the worksheet in question.
This is (very roughly speaking) the rectangular range that encompasses
every cell in the sheet that has held data or is formatted with anything
other than the default "Normal" cell format for that sheet.

I feel the best way round this is to define a named range that covers
just your actual data and import the range rather than the worksheet.
Alternatively you could do Edit|Clear|All on all the cells except your
table, or delete (Edit|Delete) all the columns to the right of the table
and all the rows below it.

>I have a routine that imports data from an Excel
>spreadsheet which has relatively fiew records. When the
[quoted text clipped - 5 lines]
>any way I can do the export and avoid importing those
>records?

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

Jamie Collins - 02 Aug 2004 08:31 GMT

> I have a routine that imports data from an Excel
> spreadsheet which has relatively fiew records. When the
> export runs, I get something like 65,000+ records, 99.999%
> of which have null values.

Are you using a query? Sounds like you should be so you can test for
null values e.g.

SELECT
MyKeyCol, MyDataCol
INTO
MyImportTable
FROM
[Excel 8.0;Database=C:\MyWorkbook.xls].[MySheet$]
WHERE
MyKeyCol IS NOT NULL
;

Jamie.

--

Reply to this Message