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
– Robert Harvey, Commented Jul 23, 2011 at 4: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 codeproject.com/KB/cs/UsingJetForImport.aspx for the pros and cons of each approach. — Robert Harvey
– Robert Harvey, Commented Jul 23, 2011 at 4:33

Community · Accepted Answer · 2017-05-23 12:26:52Z

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, 2017 at 12:26

CommunityBot

11 silver badge

answered Jul 25, 2011 at 16:15

Robert Tanenbaum

4194 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stuart · Accepted Answer · 2011-07-23 09:57:53Z

0

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

answered Jul 23, 2011 at 9:57

Stuart

66.9k7 gold badges116 silver badges168 bronze badges

Comments

user677607 · Accepted Answer · 2012-11-09 00:00:54Z

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 9, 2012 at 0:00

user677607

Collectives™ on Stack Overflow

Query CSV using LINQ C# 2010

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related