How to append data to

I have a large group of Excel files that are my data source for a application.  I use OleDb to extract the relevant data from each spreadsheet (one row of data per spreadsheet).

My end product will be a table with each one of those data rows from above.

I need help figuring out how to dump the data into a single DataSet.  (I don't really understand details about TableMappings and DataSet.)

The code below works fine for single iteration, but I need to uncomment the "for loop" and get everything into the dataset and datagridview.  Can someone help?
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

DtSet = New System.Data.DataSet

#For Each xMarket in MarketArray
Dim xMarket = "Atlanta"
	MyConnection = New System.Data.OleDb.OleDbConnection _
		("provider=Microsoft.ACE.OLEDB.12.0;" & _
		" Data Source='" xMarket & ".xlsx" & "'; " & _
		" Extended Properties=" + Convert.ToChar(34).ToString() +
		"Excel 12.0;HDR=Yes" + Convert.ToChar(34).ToString())
	MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [" & myTab & "$] where Week like '%" & xWeek & "%'", MyConnection)
	MyCommand.TableMappings.Add("Table", "TestTable")
	DataGridView1.DataSource = DtSet.Tables(0)

Open in new window

Who is Participating?
Chandan_GowdaConnect With a Mentor Commented:
you can use dataset merge method. Please follow the below link
khan_webguruConnect With a Mentor Commented:
Hello Bro!

You can use this approach

Sub BindGrid()
     Dim myConnection as New SqlConnection (strConn)
     Dim DS1 As DataSet
     Dim DS1 As DataSet
     Dim MyCommand As SqlDataAdapter
     MyCommand = new SqlDataAdapter("exec s_get_table1", MyConnection)
     DS1 = new DataSet()
     MyCommand.Fill(DS1, "MyTable")
     MyCommand = new SqlDataAdapter("exec s_get_table2", MyConnection)
     DS2 = new DataSet()
     MyCommand.Fill(DS2, "MyTable")

End Sub

Open in new window

Hope this will provide you some idea


Asif Ahmed KHan
motokentAuthor Commented:
Thank you both.  (The solutions were the same.)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.