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?
I have been using
OleDb
provider. However, it has problems if you are reading in rows that have numeric values but you want them treated as text. However, you can get around that issue by creating aschema.ini
file. Here is my method I used:Here's a solution that uses ADO.Net's ODBC text driver:
Once filled, you can value properties of the datatable, like ColumnName, to make utilize all the powers of the ADO.Net data objects.
In VS2008 you can use Linq to achieve the same effect.
NOTE: This may be a duplicate of this SO question.
With Cinchoo ETL - an open source library, you can easily convert CSV file to DataTable with few lines of code.
For more information, please visit codeproject article.
Hope it helps.
Here's an excellent class that will copy CSV data into a datatable using the structure of the data to create the DataTable:
A portable and efficient generic parser for flat files
It's easy to configure and easy to use. I urge you to take a look.
I came across this piece of code that uses Linq and regex to parse a CSV file. The refering article is now over a year and a half old, but have not come across a neater way to parse a CSV using Linq (and regex) than this. The caveat is the regex applied here is for comma delimited files (will detect commas inside quotes!) and that it may not take well to headers, but there is a way to overcome these). Take a peak: