Excel To ADO RecordSet has empty values for numeric cells

Question

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?

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. — mr.Reband
Thank you so much, that is exactly what I needed. If you want to post that as the answer, I'll mark it as the answer to the question — gdawgrancid

mr.Reband mr.Reband · Accepted Answer · 2013-05-23T19:06:08

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.

Excel To ADO RecordSet has empty values for numeric cells

2 Answers