Import Excell Numbers as Text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ken Nicholas - 13 Jul 2005 15:03 GMT

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.

Signature

Ken Nicholas

Reply to this Message

Jeff Boyce - 13 Jul 2005 15:08 GMT

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>

> 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
[quoted text clipped - 4 lines]
> lines are numbers. The last couple of thousand lines are text. The p/n
> field in my data base is text.

Reply to this Message

Ken Nicholas - 13 Jul 2005 19:59 GMT

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.

Signature

Ken Nicholas

> Ken
>
[quoted text clipped - 23 lines]
> > lines are numbers. The last couple of thousand lines are text. The p/n
> > field in my data base is text.

Reply to this Message

Ken Nicholas - 13 Jul 2005 20:01 GMT

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. . .

Signature

Ken Nicholas

> Ken
>
[quoted text clipped - 23 lines]
> > lines are numbers. The last couple of thousand lines are text. The p/n
> > field in my data base is text.

Reply to this Message

Van T. Dinh - 14 Jul 2005 13:23 GMT

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.

Signature

HTH
Van T. Dinh
MVP (Access)

> Is there any way to control the data type when importing Excell
> Spreadsheets?
[quoted text clipped - 9 lines]
> lines are numbers. The last couple of thousand lines are text. The p/n
> field in my data base is text.

Reply to this Message