Import from Excel - Text vs Numbers

[JeffH]

I'm importing a spreadsheet from Excel. My problem is that the column of
numbers always imports as text. In using the import wizard it does not allow
me to check the data type. It is greyed out with the setting as "text".

How do I import numbers?

Note that once imported, I have changed to text to numbers but all the
numbers loose their precision.

 

[Wayne-I-M]

Hi

Import the data into a temp table - run this through a query and use val
function (or change the format) in the query. Use the query to either update
or append.

HTH

 

[JeffH]

What val function would I use? What is the syntax of the function?

 

[Paul Shapiro]

Try formatting the Excel column with a numeric format. In Excel, select the
column that contains the numbers. On the Format menu, choose Cell and
specify a numeric format for the column. I haven't tested this, but I have
formatted cells as text to force a column to import as text, for example, a
zip code. I believe Access assigns the data type based on the data type it
infers for the first row.

You could also create your table first, specifying the data types you want.
Then import the spreadsheet into the existing table.

 

[Wayne-I-M]

Hi Sorry for not getting back to your sooner

try this link

http://msdn2.microsoft.com/en-us/library/Aa263400(VS.60).aspx


hope this helps

 

[cram]

If this happens, this is what I do.
I import "as such " the excel table. An automatic table design is made by
Access where numberic field have eventualy a txt-format. Delete all records
and change the format(s) in the empty table as you want them (number (fixed,
double..), text, memo....) including length of the field (excel imports tham
all as 255 text-length!!).
make an append-query with your "empty" access-shell and add the excel table
to append. If headers in excel have same denomination as fieldnames in
access, normaly corresponding field will find each other. I always check on
before hand if column-denominations in excel are compatible with access
fieldnames (with preference no blanks, no "-"...).
So you have immediately an table that suits your needs and will be
"exploitable" without any difficulty
Good luck