Can a .csv file be used as a data source in Visual Studio 2008?

Question

I'm pretty new to C# and Visual Studio. I'm writing a small program that will read a .csv file and then write the records read to a SQL Server database table. I can manually parse the .csv file, but I was wondering if it is possible to somehow "describe" the .csv file to Visual Studio so that I can use it as a data source? I should mention that the first two lines in the .csv file contain header information and the following lines are the actual comma-delimited data.

Also, I should mention that this program is a stand-alone console program with no user interface.

Answer 1

If you have a 2 line header, it's not a standard CSV file.

In this case, the automatic tools won't work, and you'll have to revert to parsing the file manually.

If you want to remove one of the header lines, you might be able to use this technique of parsing CSV files into an ADO.NET DataTable.

If not, however, the TextFieldParser in the Microsoft.VisualBasic.dll assembly (usable from C# too) makes parsing CSV files very simple.

Answer 2

This is a great example of using the power of LINQ. Here's a quick reference with an example of how to do it.

The run down is this. You can read in your CSV to a string array, then use LINQ to query against that collection. As Reed points out though, you'll have to code around your header line, as it will throw off your query.

You can also use the TextFieldParser too to handle escaping commas. Here's an example on thinqlinq that uses the TextFieldParser to parse the file, and a LINQ query to get the results. It even has a unit test to make sure escaped commas are handled.

Answer 3

To parse it manually is very simple, and you could have a program that parses it, strips out the first two unnecessary lines and then feeds it directly to SSIS.

Here is a link for using LINQ to read it in: http://blogs.msdn.com/wriju/archive/2009/05/24/linq-to-csv-getting-data-the-way-you-want.aspx

Answer 4

Using The Built In OLEDB CSV Parser via C# in order to parse a CVS file. You can find a sample here

It basically lets you treat the csv file like a database table.