[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. :confused::confused:


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
Dim myExcel, myWorkBook As Object
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, "\"))
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
Return True
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
myExcel = Nothing
myWorkBook = Nothing
Return False
End Try
myWorkBook.Close()
myExcel.Quit()
myExcel = Nothing
myWorkBook = Nothing
Connection.Close()
GC.Collect()
Else
Return False
End If
End Function

[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 FunctionAlso 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