Query CSV using LINQ C# 2010

I follow the tutorial here to query content from CSV file: http://rudesyle.wordpress.com/2008/01/28/using-linq-to-query-a-csv-file/

However, the result misses the first row which is "Mets","New York","NL"

After some testing,开发者_运维问答 I realize that if I have a empty line at the top of the file, the result is as expected.

Bellow is my code and .csv file

"teams.csv"

"Mets","New York","NL"
"Marlins","Florida","NL"
"Orioles","Baltimore","AL"
"Pirates","Pittsburgh","NL"
"Phillies","Philadelphia","NL"

Program:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace CSVQuery
{
    class Program
    {
        static void QueryCsv()
        {

            OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\;Extended Properties='Text;HDR=No;FMT=Delimited'");
            OleDbCommand cmd = new OleDbCommand(@"SELECT * FROM ..\..\teams.csv", cn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);

            cn.Open();

            DataTable dt = new DataTable();
            da.Fill(dt);
            var teams = from r in dt.AsEnumerable() where r.Field<string>(2) == "NL"
                            select new { City = r.Field<string>(0),
                            TeamName = r.Field<string>(1) };

            foreach (var team in teams)
            {
                Console.WriteLine(String.Format("The {0} {1}", team.TeamName, team.City));
            }

            Console.ReadLine();

            cn.Close();

        }
        static void Main(string[] args)
        {
            CSVQuery.Program.QueryCsv();
        }
    }
}

Actual result:

The Florida Marlins
The Pittsburgh Pirates
The Philadelphia Phillies

Expected result:

The New York Mets
The Florida Marlins
The Pittsburgh Pirates
The Philadelphia Phillies

The question is that why I can't query the first line of the teams.csv file? (I need to add 1 empty line at the top so that I can receive expected result).

---

This post Reading CSV file with OLEDB ignores first line even with HDR=No in Connection String suggests that the problem is because the full path is in the Select string. Try putting the path in the connection string and just put the file name in the Select string. I cannot imagine why this should make a difference, but that is what was suggested over there.

---

It looks like the first row is being interpreted as a header row - despite you supplying HDR=NO

You could try to work around this by providing a schema.ini file to see if that works - http://msdn.microsoft.com/en-us/library/ms709353.aspx

However, if you can, I'd actually recommend changing import mechanism - I've used the Lumenworks library for CSV several times - it's fast and good - http://www.codeproject.com/KB/database/CsvReader.aspx

---

There is a LINQ to CSV Library available implemented using .NET 3.5 on CodeProject you can find it here, http://www.codeproject.com/Articles/25133/LINQ-to-CSV-library