Opening XLS file through OleDb. Header issues.

[cicatrix]

Hello, there is a problem I'm afraid there is no solution for, but I wish to ask anyway just in case someone knows.

My application downloads a third-party XLS file from the web. I cannot change its format so I have to teach my application to open it as it is.

I'm using OleDb for opening, here's my code:

'
If XLSFile = "" Then Exit Sub
Dim sExcelConn As String =  _ 
    String.Format( _
     "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";", XLSFile)
 
Dim conn As New OleDbConnection(sExcelConn)
Dim ds As New DataSet()
conn.Open()
 
Dim dtschema As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim tablename As String = ""
If dtschema.Rows.Count > 0 Then
    tablename = dtschema.Rows(0).Item("TABLE_NAME").ToString()
Else 
    Exit Sub
End If
 
Dim cmd As New OleDbCommand(String.Format("SELECT * FROM [{0}];", tablename), conn)
 
Dim dtData As New DataTable
Dim da As New OleDbDataAdapter(cmd)
 
da.Fill(dtData)

Everything works fine except one thing.

The headers of the table I'm trying to get are on the second row, not on the first one. It looks like this:

      |    A    |    B    |    C    |    D    | ...
------|---------+---------+---------+---------+     
     1|This is a main table title which spans the whole table
------|---------+---------+---------+---------+     
     2| Header1 | Header2 | Header3 | Header4 | ...
------|---------+---------+---------+---------+
     3| Data    | Data    |  Data   |  Data   |
------|---------+---------+---------+---------+     
...

Now, when I get that table the headers are treated as data while the table title from the first row is considered a header. Is there a workaround or I just have to live with it?

[kareninstructor]

You can select a range as shown below. Let's say your data begins on row A and spans to D, you have some idea how many hows there are, say between 50 and 80 but want to make sure you get them all so ask for rows up to 100. If there are say 30 rows the row count will be 30, not the amount you asked for.

So in my example below I only have A36 but asked for up to D100, my row count is 4.

Private ConnectionNoHeader As String = "provider=Microsoft.Jet.OLEDB.4.0; data source='{0}';Extended Properties=""Excel 8.0;IMEX=1; HDR=No;"""

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim FileName As String = "Test.xls"
        Using MyConnection As New System.Data.OleDb.OleDbConnection(String.Format(ConnectionNoHeader, FileName))
            MyConnection.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("select * from [Sheet1$A3:D100]", MyConnection)
            Dim dr As System.Data.IDataReader = cmd.ExecuteReader
            Dim dt As New DataTable
            dt.Load(dr)
            Me.Text = dt.Rows.Count.ToString
        End Using
    End Sub

An alternate is to read as you are now and remove the first few data rows from the returning DataTable so all that remains is data.

[cicatrix]

Well, this could help, I'll try it, but still, I need to know the number of rows to query AND I need those headers.

Right now, I used this code which solves that particular problem:

'
If XLSFile = "" Then Exit Sub
Dim sExcelConn As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"";", XLSFile)
Dim conn As New OleDbConnection(sExcelConn)
LicData = New DataTable
 
Try
    conn.Open()
    Dim dtschema As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
 
    Dim tablename As String = ""
 
    If dtschema.Rows.Count > 0 Then
        tablename = dtschema.Rows(0).Item("TABLE_NAME").ToString()
    End If
 
    Dim cmd As New OleDbCommand(String.Format("SELECT * FROM [{0}];", tablename), conn)
 
    Dim dr As OleDbDataReader = cmd.ExecuteReader
    Dim rownumber As Integer = 0
 
    Do While dr.Read()
        Select Case rownumber
            Case Is > 1
                Dim objs(dr.FieldCount - 1) As Object
                dr.GetValues(objs)
                LicData.Rows.Add(objs)
            Case 0 ' Skip
            Case 1 ' Headers
                For cln As Integer = 0 To dr.FieldCount - 1
                    LicData.Columns.Add(New DataColumn(dr.Item(cln).ToString()))
                Next
        End Select
        rownumber += 1
    Loop
    Message(String.Format("Считано {0} записей.", records), False, True)
    Message(Environment.NewLine & "Данные успешно считаны.")
Catch ex As Exception
    Message("Ошибка: " & Environment.NewLine & ex.Message)
Finally
    conn.Close()
End Try

[kareninstructor]

If the format is always the same i.e. data begins on a specific row then this (same as before) gives you that plus tells you how many rows will be returned.

Public Class Form1

    Private ConnectionNoHeader As String = "provider=Microsoft.Jet.OLEDB.4.0; data source='{0}';" & _
        "Extended Properties=""Excel 8.0;IMEX=1; HDR=No;"""
    Private FileName As String = "Test.xls"

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Using MyConnection As New System.Data.OleDb.OleDbConnection(String.Format(ConnectionNoHeader, FileName))

            MyConnection.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("select COUNT(*) from [Sheet3$A3:D100]", MyConnection)

            Dim RowCount = CLng(cmd.ExecuteScalar)
            lblRowCount.Text = String.Format("Row Count: {0}", RowCount)

            cmd = New OleDbCommand("select F1 As Column1, F2 As Whatever, F3 As LastCol from [Sheet3$A3:D100]", MyConnection)

            Dim dr As System.Data.IDataReader = cmd.ExecuteReader
            Dim dt As New DataTable
            dt.Load(dr)

            DataGridView1.DataSource = dt

        End Using
    End Sub
End Class

[cicatrix]

If the format is always the same i.e. data begins on a specific row then this (same as before) gives you that plus tells you how many rows will be returned.

            Dim cmd As OleDbCommand = New OleDbCommand("select COUNT(*) from [Sheet3$A3:D100]",

Well, as I understand, I should query for A3:H65535, what I mean - there are quite a number of rows (20-30K at least) and it varies. The data always begins at row #3 though.

P.S. Would give you rep, but it appears that I need to distribute it some more. Thanks anyway.

[kareninstructor]

To make the rows to read dynamic

Private ConnectionNoHeader As String = "provider=Microsoft.Jet.OLEDB.4.0; data source='{0}';" & _
    "Extended Properties=""Excel 8.0;IMEX=1; HDR=No;"""

Private FileName As String = "Test.xls"

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Using MyConnection As New System.Data.OleDb.OleDbConnection(String.Format(ConnectionNoHeader, FileName))

        MyConnection.Open()
        Dim cmd As OleDbCommand = New OleDbCommand("select COUNT(*) from [Sheet3$]", MyConnection)

        Dim RowCount = CLng(cmd.ExecuteScalar)
        Dim RowsToSkip = 2 ' make adjustment for rows we are skipping i.e. header rows

        lblRowCount.Text = String.Format("Row Count: {0}", RowCount)

        Dim StartRow As String = 1.ExcelColumnName
        Dim StartCol As Integer = 3
        Dim EndRow As String = 3.ExcelColumnName
        Dim EndCol As Integer = CInt(RowCount) + RowsToSkip

        Dim StatementToUse = _
        <SQL>
            SELECT 
                F1 As Column1, 
                F2 As Whatever, 
                F3 As LastCol 
            FROM [Sheet3$<%= StartRow %><%= StartCol %>:<%= EndRow %><%= EndCol %>]
        </SQL>.Value

        cmd = New OleDbCommand(StatementToUse, MyConnection)

        Dim dr As System.Data.IDataReader = cmd.ExecuteReader
        Dim dt As New DataTable
        dt.Load(dr)

        DataGridView1.DataSource = dt

    End Using
End Sub

Code module

Module Module1
    <System.Runtime.CompilerServices.Extension()> _
    Public Function ExcelColumnName(ByVal Index As Integer) As String
        Dim chars = New Char() _
            { _
                "A"c, "B"c, "C"c, "D"c, "E"c, "F"c, "G"c, "H"c, "I"c, _
                "J"c, "K"c, "L"c, "M"c, "N"c, "O"c, "P"c, "Q"c, "R"c, _
                "S"c, "T"c, "U"c, "V"c, "W"c, "X"c, "Y"c, "Z"c _
            }

        Index -= 1

        Dim columnName As String

        Dim quotient = Index \ 26

        If quotient > 0 Then
            columnName = ExcelColumnName(quotient) + chars(Index Mod 26)
        Else
            columnName = chars(Index Mod 26).ToString()
        End If

        Return columnName

    End Function
End Module