Convert Datatable to CSV Stream

Convert DataTable to CSV stream

You can just write something quickly yourself:

public static class Extensions
{
public static string ToCSV(this DataTable table)
{
var result = new StringBuilder();
for (int i = 0; i < table.Columns.Count; i++)
{
result.Append(table.Columns[i].ColumnName);
result.Append(i == table.Columns.Count - 1 ? "\n" : ",");
}

foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
result.Append(row[i].ToString());
result.Append(i == table.Columns.Count - 1 ? "\n" : ",");
}
}

return result.ToString();
}
}

And to test:

  public static void Main()
{
DataTable table = new DataTable();
table.Columns.Add("Name");
table.Columns.Add("Age");
table.Rows.Add("John Doe", "45");
table.Rows.Add("Jane Doe", "35");
table.Rows.Add("Jack Doe", "27");
var bytes = Encoding.GetEncoding("iso-8859-1").GetBytes(table.ToCSV());
MemoryStream stream = new MemoryStream(bytes);

StreamReader reader = new StreamReader(stream);
Console.WriteLine(reader.ReadToEnd());
}

EDIT: Re your comments:

It depends on how you want your csv formatted but generally if the text contains special characters, you want to enclose it in double quotes ie: "my,text". You can add checking in the code that creates the csv to check for special characters and encloses the text in double quotes if it is. As for the .NET 2.0 thing, just create it as a helper method in your class or remove the word this in the method declaration and call it like so : Extensions.ToCsv(table);

Most efficient way of converting a DataTable to CSV

Use a System.Text.StringBuilder for huge strings - that's pretty fast.
I implemented this one:

public static string DataTableToCSV(this DataTable datatable, char seperator)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < datatable.Columns.Count; i++)
{
sb.Append(datatable.Columns[i]);
if (i < datatable.Columns.Count - 1)
sb.Append(seperator);
}
sb.AppendLine();
foreach (DataRow dr in datatable.Rows)
{
for (int i = 0; i < datatable.Columns.Count; i++)
{
sb.Append(dr[i].ToString());

if (i < datatable.Columns.Count - 1)
sb.Append(seperator);
}
sb.AppendLine();
}
return sb.ToString();
}

How can I turn a DataTable to a CSV?

The following shorter version opens fine in Excel, maybe your issue was the trailing comma

.net = 3.5

StringBuilder sb = new StringBuilder(); 

string[] columnNames = dt.Columns.Cast<DataColumn>().
Select(column => column.ColumnName).
ToArray();
sb.AppendLine(string.Join(",", columnNames));

foreach (DataRow row in dt.Rows)
{
string[] fields = row.ItemArray.Select(field => field.ToString()).
ToArray();
sb.AppendLine(string.Join(",", fields));
}

File.WriteAllText("test.csv", sb.ToString());

.net >= 4.0

And as Tim pointed out, if you are on .net>=4, you can make it even shorter:

StringBuilder sb = new StringBuilder(); 

IEnumerable<string> columnNames = dt.Columns.Cast<DataColumn>().
Select(column => column.ColumnName);
sb.AppendLine(string.Join(",", columnNames));

foreach (DataRow row in dt.Rows)
{
IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString());
sb.AppendLine(string.Join(",", fields));
}

File.WriteAllText("test.csv", sb.ToString());

As suggested by Christian, if you want to handle special characters escaping in fields, replace the loop block by:

foreach (DataRow row in dt.Rows)
{
IEnumerable<string> fields = row.ItemArray.Select(field =>
string.Concat("\"", field.ToString().Replace("\"", "\"\""), "\""));
sb.AppendLine(string.Join(",", fields));
}

And last suggestion, you could write the csv content line by line instead of as a whole document, to avoid having a big document in memory.

convert Datatable to csv file in R

It is not working and Error appears because as MrFlick said, Your write.xlsx(...) function must be in reactive object. I guess You are using ShinyApp and to download data you have a downloadButton, then the code would look like that:

ui.R:

downloadButton('download',label='Download data')

server.R:

output$download <- downloadHandler(
"probabilitymatrix.csv", content = function(file) {
write.xlsx(MDPData(),'www/probabilitymatrix.csv')})

To download and convert csv file to Datatable

You can use StreamReader class to download csv without the need to save it locally:

public string DownloadCSV(string url)
{
HttpWebRequest httpWebRequest = (HttpWebRequest)WebRequest.Create(url);
HttpWebResponse httpWebResponse = (HttpWebResponse)httpWebRequest .GetResponse();

StreamReader streamReader = new StreamReader(httpWebResponse .GetResponseStream());
string results = streamReader.ReadToEnd();
streamReader .Close();

return results;
}

Convert DataTable to CSV

You can export a DataTable object to CSV simply by piping the table into the Export-Csv cmdlet:

$table | Export-Csv C:\table.csv -NoType

However, by doing that you lose all type information of the table columns (the Export-Csv cmdlet can only save information about the type of the objects that represent the rows, not about the type of their properties).

A better way to save and restore a DataTable object is to save the table as XML:

$writer = New-Object IO.StreamWriter 'C:\path\to\data.xml'
$table.WriteXml($writer, [Data.XmlWriteMode]::WriteSchema)
$writer.Close()
$writer.Dispose()

and restore the XML into a DataSet:

$ds = New-Object Data.DataSet
$ds.ReadXml('C:\path\to\data.xml', [Data.XmlReadMode]::ReadSchema)
$table = $ds.Tables[0]

Make sure to export and import the schema along with the data, because that's where the type information is stored.

How to read a CSV file into a .NET Datatable

Here's an excellent class that will copy CSV data into a datatable using the structure of the data to create the DataTable:

A portable and efficient generic parser for flat files

It's easy to configure and easy to use. I urge you to take a look.



Related Topics



Leave a reply



Submit