Exporting Data from SDF Database to CSV file

Hi guys,

Would any of you be able to provide some guide on how am I going to export the selected data (multiple rows and columns) into Excel or CSV file?

Or at least export into Text file, which I can later on save the file name as .CSV, so it become a CSV file after saving.

Thanks.

Regards,

Jenson

In which context is the data "selected". You can always enumerate your data and use the System.IO namespace to create the CSV file in code?|||

Hi Erik,

Yes Erik, that's what I intend to do, do you have any guide for me to follow, or any sample codes to refer to? I need to refer to them and write my own one, as I don't really think what I wanted to do is the same, I just need to structure and check how they do it, and what are the various ways to achieve the same thing.

Thanks.

Regards,

Jenson

|||

Hope this sample is what you are looking for:

Code Snippet

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlServerCe;

namespace ExportSDF

{

class Program

{

static void Main(string[] args)

{

SqlCeConnection conn = null;

SqlCeCommand cmd = null;

SqlCeDataReader rdr = null;

try

{

// Based on this sample: http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlcedatareader.aspx

// Open the connection and create a SQL command

//

conn = new SqlCeConnection(@."Data Source = C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\SDK\Samples\Northwind.sdf;max database size=256");

conn.Open();

cmd = new SqlCeCommand("SELECT * FROM Customers", conn);

rdr = cmd.ExecuteReader();

System.IO.TextWriter stm = new System.IO.StreamWriter(new System.IO.FileStream(@."C:\customers.csv", System.IO.FileMode.Create), Encoding.Default);

// Iterate through the results

//

while (rdr.Read())

{

// Write all fields except the last one...

for (int i = 0; i < rdr.FieldCount-2; i++)

{

if (rdr[i] != null)

{

stm.Write(rdr[i].ToString());

stm.Write(";");

}

else

{

stm.Write(";");

}

}

if (rdr[rdr.FieldCount-1] != null)

{

stm.Write(rdr[0].ToString());

}

stm.Write(System.Environment.NewLine);

}

// Always dispose data readers and commands as soon as practicable

//

stm.Close();

rdr.Close();

cmd.Dispose();

}

finally

{

// Close the connection when no longer needed

//

conn.Close();

}

}

}

}

Happy coding!

|||Hi Erik,

Thanks for the great reply and sorry for the late reply. The reason behind is I have finished the workaround for this requirements and it's working just fine. By looking at your code, I find that this code is even easier to understand!

I will try it out later as I'm currently busy with other stuff. Thanks for the great codes, Erik!

Have marked your answer as answer =)

Export XML data to File

I have seen other questions like this, but none that provide the answer I need.

I need to move data from one SQL DB to another, located in different offices.

I want to do this programmatically as part of an application, ether using VB or T-SQL.

I understan how to use FOR XML to get the data into an XML column.

How do I save that to a file that I can move to a different machine?

I would prefer not to use bcp, so that if I do it in a stored proc, I do not have to use xp_cmdshell.

Thank you.

Richard

Hi Richard,

We dont have any tools that would allow you to export a table to XML and then have another table automatically consume it. There are a few ways to achieve this though. Here are a couple:

Option 1) You can generate a FOR XML statement for your source table. Save the results to an XML file, and then on the destination database you the XML Datatype's nodes() method to convert the xml into relational data, and insert it into your destination table. (Here is some documentation on the nodes() function. http://msdn2.microsoft.com/en-us/ms188282.aspx)

Option 2) You could fill a dataset with the relational data that you are interested in. Serialize the dataset as XML. Rehydarate another dataset with the xml and insert it into your destination tables.

Option 1 can be done using just T-SQL within SP's. Option 2 requires the use of .Net, but you could do it within an SP using SQLCLR in the engine.

|||

Here is code to write XML as destination. Variable uvRecordSet was created by Recordset Destination

Public Sub Main()

Dim adp As New OleDb.OleDbDataAdapter

Dim dt As New DataTable("WriteXMLTableName")

adp.Fill(dt, Dts.Variables("uvRecordSet").Value)

dt.WriteXml("C:\myxml.xml", False)

Dts.TaskResult = Dts.Results.Success

End Sub

Export XML data to File

I have seen other questions like this, but none that provide the answer I need.

I need to move data from one SQL DB to another, located in different offices.

I want to do this programmatically as part of an application, ether using VB or T-SQL.

I understan how to use FOR XML to get the data into an XML column.

How do I save that to a file that I can move to a different machine?

I would prefer not to use bcp, so that if I do it in a stored proc, I do not have to use xp_cmdshell.

Thank you.

Richard

Hi Richard,

We dont have any tools that would allow you to export a table to XML and then have another table automatically consume it. There are a few ways to achieve this though. Here are a couple:

Option 1) You can generate a FOR XML statement for your source table. Save the results to an XML file, and then on the destination database you the XML Datatype's nodes() method to convert the xml into relational data, and insert it into your destination table. (Here is some documentation on the nodes() function. http://msdn2.microsoft.com/en-us/ms188282.aspx)

Option 2) You could fill a dataset with the relational data that you are interested in. Serialize the dataset as XML. Rehydarate another dataset with the xml and insert it into your destination tables.

Option 1 can be done using just T-SQL within SP's. Option 2 requires the use of .Net, but you could do it within an SP using SQLCLR in the engine.

|||

Here is code to write XML as destination. Variable uvRecordSet was created by Recordset Destination

Public Sub Main()

Dim adp As New OleDb.OleDbDataAdapter

Dim dt As New DataTable("WriteXMLTableName")

adp.Fill(dt, Dts.Variables("uvRecordSet").Value)

dt.WriteXml("C:\myxml.xml", False)

Dts.TaskResult = Dts.Results.Success

End Sub

Export to Excel: Shortcut button back to document map

Anyone know of a way to provide a quick "back button" on each tab of
the excel export to go back to the document map tab?Bump
zach.bowling@.gmail.com wrote:
> Anyone know of a way to provide a quick "back button" on each tab of
> the excel export to go back to the document map tab?