Excel To ADO RecordSet has empty values for numeri

问题:

I'm trying to import some data to work with from an Excel file but I'm running into problems with numeric cell values. I have some columns that will have values where some are all numeric while other values might be a mix of numeric and non numeric values (no special characters, just A-Z letters). To pull the data into a recordset, I'm doing the following

 Set oconn = New ADODB.connection
 oconn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & excelFile & ";" & "Extended Properties=""Excel 8.0;HDR=YES;"""

  sTableName = "[sheet1$]"
  sTableName = "select * from " & sTableName

  Set oRs = New ADODB.Recordset
  oRs.Open sTableName, oconn, adOpenStatic, adLockOptimistic

When I'm reading the recordset's values though, numeric values are showing up as blank in the columns where the data is in a mixed format. Is there any way to get the recordset to just read all values as text or an alternative way to reading the Excel file to avoid this issue?

回答1:

I had a similar problem but in reverse, where the numbers showed up fine but the alphas did not. After adding

;IMEX=1

to the Extended Properties, it worked fine.

IMEX=1 means always read mixed data columns as text.

回答2:

If you can later exclude it the easiest way to overcome this is always set the first eight rows of your data to the desired type. Despite option to specify 'rows to scan' in ODBC, it turns out it always scans the first 8 rows. If you can afford to have some dummy data at top (that is later excluded by pivot etc) this works well! You can see the Microsoft reference here: https://support.microsoft.com/en-us/kb/141284