How to merge multiple datatables into one?

Question

I have multiple excel files which I open as datatable I want to merge all this datatables single DataTable in .net.

eg, Apr09.xls,May09.xls,Jun09.xls

All have dataas follows

Apr09.xls

EMPCODE,PresentDays
0001   ,30
0002   ,21

May09.xls

EMPCODE,PresentDays
0001   ,25
0002   ,30

New datatable will be as follows

EMPCODE,PresentDays
0001   ,30
0002   ,21
0001   ,25
0002   ,30

How to create this.

Sachin Chavan · Accepted Answer · 2010-07-22 08:38:37Z

1

Datatable.Merge can be used as follows

For Each fileName As String In Directory.GetFiles("C:\\TEMP\\", "*.xls")
    Dim connectionString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=""Excel 8.0;HDR=NO;IMEX=1;""", fileName)
    Dim adapter As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString)
    Dim ds As New DataSet
    adapter.Fill(ds, "anyNameHere")
    Dim TempTable As DataTable
    TempTable = ds.Tables.Item("anyNameHere")
    table1.Merge(TempTable)
    MsgBox(fileName)
Next
DataGridView1.DataSource = table1
MsgBox(table1.Rows.Count)

edited Jul 22, 2010 at 8:38

answered Feb 11, 2009 at 6:53

Sachin Chavan

5,6205 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to merge multiple datatables into one?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related