CSV parsing using OLEDB
For a few days I did run into the problem of importing CSV data with OLEDB. The first thing I tried was creating my own parser, but later I found an easy built in way. I show both methods here.
My own parser is simple based on the split string functionality.
//create the table
DataTable dt = new DataTable("Import");
dt.Columns.Add("col1", typeof(string));
dt.Columns.Add("col2", typeof(string));
dt.Columns.Add("col3", typeof(string));
//read the data
using (StreamReader sr = new StreamReader(filepath))
{
int i = 0;
while (!sr.EndOfStream)
{
//skip the header
while (i < 1)
{
sr.ReadLine();
i++;
}
string[] text = sr.ReadLine().Split(',');
DataRow dr = dt.NewRow();
dr["col1"] = text[0];
dr["col2"] = text[1];
dr["col3"] = text[2];
dt.Rows.Add(dr);
}
} |
The build in CVS parser uses a connection string and a SQL query to handle the CVS data. The connection string is configured according to:
• Provider: Jet OLEDB
• Data source: The directory that contains the CSV file
• Extended properties: ‘text’ means that we are parsing a text, the HDR property can be set to ‘Yes’ (the first row in the CSV files is header information) or ‘No’ (there is no header row), and setting the FMT property set to ‘Delimited’ essentially says that we will be working with a comma separated value file.
public static DataTable ParseCSV(string filePath)
{
if (!File.Exists(filePath))
{
return null;
}
string fileName = Path.GetFileName(filePath);
string dirName = Path.GetDirectoryName(filePath);
//connection string
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source="" + dirName + "\";" + "Extended Properties="text;HDR=No;FMT=Delimited"";
string query = "SELECT * FROM " + fileName;
DataTable dt = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter(query, connString);
try
{
adapter.Fill(dt);
}
catch (InvalidOperationException)
{ }
adapter.Dispose();
return dt;
} |
