Importing Excel – Number as Text

Topic

New Reply

I am trying to import an Excel file that contains two columns. The second column is all numeric, and is not giving me any trouble, but the first is a mix of all-numeric and alpha-numeric fields. Access imports this column as a number field, erroring on all other field types being imported. How do I force Access to import this column into a text field? I’ve tried linking to the file, and the macro command TransferSpreadsheet as well.

Reply | Quote

Replies

Hi David,
It seems that Access looks at the top few values in the Excel column to determine what the data type is for the field. This might be a case where you need to use ADO with ODBC to transfer the data.

If it is only a one-time thing, you could possibly sort your Excel columns so that a text value is in the row beneath the field name. That way Access would be more likely to deem that field a text field rather than numeric.

HTH

Reply | Quote

The simplest method is to create the target table first and make the target field a text field. I’ve never had any problem importing numbers into a text field, only the other way around. The problem is really an Excel problem, since it refuses to recognize numbers as text unless you put a single quote at the first position in the cell.

Reply | Quote

Chiming in with my , if it’s a one time deal, I’ve found the easiest thing to do is insert a row directly under the column headings in the Excel file and fill the columns with the value “ZZZ” for that row; it forces Access to recognize the columns as text, and the record is easily recognized and deleted once it’s in Access.

If anyone from Redmond is reading, though the functionality of Access guessing at the data type in an Excel import is handy, ultimate discretion should be with the user/developer.

Reply | Quote

I also use this approach.
If it sees an text value in the top row of your column, it automatically assigns it a text value as it’s default datatype.
A fairly easy fix for me.

Reply | Quote

From all these good suggestions, this one seems to offer the best solution to me. A query can strip the first “dummy” record easily, and the rest manipulated at will.

As a side thought — does anyone know if MS does monitor these boards?

Reply | Quote

I’ve also had luck by saving the Excel spreadsheet as delimited text, and then importing the delimited text file to Access, because then it will come up with the screen options of assigning (manually) each data type for each column/field. Pribb

Reply | Quote