Import Excell Numbers as Text

[Ken Nicholas]

Is there any way to control the data type when importing Excell Spreadsheets?
I import part numbers from several different suppliers. Some of their p/n's
are text, some are numeric most are mixed. I wish to link spreadsheets from
a supplier and view the p/n field as text. I then run update queries to
change prices, add new products, delete obsolete, etc. But, the p/n field,
when linked or imported comes up as number because the first several thousand
lines are numbers. The last couple of thousand lines are text. The p/n
field in my data base is text.

 

[Jeff Boyce]

Ken

Consider using an "import" table and a "permanent" table. Your import table
will be what you have in Excel. Your permanent table will be defined as
'text' for the field in question.

Create/modify a query that converts the p/n field from the import table into
string (CStr() function) values for your permanent table.

Good Luck

Jeff Boyce
<Access MVP>

 

[Ken Nicholas]

Thanks for the suggestion but it didn't work.

As I said, some of the p/n's are text in the excell spreadsheet. When the
spreadsheet is imported, these get marked as err# and the cell contents do
not import because that field is looking for numbers. So the "import" table
has errors instead of part numbers.

 

[Ken Nicholas]

This leads me to another question -- do I have to link an excell table to use
it in a query. If I do not, I may be able to run an Access query that makes
a table, bringing the data in whole. . .

 

[Van T. Dinh]

For a mixed Column in Excel, I normally create a "calculated" Column in the
spreadsheet using the Excel Spreadsheet function like:

= Text([CellInMixedColumnAnsSameRow], "General")

This converts all values to Text regardless of the data type in the mixed
Column.

When I import, I skip the mixed Column and import the "calculated" Column.