Read CSV Using Linq

Read Csv using LINQ

var stuff = from l in File.ReadAllLines(filename)
            let x = l.Split(new [] {',', ' '}, StringSplitOptions.RemoveEmptyEntries)
                     .Skip(1)
                     .Select(s => int.Parse(s))
            select new
            {
                Sum = x.Sum(),
                Average = x.Average()
            };

If you're reading big files and memory use is a concern, then the following will work better using .NET 4:

var stuff = from l in File.ReadLines(filename)
            let x = l.Split(new [] {',', ' '}, StringSplitOptions.RemoveEmptyEntries)
                     .Skip(1)
                     .Select(s => int.Parse(s))
            select new
            {
                Sum = x.Sum(),
                Average = x.Average()
            };

In both cases, the stuff variable contains an enumerable which won't actually be executed until you start reading from it (e.g. inside a foreach loop).

how to get csv file records using LINQ

I think this is what you're looking for:

var query = from line in File.ReadLines(filename)
    let csvLines = line.Split(';')
    from csvLine in csvLines
    where !String.IsNullOrWhiteSpace(csvLine)
    let data = csvLine.Split(',')
    select new
    {
        ID = data[0],
        FirstNumber = data[1],
        SecondNumber = data[2],
        ThirdNumber = data[3]
    };

Using fluent syntax:

var query = File.ReadLines(filename)
    .SelectMany(line => line.Split(';'))
    .Where(csvLine => !String.IsNullOrWhiteSpace(csvLine))
    .Select(csvLine => new {data = csvLine.Split(',')})
    .Select(s => new
    {
        ID = s.data[0], 
        FirstNumber = s.data[1], 
        SecondNumber = s.data[2], 
        ThirdNumber = s.data[3]
    });

The where is there to prevent trying to parse the blank line that's returned by Split when there's a semicolon at the end of a line. An alternative would be to remove that where clause and replace it with

let data = csvLine.Split(',')
where data.Length >= 4

Or, make the equivalent change in the fluent syntax.

read CSV file Save output to LIST using LINQ

After considering the accepted answer, I'd like to suggest a solution that requires less object initializations. If the list is large, this will make a difference.

var query = File.ReadAllLines("test.txt")
.Select(record => record.Split(','))
.Select(tokens => new icSASList(){ ClearNum = tokens[0], MPID = tokens[1] }); 

var clearList = query.ToList();

Oh, yeah, using record.Split(',') is naive - it's normally allowed to have commas in " (quoted) fields, which will break your program. Better use something like http://www.filehelpers.com/.

Linq: Get all values of a group by in csv-format

It's unclear, what is the desired outcome; for instance for

   ID, Priority, Value
    1,      300,  0.90
    2,      400,  0.50
    3,      300,  0.80 
    4,      500,  0.70

according to your requirements (ordering followed by grouping) you'll get the structure below:

Priority (key) Items
     400       {(2, 400, 0.50)}                 # group has 1 item
     300       {(1, 300, 0.90), (3, 300, 0.80)} # group has 2 items 
     500       {(4, 500, 0.70)}

If you want to turn this to comma separated lines (please, note, that you are going to have different values in different lines)

     2, 400, 0.50               # 3 columns : from group with 1 item
     1, 300, 0.90, 3, 300, 0.80 # 6 columns : from group with 2 items
     4, 500, 0.70

You can use the code below

var data = File
  .ReadLines(@"C:\temp\Values.csv")
  .Where(line => !string.IsNullOrWhiteSpace(line)) // To be on the safe side
  .Skip(1)                                         // Skip title  
  .Select(line => line.Split(','))                  
  .Select(items => new {
     ID = long.Parse(items[0]), 
     Priority = int.Parse(items[1]), 
     Value = Decimal.Parse(items[2]),
   })
   .OrderBy(item => item.Value)                    // order by Value
   .GroupBy(item => item.Priority)                 // group by priority 
   .Select(group => string.Join(",", group         // turn group to comma separated
      .Select(item => string.Join(",", item.ID, item.Priority, item.Value))));

File
  .WriteAllLines(@"C:\temp\sorted.csv", data);

If you actually want to sort items with equal Value by Priority, for instance for given

 ID, Priority, Value
  2,      400,  0.50
  4,      500,  0.70
  1,      300,  0.70
  3,      300,  0.80 

you want

 ID, Priority, Value
  2,      400,  0.50
  1,      300,  0.70 # in case of tie (same Values) order by priority
  4,      500,  0.70
  3,      300,  0.80 

you don't want any GroupBy, but ThenBy:

var data = File
  .ReadLines(@"C:\temp\Values.csv")
  .Where(line => !string.IsNullOrWhiteSpace(line)) // To be on the safe side
  .Skip(1)                                         // Skip title  
  .Select(line => line.Split(','))                  
  .Select(items => new {
     ID = long.Parse(items[0]), 
     Priority = int.Parse(items[1]), 
     Value = Decimal.Parse(items[2]),
   })
  .OrderBy(item => item.Value)    // order by Value
  .ThenBy(item => item.Priority)  // ! In case of tie, order by priority
  .Select(item => $"{item.ID},{item.Priority},{item.Value}"); 

File
  .WriteAllLines(@"C:\temp\sorted.csv", new string[] {
     "ID", "Priority", "Value"}.Concat(data)
);

How to query CSV like SQL using LINQ?

I solve my issue this way and i like to share the code.

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        TimeSpan StartTime = TimeSpan.Parse("09:00:00");
        TimeSpan EndTime = TimeSpan.Parse("09:30:00");

        List<PhoneData> oPhoneData = GetPhoneData(@"z:\smdr(backup12-04-2016).csv");
        dgList.DataSource = oPhoneData;

        int incomingCount = (from row in oPhoneData
                             where row.direction == "I"
                             && row.Call_Start.TimeOfDay >= StartTime
                             && row.Call_Start.TimeOfDay <= EndTime
                             && row.Is_Internal == 0
                             && row.continuation == 0
                             && row.call_duration.TotalSeconds > 0
                             && row.party1name != "Voice Mail"
                             && !row.party1name.StartsWith("VM ")
                             select 1).Count();

    }

    public List<PhoneData> GetPhoneData(string strFileName)
    {
        return File.ReadLines(strFileName)
            .Skip(1)
            .Where(s => s != "")
            .Select(s => s.Split(new[] { ',' }))
            .Select(a => new PhoneData
            {
                Call_Start = DateTime.Parse( a[0]),
                call_duration = TimeSpan.Parse(a[1]),
                Ring_duration = int.Parse(a[2]),
                direction = a[4],
                Is_Internal =Convert.ToInt32( a[8]),
                continuation = int.Parse( a[10]),
                party1name = a[13]
            })
            .ToList();
    }
}

public class PhoneData
{
    public DateTime Call_Start { get; set; }
    public TimeSpan call_duration { get; set; }
    public Int32 Ring_duration { get; set; }
    public string direction { get; set; }
    public Int32 Is_Internal { get; set; }
    public Int32 continuation { get; set; }
    public string party1name { get; set; }

}

---

Related Topics