How can I load a CSV file into a System.Data.DataTable
, creating the datatable based on the CSV file?
Does the regular ADO.net functionality allow this?
How can I load a CSV file into a System.Data.DataTable
, creating the datatable based on the CSV file?
Does the regular ADO.net functionality allow this?
For those of you wishing not to use an external library, and prefer not to use OleDB, see the example below. Everything I found was either OleDB, external library, or simply splitting based on a comma! For my case OleDB was not working so I wanted something different.
I found an article by MarkJ that referenced the Microsoft.VisualBasic.FileIO.TextFieldParser method as seen here. The article is written in VB and doesn't return a datatable, so see my example below.
You can achieve it by using Microsoft.VisualBasic.FileIO.TextFieldParser dll in C#
We always used to use the Jet.OLEDB driver, until we started going to 64 bit applications. Microsoft has not and will not release a 64 bit Jet driver. Here's a simple solution we came up with that uses File.ReadAllLines and String.Split to read and parse the CSV file and manually load a DataTable. As noted above, it DOES NOT handle the situation where one of the column values contains a comma. We use this mostly for reading custom configuration files - the nice part about using CSV files is that we can edit them in Excel.
Can't resist adding my own spin to this. This is so much better and more compact than what I've used in the past.
This solution:
Here's what I came up with:
It depends on an extension method (
Unique
) to handle duplicate column names to be found as my answer in How to append unique numbers to a list of stringsAnd here's the
BlankToNothing
helper function:I have decided to use Sebastien Lorion's Csv Reader.
Jay Riggs suggestion is a great solution also, but I just didn't need all of the features that that Andrew Rissing's Generic Parser provides.
UPDATE 10/25/2010
After using Sebastien Lorion's Csv Reader in my project for nearly a year and a half, I have found that it throws exceptions when parsing some csv files that I believe to be well formed.
So, I did switch to Andrew Rissing's Generic Parser and it seems to be doing much better.
UPDATE 9/22/2014
These days, I mostly use this extension method to read delimited text:
https://github.com/Core-Techs/Common/blob/master/CoreTechs.Common/Text/DelimitedTextExtensions.cs#L22
https://www.nuget.org/packages/CoreTechs.Common/
UPDATE 2/20/2015
Example:
this is the code i use it but your apps must run with net version 3.5