Reading multiple excel file into 1 dataset.

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.

re: Reading multiple excel file into 1 dataset.

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

re: Reading multiple excel file into 1 dataset.

On Thu, 29 Jan 2004 09:56:32 +0800, "Phoebe." <phoebe8124@hotmail.com> wrote:

¤ 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 ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

re: Reading multiple excel file into 1 dataset.

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" <UseAdddressAtEndofMessage@swspectrum.com> wrote in message
news:u8ui101m0ojaa58rrob1j0cn59u1unkfu6@4ax.com...[color=blue]
> On Thu, 29 Jan 2004 09:56:32 +0800, "Phoebe." <phoebe8124@hotmail.com>[/color]
wrote:[color=blue]
>
> ¤ 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[/color]
dataset?[color=blue]
> ¤ How can I append those data?
> ¤
> ¤ Can someone help?
>
> Assuming that your connection is to an Excel Workbook, the following[/color]
example, using the UNION[color=blue]
> keyword, should work:
>
> "Select * from [Sheet7$] UNION Select * from [Sheet3$]"
>
>
> Paul ~~~ pclement@ameritech.net
> Microsoft MVP (Visual Basic)[/color]

re: Reading multiple excel file into 1 dataset.

On Fri, 30 Jan 2004 10:51:44 +0800, "Phoebe." <phoebe8124@hotmail.com> wrote:

¤ 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 ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)