0

I want to import my Excel .xlsx file into a datatable in vb.net in order to process the data later. My Excel file called Data.xlsx looks like this:

Référence Date Concerned Numbers Comment
2022-A34 24.01.2022 738/3784 Checked
2022-A36 28.01.2022 7/3, 8733/28373,938/24755

and tried to use the code given by Ciarán here: Best /Fastest way to read an Excel Sheet into a DataTable?

    Dim sSheetName As String = ""
    Dim sConnection As String
    Dim nOutputRow As Integer = 0
    Dim dtTablesList As DataTable
    Dim oleExcelCommand As OleDb.OleDbCommand
    Dim oleExcelReader As OleDb.OleDbDataReader
    Dim oleExcelConnection As OleDb.OleDbConnection

    sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Folder\Data.xlsx;Extended Properties=""Excel 12.0;HDR=No;IMEX=1"""

    oleExcelConnection = New OleDb.OleDbConnection(sConnection)
    oleExcelConnection.Open()

    dtTablesList = oleExcelConnection.GetSchema("Tables")

    If dtTablesList.Rows.Count > 0 Then
        sSheetName = dtTablesList.Rows(0)("TABLE_NAME").ToString
    End If

    dtTablesList.Clear()
    dtTablesList.Dispose()

    If sSheetName <> "" Then
        oleExcelCommand = oleExcelConnection.CreateCommand()
        oleExcelCommand.CommandText = "Select * From [" & sSheetName & "]"
        oleExcelCommand.CommandType = CommandType.Text
        oleExcelReader = oleExcelCommand.ExecuteReader
        nOutputRow = 0
        While oleExcelReader.Read
        End While
        oleExcelReader.Close()
    End If

    oleExcelConnection.Close()

My problem is now that when I run that code and check the dtTablesList to see the content, the whole table is basically empty. I only see this: After the code has been executed, I see this

Am I missing here something or is my approach totally wrong?

Cliff
  • 61
  • 1
  • 10
  • At what line do you check `dtTablesList`? It is cleared and disposed in the middle of your code. – KekuSemau Apr 25 '22 at 09:58
  • 1
    oh no. Ok that is my problem. I was so dumb to check the `dtTablesList` when it is disposed. My fault, sorry for this! – Cliff Apr 25 '22 at 10:01

1 Answers1

1

This

While oleExcelReader.Read
End While

will let you process each row inside the loop. This is good if you want to read and handle each field for each row individually, e.g. with oleExcelReader.GetString(0) etc.

If you just want to read the entire table, one way is:

...
dtTablesList.Clear()
dtTablesList.Dispose()

Dim dt As DataTable

If sSheetName <> "" Then
    oleExcelCommand = oleExcelConnection.CreateCommand()
    oleExcelCommand.CommandText = "Select * From [" & sSheetName & "]"
    oleExcelCommand.CommandType = CommandType.Text
    Using da As New OleDb.OleDbDataAdapter(oleExcelCommand)
        dt = New DataTable
        da.Fill(dt)
    End Using
End If

oleExcelConnection.Close()
KekuSemau
  • 6,830
  • 4
  • 24
  • 34
  • By using this, my problem is solved, however the first line in the excel files are not used as headers. – Cliff Apr 25 '22 at 10:03
  • You can try to use `HDR=Yes` in the connection string. – KekuSemau Apr 25 '22 at 10:07
  • is it actually normal that some content of my .xlsx-file is imported wrongly. I have for example a header called `Date of F.C.`, but the header in my datatable is called `Date of F#C#`. This means that the . is replaced by the # – Cliff Apr 26 '22 at 09:50
  • @Cliff Phew I can't help you with that. You may have to ask another question about (I presume) the exact limitations of ACE / OLEDB. But I tested it quickly and see that too for dots. – KekuSemau Apr 26 '22 at 12:13