DataSet tables into XML, Excel and csv File

[Progress2007]

Hi
I am using Vb.NET 2008 with 3.5 Framework.

Now my problem is that i hv a dataset which has 3 datatables. Wht i want that to Export all datatbale XML into a single XML file.

The XML File should be created lke that we can read it easily and store into the database if neded.

Again i want that dataset tables to be exported to XLS and CSV File.

Please tell me how can i do this...

 

[Tom]

XML, Excel (XLS) and CSV are three different file types, which do you want?

Exporting to XML would be the easiest, myDataset.WriteXML(strFileName) would output all three of your tables to a single file. Relations might need to be added in order to get parent/child nesting formatted

An XML file can be opened in Excel and make it easy to edit data but the problem you will see is with the multiple tables rather then a single table. Despite being in different tables, the records from all 3 tables will show per each line in a worksheet.

You could output each table individually so that each table has its own worksheet within the same workbook/file but this would depend of if this type of formatting meets your needs or not to have detail tables in seperate worksheets.

 

[Progress2007]

Thank u so much for ur comment.

I have written code for XML and it is working fine.

Now my issue is that how could i solve my problem of Excel file. I would like to display the diffrent datatables into diffrent sheets into a single work book.

If u have some formatting Code for this then please let me know.

I will be highly obliged to you...

XML, Excel (XLS) and CSV are three different file types, which do you want?

Exporting to XML would be the easiest, myDataset.WriteXML(strFileName) would output all three of your tables to a single file. Relations might need to be added in order to get parent/child nesting formatted

An XML file can be opened in Excel and make it easy to edit data but the problem you will see is with the multiple tables rather then a single table. Despite being in different tables, the records from all 3 tables will show per each line in a worksheet.

You could output each table individually so that each table has its own worksheet within the same workbook/file but this would depend of if this type of formatting meets your needs or not to have detail tables in seperate worksheets.

 

[MattP]

If you're willing to use interop you should be able to get a good idea from: How Do I: Transfer Data Into an Excel Worksheet?

If interop is not an option then I would look at using the Jet or ACE providers to connect to the xls file and insert the data. You can find the connection string information here: ConnectionStrings.com - Forgot that connection string? Get it here!

 

[Tom]

To clarify what I meant was, xml would be the easist but still leaves you with the problem of redundant data per line if viewed in Excel.

If you want to break it down to different tables per different worksheets, I would not use XML. In this case I would suggest using OleDb to output each table individually to there own worksheets within a file.

Attached is an example of outputting multiple tables from a dataset to an single excel file. Being a simple example I didnt do much in the ways of being creative with the dummy data or put error handling for the calling procedures, so if you need anything explained just ask.

 

[sammizai]

To clarify what I meant was, xml would be the easist but still leaves you with the problem of redundant data per line if viewed in Excel.

If you want to break it down to different tables per different worksheets, I would not use XML. In this case I would suggest using OleDb to output each table individually to there own worksheets within a file.

Attached is an example of outputting multiple tables from a dataset to an single excel file. Being a simple example I didnt do much in the ways of being creative with the dummy data or put error handling for the calling procedures, so if you need anything explained just ask.

Tom, any hint to change ur code to accept dataset generated by SQL?
I try to supply a dynamic dataset to 'ExportDatasetToExcel' function.
As result an error return at modExcel.vb -> ExportTableToExcel -> Line 129 - daXls.Update(dtTarget) showing "Syntax error in INSERT INTO statement."

Any idea on this?