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.

Answer 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)