Cannot import data from Excel 2013 into ASP file

[Mr. Snrub]

I am using Excel 2013, and I am following the example here: VBScript Scripting Techniques: Read Excel files without using Excel that reads in Excel data as an ADO record set to a classic ASP file using VBScript. I am not able to import all the Excel data successfully, and I need to know what I'm doing wrong. Note that in all these samples cell A1 is the heading text "Column1" and the main data starts on cell A2 (consistent with the example code).

When my source Excel data looks like the following:

Column1
1
2
3
4
5
6
7
X
9
10

…it imports everything OK. However, if I move the X to the next row:

Column1
1
2
3
4
5
6
7
8
X
10

...the "X" cell gets imported as an empty string. So the imported array looks like this:

arrSheet[0][0]: Column1
arrSheet[1][0]: 1
arrSheet[2][0]: 2
arrSheet[3][0]: 3
arrSheet[4][0]: 4
arrSheet[5][0]: 5
arrSheet[6][0]: 6
arrSheet[7][0]: 7
arrSheet[8][0]: 8
arrSheet[9][0]: (empty string)
arrSheet[10][0]: 10

But if I add another X to an earlier row in the source worksheet, like so:

Column1
1
2
3
4
5
X
7
8
X
10

...this gets imported OK. What on earth is going on here???

 

[Kyle123]

At a guess the data provider is making a guess at the datatype of your column. The way it does this is to scan the first x number of rows in a column to work out its type, I suspect that moving the x further up the column means that it gets scanned and evaluated so the record set allows for it in its field type. When the x is further down, it is not evaluated so the recordset assigns a number type to the field which prevents text from being imported.

From msdn:

Data Types

Unlike a traditional database, there is no direct way to specify the data types for columns in Excel tables. Instead, the OLE DB provider scans a limited number of rows in a column to "guess" the data type for the field. The number of rows to scan defaults to eight (8) rows; you can change the number of rows to scan by specifying a value between one (1) and sixteen (16) for the MAXSCANROWS setting in the extended properties of your connection string.

I suspect that making sure that your connection string contains IMEX=1 as a first port of call. If you have already done, this try adding MAXSCANROWS=0. I believe that this will force an evaluation of all the rows to determine the data type (it's been a long time since I've needed to do anything like this, so I could very well be wrong).

Alternatively, you could make sure that all the data is text in the column - even the numbers

 

[Mr. Snrub]

Unfortunately, adding MAXSCANROWS=0 did not work. My connection string is as follows:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source='E:\path\to\my\excel\file\test.xls;Extended Properties="Excel 12.0;MAXSCANROWS=0;IMEX=1;HDR=YES;"

I have no control over the real source document, but on my test source document converting every cell to text did work. Is there a way to force import as text?

 

[Mr. Snrub]

By making the field headers be the first item in each array, it tells Excel "This is a text-based array," I found an acceptable workaround. I just use items at index zero be the headers, and the actual arrays start at index one.