Skip to main content

How to pivot complex list object into comma delimited CSV string in c#


Introduction

In SQL, PIVOT Function used to convert rows to the column.Likewise , In c# we can use string.join method to pivot integer and string collection easily to an any delimeted character . In case of complex objects really it is pain to pivot or transpose into some specified format like CSV  formatted string .In this article , I would like  to explain how to pivot complex list object into comma delimeted CSV string of dynamic columns.Let us look into this,

Sample Use  Case

We need to convert the list object elements to specific dynamic columned CSV Format string. For example, consider this employee list object

var employees = new List { 
                new Employee {Id="1", Name="Sekar" , Address="Valapady" } ,
                new Employee {Id="2", Name="Divya" , Address="Salem" },
                new Employee {Id="3", Name="Iniyan" , Address="Belur" },
                new Employee {Id="4", Name="Vijai" , Address="Attur" },
                new Employee {Id="5", Name="Baskar" , Address="P.G.Palayam" },
                new Employee {Id="6", Name="Baskaran" , Address="U.G.Palayam" }
            };

Expected Output

If we need to transpose list elements to additional 1 dynamic columned CSV Formatted string ,expected output should look like this
If we need to transpose list elements to additional 2 dynamic columned CSV Formatted string , then expected output should look like this


 Approach

Transpose Algorithm

1. Let sb = new  StringBuilder() object , List<t> items (input), int noOfTransposeColumns(input)
2. Get propertyNames from  generic Type using reflection during  runtime
3. Add Dynamic column names for header : Let i=1; Iterate  Until i&lt;= noOfTransposeColumns , If true goto step 3.1 else goto step 4 , i++
        3.1. propertyNames.AddRange(propertyNames+i) 
4. Add Header - Iterate : propertyNames until EOL(end of the list) If EOL goto else goto 5 step 4.1
        4.1. Let prop = propertyNames[currentIndex]
        4.2. sb.Append(prop + “,”)
5. sb.Remove(sb.Length-1 ,1) Remove last comma delimeter
6. sb.Append(“/r/n”)  new line character
7. Get listCount = noOfTransposeColumns +1
8. Get splitLists , Iterate to split list into elements  of list based on listCount
9. Add data  - Iterate : Let i=0 ;Until i&lt; splitLists ,  If true goto 9.1 else goto 10 , i++
        9.1. Let itemList= splitLists[i]
        9.2. Iterate : propertyNames until EOL(end of the list) if EOL goto 9.3 else goto 9.2.1
                9.2.1. Let prop = propertyNames[currentIndex]
                9.2.2. Get the index of current prop
                9.2.3. Index != 0 goto else 9.2.3.1 goto 9.2.4
                        9.2.3.1. Get propertyName by removing extra index
                9.2.4. itemList.Count&gt; index goto 9.2.4.1 else 9.2.5
                        9.2.4.1. propValue = itemList[index].GetType().GetProperty(propertyName).GetValue(itemList[index],null) from the reflection
                        9.2.4.2. sb.Append(propValue + “,”) goto 9.3
                9.2.5. sb.Append(“” + “,”)
       9.3. sb.Remove(sb.Length-1 ,1) Remove last comma delimeter
       9.4. sb.Append(“/r/n”)  new line character
10. sb.ToString(),transposed csv formatted string

How to compute its time complexity?


=>{O(3) +O(1)+ O(N) + O(N) + O(1) +O(1) + O(1) + O(log(N)) + O(N) *O(N) + O(1) + O(1) + O(1)}
=>{O(10)+ O(N) + O(N) + O(log(N)) + O(N) *O(N)}
=>{O(10)+ O(2N) + O(log(N)) + O(N^2)}
=>{O(N)+ O(log(N) ) + O(N^2)}
=>{O(N^2)}, Is quadratic time , its execution is time propotional to the square of the input size

Pivot Function

Based on the algorithm, I implemented the c# code 

static string TransposeCSV(List items,int noOfTransposeColumns)
        {
            if (items.Count > 0)
            {
                StringBuilder sb = new StringBuilder();
                List propertyNames = items[0].GetType().GetProperties().Select(p => p.Name).ToArray().ToList();
                for(var i=1; i<=noOfTransposeColumns; i++)
                {
                    propertyNames.AddRange(items[0].GetType().GetProperties().Select(p => p.Name + i).ToArray().ToList());
                }
                for (int j = 0; j < propertyNames.Count; j++)
                {
                    sb.Append("\"" + propertyNames[j] + "\"" + ',');
                }
                sb.Remove(sb.Length - 1, 1);
                sb.Append("\r\n");
                var listCount =noOfTransposeColumns+1;
                var splitList = SplitList(items, listCount).ToList();
                for (int i=0; i< splitList.Count; i++)
                {
                    var itemList = splitList[i];
                    foreach (var prop in propertyNames)
                    {
                        string propertyName = prop;
                        int index = GetIndex(propertyName);
                        if(index!=0)
                        {
                            propertyName = prop.Substring(0, prop.Length - 1);
                        }
                        if(itemList.Count> index)
                        {
                            object propValue = itemList[index].GetType().GetProperty(propertyName).GetValue(itemList[index], null);
                            sb.Append("\"" + propValue + "\"" + ",");
                        }
                        else
                        {
                            sb.Append("\"" + "" + "\"" + ",");
                        }
                       
                    }
                    sb.Remove(sb.Length - 1, 1);
                    sb.Append("\r\n");

                }
                return sb.ToString();
            }
            else
            {
                return string.Empty;
            }

        }

        static int GetIndex(string prop)
        {
            var lastStr = prop.Substring(prop.Length - 1, 1);
            int index;
            int.TryParse(lastStr, out index);
            return index;
        }

        static IEnumerable> SplitList(List collections, int nSize )
        {
            for (int i = 0; i < collections.Count; i += nSize)
            {
                yield return collections.GetRange(i, Math.Min(nSize, collections.Count - i));
            }
        }

Full Implementation

employee.cs 

  public class Employee
    {
        public string Id { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
    }
program.cs 

 class Program
    {
        static void Main(string[] args)
        {
            var csvString = TransposeCSV(GetEmployees(),2);
            Console.WriteLine(csvString);
            Console.ReadKey();
        }

        static List GetEmployees()
        {
            var employees = new List { 
                new Employee {Id="1", Name="Sekar" , Address="Valapady" } ,
                new Employee {Id="2", Name="Tamil" , Address="Salem" },
                new Employee {Id="3", Name="Iniyan" , Address="Belur" },
                new Employee {Id="4", Name="Vijai" , Address="Attur" },
                new Employee {Id="5", Name="Baskar" , Address="P.G.Palayam" },
                new Employee {Id="6", Name="Baskaran" , Address="U.G.Palayam" }
            };
            return employees;
        }

      

        static string TransposeCSV(List items,int noOfTransposeColumns)
        {
            if (items.Count > 0)
            {
                StringBuilder sb = new StringBuilder();
                List propertyNames = items[0].GetType().GetProperties().Select(p => p.Name).ToArray().ToList();
                for(var i=1; i<=noOfTransposeColumns; i++)
                {
                    propertyNames.AddRange(items[0].GetType().GetProperties().Select(p => p.Name + i).ToArray().ToList());
                }
                for (int j = 0; j < propertyNames.Count; j++)
                {
                    sb.Append("\"" + propertyNames[j] + "\"" + ',');
                }
                sb.Remove(sb.Length - 1, 1);
                sb.Append("\r\n");
                var listCount =noOfTransposeColumns+1;
                var splitList = SplitList(items, listCount).ToList();
                for (int i=0; i< splitList.Count; i++)
                {
                    var itemList = splitList[i];
                    foreach (var prop in propertyNames)
                    {
                        string propertyName = prop;
                        int index = GetIndex(propertyName);
                        if(index!=0)
                        {
                            propertyName = prop.Substring(0, prop.Length - 1);
                        }
                        if(itemList.Count> index)
                        {
                            object propValue = itemList[index].GetType().GetProperty(propertyName).GetValue(itemList[index], null);
                            sb.Append("\"" + propValue + "\"" + ",");
                        }
                        else
                        {
                            sb.Append("\"" + "" + "\"" + ",");
                        }
                       
                    }
                    sb.Remove(sb.Length - 1, 1);
                    sb.Append("\r\n");

                }
                return sb.ToString();
            }
            else
            {
                return string.Empty;
            }

        }

        static int GetIndex(string prop)
        {
            var lastStr = prop.Substring(prop.Length - 1, 1);
            int index;
            int.TryParse(lastStr, out index);
            return index;
        }

        public static IEnumerable> SplitList(List collections, int nSize )
        {
            for (int i = 0; i < collections.Count; i += nSize)
            {
                yield return collections.GetRange(i, Math.Min(nSize, collections.Count - i));
            }
        }
    }

Conclusion

I hope this article is helpful to you. Please post your comments or if you have any different ideas .Happy coding!!!
Labels:

Comments

Post a Comment

Popular posts from this blog

How to resolve ASP.NET core web API 2 mins timeout issue

Introduction We are in the new world of microservices and cross-platform applications which will be supported for multiple platforms and multiple heterogeneous teams can work on the same application. I like ASP.NET Core by the way its groomed to support modern architecture and adhere to the software principles. I am a big fan of dot net and now I become the craziest fan after seeing the sophisticated facility by dot net core to support infrastructure level where we can easily perform vertical and horizontal scaling. It very important design aspect is to keep things simple and short and by the way, RESTFul applications are build and it is a powerful mantra for REST-based application and frameworks. Some times we need to overrule some principles and order to handle some situations. I would like to share my situation of handling HTTP long polling to resolve the ASP.Net core 2 mins issue. What is HTTP Long polling? In the RESTFul term, when a client asks for a query from the serv
6 comments
Read more

How to Resolve ASP.NET Core Key Protection Ring Problem in AWS Lambda

Introduction When it comes to server less web application design using asp.net core razor pages, we definitely need to consider a factor of data protection key management and its lifetime in asp.net core. I developed a site using AWS toolkit of ASP.NET Core Razor Pages. The main advantage of ASP.NET Core is cross-platform from where we can deploy our application in MAC, Linux or windows. I deployed my site initially in IIS Server from which I got the results as expected .but later period I decided to host my site in AWS Lambda in order to meet our client requirement. Strangely, I got unexpected behavior from my site. I just refer the cloud information Lambda Log to identify or pinpoint the case, I got the error Information like “Error Unprotecting the session cookie” from the log. In this article, I tried to explain the root cause of the problem and its solution to overcome such kind of issue. Data Protection in ASP.NET Core This is feature in ASP.NET Core which acts as repl
4 comments
Read more

Which linq method performs better: Where(expression).FirstorDefault() vs .FirstOrDefault(expression)

 Introduction When it comes to LINQ, we always have multiple options to execute the query for the same scenario. Choosing correct one is always challenging aspect and debatable one. In one of our previous articles   Any Vs Count  , we have done performance testing about best LINQ methods over .NET types. In this article, I would like to share about  Where(expression).FirstorDefault() vs .FirstOrDefault(expression) Approaches Performance testing for  Where(expression).FirstorDefault() vs .FirstOrDefault(expression) is very interesting IEnumerable<T> or ICollcetion<T>  .FirstOrDefault(expression) is better than  Where(expression).FirstorDefault() Public API To check the performance, I need some amount of data which should already available. So I decided to choose this  public api . Thanks to publicapis Public API Models Entry class using System ; using System.Collections.Generic ; using System.Text ;   namespace AnyVsCount { public class Entry { pub
Post a Comment
Read more