Merge multiple rows using C#

Article
2018-09-13

Question

_{Thursday, September 13, 2018 9:05 AM}

Hi,

I have a csv file with data as follows:

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

All replies (10)

_{Thursday, September 13, 2018 9:54 AM | 1 vote}

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 11:56 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 12:09 PM | 1 vote}

>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

_{Thursday, September 13, 2018 12:18 PM}

Hi Andrey, Will you be able to post some code for this?
Thanks!

_{Friday, September 14, 2018 6:24 AM}

Hi,

I have a csv file with data as follows:

id

R1

R1

R1

R1

R2

R3

R4

R2

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

id

R1

R2

R3

R4

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:33 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: lines, dlmtr and sprtr are defined in the following post.

•

_{Friday, September 14, 2018 6:34 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:38 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:54 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 [email protected].

_{Friday, September 14, 2018 7:12 AM}