Reading multiple excel file into 1 dataset.

  • Thread starter Thread starter Phoebe.
  • Start date Start date
P

Phoebe.

Hi, Good Day!

Reading 1 excel file into a dataset is fine.
How can I read multiple excel with the same data structure into 1 dataset?
How can I append those data?

Can someone help?
Thanks in advanced.

rgds,
Phoebe.
 
Hi Phoepe,

I never did it or try it, but try to fill it in a dataset, than it should go
like this

\\\watch typos just written here
dim ds as new dataset
dim cmd as xxxcommand(sqlstring,con)
dim da as new xxxdataadapter(cmd)
da.fill(ds, "sheet1")
cmd.command = ("a new sqlstring")
da.fill(ds, "sheet2")
///

I hope this helps

Cor
 
¤ Hi, Good Day!
¤
¤ Reading 1 excel file into a dataset is fine.
¤ How can I read multiple excel with the same data structure into 1 dataset?
¤ How can I append those data?
¤
¤ Can someone help?

Assuming that your connection is to an Excel Workbook, the following example, using the UNION
keyword, should work:

"Select * from [Sheet7$] UNION Select * from [Sheet3$]"


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Thanks Paul,

Problem solved if those worksheet is in 1 excel file.

Mine is same worksheet but different excel file. i.e. datasource is
different.
Currently I have 5 excel file, so i connect using 5 connection and data
selected using 5 dataadapter and fill in using 5 dataset into 1 datagrid.

msConnectString = New System.Data.OleDb.OleDbConnection(
"provider=Microsoft.Jet.OLEDB.4.0; data source=C:\a.xls; Extended
Properties=Excel 8.0;")

msConnectString = New System.Data.OleDb.OleDbConnection(
"provider=Microsoft.Jet.OLEDB.4.0; data source=C:\b.xls; Extended
Properties=Excel 8.0;")

........


' Select the data from Sheet1 of the workbook.

da1 = New System.Data.OleDb.OleDbDataAdapter( "select * from [Sheet1$]",
msConnectString)

da2 = New System.Data.OleDb.OleDbDataAdapter( "select * from [Sheet1$]",
msConnectString)

.......

dsExcel = New System.Data.DataSet()

da1.Fill(dsExcel)

da2.Fill(dsExcel)

.......

dgExcel.DataSource = dsExcel


Can this be solved by connecting 5 excel file using 1 connenction?
Thanks in advanced.

rgds,
Phoebe.

Paul Clement said:
¤ Hi, Good Day!
¤
¤ Reading 1 excel file into a dataset is fine.
¤ How can I read multiple excel with the same data structure into 1 dataset?
¤ How can I append those data?
¤
¤ Can someone help?

Assuming that your connection is to an Excel Workbook, the following example, using the UNION
keyword, should work:

"Select * from [Sheet7$] UNION Select * from [Sheet3$]"


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
Click to expand...
Click to expand...
 
¤ Thanks Paul,
¤
¤ Problem solved if those worksheet is in 1 excel file.
¤
¤ Mine is same worksheet but different excel file. i.e. datasource is
¤ different.
¤ Currently I have 5 excel file, so i connect using 5 connection and data
¤ selected using 5 dataadapter and fill in using 5 dataset into 1 datagrid.

You can connect to a specific Workbook source but also include other Workbook sources in your SQL
statement:

"Select * from [Sheet7$] UNION Select * from [Excel 8.0;DATABASE=e:\My
Documents\Book20.xls;HDR=No;IMEX=1].[Table4$]"


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top