?
Solved

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

Posted on 2003-03-13
11
Medium Priority
?
757 Views
Last Modified: 2012-05-04
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.
0
Comment
Question by:colly92002
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 1

Expert Comment

by:nus9259
ID: 8128977
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
 
LVL 4

Expert Comment

by:mberumen
ID: 8129331
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
 
LVL 15

Author Comment

by:colly92002
ID: 8129472
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:mberumen
ID: 8129848
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
 
LVL 10

Expert Comment

by:apollois
ID: 8130790
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
 
LVL 15

Author Comment

by:colly92002
ID: 8135282
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
 
LVL 15

Author Comment

by:colly92002
ID: 8135718
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
 
LVL 4

Accepted Solution

by:
mberumen earned 600 total points
ID: 8137707
0
 
LVL 15

Author Comment

by:colly92002
ID: 8180251
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
 
LVL 10

Expert Comment

by:apollois
ID: 8182961
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
 
LVL 15

Author Comment

by:colly92002
ID: 8196594
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question