ADO ODBC connected recordset of Excel file is returning NULL when value is "2 2"

Using Excel 97, MDAC 2.6

Problem:
opening an ODBC connection to an Excel spreadsheet (OLEDB connection files with "cannot find installable ISAM").
Open recordset object and read in data.

In one column, the first 4 rows of data are integer values, the fifth is alpahanumeric ("2 2" to be exact).
When the recordset reads in the 5th row, it set the value of the column to NULL, and sets its vartype to 1 (vbNull or no valid data).
The earlier columns work, and the data type is set to 5 (vbDouble).

How can I get it to read in the column as alphanumeric ?  I have set the connection string to have fg=0 (field guess - I think this is deprecated because it doesnt work), but still no joy.  The ODBC config is to sample 8 rows (but I would rather it didnt sample anything and just read in the raw data).

All help greatly appreciated.

Iain.
LVL 15
colly92002Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nus9259Commented:
I've had this same problem and never found a solution, other than to read each cell in separately.  In MS Access you can specify the data type, but I'm not aware of a way to do it through ODBC.
0
mberumenCommented:
colly.     The the ODBC excel driver uses the first few rows of each column to determine the field type for that column.  (it's a registry setting).  Any values that are not of the same data type as the first colums are thrown away.

If you have control over how the excel file is originaly created then you can format all cells as text before any data is added to it.  But then it is prone to error when somebody pastes values into it.

The only way I've found to fully validate the values is  to execute a macro against the excel file on the BeforeSave worksheet event.

the macro formats the cells as string and adds an apostrophe to the beginning of it

looks something like this

 For Each cell In Selection 'loop through each cell in the selection
     
            temp = cell.Value   'place value in a temporary variable
            cell.Value = ""   'empty the cell
            cell.NumberFormat = "@"  'format cell as text
            cell.Value = "'" & Trim(temp)  'reenter the value in the cell with a text identifier '
       
        Next cell
     

hope this helps
0
colly92002Author Commented:
Thanks the the replies.

I would prefer not to touch the Excel speadsheet, as this comes from another department and I dont have any control over it.  However, I will bear it in mind as a last resort !

Also, as a test, I formated the offending column as "Text", but ASP still thinks the first 4 values are doubles, and the fifth is invalid.
Furthermore, the ODBC connection if set to sample the first 8 rows, so it *should* notice that one of the values is not numeric.

Has anybody got the OLEDB connection to Excel working ?  Perhaps that is the answer (most wierd OBDC probs can be fixed by changing to OLEDB I have found).  For me, the "cannot find installable ISAM" problem has followed me over 2 server and 2 operating systems (NT4 + W2000 Server).
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

mberumenCommented:
Good luck,  I found the Excel drivers to be very unreliable ( and users even more so) lol

My current working implementation has tons of validations checks and the users still manage to do something to the template that makes it hard to read programatically.

I'll listen in to learn more about OLEDB, perhaps that would be the solution to my problems



0
apolloisCommented:
Hi colly92002,

>>>How can I get it to read in the column as alphanumeric ?

I don't think this is going to be possible using ADO.

Think about it.  ADO must assume that all values in a column are of the same type, or else it won't work.

An easy work-around:  Write your own Excel macro to convert all the data in each column to the type you wish it to be.  Before posting the Excel file to the Web site,  run your macro, and save as a new file.   Quick and easy.

A little more complicated, but much better work-around, is to write an Access procuedure to load the Excel data into an Access DB.  You could use automation from Access to open Excel in the backgound and read the data as Excel ranges, converting the data as needed.

Reading Access from ASP is much easier and more reliable than Excel.

Best Regards,
>apollois<
0
colly92002Author Commented:
I like the idea of using Access as a middle tier, but that still to me seems like a lot of work for something that should *should* work.

After all, I am not asking the Excel ODBC driver to guess the type of a field, in fact, I am trying to stop it from doing that !  By default, one would imagine that the contents of each cell would be read in in an alphanumeric format (i.e., no matter what data is in the cell, its read, whether using a Unicode convention, or acsii, or whatever, its read in.).

What does not appear to be working is the sampling of data to determine type.  For example, the following spread sheet works: (1 column, 5 rows)

test
123
is
1 1
fri

because the first cell is set to "test", clearly a string, with a vartype of 8 (vbString) in ASP, the rest of the rows are also read in correctly (including row 2, with is a number), all as vartype 8, and all returning the data in the cell.

However, the follwing fails:

123
test
test 2
1 1
fri

With this data, the first cell returns a NULL, with a vartype of 1 (Null of invalid data), but all the other cells are read in correctly as strings (vartype 8).

To me this looks like a bug.

I am going to try it with old version of the ODBC driver - I believe you used to be able to turn off the "data type sampling" feature, and set everything to a string.


0
colly92002Author Commented:
OK, I've got the OLEDB connection string working thus:

Set objCONN = Server.CreateObject("ADODB.Connection")
strProviderString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strWorkingFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"

objCONN.Open strProviderString

(where strWorkingFile is the path to the .xls file)

Note this is exactly as I have tried before, with more "" and ; in it !

Anyway, I get the same problem ! (but now I dont need a DSN, so I can use different file names).

I though you might be interested in that.
Iain.
     
0
mberumenCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
colly92002Author Commented:
I am going to close this question - points to mberumen because it seems to me that the answer is that the Excel Driver is buggy, and the Microsoft link supplied seems to confirm that (although that document also contains errors !).  

Anyway, I have decided to ditch excel, save files as CSV's, and try using the text driver.

This is a nightmare problem - I have tried so many things, and the results are, frankly, inconsistant.

So, the answer - DO NOT USE THE EXCEL ODBC DRIVERS.

Thanks again for everyone who replied.

Iain.
0
apolloisCommented:
colly92002,

>>> So, the answer - DO NOT USE THE EXCEL ODBC DRIVERS. <<<

This the same as the answer I gave you early on in my post:
I don't think this is going to be possible using ADO.

Also, I provided you with two good work-arounds:
     1.  Excel macros
     2.  Convert to Access DB

Converting to Access is much better than converting to CSV.  You have more control and the OLEDB driver is better/faster.

Best Regards,
>apollois<
0
colly92002Author Commented:
sorry apollois !  I really appreciated your help and I will bear in mind your macros/Access DB work arounds.

Next time I get two equally good answers I will give you the answer !

Like I say, Thank you to everybody who replied.  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.