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
How to Compare Values of Generic Types
How to Invoke a UI Method from Another Thread
How to Select Min and Max Values of a Column in a Datatable
Why Can't I Use System.Io.File Methods in an MVC Controller
Wpf: How to Override Part of a Controltemplate Without Redefining the Whole Style
How to Restrict/Control the Navigation Routes the User Can Visit Based on Login Status/Role
How to Run Visual Studio Without Plugin and All Third Party Feature
How to Implement a Custom Razorviewengine to Find Views in Non-Standard Locations
Where's the Datetime 'Z' Format Specifier
What Does It Mean for a Property to Be [Required] and Nullable
How to Post a List of Items in MVC
Why Was "Switchto" Removed from Async Ctp/Release
How to Protect Resources That May Be Used in a Multi-Threaded or Async Environment
What's the How to Minimize to Tray a C# Winforms App
Connect to Network Drive with User Name and Password
Is It Necessary to Manually Close and Dispose of SQLdatareader