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

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 ma开发者_开发问答nually 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.

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.

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.

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

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.

The link in Development 4.0's post has dissapeared. The code in that link was the following:

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;
   }
 }
}