Transfer from Excel question

[HeyNow]

Hello Guru's,

I've got an append query that transfers data from an excel file into my
tables as needed. The way the excel file is generated must have changed.
It comes from a mini computer via some kind of batch transfer process. I
know NOTHING about that. The wizards of MIS just give me an excel file.
I've got a service request into them, but I KNOW they're gonna say "nope, we
didn't change anything....."

Up to now, the excel files I've always gotten have worked great. Now,
everytime I run my transfer query it gives me a "numeric field overflow"
error. I've traced it to one field that should be a text field. In most
cases, the text field is a combination of letters and numbers. However,
sometimes, it's just all numbers--and that seems to be causing this error.
I've tried forcing the column (in the excel file) to be text (it's normally
what excel calls general) and that makes no difference. If I toss in a
letter into the straight numbers, it then works fine...but as soon as it's
all numbers, I get the error.

Any suggestions? Should this perhaps be something better answered in the
Excel NG (since nothing I do from Access's end will see the column if it's
even got ONE straight number in it)?

TIA
Kohai

 

[Clif McIrvin]

Kohai -

Perhaps the easiest solution is to change your import from one step to
two:

1. Export Excel to .csv format
2. Import the .csv file into Access


The symptom you describe is well-known to people who routinely link to
or import from Excel, and is a direct consequence of Excel's loose data
typing not being compatible with Access' rigid data typing.

For more background, here's a summary of the problem I put together a
while back:
--
Clif


Here is some information I have gleaned over time regarding the #NUM!
error when importing or linking to Excel spreadsheets.

Excel stores cell values as a variant, and regardless of how you format
the entire column you can end up with different variant data types on a
cell by cell basis.

This becomes a nasty problem because the 'transfer spreadsheet' function
that Access uses to either import or link from an Excel worksheet does
not do any data type conversion. If you 'trick' Access into expecting
text data and one of the Excel cells has a number(or date) data type you
will get the #NUM error; conversely if you have everything formatted as
numeric(or date) and Access comes across a text datatype cell you will
get the #NUM error. (Empty cells properly come across as Null in either
case.)

I have also found that a cell containing a single space (in an otherwise
numeric or date column) will link as #Num!.

Threads containing additional discussion:

http://groups.google.com/group/micr...read/809542461417afdf/7440746c9fa31c61?q=#num

http://groups.google.com/group/micr...read/b4b7748816387977/f8eaa8f9c95bc766?q=#num

Barry Gilbert discussing multiple data types in an Excel comumn:
http://groups.google.com/group/micr...roup:microsoft.public.access#a8b5bcd4f77450d9

This post by Mark shows a macro to force Excel cells to text data type
and another to force cells to a numeric type:
http://tinyurl.com/3vt5rm

Van T. Dinh finds the easy way is to modify the Excel file as follows:
* Insert a "calculated" Column next to the MixedColumn
* Link the Excel file to Access, ignore the original MixedColumn and use
the "calculated" Column. All values in this Column will be Text so the
values won't have #NUM entries.
His discussion is at http://tinyurl.com/4vf6uv .

One possible work-around is to use the procedure in this KB article to
force every cell to text type in the Excel sheet:
(815277) - Explains the "Numeric Field Overflow" error message that
occurs when you query a table that is linked to an Excel spreadsheet.
This article provides a workaround to resolve this problem. Requires
basic macro, coding, and interoperability skills.
http://support.microsoft.com/kb/815277/en-us

Avoid DataType Mismatch Errors when Importing Data from an EXCEL File or
when Linking to an EXCEL File (additional discussion of Jet, possible
registry hack)
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#DataTypeErr

From MS Access MVP Roger Carlson:
Because I've had so much trouble with linking Excel files into Access
(previous to Access 2007, you had no control over the datatype), I've
taken to ALWAYS linking them as CSVs. That way I can define the
datatype in the Import Specification. (www.rogersaccesslibrary.com)

 

[kit]

Hello Guru's,

I've got an append query that transfers data from an excel file into my
tables as needed. The way the excel file is generated must have changed.
It comes from a mini computer via some kind of batch transfer process. I
know NOTHING about that. The wizards of MIS just give me an excel file.
I've got a service request into them, but I KNOW they're gonna say "nope,
we didn't change anything....."

Up to now, the excel files I've always gotten have worked great. Now,
everytime I run my transfer query it gives me a "numeric field overflow"
error. I've traced it to one field that should be a text field. In most
cases, the text field is a combination of letters and numbers. However,
sometimes, it's just all numbers--and that seems to be causing this error.
I've tried forcing the column (in the excel file) to be text (it's
normally what excel calls general) and that makes no difference. If I
toss in a letter into the straight numbers, it then works fine...but as
soon as it's all numbers, I get the error.

Any suggestions? Should this perhaps be something better answered in the
Excel NG (since nothing I do from Access's end will see the column if it's
even got ONE straight number in it)?

TIA
Kohai