How to write an in memory DataTable to a CSV File

[Guest]

Hi,

I want to write an in memory DataTable to a CSV file. Just to prove I can
get it working I created a DataTable with a single column called "IntColumn"
(containing a couple of rows of data) and ran the the following code :

public static void WriteToCSVFile( System.Data.DataTable dataTable,
string csvPath ) {
OleDbConnection connection = new OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" +
csvPath + ";" +
"Extended
Properties=\"Text;HDR=Yes\"" );
connection.Open();
try {
// Create a CSV file with appropriate column headers
// OleDbCommand command = new OleDbCommand( "CREATE TABLE
[TestDataTable]([IntColumn] INT);", connection, null );
// command.ExecuteNonQuery();

// Populate the CSV file with all data from the dataTable
OleDbDataAdapter adapter = new OleDbDataAdapter( "SELECT * INTO ["
+ dataTable.TableName + "]", connection );
OleDbCommandBuilder builder = new OleDbCommandBuilder( adapter );
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";
adapter.InsertCommand = builder.GetInsertCommand();
adapter.Update( dataTable );
} finally {
connection.Close();
}
}


But get the following error :
System.InvalidOperationException : Dynamic SQL generation is not supported
against a SelectCommand that does not return any base table information.

I tried creating the CSV file by uncommenting the lines above but I get the
following error (which is understandable) :
System.Data.OleDb.OleDbException : Cannot modify the design of table
'TestDataTable'. It is in a read-only database.

I have found the following code in an old posting to
microsoft.public.vb.general which writes an access table to a CSV file using
ADO (so I presume it should be possible to do a similar thing with ADO.Net) :

Sub ExportAccessToTextADO()

Dim cnn As New ADODB.Connection
Dim sqlString As String

cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\My Documents\DB1.mdb;" & _
"Jet OLEDB:Engine Type=4;"

sqlString = "SELECT * INTO [Text;DATABASE=d:\My
Documents\TextFiles].[Plot.csv] FROM [tblPlot]"

cnn.Execute sqlString

cnn.Close
Set cnn = Nothing

End Sub

Any help much appreciated.

Thanks in advance,
Joel Gordon.

 

[Grzegorz Danowski]

Hi,

I want to write an in memory DataTable to a CSV file. Just to prove I can
get it working I created a DataTable with a single column called
"IntColumn"
(containing a couple of rows of data) and ran the the following code :

public static void WriteToCSVFile( System.Data.DataTable dataTable,
string csvPath ) {
OleDbConnection connection = new OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" +
csvPath + ";" +
"Extended
Properties=\"Text;HDR=Yes\"" );

Please read my function:

public static void WriteToCSVFile(System.Data.DataTable dataTable,
string csvFolder)
{
OleDbConnection connection = new OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" +
csvFolder + ";" +
"Extended Properties=\"Text;HDR=Yes\"" );
try
{
connection.Open();
}
catch(OleDbException ex)
{
System.Console.WriteLine(ex.Message);
return;
}
try
{
// Populate the CSV file with all data from the dataTable
OleDbCommand command = new OleDbCommand(
"Insert Into TestDataTable.csv(IntColumn) Values(?)",
connection);
OleDbParameter par = new OleDbParameter(
"intC", OleDbType.Integer);
command.Parameters.Add(par);

DataColumn dc = dataTable.Columns[0]; //to fast access

foreach(DataRow row in dataTable.Rows)
{
par.Value = row[dc];
command.ExecuteNonQuery();
}
}
catch(OleDbException ex)
{
System.Console.WriteLine(ex.Message);
return;
}
finally
{
connection.Close();
}
}

And example of using:

public void TestWrite()
{
TableToCsv.WriteToCSVFile(myDt, "E:\\Data");
}

Notice, Data Source is not file path but folder path.
Regards,
Grzegorz

 

[Joel Gordon]

Użytkownik "Joel Gordon" <[email protected]>
napisał w wiadomości

Hi,

I want to write an in memory DataTable to a CSV file. Just to prove
I can get it working I created a DataTable with a single column
called "IntColumn"
(containing a couple of rows of data) and ran the the following
code :

public static void WriteToCSVFile( System.Data.DataTable
dataTable, string csvPath ) {
OleDbConnection connection = new OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data
Source=" + csvPath + ";" +
"Extended
Properties=\"Text;HDR=Yes\"" );

Please read my function:

public static void WriteToCSVFile(System.Data.DataTable dataTable,
string csvFolder)
{
OleDbConnection connection = new OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" +
csvFolder + ";" +
"Extended Properties=\"Text;HDR=Yes\"" );
try
{
connection.Open();
}
catch(OleDbException ex)
{
System.Console.WriteLine(ex.Message);
return;
}
try
{
// Populate the CSV file with all data from the dataTable
OleDbCommand command = new OleDbCommand(
"Insert Into TestDataTable.csv(IntColumn) Values(?)",
connection);
OleDbParameter par = new OleDbParameter(
"intC", OleDbType.Integer);
command.Parameters.Add(par);

DataColumn dc = dataTable.Columns[0]; //to fast access

foreach(DataRow row in dataTable.Rows)
{
par.Value = row[dc];
command.ExecuteNonQuery();
}
}
catch(OleDbException ex)
{
System.Console.WriteLine(ex.Message);
return;
}
finally
{
connection.Close();
}
}

And example of using:

public void TestWrite()
{
TableToCsv.WriteToCSVFile(myDt, "E:\\Data");
}

Notice, Data Source is not file path but folder path.
Regards,
Grzegorz

Hi Grzegorz,

Thanks for the reply.

Is there a way to write the whole DataTable out in one hit so that the
WriteToCSVFile method does not need to know the number and types of the
columns in the dataTable ?

I hoped to use code similar to the following which I have used
successfully to insert a whole DataTable into a worksheet in an Excel
spreadsheet :

internal static void WriteToExcelFile( DataTable dataTable,
string excelFileName ) {
OleDbConnection connection = new OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + excelFileName + ";" +
"Extended Properties=\"Excel 8.0;HDR=Yes\"" );
WriteDataTable( dataTable, connection );
}

private static void WriteDataTable( DataTable dataTable,
OleDbConnection connection ) {
connection.Open();
try {
// Create workbook with appropriate column headers
OleDbCommand command =
new OleDbCommand( GetCreateString(dataTable),
connection, null );
command.ExecuteNonQuery();

// Populate worksheet with all data from the dataTable
OleDbDataAdapter adapter =
new OleDbDataAdapter(
"SELECT * FROM [" + dataTable.TableName + "]",
connection );
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";
adapter.Update( dataTable );
} finally {
connection.Close();
}
}

private static string GetCreateString( DataTable table) {
string createString = "CREATE TABLE [" + table.TableName + "](\n";
foreach( DataColumn column in table.Columns ){
createString += "[" + column.ColumnName + "] ";
switch( column.DataType.ToString() ){
case "System.String":
createString += "TEXT";
break;
case "System.Int32":
createString += "INT";
break;
case "System.Double":
case "System.Single":
createString += "FLOAT";
break;
case "System.DateTime":
createString += "DATETIME";
break;
default:
throw new ApplicationException( "Unknown type" );
}
createString += ",\n";
}
createString = createString.TrimEnd( new char[]{'\n',','} );
createString += ");";
return createString;
}

Thanks in advance,
Joel.