在c#中解析CSV文件，带头

让一个库为你处理所有的细节!: -)

检查FileHelpers和保持干燥-不要重复自己-不需要重新发明车轮十亿次....

基本上，您只需要定义数据的形状——CSV中各个行中的字段——通过一个公共类(以及诸如默认值、NULL值替换等经过精心考虑的属性)，将FileHelpers引擎指向一个文件，然后就可以从该文件中获得所有条目。一个简单的操作-卓越的性能!

如果你只需要读取csv文件，那么我推荐这个库:一个快速的csv阅读器
如果你还需要生成csv文件，那么使用这个:FileHelpers

它们都是免费和开源的。

在商业应用程序中，我使用codeproject.com上的开源项目，CSVReader。

它工作良好，具有良好的性能。我提供的链接上有一些基准测试。

一个简单的例子，从项目页面复制:

using (CsvReader csv = new CsvReader(new StreamReader("data.csv"), true))
{
int fieldCount = csv.FieldCount;
string[] headers = csv.GetFieldHeaders();


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


Console.WriteLine();
}
}

如你所见，这很容易处理。

CsvHelper(我维护的库)将读取CSV文件到自定义对象。

using (var reader = new StreamReader("path\\to\\file.csv"))
using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
{
var records = csv.GetRecords<Foo>();
}

有时候你并不拥有你想读的对象。在这种情况下，您可以使用流畅映射，因为您不能将属性放在类上。

public sealed class MyCustomObjectMap : CsvClassMap<MyCustomObject>
{
public MyCustomObjectMap()
{
Map( m => m.Property1 ).Name( "Column Name" );
Map( m => m.Property2 ).Index( 4 );
Map( m => m.Property3 ).Ignore();
Map( m => m.Property4 ).TypeConverter<MySpecialTypeConverter>();
}
}

这里有一个我经常使用的helper类，以防有人回到这个线程(我想分享它)。

我这样做是为了简单地将它移植到可以使用的项目中:

public class CSVHelper : List<string[]>
{
protected string csv = string.Empty;
protected string separator = ",";


public CSVHelper(string csv, string separator = "\",\"")
{
this.csv = csv;
this.separator = separator;


foreach (string line in Regex.Split(csv, System.Environment.NewLine).ToList().Where(s => !string.IsNullOrEmpty(s)))
{
string[] values = Regex.Split(line, separator);


for (int i = 0; i < values.Length; i++)
{
//Trim values
values[i] = values[i].Trim('\"');
}


this.Add(values);
}
}
}

像这样使用它:

public List<Person> GetPeople(string csvContent)
{
List<Person> people = new List<Person>();
CSVHelper csv = new CSVHelper(csvContent);
foreach(string[] line in csv)
{
Person person = new Person();
person.Name = line[0];
person.TelephoneNo = line[1];
people.Add(person);
}
return people;
}

[更新的csv helper:修复了最后一个新行字符创建新行的错误]

我知道有点晚了，但刚刚找到了一个库Microsoft.VisualBasic.FileIO，它有TextFieldParser类来处理csv文件。

CSV解析器现在是. net框架的一部分。

添加对Microsoft.VisualBasic.dll的引用(在c#中工作良好，不要介意名称)

using (TextFieldParser parser = new TextFieldParser(@"c:\temp\test.csv"))
{
parser.TextFieldType = FieldType.Delimited;
parser.SetDelimiters(",");
while (!parser.EndOfData)
{
//Process row
string[] fields = parser.ReadFields();
foreach (string field in fields)
{
//TODO: Process field
}
}
}

文档在这里——TextFieldParser类

附注:如果你需要一个CSV 出口国，试试CsvExport (discl:我是贡献者之一)

我已经为。net编写了TinyCsvParser，它是目前最快的。net解析器之一，并且高度可配置，可以解析几乎任何CSV格式。

它在MIT许可下发布:

• https://github.com/bytefish/TinyCsvParser < a href = " https://github.com/bytefish/TinyCsvParser " > < / >

你可以使用NuGet来安装它。在“包管理控制台. conf”文件中执行以下命令。

PM> Install-Package TinyCsvParser

使用

假设我们在CSV文件persons.csv中有一个person列表，包含他们的姓、名和生日。

FirstName;LastName;BirthDate
Philipp;Wagner;1986/05/12
Max;Musterman;2014/01/02

系统中相应的域模型可能是这样的。

private class Person
{
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime BirthDate { get; set; }
}

当使用TinyCsvParser时，您必须定义CSV数据中的列和域模型中的属性之间的映射。

private class CsvPersonMapping : CsvMapping<Person>
{


public CsvPersonMapping()
: base()
{
MapProperty(0, x => x.FirstName);
MapProperty(1, x => x.LastName);
MapProperty(2, x => x.BirthDate);
}
}

然后我们可以使用映射用CsvParser解析CSV数据。

namespace TinyCsvParser.Test
{
[TestFixture]
public class TinyCsvParserTest
{
[Test]
public void TinyCsvTest()
{
CsvParserOptions csvParserOptions = new CsvParserOptions(true, new[] { ';' });
CsvPersonMapping csvMapper = new CsvPersonMapping();
CsvParser<Person> csvParser = new CsvParser<Person>(csvParserOptions, csvMapper);


var result = csvParser
.ReadFromFile(@"persons.csv", Encoding.ASCII)
.ToList();


Assert.AreEqual(2, result.Count);


Assert.IsTrue(result.All(x => x.IsValid));


Assert.AreEqual("Philipp", result[0].Result.FirstName);
Assert.AreEqual("Wagner", result[0].Result.LastName);


Assert.AreEqual(1986, result[0].Result.BirthDate.Year);
Assert.AreEqual(5, result[0].Result.BirthDate.Month);
Assert.AreEqual(12, result[0].Result.BirthDate.Day);


Assert.AreEqual("Max", result[1].Result.FirstName);
Assert.AreEqual("Mustermann", result[1].Result.LastName);


Assert.AreEqual(2014, result[1].Result.BirthDate.Year);
Assert.AreEqual(1, result[1].Result.BirthDate.Month);
Assert.AreEqual(1, result[1].Result.BirthDate.Day);
}
}
}

用户指南

完整的用户指南可在以下网址查阅:

• http://bytefish.github.io/TinyCsvParser/ < a href = " http://bytefish.github.io/TinyCsvParser/ " > < / >

基于unlimit在如何使用c# split()函数正确分割CSV ?上的帖子:

string[] tokens = System.Text.RegularExpressions.Regex.Split(paramString, ",");

注意:这并不处理转义/嵌套的逗号等，因此只适用于某些简单的CSV列表。

这个解决方案使用官方的微软。VisualBasic程序集来解析CSV。

优点:

• 分隔符逃离
• 忽略了头
• 装饰空间
• 忽略评论

代码:

    using Microsoft.VisualBasic.FileIO;


public static List<List<string>> ParseCSV (string csv)
{
List<List<string>> result = new List<List<string>>();




// To use the TextFieldParser a reference to the Microsoft.VisualBasic assembly has to be added to the project.
using (TextFieldParser parser = new TextFieldParser(new StringReader(csv)))
{
parser.CommentTokens = new string[] { "#" };
parser.SetDelimiters(new string[] { ";" });
parser.HasFieldsEnclosedInQuotes = true;


// Skip over header line.
//parser.ReadLine();


while (!parser.EndOfData)
{
var values = new List<string>();


var readFields = parser.ReadFields();
if (readFields != null)
values.AddRange(readFields);
result.Add(values);
}
}


return result;
}

这是我的KISS实现…

using System;
using System.Collections.Generic;
using System.Text;


class CsvParser
{
public static List<string> Parse(string line)
{
const char escapeChar = '"';
const char splitChar = ',';
bool inEscape = false;
bool priorEscape = false;


List<string> result = new List<string>();
StringBuilder sb = new StringBuilder();
for (int i = 0; i < line.Length; i++)
{
char c = line[i];
switch (c)
{
case escapeChar:
if (!inEscape)
inEscape = true;
else
{
if (!priorEscape)
{
if (i + 1 < line.Length && line[i + 1] == escapeChar)
priorEscape = true;
else
inEscape = false;
}
else
{
sb.Append(c);
priorEscape = false;
}
}
break;
case splitChar:
if (inEscape) //if in escape
sb.Append(c);
else
{
result.Add(sb.ToString());
sb.Length = 0;
}
break;
default:
sb.Append(c);
break;
}
}


if (sb.Length > 0)
result.Add(sb.ToString());


return result;
}


}

前段时间我写了一个基于Microsoft.VisualBasic库的CSV读/写的简单类。使用这个简单的类，您将能够像使用二维数组一样使用CSV。你可以通过下面的链接找到我的类:https://github.com/ukushu/DataExporter

用法的简单例子:

Csv csv = new Csv("\t");//delimiter symbol


csv.FileOpen("c:\\file1.csv");


var row1Cell6Value = csv.Rows[0][5];


csv.AddRow("asdf","asdffffff","5")


csv.FileSave("c:\\file2.csv");

对于读取头文件，你只需要读取csv.Rows[0]单元格:)

这段代码读取csv到DataTable:

public static DataTable ReadCsv(string path)
{
DataTable result = new DataTable("SomeData");
using (TextFieldParser parser = new TextFieldParser(path))
{
parser.TextFieldType = FieldType.Delimited;
parser.SetDelimiters(",");
bool isFirstRow = true;
//IList<string> headers = new List<string>();


while (!parser.EndOfData)
{
string[] fields = parser.ReadFields();
if (isFirstRow)
{
foreach (string field in fields)
{
result.Columns.Add(new DataColumn(field, typeof(string)));
}
isFirstRow = false;
}
else
{
int i = 0;
DataRow row = result.NewRow();
foreach (string field in fields)
{
row[i++] = field;
}
result.Rows.Add(row);
}
}
}
return result;
}

这个列表中的另一个是Cinchoo ETL——一个用于读写多种文件格式(CSV、平面文件、Xml、JSON等)的开源库。

下面的示例显示了如何快速读取CSV文件(不需要POCO对象)

string csv = @"Id, Name
1, Carl
2, Tom
3, Mark";


using (var p = ChoCSVReader.LoadText(csv)
.WithFirstLineHeader()
)
{
foreach (var rec in p)
{
Console.WriteLine($"Id: {rec.Id}");
Console.WriteLine($"Name: {rec.Name}");
}
}

下面的示例展示了如何使用POCO对象读取CSV文件

public partial class EmployeeRec
{
public int Id { get; set; }
public string Name { get; set; }
}


static void CSVTest()
{
string csv = @"Id, Name
1, Carl
2, Tom
3, Mark";


using (var p = ChoCSVReader<EmployeeRec>.LoadText(csv)
.WithFirstLineHeader()
)
{
foreach (var rec in p)
{
Console.WriteLine($"Id: {rec.Id}");
Console.WriteLine($"Name: {rec.Name}");
}
}
}

请在CodeProject上上查看如何使用它的文章。

单源文件解决方案用于简单的解析需求，非常有用。处理所有令人讨厌的边缘情况。比如新行归一化和在带引号的字符串字面量中处理新行。你的欢迎!

如果您的CSV文件有一个标题，您只需从第一行读出列名(并计算列索引)。就这么简单。

注意Dump是一个LINQPad方法，如果你不使用LINQPad，你可能想要删除它。

void Main()
{
var file1 = "a,b,c\r\nx,y,z";
CSV.ParseText(file1).Dump();


var file2 = "a,\"b\",c\r\nx,\"y,z\"";
CSV.ParseText(file2).Dump();


var file3 = "a,\"b\",c\r\nx,\"y\r\nz\"";
CSV.ParseText(file3).Dump();


var file4 = "\"\"\"\"";
CSV.ParseText(file4).Dump();
}


static class CSV
{
public struct Record
{
public readonly string[] Row;


public string this[int index] => Row[index];


public Record(string[] row)
{
Row = row;
}
}


public static List<Record> ParseText(string text)
{
return Parse(new StringReader(text));
}


public static List<Record> ParseFile(string fn)
{
using (var reader = File.OpenText(fn))
{
return Parse(reader);
}
}


public static List<Record> Parse(TextReader reader)
{
var data = new List<Record>();


var col = new StringBuilder();
var row = new List<string>();
for (; ; )
{
var ln = reader.ReadLine();
if (ln == null) break;
if (Tokenize(ln, col, row))
{
data.Add(new Record(row.ToArray()));
row.Clear();
}
}


return data;
}


public static bool Tokenize(string s, StringBuilder col, List<string> row)
{
int i = 0;


if (col.Length > 0)
{
col.AppendLine(); // continuation


if (!TokenizeQuote(s, ref i, col, row))
{
return false;
}
}


while (i < s.Length)
{
var ch = s[i];
if (ch == ',')
{
row.Add(col.ToString().Trim());
col.Length = 0;
i++;
}
else if (ch == '"')
{
i++;
if (!TokenizeQuote(s, ref i, col, row))
{
return false;
}
}
else
{
col.Append(ch);
i++;
}
}


if (col.Length > 0)
{
row.Add(col.ToString().Trim());
col.Length = 0;
}


return true;
}


public static bool TokenizeQuote(string s, ref int i, StringBuilder col, List<string> row)
{
while (i < s.Length)
{
var ch = s[i];
if (ch == '"')
{
// escape sequence
if (i + 1 < s.Length && s[i + 1] == '"')
{
col.Append('"');
i++;
i++;
continue;
}
i++;
return true;
}
else
{
col.Append(ch);
i++;
}
}
return false;
}
}

如果任何人想要一个代码片段，他们可以直接输入自己的代码，而不必绑定库或下载包。以下是我写的一个版本:

    public static string FormatCSV(List<string> parts)
{
string result = "";


foreach (string s in parts)
{
if (result.Length > 0)
{
result += ",";


if (s.Length == 0)
continue;
}


if (s.Length > 0)
{
result += "\"" + s.Replace("\"", "\"\"") + "\"";
}
else
{
// cannot output double quotes since its considered an escape for a quote
result += ",";
}
}


return result;
}


enum CSVMode
{
CLOSED = 0,
OPENED_RAW = 1,
OPENED_QUOTE = 2
}


public static List<string> ParseCSV(string input)
{
List<string> results;


CSVMode mode;


char[] letters;


string content;




mode = CSVMode.CLOSED;


content = "";
results = new List<string>();
letters = input.ToCharArray();


for (int i = 0; i < letters.Length; i++)
{
char letter = letters[i];
char nextLetter = '\0';


if (i < letters.Length - 1)
nextLetter = letters[i + 1];


// If its a quote character
if (letter == '"')
{
// If that next letter is a quote
if (nextLetter == '"' && mode == CSVMode.OPENED_QUOTE)
{
// Then this quote is escaped and should be added to the content


content += letter;


// Skip the escape character
i++;
continue;
}
else
{
// otherwise its not an escaped quote and is an opening or closing one
// Character is skipped


// If it was open, then close it
if (mode == CSVMode.OPENED_QUOTE)
{
results.Add(content);


// reset the content
content = "";


mode = CSVMode.CLOSED;


// If there is a next letter available
if (nextLetter != '\0')
{
// If it is a comma
if (nextLetter == ',')
{
i++;
continue;
}
else
{
throw new Exception("Expected comma. Found: " + nextLetter);
}
}
}
else if (mode == CSVMode.OPENED_RAW)
{
// If it was opened raw, then just add the quote
content += letter;
}
else if (mode == CSVMode.CLOSED)
{
// Otherwise open it as a quote


mode = CSVMode.OPENED_QUOTE;
}
}
}
// If its a comma seperator
else if (letter == ',')
{
// If in quote mode
if (mode == CSVMode.OPENED_QUOTE)
{
// Just read it
content += letter;
}
// If raw, then close the content
else if (mode == CSVMode.OPENED_RAW)
{
results.Add(content);


content = "";


mode = CSVMode.CLOSED;
}
// If it was closed, then open it raw
else if (mode == CSVMode.CLOSED)
{
mode = CSVMode.OPENED_RAW;


results.Add(content);


content = "";
}
}
else
{
// If opened quote, just read it
if (mode == CSVMode.OPENED_QUOTE)
{
content += letter;
}
// If opened raw, then read it
else if (mode == CSVMode.OPENED_RAW)
{
content += letter;
}
// It closed, then open raw
else if (mode == CSVMode.CLOSED)
{
mode = CSVMode.OPENED_RAW;


content += letter;
}
}
}


// If it was still reading when the buffer finished
if (mode != CSVMode.CLOSED)
{
results.Add(content);
}


return results;
}

这里有一个简短而简单的解决方案。

                using (TextFieldParser parser = new TextFieldParser(outputLocation))
{
parser.TextFieldType = FieldType.Delimited;
parser.SetDelimiters(",");
string[] headers = parser.ReadLine().Split(',');
foreach (string header in headers)
{
dataTable.Columns.Add(header);
}
while (!parser.EndOfData)
{
string[] fields = parser.ReadFields();
dataTable.Rows.Add(fields);
}
}

这个解析器支持在列中嵌套逗号和引号:

static class CSVParser
{
public static string[] ParseLine(string line)
{
List<string> cols = new List<string>();
string value = null;


for(int i = 0; i < line.Length; i++)
{
switch(line[i])
{
case ',':
cols.Add(value);
value = null;
if(i == line.Length - 1)
{// It ends with comma
cols.Add(null);
}
break;
case '"':
cols.Add(ParseEnclosedColumn(line, ref i));
i++;
break;
default:
value += line[i];
if (i == line.Length - 1)
{// Last character
cols.Add(value);
}
break;
}
}


return cols.ToArray();
}//ParseLine


static string ParseEnclosedColumn(string line, ref int index)
{// Example: "b"",bb"
string value = null;
int numberQuotes = 1;
int index2 = index;


for (int i = index + 1; i < line.Length; i++)
{
index2 = i;
switch (line[i])
{
case '"':
numberQuotes++;
if (numberQuotes % 2 == 0)
{
if (i < line.Length - 1 && line[i + 1] == ',')
{
index = i;
return value;
}
}
else if (i > index + 1 && line[i - 1] == '"')
{
value += '"';
}
break;
default:
value += line[i];
break;
}
}


index = index2;
return value;
}//ParseEnclosedColumn
}//class CSVParser

< p > schema_name、描述utype
“ix_he”，“高能数据”，“x”
“iii光谱”，“光谱数据”，“d”
“VI_misc",“Miscellaneous",“f"
“vcds1”，“"目录只在cd "，"d"
“j_other”，“其他期刊发表文章”，“&;b"

当我们将整个内容读入一个名为数据的字符串时，则

using System;
using System.IO;
using System.Linq;


var data = File.ReadAllText(Path2CSV);


// helper split characters
var newline = Environment.NewLine.ToCharArray();
var comma = ",".ToCharArray();
var quote = "\"".ToCharArray();


// split input string data to lines
var lines = data.Split(newline);


// first line is header, take the header fields
foreach (var col in lines.First().Split(comma)) {
// do something with "col"
}


// we skip the first line, all the rest are real data lines/fields
foreach (var line in lines.Skip(1)) {
// first we split the data line by comma character
// next we remove double qoutes from each splitted element using Trim()
// finally we make an array
var fields = line.Split(comma)
.Select(_ => { _ = _.Trim(quote); return _; })
.ToArray();
// do something with the "fields" array
}