none
Merge multiple rows using C# RRS feed

  • Question

  • Hi,

    I have a csv file with data as follows:

    id C1 C2 C3 C4
    R1 12      
    R1   13    
    R1     14  
    R1       15
    R2   13    
    R3 12      
    R4     13  
    R2 12     15

    I would like the data to be merged based on the 'id' in column 1 so that the output is as follows :

    id C1 C2 C3 C4
    R1 12 13 14 15
    R2 12 13 15
    R3 12
    R4 13

    Thursday, September 13, 2018 9:05 AM

All replies

  • Quick solution, tab as delimiter in csv:

                var data = new List<List<string>>();
                string line;
                using (StreamReader sr = new StreamReader(@"X:\test.csv"))
                    while ( (line = sr.ReadLine()) != null)
                        data.Add(line.Split('\t').ToList());
    
                var result = from elements in data
                        group elements by elements[0] into g
                        select new
                        {
                            id = g.Key,
                            C1 = g.Max(e => e[1]), // Max as an aggregate method
                            C2 = g.Max(e => e[2]),
                            C3 = g.Max(e => e[3]),
                            C4 = g.Max(e => e[4])
                        };
    
                foreach (var r in result)
                    Console.WriteLine($"{r.id,2} {r.C1,2} {r.C2,2} {r.C3,2} {r.C4,2}");
    Header is in the first row.


    Thursday, September 13, 2018 9:54 AM
  • HI Cieslak,Thanks for the update but I want everything to be arranged at a time.  Above is only example like that I have 1000 records which I need to merge into single row.

    Please provide the permanent solution to it.

    Thursday, September 13, 2018 11:56 AM
  • >Please provide the permanent solution to it.

    I hope that "permanent solution" you will be able to develop by your self.

    But one option I can recommend.

    Take your CSV file as mapped external table (or import it) for any of SUBD and use regular SELECT with GROUP BY to get what you need.

    Best regards,

    Andrey

    • Marked as answer by bhargavi.m Thursday, September 13, 2018 12:17 PM
    • Unmarked as answer by bhargavi.m Thursday, September 13, 2018 12:17 PM
    Thursday, September 13, 2018 12:09 PM
  • Hi Andrey, Will you be able to post some code for this?
    Thanks!
    Thursday, September 13, 2018 12:18 PM
  • Hi,

    I have a csv file with data as follows:

    id C1 C2 C3 C4
    R1 12      
    R1   13    
    R1     14  
    R1       15
    R2   13    
    R3 12      
    R4     13  
    R2 12     15

    I would like the data to be merged based on the 'id' in column 1 so that the output is as follows :

    id C1 C2 C3 C4
    R1 12 13 14 15
    R2 12 13 15
    R3 12
    R4 13

    Notice, you did not mention if collisions are possible, and what should happen then.

    Therefore, I'm considering collisions are possible, and the collided value should be appended to the already existing value(s). For instance: 15 colliding with 12, would result in 12,15 where the comma is there to separate the values.

    All that said, the solution is r-i-d-i-c-u-l-o-u-s simple. All that's necessary is a GroupBy well implemented:

    var lines = ... // lines read from the CSV file

    var query = lines.GroupBy(...);

    Isn't it r-i-d-i-c-u-l-o-u-s simple?

    Friday, September 14, 2018 6:24 AM
  • Here's the code:

    var query=lines
        .GroupBy
        (
            line=>Regex
                .Replace
                (
                    line
                    ,string.Concat(dlmtr,".+$")
                    ,""
                )
            ,line=>Regex
                .Replace
                (
                    line
                    ,string.Concat(".+?",dlmtr,"(.+)$")
                    ,"$1"
                )
                .Split(dlmtr[0])
            ,(key,elms)=>
            {
                var rslt=elms.First();
                foreach(var e in elms.Skip(1))
                    rslt=rslt.Zip(e, (a,b)=>
                         string.IsNullOrEmpty(b)?
                                  a
                                  :string.IsNullOrEmpty(a)?
                                  b
                                  :string.Concat(a,sprtr,b)
                         ).ToArray();
                return string.Concat
                    (
                        key
                        ,dlmtr
                        ,string.Join(dlmtr,rslt)
                    );
            }
        )
        ;
    

    Notice: linesdlmtr and sprtr are defined in the following post.

    Friday, September 14, 2018 6:33 AM
  • var lines=new[] // CSV file lines
    {
        "Id;C1;C2;C3;C4;C5"
        ,"R1;11;12;;;15"
        ,"R2;21;22;23;;25"
        ,"R2;26;27;;;"
        ,"R3;36;37;;39;"
        ,"R1;;17;18;;20"
        ,"R1;51;52;;;55"
        ,"R2;66;;68;;70"
    };
    const string dlmtr=";"; // CSV lines' delimiter
    const string sprtr=","; // separator in case of collision
    

    Friday, September 14, 2018 6:34 AM
  • You can output the values, this way:

    foreach(string ss in query){
        foreach(string s in ss.Split(dlmtr[0])){
            Console.Write("{0,11}",s);
        }
        Console.WriteLine();
    }
    
    The output:
    
             Id         C1         C2         C3         C4         C5
             R1      11,51   12,17,52         18              15,20,55
             R2   21,26,66      22,27      23,68                 25,70
             R3         36         37                    39           
    

    Friday, September 14, 2018 6:38 AM
  • Hi bhargavi.m,

    Here is sample code by using LINQ for your reference.

    using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ConsoleApp24
    {
        class Program
        {
            static void Main(string[] args)
            {
    
                List<MyClass> values = File.ReadAllLines(@"D:\Data\Excel\Test2.csv")
                                               .Skip(1)
                                               .Select(v => MyClass.FromCsv(v))
                                               .ToList();
    
    
                var result = values.GroupBy(t => t.Id).Select(g => new MyClass {
                    Id = g.Key,
                    C1 = g.OrderByDescending(t=>t.C1).FirstOrDefault().C1,
                    C2 = g.OrderByDescending(t => t.C2).FirstOrDefault().C2,
                    C3 = g.OrderByDescending(t => t.C3).FirstOrDefault().C3,
                    C4 = g.OrderByDescending(t => t.C4).FirstOrDefault().C4,
                }).ToList();
    
            }
        }
    
        public class MyClass
        {
            public string Id { get; set; }
            public string C1 { get; set; }
            public string C2 { get; set; }
            public string C3 { get; set; }
            public string C4 { get; set; }
    
            public static MyClass FromCsv(string csvLine)
            {
                string[] values = csvLine.Split(',');
                MyClass dailyValues = new MyClass();
                dailyValues.Id = values[0];
                dailyValues.C1 = values[1];
                dailyValues.C2 = values[2];
                dailyValues.C3 = values[3];
                dailyValues.C4 = values[4];
                return dailyValues;
            }
        }
    }
    

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 14, 2018 6:54 AM
    Moderator
  • Here's the code:

    var query=lines
        .GroupBy
        (
            line=>Regex
                .Replace
                (
                    line
                    ,string.Concat(dlmtr,".+$")
                    ,""
                )
            ,line=>Regex
                .Replace
                (
                    line
                    ,string.Concat(".+?",dlmtr,"(.+)$")
                    ,"$1"
                )
                .Split(dlmtr[0])
            ,(key,elms)=>
            {
                var rslt=elms.First();
                foreach(var e in elms.Skip(1))
                    rslt=rslt.Zip(e, (a,b)=>
                         string.IsNullOrEmpty(b)?
                                  a
                                  :string.IsNullOrEmpty(a)?
                                  b
                                  :string.Concat(a,sprtr,b)
                         ).ToArray();
                return string.Concat
                    (
                        key
                        ,dlmtr
                        ,string.Join(dlmtr,rslt)
                    );
            }
        )
        ;

    Notice: linesdlmtr and sprtr are defined in the following post.


    Isn't it r-i-d-i-c-u-l-o-u-s simple?

    Friday, September 14, 2018 7:12 AM