Import Data to CSV File FROM DataTable

In th First step we will Clear the Response Object and then we'll attach a csv file with the Response Object. that will be popup once we'll write the Data into it and End the Response Object.

After Attaching the file we'll write the column Name into the CSV File. and then iterate for all the Records to write them into the CSV file.

"" is used to protect the Data if the data contain ',' (Comma) .

Sample Code:

DataTable dtProducts=GetProductsFromDB();
string attachment = "attachment; filename=products.csv";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", attachment);
HttpContext.Current.Response.ContentType = "application/octet-stream";

//Write Column Names
string str = "ProductNo,Product,SKU,ProductType,Price";
HttpContext.Current.Response.Write(str);
HttpContext.Current.Response.Write(Environment.NewLine);
for(int i =0; i
{
string strRowData="";
for(int jColumns=0; jColumns
{
if(strRowData=="")
strRowData='"'+dtProducts.Rows[i][jColumns].ToString()+'"';
else
{
strRowData=","+'"'+dtProducts.Rows[i][jColumns].ToString()+'"';
}
}
HttpContext.Current.Response.Write(strRowData);
HttpContext.Current.Response.Write(Environment.NewLine);
strRowData="";
}
HttpContext.Current.Response.End();

Read the CSV Data and Save the Data in DataSet.

In this first we will read the data from csv file using the select query and load the data into a dataset.

Query To read data from CSV file: 
SELECT * FROM [test.csv]

OLEDB Connectionstring:
 @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Lakhan\Projects\Testweb\Test\;Extended
Properties=""text;HDR=Yes;FMT=Delimited"""

if you want to consider first row as column then mention HDR=Yes otherwise no.
using System.Data.OleDb;
for(Int32 i=0; i
   {
       Response.Write(dataSetFromCSV.Tables[0].Columns[i].
       ColumnName + "");
   }
the above code is used to print all the column names

Sample Code:
using System.Data.OleDb;
private void ReadCSVFile()
{
 string cnStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data
 Source=E:\Lakhan\Projects\Testweb\Test\;Extended
 Properties=""text;HDR=Yes;FMT=Delimited""";
 OleDbConnection ExcelConnection =
 new OleDbConnection(cnStr);
 OleDbCommand ExcelCommand = new OleDbCommand
 (@"SELECT * FROM [test.csv]",
 ExcelConnection);
 OleDbDataAdapter ExcelAdapter =
 new OleDbDataAdapter(ExcelCommand);
 ExcelConnection.Open();
      
 DataSet dataSetFromCSV = new DataSet();
 ExcelAdapter.Fill(dataSetFromCSV);
 ExcelConnection.Close();
 for(Int32 i=0; i
 {
     Response.Write(dataSetFromCSV.Tables[0].
     Columns[i].ColumnName + "");
 }
}

CSV File's Content:
Customer Number,Last Name,First Name,Address,City,Province,
Postal Code,Balance10001,Smith,Dave,123 Parkside Ave.,London,
ON,N6J 4G6,125.3510002,Pearson,Anne,44 Northside Road,Toronto,
ON,N0M 5L8,38.1210003,Carson,Ronald,12 Talbot Road,London,
ON,N6U 3G8,1024.5610006,Davis,Albert,19 Southam Road,Ajax,
ON,N7J 5H7,-8.5510007,Anderson,Theresa,118 Sarnia Road,
London,ON,N6G 5C6,1181.1210009,Jones,Jason,1008,
Carver Place,Ottawa,ON,N8K 8H4,0.00