C# Tutorial - Using The Built In OLEDB CSV Parser
So, a while ago, one of the other writers here wrote a small tutorial on parsing simple CSV files in C#. It mostly just showed off the string split method, and only worked on really simple CSV files - no quoted fields, etc. Well, we got a comment asking about that, so today I sat down thinking I would write up a more robust parser. But as I read through the RFC that describes CSV files, I thought to myself, am I suffering from NIH (not invented here) syndrome? Do I really need to write a full CSV parser?
| And, as you might expect, the answer is no. Not only did I not need to write a parser, I found that there is one that is built into OLEDB subsystem of Windows! And it actually takes fewer lines of code to use this built in parser than it does to do the simple string split algorithm that was in the previous tutorial (and it feels a lot nicer too). I was originally planning on this being a decently long tutorial, when I thought I would write my own parser - but you are actually in for a really short and simple one today. Less for me to write, less for you to read, and more functionality to boot! |
So, without further ado, I think we can jump straight into the code:
using System.Data;
using System.IO; //not used by default
using System.Data.OleDb; //not used by default
namespace CSVParserExample
{
class CSVParser
{
public static DataTable ParseCSV(string path)
{
if (!File.Exists(path))
return null;
string full = Path.GetFullPath(path);
string file = Path.GetFileName(full);
string dir = Path.GetDirectoryName(full);
//create the "database" connection string
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;"
+ "Data Source=\"" + dir + "\\\";"
+ "Extended Properties=\"text;HDR=No;FMT=Delimited\"";
//create the database query
string query = "SELECT * FROM " + file;
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//create an OleDbDataAdapter to execute the query
OleDbDataAdapter dAdapter =
new OleDbDataAdapter(query, connString);
try
{
//fill the DataTable
dAdapter.Fill(dTable);
}
catch (InvalidOperationException /*e*/)
{ }
dAdapter.Dispose();
return dTable;
}
}
}
So, first off, you will need to add the namespaces System.IO and System.Data.OleDb. The first we need because we will be doing some path manipulation, and the second gives us access to what we will need to do CSV parsing. I've created a nice static function here that takes a path to a CSV file and returns the contents of the file in a DataTable (which is really easy to view and manipulate).
The weird thing about all of this is that the CSV file gets treated as a database table. We need to create a connection string with the Jet OLEDB provider, and you set the Data Source to be the directory that contains the CSV file. Under extended properties, 'text' means that we are parsing a text file (as opposed to, say, an Excel file), 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. You can also set FMT to 'FixedLength', for files where the fields are fixed length - but that wouldn't be a CSV file anymore, would it?
The next part to do is create the actual query. In this case, we want everything, so we have a "SELECT *". What are we selecting from? Well, in this somewhat twisted worldview, the directory is the database, so the file is the table we are selecting from.
Now we are into normal OLEDB territory - we create a DataTable that we will be filling with results, and we create a OleDbDataAdapter to actually execute the query. Then (inside of a try block, because it can throw an exception) we fill the data table with the results of the query. Afterwords, we clean up after ourselves by disposing the OleDbDataAdapter, and we return the now filled data table.
And using the now filled data table is extremely simple - we actually talk about it here and here.
And there you go! I'm not sure how parsing CSV could be much easier. If you would like the Visual Studio project I used to test all this, you can grab it here. And, as always, if you have any questions or comments, leave them below.
Posted in C#, All Tutorials by The Tallest |
July 10th, 2008 at 3:27 pm
Great stuff! I would never have thought of that…
July 21st, 2008 at 8:51 am
When I try the above code I receive an exception “Syntax error in FROM clause.”
Excel can read the file with no questions or errors.
The variable ‘query’ is set as follows “SELECT * FROM hba-info.csv”. The exception occurs when executing dAdapter.Fill(dTable);
July 29th, 2008 at 4:44 am
Nice article.
I have found that there can be issues with some foreign characters example the file contained äüö and then the data table held äüö
But very handy for file with non foreign characters.
Thank you.
July 31st, 2008 at 8:33 am
Is there a generalization that allows reading from delimited files that use a separator other than the comma?
July 31st, 2008 at 9:25 am
Is there any provision for using a delimiter other than a comma?
August 19th, 2008 at 10:50 am
Great writeup, that connection string has saved me a lot of time migrating a legacy system record file which I managed to get to CSV format.
August 19th, 2008 at 10:55 am
FMT=Delimited(,)
Substitute that in, in place of the existing parameter, and change the comma to your delimiter.
August 20th, 2008 at 3:32 pm
getting a oleDBException with Cannot update, database or object is read-only.
on line Adapter.Fill()
——————–
try
{
//fill the DataTable
dAdapter.Fill(dTable);
}
catch (InvalidOperationException /*e*/)
{ }
——————–
i only added the headers = yes on the connection string. the file on disk is read/write and not open.
any idea what i could be doing wrong. I had this working before.
the dat file is from a recently opened file and then resaved with a few lines ripped off and resaved as a dat file so the parser will work well. could the adapter think the file is still open for writing.??
August 28th, 2008 at 2:05 pm
This is a great routine! Thanks for posting this gem.
September 18th, 2008 at 2:29 am
No doubt this is great, but i have face one problem with this. In my csv file i had decimal data type, after parsing csv using OLEDBConnection I checked the data and i came to know that my data is converted to integer.
Is there any solution for it???
September 19th, 2008 at 3:56 am
Hi Mehul,
If you want to solve this problem, you may need to create a schema(.ini) file to define your data types in file.
Please refer to following links:
http://doc.ddart.net/mssql/sql2000/html/mdacxml/htm/odbcjetschema_ini_file.htm
http://users.drew.edu/skass/sql/TextDriver.htm
October 4th, 2008 at 1:23 pm
if you have “Syntax error in FROM clause.”…
is beacause u must to clear the whitespaces in the filename.
October 6th, 2008 at 3:07 am
Also, putting sqaure brackets around the file name in the FROM clause of the select string may fix the Syntax error in FROM clause problem.