Query CSV using LINQ C# 2010

Question

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).

I would start by making sure your `DataTable` is being fully populated, and is not missing a record at the beginning. — Robert Harvey, Jul 23 '11 at 04:29
Also, I would consider the possibility that you don't need an OleDb provider to do this. Reading CSV files (assuming they are well-behaved) is relatively easy to do with simple C# code: see http://www.codeproject.com/KB/cs/UsingJetForImport.aspx for the pros and cons of each approach. — Robert Harvey, Jul 23 '11 at 04:33

score 0 · Answer 1 · answered Nov 09 '12 at 00:00

0

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

answered Nov 09 '12 at 00:00

score 0 · Answer 2 · answered Jul 23 '11 at 09:57

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

score 0 · Accepted Answer · edited May 23 '17 at 12:26

0

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.

edited May 23 '17 at 12:26

Community

1
1

answered Jul 25 '11 at 16:15

Robert Tanenbaum

419
4
4

Query CSV using LINQ C# 2010

3 Answers3

Linked