Importing an excel spreadsheet problem with column format

[Kate]

I am trying to import an Excel spreadsheet where one of
the columns is both text and numerical. When I import the
file into my database that column reads the first 38 lines
whatever they might be text or numbers and then formats
the entire column the same way. Is there any way to
correct this? I have the field set as text in the
database. This still does not help.

 

[Ken Snell]

Do you want the field to be text? Three possibilities:

1) Add a record as row 1 in the EXCEL spreadsheet. Have nonnumeric text
value in that cell in that first row. Then delete that record from the table
after the data have been imported.

2) Import the data into a temporary table, then use an append query to copy
the data in the correct format into the permanent table. Note that this will
not fix any "dropped" leading zeroes that were in a text value.

3) Write VBA code routine that opens the EXCEL file via Automation, and
which then reads each row one cell at a time and writes the values into the
fields in the table. Not recommended for one-time importing.

 

[Joe Fallon]

Since Excel is not a database, using "mixed" data types in a column causes a
problem when Access has to "guess" which one to use. Access examines the
first 15 rows (or so) and makes a guess. If you want to "force" Access to
guess a text data type then make the first entry in that column a text
value. The reason you get errors is that the first 15 rows are all numeric
and then the text data shows up after Access has already guessed the data
type to be Numeric.

The way to make a text value in your data of numbers is to include a
character!
e.g. 123A