VB 2010 Reading xls file

Question

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 29-Apr-13 14:39pm

Member 9991679

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

TnTinMn · Answer 1 · 2013-04-30T08:18:00

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.