通过

LINQ to CSV : Getting data the way you want

  • 项目

Getting data from CSV is one of the mostly used business in applications/tools development.

Here how we can do it in LINQ,

You have a table called Emp with the below details,

CREATE TABLE [dbo].[Emp](

      [Id] [int] IDENTITY(1,1) NOT NULL,

      [FirstName] [varchar](50) NOT NULL,

      [LastName] [varchar](50) NULL,

 CONSTRAINT [PK_Emp] PRIMARY KEY CLUSTERED

(

      [Id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Now you may want to get the CSV out of it. To do that you can save the query output to a .csv file.

The contents of that csv would look like,

1,Wriju,Ghosh

10,Writam,Ghosh

11,Debajyoti,Ghosh

12,Sumitra,Ghosh

82,Tupur,Sanyal

So when you get a single line you can Split() them with a comma (,). The code is very simple,

string[] allLines = File.ReadAllLines(@"E:\Temp\Emp.csv");

var query = from line in allLines

            let data = line.Split(',')

            select new

            {

                ID = data[0],

                FirstName = data[1],

                LastName = data[2]

            };

foreach (var s in query)

{

    Console.WriteLine("[{0}] {1} {2}", s.ID, s.FirstName, s.LastName);

}

Namoskar!!!

Comments

  • Anonymous
    May 24, 2009
    PingBack from http://microsoft-sharepoint.simplynetdev.com/linq-to-csv-getting-data-the-way-you-want/

  • Anonymous
    May 24, 2009
    This is interesting.  I hadn't htought of using LINQ for csv files.  One question comes to mind.  Sometime fields may have a comma (,) with the text like a last name might be O'Brian and csv include text fields in quote eg. 1,"James","O'Brian". Is there some way of tackling this with LINQ?

  • Anonymous
    May 24, 2009
    For more advanced CSV's that include comma-quote delimitation or something else I've found this library works very well http://www.codeproject.com/KB/linq/LINQtoCSV.aspx

  • Anonymous
    May 24, 2009
    My understanding is that CSV is a full standard that requires handling of commas within fields, by surrounding the field with quotation marks, and converting single quotation marks with two quotation marks.  If you really want code that handles true CSV, you'd need to incorporate this logic.  But aside from that, it's a good simple sample of LINQ. Also, this example is really just using LINQ to loop through a string array.  When I think of a "LINQ to {something}", I think more of a full provider, like: http://www.codeproject.com/KB/linq/LINQtoCSV.aspx

  • Anonymous
    February 06, 2010
    There can be a performance issue with the File.ReadAllLines(@"E:TempEmp.csv"); You are reading the entire file in memory at once. This can surely be optimised with extension methods but great example anyway.

  • Anonymous
    February 18, 2010
    The comment has been removed