[RESOLVED] Import Excel Data to Dataset

[zuhalterr]

I have a problem with the function below. If anybody knows of a simpler way to accomplish the same results, then please advice.

The problem that I have with this function is that sometimes, not always, it keeps the Excel process running and when this happens I can run the function again until I kill the Excel process manually.

Another problem with this function is that it is slow if the Excel sheet contains a lot of data.

And also I can't get this function to read all the sheets in the Excel workbook and put the contents of each sheet in a separate data table in my dataset.

Thanks in advance for all your help.

PublicFunction StreamExcelData() AsBoolean
Dim Connection As System.Data.OleDb.OleDbConnection
Dim Command As System.Data.OleDb.OleDbDataAdapter
Dim OpenFileDB AsNew OpenFileDialog
Dim i AsInteger
Dim Sheet AsString
Dim myExcel, myWorkBook AsObject
With OpenFileDB
.Filter = "All files | *.* | Excel files | *.xl*"
.FilterIndex = 2
.InitialDirectory = "C:\My Documents"
.AddExtension = True
.DefaultExt = "xls"
.CheckFileExists = True
.CheckPathExists = True
.Title = "Open Input Source File"
EndWith
If (OpenFileDB.ShowDialog() = DialogResult.OK) Then
gblSaveNewFile = OpenFileDB.FileName
gblFileName = Left(gblSaveNewFile, InStr(gblSaveNewFile, ".") - 1)
gblFileName = gblFileName.Substring(InStrRev(gblFileName, "\"))
Try
Connection = New System.Data.OleDb.OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & gblSaveNewFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""")
myExcel = CreateObject("Excel.Application")
'Open the selected Excel document
myWorkBook = myExcel.WorkBooks.Open(gblSaveNewFile)
'Select the first sheet from the document
Sheet = myWorkBook.Worksheets(1).Name
'Import the data from Excel's first sheet
Command = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" & Sheet & "$]", Connection)
Command.TableMappings.Add("MyDataSample", "MyDataSample")
MyDs = New DataSet()
Command.Fill(MyDs)
myWorkBook.Close()
myExcel.Quit()
myExcel = Nothing
myWorkBook = Nothing
Connection.Close()
GC.Collect()
'Rename the column names by replacing the spaces with _ where applicable
For i = 0 To MyDs.Tables(0).Columns.Count - 1
MyDs.Tables(0).Columns(i).ColumnName = Trim(MyDs.Tables(0).Columns(i).ColumnName).Replace(" ", "_")
Next
MyTable = New DataTable("MY_DATA_TABLE")
MyTable = MyDs.Tables(0).Copy
ReturnTrue
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
myExcel = Nothing
myWorkBook = Nothing
ReturnFalse
EndTry
myWorkBook.Close()
myExcel.Quit()
myExcel = Nothing
myWorkBook = Nothing
Connection.Close()
GC.Collect()
Else
ReturnFalse
EndIf
EndFunction

[sknake]

Why don't you open the Excel sheet with an OleDbConnection and query with an OleDbCommand instead of doing excel automation? Since you obviously want to use this in a dataset you could load the data from excel in to a DataTable and add it to your DataSet.

[Shuja Ali]

There is no need to open the excel file to know the sheet names. You can use GetOleDbSchemaTable method of the OleDbConnection class to get the schema of the excel file. This way you can then loop through the datatable and then query each sheet. I have changed your code a bit

    Public Function StreamExcelData() As Boolean
        Dim Connection As System.Data.OleDb.OleDbConnection
        Dim Command As System.Data.OleDb.OleDbDataAdapter
        Dim OpenFileDB As New OpenFileDialog
        Dim i As Integer
        Dim Sheet As String
        With OpenFileDB
            .Filter = "All files | *.* | Excel files | *.xl*"
            .FilterIndex = 2
            .InitialDirectory = "C:\My Documents"
            .AddExtension = True
            .DefaultExt = "xls"
            .CheckFileExists = True
            .CheckPathExists = True
            .Title = "Open Input Source File"
        End With
        If (OpenFileDB.ShowDialog() = DialogResult.OK) Then
            Dim gblSaveNewFile As String = OpenFileDB.FileName
            Try
                Connection = New System.Data.OleDb.OleDbConnection( _
                "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & gblSaveNewFile & ";" & _
                "Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""")
                'open connection 
                Connection.Open()
                'get the sheet names from the schema
                Dim schemaTable As DataTable = Connection.GetOleDbSchemaTable(Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
                'schemaTable now contains the schema of the excel file and sheet names can be retrieved using the TABLE_NAME column
                Sheet = schemaTable.Rows(0).Item("TABLE_NAME").ToString()
                Command = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" & Sheet & "]", Connection)
                Command.TableMappings.Add("MyDataSample", "MyDataSample")
                Dim MyDs = New DataSet()
                Command.Fill(MyDs)
                Connection.Close()
                'Rename the column names by replacing the spaces with _ where applicable
                For i = 0 To MyDs.Tables(0).Columns.Count - 1
                    MyDs.Tables(0).Columns(i).ColumnName = Trim(MyDs.Tables(0).Columns(i).ColumnName).Replace(" ", "_")
                Next
                Dim mytable As DataTable
                MyTable = New DataTable("MY_DATA_TABLE")
                MyTable = MyDs.Tables(0).Copy
                Return True
            Catch ex As System.Exception
                System.Windows.Forms.MessageBox.Show(ex.Message)
                
                Return False
            End Try
            Connection.Close()
        Else
            Return False
        End If
    End Function

Also could you explain why you are using GC.Collect() method? I don't see any advantage of using that method in this piece of code.

[zuhalterr]

Thank you both, Shuja Ali and sknake, for your responses.

After trying once and again, I was able to implement a solution similar to Shuja Ali's solution

To answer your question about the GC(), your right, I don't need it anymore. I used it before because I was having problems with some COM objects and some variables not being released.

Anyway, here's the code I am using now that works fine:

    Public MyWorkSheets As List(Of String)
    Public MyDs As DataSet
    Public MyTable As DataTable

    Private Function GetWorksheets(ByVal Connection As OleDbConnection) As IEnumerable
        Try
            Dim schema As DataTable = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "Table"})
            MyWorkSheets = New List(Of String)
            For Each row As DataRow In schema.Rows
                MyWorkSheets.Add(row("TABLE_NAME").ToString)
            Next
            Return MyWorkSheets
        Catch ex As Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
            Throw
        End Try
    End Function

    Public Sub LoadSpreadsheet()
        Try
            Dim ConnectionString As String = ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                              "Data Source=" & gblSaveNewFile & ";" & _
                                              "Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""")
            Dim Connection As New OleDbConnection(ConnectionString)
            Connection.Open()
            Dim MyCommand As New OleDbCommand
            Dim MyAdapter As New OleDbDataAdapter(MyCommand)
            MyDs = New DataSet
            For Each ws As String In GetWorksheets(Connection)
                MyCommand.Connection = Connection
                MyCommand.CommandText = ("select * from [" + ws + "]")
                Dim Table As DataTable = New DataTable(ws)
                MyAdapter.Fill(Table)
                MyDs.Tables.Add(Table)
            Next
            Connection.Close()
        Catch ex As Exception
            ' Ignore the error here if Excel returned something temporary 
            ' and invisible to the user or a sheet with a filter on it
        End Try
    End Sub

    Public Function StreamExcelData() As Boolean
        Dim OpenFileDB As New OpenFileDialog
        Dim i, j As Integer
        With OpenFileDB
            .Filter = "All files | *.* | Excel files | *.xl*"
            .FilterIndex = 2
            .InitialDirectory = "C:\My Documents"
            .AddExtension = True
            .DefaultExt = "xls"
            .CheckFileExists = True
            .CheckPathExists = True
            .Title = "Open Input Source File"
        End With
        If (OpenFileDB.ShowDialog() = DialogResult.OK) Then
            gblSaveNewFile = OpenFileDB.FileName
            gblFileName = Left(gblSaveNewFile, InStr(gblSaveNewFile, ".") - 1)
            gblFileName = gblFileName.Substring(InStrRev(gblFileName, "\"))
            LoadSpreadsheet()
            'Rename the column names by replacing the spaces with _ where applicable
            For j = 0 To MyDs.Tables.Count - 1
                MyWorkSheets(j) = Left(MyDs.Tables(j).TableName.ToString(), Len(MyDs.Tables(j).TableName.ToString()) - 1)
                For i = 0 To MyDs.Tables(j).Columns.Count - 1
                    MyDs.Tables(j).Columns(i).ColumnName = Trim(MyDs.Tables(j).Columns(i).ColumnName).Replace(" ", "_")
                Next
            Next
            Return True
        Else
            Return False
        End If
    End Function