Excel - Cell Value Problem

[edauthier]

I have another strange problem with Excel. In order to understand the problem use code below and recreate the issue:

step 1 - Create a spreadsheet with 10 rows of data (C:\1\book1.xls)
step 2 - In column 1 row 6 type hello
step 3 - Column 9 type 1

Then run this script and open text file it creates: c:\1\test\test.txt

Code: Select all

Let>connStr=Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=c:\1\Book1.xls;
Let>connStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\1\Book1.xls;Extended Properties=Excel 8.0;
DBConnect>connStr,dbH


Let>SQL=select * from [Sheet1$]
DBQuery>dbH,SQL,rsSheet1,nR,nF


label>Start
  Let>row=0
Repeat>row
  Let>row=row+1

Let>cell1=rsSheet1_%row%_1

WriteLn>c:\1\test\test.txt,r,%cell1%%TAB%%row%


        Until>row=nR

DBClose>dbH

In my result text file hello does not appear, but the 1 does.

However, if I follow the same steps:
step 1 - Create a spreadsheet with 10 rows of data (C:\1\book1.xls)
step 2 - In column 1 row 6 type hello
step 3 - Column 9 type 1

But add:
step 4 - In column 1 any cell 1-5 type a number.

Run the script and 'the number', sometimes hello and 1 appear in text file. But hello does not always appear for me.

[Marcus Tettmar]

This is a data type issue. The data type of column 1 is clearly being deemed to be numeric. See: http://support.microsoft.com/kb/257819

And note the following paragraph:

Rows to Scan: Excel does not provide ADO with detailed schema information about the data it contains, as a relational database would. Therefore, the driver must scan through at least a few rows of the existing data in order to make an educated guess at the data type of each column. The default for "Rows to Scan" is eight (8) rows. You can specify an integer value from one (1) to sixteen (16) rows, or you can specify zero (0) to scan all existing rows. This is done by adding the optional MaxScanRows= setting to the connection string, or by changing the Rows to Scan setting in the DSN configuration dialog box.

However, due to a bug in the ODBC driver, specifying the Rows to Scan (MaxScanRows) setting currently has no effect. In other words, the Excel ODBC driver (MDAC 2.1 and later) always scans the first 8 rows in the specified data source in order to determine each column's datatype.

And a bit further down:

A Caution about Mixed Data Types

As stated previously, ADO must guess at the data type for each column in your Excel worksheet or range. (This is not affected by Excel cell formatting settings.) A serious problem can arise if you have numeric values mixed with text values in the same column. Both the Jet and the ODBC Provider return the data of the majority type, but return NULL (empty) values for the minority data type. If the two types are equally mixed in the column, the provider chooses numeric over text.

For example:
• In your eight (8) scanned rows, if the column contains five (5) numeric values and three (3) text values, the provider returns five (5) numbers and three (3) null values.
• In your eight (8) scanned rows, if the column contains three (3) numeric values and five (5) text values, the provider returns three (3) null values and five (5) text values.
• In your eight (8) scanned rows, if the column contains four (4) numeric values and four (4) text values, the provider returns four (4) numbers and four (4) null values.
As a result, if your column contains mixed values, your only recourse is to store numeric values in that column as text, and to convert them back to numbers when needed in the client application by using the Visual Basic VAL function or an equivalent.

To work around this problem for read-only data, enable Import Mode by using the setting "IMEX=1" in the Extended Properties section of the connection string. This enforces the ImportMixedTypes=Text registry setting. However, note that updates may give unexpected results in this mode. For additional information about this setting, click the article number below to view the article in the Microsoft Knowledge Base:
194124 (http://support.microsoft.com/kb/194124/EN-US/) PRB: Excel Values Returned as NULL Using DAO OpenRecordset

So according to the above the solution is to modify your connection string to:

Let>connStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\example.xls;Extended Properties="Excel 8.0;IMEX=1"

More free Microsoft support from MJT Net :-)

[edauthier]

Hmmm.. Thanks I think.. seem a bit fresh towards the end..lol


When DBCONNECT runs its powerful, but the problems with Excel have been killing me. The connection string nuances for the different versions, vbscripting problems, data in cells the "255" char issue, wrapping, etc.
I can't be alone out here..

Anyhow, I am very appreciative as always to support.
Thanks again.