Importing from Excel

[Guest]

I've got an Excel workbook with three sheets that I'm trying to import into
Access. The excel file contains data in column 1 that should be imported as
Text although it contains numbers ----- I need to have 8 characters so a
leading 0 in some cases. Column A is formatted as Text on all three sheets.

The Problem: Sheet 2 imports without a problem. Sheets 1 and 3 import with
errors on column 1. Access appears to import the first column on sheets 1
and 3 as numbers and sheet 2 as text. Sheets 1 and 3 have the field blank
where there are only 7 characters.

What am I doing wrong?

 

[Ken Snell [MVP]]

Jet database engine does not look at all the data in the EXCEL spreadsheet's
columns, default is the first 8 or 25 rows (depending upon your registry
settings).

There is no easy way to tell Jet the specific format, so you will either
need to
move some rows with nonnumeric data in the text columns to the first or
second row, or else preface all cells in the text-formatted column with '
characters so that Jet will know those values are text and not numeric.

If you want to try a more complex way, see
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

 

[Charlie]

I've got an Excel workbook with three sheets that I'm trying to import into
Access. The excel file contains data in column 1 that should be imported as
Text although it contains numbers ----- I need to have 8 characters so a
leading 0 in some cases. Column A is formatted as Text on all three sheets.

The Problem: Sheet 2 imports without a problem. Sheets 1 and 3 import with
errors on column 1. Access appears to import the first column on sheets 1
and 3 as numbers and sheet 2 as text. Sheets 1 and 3 have the field blank
where there are only 7 characters.

What am I doing wrong?

You may not be doing anything wrong, but what I've done in a similar
situation is to insert a dummy first row in Excel, and in each column
of that row, insert characters or numbers as needed so that Access
interprets the column correctly. so for a text field, make the first
cell obviously text only (no numbers at all), and so on. This usually
results in the columns being interpreted correctly then.