Link to home
Create AccountLog in
Avatar of Sean Clarke
Sean ClarkeFlag for United Kingdom of Great Britain and Northern Ireland

asked on

ASP with ADO to Read Excel File - but number columns are blank

Hi,

I'm using ADO to read an Excel spreadsheet of data.

The format of the spreadsheet is out of my control, so has a mix of differently formatted columns, some text, some numbers and some currency.

ADO is looping through the recordset just fine, but the number fields are being returned as blank (or maybe Null)

I've read various articles that this is a feature of ADO and Excel data and that a workaround is required, but I cannot find anyone who can tell me what the workaround is.

Surely the Excel ADO driver can read numbers, this is after all basically a spreadsheet.

I've set IMEX to 1 to have mixed data type and HDR=Yes to skip the header row


<%
     Dim objConnTyre, strSQL, objRecordSetTyre, OpenFile

     Set objConnTyre = Server.Createobject("ADODB.Connection")
     objConnTyre.Open "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=YES; Excel 12.0; DBQ=" & Server.MapPath(excelfile.xl) & "; "

     Set objRecordSetTyre = Server.CreateObject("ADODB.RecordSet")
     objRecordSetTyre.ActiveConnection = objConnTyre
     objRecordSetTyre.CursorType = 3
     objRecordSetTyre.LockType = 2
     objRecordSetTyre.Source = "SELECT * FROM [Sheet1$]"
     objRecordSetTyre.Open

     If objRecordSetTyre.EOF Then
        Response.Write ("<p>Sorry: No records could be found !!</p>")
     Else

      Dim TotalRecord, TotalPage, intCounter, strField
 
      TotalRecord = objRecordSetTyre.RecordCount
      objRecordSetTyre.PageSize = cHowMany
      TotalPage = objRecordSetTyre.PageCount
      objRecordSetTyre.AbsolutePage = 1
 
      intCounter = 1
      
      Do While NOT objRecordSetTyre.EOF AND intCounter <= cHowMany
      
         for each strField in objRecordSetTyre.Fields
             response.write("Field: " & strField.name &" : " & strField.value & "<br>")
         Next
         
         Response.Write("<hr>")
      
         intCounter = intCounter + 1
         
         objRecordSetTyre.MoveNext
      Loop
 
     End If

     objRecordSetTyre.Close()
     objConnTyre.Close()

     Set objRecordSetTyre = Nothing
     Set objConnTyre = Nothing
     %>

Open in new window

Capture.JPG
tyres.xls
ASKER CERTIFIED SOLUTION
Avatar of pateljitu
pateljitu
Flag of Canada image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of Sean Clarke

ASKER

Absolutely superb - searched many websites for that simple solution - thank you so much - works a dream.