I am using VB 2010 to read an Excel file. The original file was in xlsx format, but I only have Excel 2003 installed, so I opened it using the MS converter and saved it in xls format.
I am reading the header row to get the header names and number of columns, but some cells are read as blank event though I can see the text in them.
I have tried replacing the text with other text, exporting the spreadsheet to a csv and importing back into a new spreadsheet, and changing the cell format from General to Text and keep getting the same results.
Here is what I am using to read the spreadsheet:
Private Sub cboFilesList_SelectedValueChanged(sender As Object, e As System.EventArgs) Handles cboFilesList.SelectedValueChanged
Dim rsHeaders As New ADODB.Recordset
Dim strSQL As New System.Text.StringBuilder
Dim dbExcel As New ADODB.Connection
Dim ColNum As Integer = 0
With dbExcel
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\NADData\ResavedExcel\SOAP.xls;Extended Properties=""Excel 8.0; HDR=No;"""
.Open()
End With
strSQL.Clear()
strSQL.Append("SELECT * FROM [Sheet1$]")
rsHeaders.Open(strSQL.ToString, dbExcel)
rsHeaders.MoveFirst()
Do Until rsHeaders.Fields(ColNum).Value.ToString = ""
ColNum += 1
Loop
End Sub
And here is the first row, exported to comma delimited text since I'm not sure how to post the actual spreadsheet row.
Attending,Fellow,repository::Last Name,repository::First Name,repository::MI,Doctors Directory refer::First Name,repository::Referring Dr. Last Name,Doctors Directory refer::Business Street,Doctors Directory refer::Business City,Doctors Directory refer::Business State,Doctors Directory refer::Business ZIP Code,Doctors Directory refer::Phone Number,repository::Age,repository::DOB,SSN,Doctors Directory family::First Name,repository::Family Dr. Last Name,Record number,repository::Gender,Doctors Directory family::Business Street,Doctors Directory family::Business City,Doctors Directory family::Business State,Doctors Directory family::Business ZIP Code,Doctors Directory family::Phone Number,repository::Other Dr. First Name,DATE OF EVALUATION,Chief Complaint,History of Present Illness,blank Copy13,IMPRESSION,RECOMMENDATION/PLAN,repository::Medications,allergies,fu,FU Visit,Blood Tests,exam phy,reveiw phy
The first cell that it reads as blank is "repository::Age". I have looked at the csv file in WinVi in Hex mode and can see no hidden characters between the fields.
I'm at a loss, can someone help?
Thanks