Linking To .XLS: Some Dates = "#Num!" ??

[PeteCresswell]

I'm linking to a .XLS that contains, among other things, an
"IssueDate" column.

Looking directly at the spreadsheet, dates look a-ok and the column is
formatted "Date".

Looking at the same sheet through an MS Access link, most of the dates
are OK, but some are rendered as "#Num!".

The link has DataType=Text.

I tried typing a perfectly good date into one of the problem rows - on
the sheet - but it still comes through the link as "#Num!".

Most come through OK, but some come through as "#Num!".

If they all came through one way or the other it wouldn't be so
mystifying.

I'm totally baffled at this point.

Can anybody shed some light?

 

[PeteCresswell]

Looking at the same sheet through an MS Access link, most of the dates
are OK, but some are rendered as "#Num!".

 

[Roger Carlson]

Just for grins, save the XLS sheet as a CSV file and link to that. See what
comes up.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[PeteCresswell]

I'm totally baffled at this point.

I took this one over to an Exel NG.

Reason: When I deleted a bunch of rows from the sheet, a problem row
suddenly rendered the date ok.

Sounds like something with the absolute cell to me.... So I took it
to microsoft.public.excel..

 

[PeteCresswell]

Just for grins, save the XLS sheet as a CSV file and link to that.  Seewhat
comes up.

It carved the bad guys down to only six rows (down from 115) out of
9,863.

The linking process also offered me the option to control data types.

Seems to me like I should be linking to .CSV instead of .XLS all the
time - as a matter of SOP.

Does this support the notion of something goofy in the cells?

 

[PeteCresswell]

It carved the bad guys down to only six rows (down from 115) out of
9,863.

But now I'm getting instances of some cells in some rows appearing to
shift a column.

e.g. we have RatingMoodyOutlook and State

For some rows, State is coming up with values like "/*S" and "POS" -
which appear tb moody outlook ratings. It's like something in one of
the columns to the left is pushing stuff to the right - maybe some
kind of faux delimiter?

 

[PeteCresswell]

For some rows, State is coming up with values like "/*S" and "POS" -
which appear tb moody outlook ratings.   It's like something in one of
the columns to the left is pushing stuff to the right - maybe some
kind of faux delimiter?

Yeah... that's it: unbalanced parens in columns to the left.....

I guess I can stop talking to myself now..... -)

 

[Roger Carlson]

Yes, I think so. There may be something there you can't see that's causing
the problem.

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.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Just for grins, save the XLS sheet as a CSV file and link to that. See
what
comes up.

It carved the bad guys down to only six rows (down from 115) out of
9,863.

The linking process also offered me the option to control data types.

Seems to me like I should be linking to .CSV instead of .XLS all the
time - as a matter of SOP.

Does this support the notion of something goofy in the cells?

 

[Clif McIrvin]

I'm totally baffled at this point.

I took this one over to an Exel NG.

Reason: When I deleted a bunch of rows from the sheet, a problem row
suddenly rendered the date ok.

Sounds like something with the absolute cell to me.... So I took it
to microsoft.public.excel..


---------

Pete -- this took me longer than I expected, because the data I thought
I remembered compiling I couldn't find when I wanted it :-( .

At any rate, here is some information I've gathered on the troublesome
issue of #Num! errors:

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)

 

[PeteCresswell]

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.

That was quite appealing to me.

But now another "gotcha" has emerged. With .XLS, there's no problem
with SecurityName="CONOCO-PHILLIPS, INC.". but when linking to .CSV,
the comma forces a new column - pushing subsequent data to the right.

My first thought is that I don't know enough to save a proper .CSV
file..... maybe some option to enclose everything in quotes....

 

[PeteCresswell]

Pete -- this took me longer than I expected, because the data I thought
I remembered compiling I couldn't find when I wanted it :-( .

(at least a thousand lines snipped)


Wow.... I'd call that Above And Beyond The Call Of Duty.

What do I owe you? Sheesh!

Thanks.

I'm wading thorugh it right now..... with, of course, a pack of half-
crazy users breathing down my neck....

 

[Clif McIrvin]

Pete -- this took me longer than I expected, because the data I
thought
I remembered compiling I couldn't find when I wanted it :-( .

(at least a thousand lines snipped)

Wow.... I'd call that Above And Beyond The Call Of Duty.

What do I owe you? Sheesh!

Don't worry about it ... some of what I know about Access I gleaned from

Thanks.

I'm wading thorugh it right now..... with, of course, a pack of half-
crazy users breathing down my neck....

At least I have the luxury (curse?) of working on my own ....

This #NUM error is one of the first things I ran into way back when I
first began getting acquainted with Access and Excel ... and what I
pulled together for you I had gathered over several months, so the
pieces of it were lying about here and there.

*Someday* the convoluted spreadsheet that is giving me all these fits is
going to become a set of related Access tables ... so once I got far
enough along to live with it I quit giving it time - other than keeping
my eye out for others having similar trouble.

 

[PeteCresswell]

I'm wading thorugh it right now.....

I went with the little routine to force everything in the current
selection to text:

Sub AddSpace()
Dim cell As Object
Dim k As Long

For Each cell In Selection
cell.Value = " " & cell.Value
cell.Value = Right(cell.Value, Len(cell.Value) - 1)
k = k + 1
Next

MsgBox k & " Cells updated", vbInformation, "Done"
End Sub

 

[Clif McIrvin]

I went with the little routine to force everything in the current
selection to text:

Nice and straightforward. Text is a pretty useful data type

 

[Ken Snell MVP]

This article might be of some assistance:

Avoid DataType Mismatch Errors when Importing Data from an EXCEL File or
when Linking to an EXCEL File
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#DataTypeErr