Click here to Skip to main content
15,116,253 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:
VB
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()
    'Determine how many fields in table
    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
Posted

1 solution

I am not clear what it is that you are trying to accomplish, so I am going to offer some generic advice for working with an excel file as a data source.

First off, use the ACE Provider (Microsoft Access Database Engine 2010) instead of the JET provider. Jet will lock you into a 32-bit environment dependency and is outdated.

You can download the ACE Provider here: http://www.microsoft.com/en-us/download/details.aspx?id=13255[^]

Second, take a look at the article How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET

It is written around using the JET provider, but the code will work the same with the ACE provider.

There is no need to convert Excel formats as the ACE provider can read any of them with the proper connection string.

Here is a method that I use to create the connection string:
VB
Friend Function GetExcelConnectionString(ByVal fn As String, Optional ByVal Hdr As Boolean = True) As String
   Dim fi As New System.IO.FileInfo(fn)

   Dim strconn As String = Nothing

   'Mode='Read';
   'Mode='Write';
   'Mode='ReadWrite';
   'Mode='Share Deny None';
   'Mode='Share Deny Read';
   'Mode='Share Deny Write';
   'Mode='Share Exclusive';

   'File Type (extension)                                             Extended Properties
   '---------------------------------------------------------------------------------------------
   'Excel 97-2003 Workbook (.xls)                                    "Excel 8.0"
   'Excel 2007-2010 Workbook (.xlsx)                                 "Excel 12.0 Xml"
   'Excel 2007-2010 Macro-enabled workbook (.xlsm)                   "Excel 12.0 Macro"
   'Excel 2007-2010 Non-XML binary workbook (.xlsb)                  "Excel 12.0"

   'IMEX = 0 : All Data Same Type in column
   'IMEX = 1 : Mixed data in column
   Select Case fi.Extension.ToLower
      Case ".xls"
         strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                                 fn & _
                                 ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"

      Case ".xlsx"
         strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                   fn & _
                   ";Mode='ReadWrite';Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1';"
      Case ".xlsm"
         strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                   fn & _
                   ";Mode='ReadWrite';Extended Properties='Excel 12.0 Macro;HDR=Yes;IMEX=1;'"
      Case ".xlsb"
         strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                   fn & _
                   ";Mode='ReadWrite';Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"
      Case Else
         Throw New ArgumentException("Invalid excel file type: (" & fi.Extension & ")")
   End Select

   If Not Hdr Then strconn = strconn.Replace("HDR=Yes", "HDR=No")

   Return strconn
End Function


To retrieve a table of column names and their respective datatypes, you can use something like this:

VB
Dim cn As New OleDb.OleDbConnection(GetExcelConnectionString("D:\My Documents\xlwrite.xls"))
Dim dt As New DataTable
cn.Open()
dt = cn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Columns, New Object() {Nothing, Nothing, "SHEETNAME", Nothing})
cn.Close()
dt.DefaultView.Sort = "ORDINAL_POSITION ASC" ' sorts based on column order in table
' Translate Data_Type to OleDbType
dt.Columns.Add("OledbType", GetType(String))
For Each r As DataRow In dt.Rows
   r("OledbType") = [Enum].GetName(GetType(OleDb.OleDbType), CInt(r("Data_Type")))
Next
DataGridView1.DataSource = dt


Hopefully this information will be of help.
   
v2
Comments
Member 9991679 30-Apr-13 14:22pm
   
Great, there is a lot of info there so I'll study it and post back.
Thank youl
Member 9991679 1-May-13 8:59am
   
That worked great. Thanks for the prompt reply.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900