Importing an Excel File

[ttp]

I have an excel file which contains a column (field) with some records that
are only numbers and some that have letters in it. I have tried to import
and link the file; but the field is coming into Access as a number field.
Thus, I am not receiving all the data. I have tried to reformat the column
in Excel using format cells and changing to a text field; but it is still not
importing. When I use the import wizard, I can't change the data type field
and it shows double. How can I imodify the excel file so that I can import
this excel file.

 

[Ken Snell \(MVP\)]

The error that you're seeing is because Jet (ACCESS) sees only number values
in the first 8 - 25 rows of data in the EXCEL sheet, even though you have
formatted the EXCEL column as Text. What ACCESS and Jet are doing is
assuming that the "text" data actually are numeric data, and thus all your
non-numeric text strings are "not matching" to a numeric data type. This
causes the "#Num!" error.

One way to "fix" this problem is to insert a ' (apostrophe) character at the
beginning of each cell's value for that column in the EXCEL file. That
should let Jet
(ACCESS) treat that column's values as text and not number.

Or insert a dummy row of data as the first row, with nonnumeric characters
in the cell in that column. That should let Jet (ACCESS) treat that column's
values as text and not number.

It's possible to force Jet to scan all the rows and not guess the data type
based on just the first few rows. See this article for information about the
registry key (see TypeGuessRows and MaxScanRows information):
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/