Linked Excel file imports wrong data type column

Question

I am using a linked Excel file in an Access db  (2010). There is an alph-numeric column called Item Number. When I view the file is Access, #Num! shows up on every field not containing a number. Why is access assuming I want this data type to be a number? I can't seem to be abel to change the data type.

The spreadsheet is a copy of a monthly report from another department. It worked fine for a year, and now this months report doesn't work.

Answer 1

Hi Michael!,

The definition with TypeGuessRows may help clear up the confusion.

"The number of rows to be checked for the data type. The data type is determined given the maximum number of kinds of data found. If there is a tie, the data type is determined in the following order: Number, Currency, Date, Text, Boolean. If data is encountered that does not match the data type guessed for the column, it is returned as a Null value. On import, if a column has mixed data types, the entire column will be cast according to the ImportMixedTypes setting.

The default number of rows to be checked is 8. Values are of type REG_DWORD."

http://msdn.microsoft.com/en-us/library/bb177610(v=office.12).aspx

In summary, the TypeGuessRows will scan the first 8 records of your excel file. For creating linked tables; if the majority of those 8 records contain numbers, the number datatype will be used. However, if you were importing the Excel file and you had the ImportMixedTypes registry key set to Text; then if any of the first 8 values were Text, the column would be imported as Text.

Regards,

Dennis

Answer 2

No, this doesn't work. I put 'Text" under each header that is should be alpha-numeric. I still get #Num!.

Access has decided that this data type should be a number and doesn't care what I think.

I would also prefer not to have to edit the monthly excel report. It is generated by another dept and is usually read-only. I am testing with an editable copy.

I have seen this problem in 2003, but I thought they fixed it.

Answer 3

One way is to put a phony record in the firxt row under the column names and have alpha characters in any column that will be alpha-numeric.  You can have something like "ZZZZZZZZ" so as to omit the record in your query.