Reading CSV file and converted to transposed table

Question

I need to read from a CSV file (separated via “;”) and a new file should be created containing the transposed (rotated) table:

my input file:

The tool shall be able to receive the filename of the table from the user and load the table to transpose the content.
The tool shall be able to save the transposed table in a new file with the filename of the input file and extended with “transposed” (“filename_transposed.csv”).

my Code

public void ReadCsv()
            {
                // open the file "data.csv" which is a CSV file with headers
                using (CsvReader csv = new CsvReader(
                                       new StreamReader("C:\\Users\\moki\\Downloads\\Input.csv"), true))
                {
                    int fieldCount = csv.FieldCount;

                    string[] headers = csv.GetFieldHeaders();
                    while (csv.ReadNextRecord())
                    {
                        for (int i = 0; i < fieldCount; i++)
                            Console.WriteLine(string.Format("{0}\n{1}",
                                          headers[0], csv[i]) );

                    }
                }
Console.ReadLine();

my Result

Think of the logical steps you need to do to get it done. Then turn each steps into code. — blaze_125, Jul 12 '18 at 13:39
People trying out things for you is not learning. Can you show us what you have tried and show us a problem you want us to solve? — Alen Genzić, Jul 12 '18 at 13:40
You want to turn rows, into columns... An easy way to get started would be to create a bunch of `List`; one per row. Then each list would represent a column of data. — blaze_125, Jul 12 '18 at 13:46
Can you add a reference to the CsvReader you are using? As far as I know, it's not part of the Framework. — Bernard Vander Beken, Jul 12 '18 at 14:51

blaze_125 · Answer 1 · 2018-07-12T15:27:39.453

Since we're providing answers...

using System;
using System.Collections.Generic;
using System.Linq;

namespace _51306985
{
    class Program
    {
        static List<List<string>> listOfList = new List<List<string>>();
        static int longestCol = 0;
        static void Main(string[] args)
        {
            FillTheList("M:\\StackOverflowQuestionsAndAnswers\\51306985\\testdata.csv");
            PadTheList();
            SpitItBackOut();
            SpitItOutToAFile("M:\\StackOverflowQuestionsAndAnswers\\51306985\\testdata.csv");
            Console.ReadLine();
        }

        private static void SpitItOutToAFile(string v)
        {
            string newPath = $"{System.IO.Path.GetDirectoryName(v)}\\{System.IO.Path.GetFileNameWithoutExtension(v)}_Rotated{System.IO.Path.GetExtension(v)}";
            using (System.IO.StreamWriter sw = new System.IO.StreamWriter(newPath))
            {
                for (int i = 0; i < longestCol; i++)
                {
                    string lineToWrite = string.Empty;
                    for (int b = 0; b < listOfList.Count; b++)
                    {
                        lineToWrite += $"{listOfList[b][i]},";
                    }
                    lineToWrite = lineToWrite.Substring(0, lineToWrite.Length - 1);//remove the hanging comma
                    if (lineToWrite != "")
                    {
                        sw.WriteLine(lineToWrite);
                    }

                }
            }
        }

        private static void SpitItBackOut()
        {
            for (int i = 0; i < longestCol; i++)
            {
                string lineToWrite = string.Empty;
                for (int b = 0; b < listOfList.Count; b++)
                {
                    lineToWrite += $"{listOfList[b][i]},";
                }
                lineToWrite = lineToWrite.Substring(0, lineToWrite.Length - 1);//remove the hanging comma
                if (lineToWrite != "")
                {
                    Console.WriteLine(lineToWrite);
                }

            }
        }

        private static void PadTheList()
        {
            foreach (List<string> item in listOfList)
            {
                while (item.Count < longestCol)
                {
                    item.Add("");
                }
            }
        }

        private static void FillTheList(string v)
        {
            using (System.IO.StreamReader sr = new System.IO.StreamReader(v))
            {
                string currentLine = string.Empty;
                while ((currentLine = sr.ReadLine()) != null)
                {
                    listOfList.Add(currentLine.Split(',').ToList());
                    if (listOfList.Last().Count > longestCol)
                    {
                        longestCol = listOfList.Last().Count;
                    }
                }
            }
        }
    }
}

Input Data

a1,b1,c1,d1,e1
a2,b2,c2,d2,e2
a3,b3,c3,d3,e3
a4,b4,c4,d4,e4
a5,b5,c5,d5,e5
a6,b6
a7,b7,c7,d7
a8,b8,c8

Output

a1,a2,a3,a4,a5,a6,a7,a8
b1,b2,b3,b4,b5,b6,b7,b8
c1,c2,c3,c4,c5,,c7,c8
d1,d2,d3,d4,d5,,d7,
e1,e2,e3,e4,e5,,,

Thank you for your solution but the output need to be table csv aswell — MokiNex, Jul 12 '18 at 14:56
@MokiNex, not sure what you mean there... In my snippet, the input and output are csv tables. The code even accounts for incomplete incoming rows. — blaze_125, Jul 12 '18 at 15:08
@MokiNex, there... I added a method to do it `SpitItOutToAFile` — blaze_125, Jul 12 '18 at 15:18

dunkleosteus · Accepted Answer · 2018-07-13T08:35:46.293

There surely is a more efficient way but this is a easy to understand way I think:

1.) put the data into a datatable, e.g. like:

StreamReader sr1 = new StreamReader("C:\\Users\\moki\\Downloads\\Input.csv");  //create the streamreader to read the input .csv
DataTable mydata = new DataTable();  //create an empty DataTable.....
string[] arr;                        //....and an array in which you will store the elemnets of each line
int i = 0;                           //just a variable to help counting where you are in your data
bool mydatasetup = false;            //a variable to check in the loop if you already added the necessary number of columns to the datatable 

        using (sr1)
        {
            while (sr1.EndOfStream == false)    //read the whole file
            {
                string line = sr1.ReadLine();    //get a line from the file

                if (line != null && line != String.Empty) //check if there is content in the line
                {
                     arr = line.Split(';');    //split the line at each ";" and put the elements in the array

                    if(mydatasetup == false)   //after reading the first line add as many columns to your datatable as you will need..... 
                    {
                        for (int u = 0; u < arr.Length; u++)
                        {
                            mydata.Columns.Add();
                        }
                        mydatasetup = true; //...but only do this once (otherwise you wil have an unneccessary big datatable
                    } 

                     mydata.Rows.Add();   //add a row in you datatable in which you will store the data of the line

                     for (int j = 0; j < arr.Length; j++)    //go throught each element in your array and put it into your datatable
                        {
                            if (arr[j] != "")
                            {
                                mydata.Rows[i][j] = arr[j];
                            }
                        }
                     i = i + 1; //increase the counter so that the program knows it has to fill the data from the next line into the next row of the datatable
                  }
              }
           }

2.) Then you can loop through your datatable's columns and add each row's contents to a Stringbuilder (whereby you transpose your data) which you then save as a .csv:

StringBuilder sb = new StringBuilder();  //create a stringbuilder

      for (int u = 0; u < mydata.Columns.Count; u++)   //loop through the COLUMNS of your datatable....
            {
            for (int i = 0; i < mydata.Rows.Count; i++)  //....but for each column go through each row in the datatable first  
                    {
                        sb.Append(mydata.Rows[i][u].ToString()); // and add the elements to the stringbuilder - here the transposing is actually done

                        if (i < mydata.Rows.Count - 1)   //add a deliminator after each element because you want a .csv as output again 
                        {
                            sb.Append(';');       
                        }     
                    }
            sb.AppendLine(); //add another line to your stringbuilder in which you will store the next column of your datatable
            }

File.WriteAllText("C:\\Users\\moki\\Downloads\\Output.csv", sb.ToString());  //finally create the output .csv

You could of course combine these two steps.

the line that's has `mydata.Rows[i][j]` i is not in the context also in the second loop `datatable.Rows[i][u]` — MokiNex, Jul 12 '18 at 14:30
still error in this line cause i its not in the context `mydata.Rows[i][j] = arr[j];` — MokiNex, Jul 12 '18 at 14:40
@MokiNex sorry, another copying error...of course you have to add columns and rows to your datatable, updated the answer again. — dunkleosteus, Jul 12 '18 at 14:58
you result not accurate because the first line has just comma — MokiNex, Jul 12 '18 at 15:05
Well for me it works... have you changed this line in your code: `for (int i = 0; i < mydata.Rows.Count; i++) ` I think originally I only started at row 1 and then you get only commas in the first line... — dunkleosteus, Jul 12 '18 at 15:09
...this row was wrong originally: `for (int j = 0; j < arr.Length; j++)` it started with j=1 but that was fixed already a few changes ago — dunkleosteus, Jul 12 '18 at 15:19

score 1 · Answer 3 · answered Jul 13 '18 at 21:46

Just in case, if anyone want to know how to do it using Cinchoo ETL with few lines of code,

string csv = @"A1;B1;C1;D1;E1
A2;B2;C2;D2;E2
A3;B3;C3;D3;E3
A4;B4;C4;D4;E4
A5;B5;C5;D5;E5
";

StringBuilder sb = new StringBuilder();
using (var p = ChoCSVReader.LoadText(csv)
    .WithDelimiter(";")
    .ThrowAndStopOnMissingField(false)
    )
{
    using (var w = new ChoCSVWriter(sb)
        .WithDelimiter(";")
        )
    {
        w.Write(p.Cast<ChoDynamicObject>().Transpose(false));
    }
}

Console.WriteLine(sb.ToString());

Output:

A1;A2;A3;A4;A5
B1;B2;B3;B4;B5
C1;C2;C3;C4;C5
D1;D2;D3;D4;D5
E1;E2;E3;E4;E5

Reading CSV file and converted to transposed table

3 Answers3

Linked