代码之家 › 专栏 › 技术社区 › MokiNex

读取csv文件并转换为转置表

csv visual-studio .net c#

MokiNex · 技术社区 · 6 年前

我需要读取一个csv文件(通过___分隔),并且应该创建一个包含转置(旋转)表的新文件:

我的输入文件:

该工具应能从用户处接收表的文件名,并加载表以转换内容。
该工具应能以输入文件的文件名将转置表保存在新文件中,并以转置(文件名_转置.csv)扩展。

我的代码

public void readcsv()。
{
//打开文件“data.csv”,它是一个带有标题的csv文件
使用(csvreader csv=新csvreader(
新建streamreader(“c:\\users\\moki\\downloads\\input.csv”),true)
{
int fieldcount=csv.fieldcount;

string[]headers=csv.getFieldHeaders();
同时(csv.readNextRecord())
{
对于(int i=0;i<fieldcount;i++)
console.writeline(string.format(“0 \n 1”),
头[0],csv[i]);

}
}
console.readline();
我的结果

.





该工具应能从用户处接收表的文件名,并加载表以转换内容。
该工具应能将转置后的表保存在新文件中,文件名为输入文件,扩展名为转置(文件名U转置.csv)。


我的代码

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();


我的结果

3 回复 | 直到 6 年前

dunkleosteus 6 年前

当然有一种更有效的方法,但我认为这是一种容易理解的方法:

1.)将数据放入数据表,例如:

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.)然后您可以循环访问数据表的列,并将每一行的内容添加到StringBuilder(通过它可以转换数据),然后保存为.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

你当然可以把这两个步骤结合起来。

blaze_125 6 年前

既然我们提供答案…

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;
                    }
                }
            }
        }
    }
}

输入数据

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

产量

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,,,

Cinchoo 6 年前

以防万一,如果有人想知道如何使用 Cinchoo ETL 只有几行代码,

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());

输出:

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