How to import comma-delimited csv file containing commas inside the field value?
In this article I am going to explain about how to import csv file data into data table and fields are defined in schema file.
Description
Recently I saw one question In forum someone is asked not able to import exact value in comma-delimited CSV file containing commas inside the field value. For example if I have CSV file like below
In that above CSV file I have one salary field value is 15,000 I read that CSV file using normal OLEDB code it return value empty value because of comma present in the inside see below image after read data into the data table. 
The reason was that issue comma is present inside of that field value, so Jet engine is not able read that field value.Schema.ini
The solution for the error is we need to create schema file and then read CSV file data to load into datatable.
What is schema file? why we need to create that file?
Schema file is nothing but it is used to defined what are the fields are present in the CSV file and also defined in the schema file how the fields are separated in that CSV file like "comma" or "pipe separated" etc. before we read data from that CSV file.
For example,
I have use three fields in that CSV file eno, empname,sal. Now I am going to defined that fields in the schema file like below
//Create Schema file to defined CSV column types
void CreateIniFile(string filename)
{
StreamWriter sw;
try
{
sw = File.CreateText(Server.MapPath("Schema.ini"));
sw.WriteLine("[" + filename + "]");
sw.WriteLine("ColNameHeader=True");
//Here i mention it is comma separated file to split field based on comma if you use pipe separated file then change pipe symbol below here
sw.WriteLine("Format=Delimited(,)");
sw.WriteLine("Col1=\"eno\" Text Width 1000");
sw.WriteLine("Col2=\"empname\" Text Width 1000");
sw.WriteLine("Col3=\"sal\" Memo");
sw.Close();
}
catch (Exception ex)
{
}
}
Here I mention column3 salary field as memo to read data.
Now you can create ExportToDataTable method in the code behind
//Export CSV data to DataTable
void ExportToDataTable(string filename)
{
//I have placed curriculam file at server path so i assign datasource as root path if you placed D:\ etc. then use datasource as D:\ something
string dsource = Server.MapPath("") + "\\";
string strCsvFilePath = dsource + "\\" + filename;
//Fetch the location of CSV file
string strFilePath = dsource;
string strSql = "SELECT * FROM [" + filename + "]";
string strCSVConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";" + "Extended Properties='text;HDR=YES;'";
OleDbDataAdapter oleda = new OleDbDataAdapter(strSql, strCSVConnString);
DataTable dtCSV = new DataTable();
oleda.Fill(dtCSV);
/***AFTER EXPORT Delete SCHEMA FILE START ***/
if (System.IO.File.Exists(Server.MapPath("Schema.ini")))
{
System.IO.File.Delete(Server.MapPath("Schema.ini"));
}
/***AFTER EXPORT Delete SCHEMA FILE END ***/
GridView1.DataSource = dtCSV;
GridView1.DataBind();
//***END*******
}
In the above code I have read that data using JET Engine before load data schema file information fired and then only data load in the data. This time make sure in data table salary value come correctly like below
That's all no need to worry if you read comma separated csv file containing commas inside the field value. Use this concept to read data for all data reading concept using JET engine.
No need to use loop to read pipe separated data file instead of that looping concept just you can use this code to read all data in single line. Just need to change in CreateIniFile method separated "|" instead of comma.Full Source Code
using System.Data.OleDb;
using System.IO;
public partial class CSV_Test : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
//Here you can use FileUpload control save file etc.
string filename = "Sample.csv";
//Create ini file to defined CSV columns and type
CreateIniFile(filename);
//Export data to data table from CSV file
ExportToDataTable(filename);
}
//Create Schema file to defined CSV column types
void CreateIniFile(string filename)
{
StreamWriter sw;
try
{
sw = File.CreateText(Server.MapPath("Schema.ini"));
sw.WriteLine("[" + filename + "]");
sw.WriteLine("ColNameHeader=True");
//Here i mention it is comma separated file to split field based on comma
sw.WriteLine("Format=Delimited(,)");
//sw.WriteLine("DateTimeFormat=dd-MMM-yyyy");
sw.WriteLine("Col1=\"eno\" Text Width 1000");
sw.WriteLine("Col2=\"empname\" Text Width 1000");
sw.WriteLine("Col3=\"sal\" Memo");
sw.Close();
}
catch (Exception ex)
{
}
}
//Export CSV data to DataTable
void ExportToDataTable(string filename)
{
//I have placed curriculam file at server path so i assign datasource as root path if you placed D:\ etc. then use datasource as D:\ something
string dsource = Server.MapPath("") + "\\";
string strCsvFilePath = dsource + "\\" + filename;
//Fetch the location of CSV file
string strFilePath = dsource;
string strSql = "SELECT * FROM [" + filename + "]";
string strCSVConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";" + "Extended Properties='text;HDR=YES;'";
OleDbDataAdapter oleda = new OleDbDataAdapter(strSql, strCSVConnString);
DataTable dtCSV = new DataTable();
oleda.Fill(dtCSV);
/***AFTER EXPORT Delete SCHEMA FILE START ***/
if (System.IO.File.Exists(Server.MapPath("Schema.ini")))
{
System.IO.File.Delete(Server.MapPath("Schema.ini"));
}
/***AFTER EXPORT Delete SCHEMA FILE END ***/
GridView1.DataSource = dtCSV;
GridView1.DataBind();
//***END*******
}
}Source Code
Here I attached the above source code as an attachment, download it and test it.
Client Side : ASP.NET
Code Behind : C#Conclusion
I hope this source code is help you to solve comma separated issue in your code.