C# 如何将 CSV 文件读入 .NET 数据表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1050112/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to read a CSV file into a .NET Datatable
提问by Ronnie Overby
How can I load a CSV file into a System.Data.DataTable, creating the datatable based on the CSV file?
如何将 CSV 文件加载到 .csv 文件中System.Data.DataTable，基于 CSV 文件创建数据表？
Does the regular ADO.net functionality allow this?
常规的 ADO.net 功能是否允许这样做？
采纳答案by Jay Riggs
Here's an excellent class that will copy CSV data into a datatable using the structure of the data to create the DataTable:
这是一个优秀的类，它将使用数据的结构将 CSV 数据复制到数据表中以创建数据表：
A portable and efficient generic parser for flat files
It's easy to configure and easy to use. I urge you to take a look.
它易于配置且易于使用。我劝你看看。
回答by Bob Mc
Here's a solution that uses ADO.Net's ODBC text driver:
这是一个使用 ADO.Net 的 ODBC 文本驱动程序的解决方案：
Dim csvFileFolder As String = "C:\YourFileFolder"
Dim csvFileName As String = "YourFile.csv"
'Note that the folder is specified in the connection string,
'not the file. That's specified in the SELECT query, later.
Dim connString As String = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" _
& csvFileFolder & ";Extended Properties=""Text;HDR=No;FMT=Delimited"""
Dim conn As New Odbc.OdbcConnection(connString)
'Open a data adapter, specifying the file name to load
Dim da As New Odbc.OdbcDataAdapter("SELECT * FROM [" & csvFileName & "]", conn)
'Then fill a data table, which can be bound to a grid
Dim dt As New DataTableda.Fill(dt)
grdCSVData.DataSource = dt
Once filled, you can value properties of the datatable, like ColumnName, to make utilize all the powers of the ADO.Net data objects.
填充后，您可以对数据表的属性（如 ColumnName）进行赋值，以利用 ADO.Net 数据对象的所有功能。
In VS2008 you can use Linq to achieve the same effect.
在 VS2008 中你可以使用 Linq 来达到同样的效果。
NOTE: This may be a duplicate of thisSO question.
注意：这可能是这个SO 问题的副本。
回答by Jim Scott
I have been using OleDbprovider. However, it has problems if you are reading in rows that have numeric values but you want them treated as text. However, you can get around that issue by creating a schema.inifile. Here is my method I used:
我一直在使用OleDb提供程序。但是，如果您正在读取具有数值的行但希望将它们视为文本，则会出现问题。但是，您可以通过创建schema.ini文件来解决该问题。这是我使用的方法：
// using System.Data;
// using System.Data.OleDb;
// using System.Globalization;
// using System.IO;
static DataTable GetDataTableFromCsv(string path, bool isFirstRowHeader)
{
string header = isFirstRowHeader ? "Yes" : "No";
string pathOnly = Path.GetDirectoryName(path);
string fileName = Path.GetFileName(path);
string sql = @"SELECT * FROM [" + fileName + "]";
using(OleDbConnection connection = new OleDbConnection(
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly +
";Extended Properties=\"Text;HDR=" + header + "\""))
using(OleDbCommand command = new OleDbCommand(sql, connection))
using(OleDbDataAdapter adapter = new OleDbDataAdapter(command))
{
DataTable dataTable = new DataTable();
dataTable.Locale = CultureInfo.CurrentCulture;
adapter.Fill(dataTable);
return dataTable;
}
}
回答by Ronnie Overby
I have decided to use Sebastien Lorion's Csv Reader.
我决定使用Sebastien Lorion 的 Csv Reader。
Jay Riggs suggestion is a great solution also, but I just didn't need all of the features that that Andrew Rissing's Generic Parser provides.
Jay Riggs 的建议也是一个很好的解决方案，但我并不需要Andrew Rssing 的 Generic Parser提供的所有功能。
UPDATE 10/25/2010
更新 10/25/2010
After using Sebastien Lorion's Csv Readerin my project for nearly a year and a half, I have found that it throws exceptions when parsing some csv files that I believe to be well formed.
在我的项目中使用Sebastien Lorion 的 Csv Reader将近一年半后，我发现它在解析一些我认为格式良好的 csv 文件时会抛出异常。
So, I did switch to Andrew Rissing's Generic Parser and it seems to be doing much better.
所以，我确实切换到了Andrew Rissing 的 Generic Parser，它似乎做得更好。
UPDATE 9/22/2014
更新 9/22/2014
These days, I mostly use this extension method to read delimited text:
这些天，我主要使用这种扩展方法来阅读分隔文本：
https://www.nuget.org/packages/CoreTechs.Common/
https://www.nuget.org/packages/CoreTechs.Common/
UPDATE 2/20/2015
更新 2/20/2015
Example:
例子：
var csv = @"Name, Age
Ronnie, 30
Mark, 40
Ace, 50";
TextReader reader = new StringReader(csv);
var table = new DataTable();
using(var it = reader.ReadCsvWithHeader().GetEnumerator())
{
if (!it.MoveNext()) return;
foreach (var k in it.Current.Keys)
table.Columns.Add(k);
do
{
var row = table.NewRow();
foreach (var k in it.Current.Keys)
row[k] = it.Current[k];
table.Rows.Add(row);
} while (it.MoveNext());
}
回答by Chuck Bevitt
We always used to use the Jet.OLEDB driver, until we started going to 64 bit applications. Microsoft has not and will not release a 64 bit Jet driver. Here's a simple solution we came up with that uses File.ReadAllLines and String.Split to read and parse the CSV file and manually load a DataTable. As noted above, it DOES NOT handle the situation where one of the column values contains a comma. We use this mostly for reading custom configuration files - the nice part about using CSV files is that we can edit them in Excel.
我们一直使用 Jet.OLEDB 驱动程序，直到我们开始使用 64 位应用程序。Microsoft 没有也不会发布 64 位 Jet 驱动程序。这是我们提出的一个简单解决方案，它使用 File.ReadAllLines 和 String.Split 来读取和解析 CSV 文件并手动加载数据表。如上所述，它不处理列值之一包含逗号的情况。我们主要使用它来读取自定义配置文件 - 使用 CSV 文件的好处是我们可以在 Excel 中编辑它们。
string CSVFilePathName = @"C:\test.csv";
string[] Lines = File.ReadAllLines(CSVFilePathName);
string[] Fields;
Fields = Lines[0].Split(new char[] { ',' });
int Cols = Fields.GetLength(0);
DataTable dt = new DataTable();
//1st row must be column names; force lower case to ensure matching later on.
for (int i = 0; i < Cols; i++)
dt.Columns.Add(Fields[i].ToLower(), typeof(string));
DataRow Row;
for (int i = 1; i < Lines.GetLength(0); i++)
{
Fields = Lines[i].Split(new char[] { ',' });
Row = dt.NewRow();
for (int f = 0; f < Cols; f++)
Row[f] = Fields[f];
dt.Rows.Add(Row);
}
回答by Nodir
public class Csv
{
public static DataTable DataSetGet(string filename, string separatorChar, out List<string> errors)
{
errors = new List<string>();
var table = new DataTable("StringLocalization");
using (var sr = new StreamReader(filename, Encoding.Default))
{
string line;
var i = 0;
while (sr.Peek() >= 0)
{
try
{
line = sr.ReadLine();
if (string.IsNullOrEmpty(line)) continue;
var values = line.Split(new[] {separatorChar}, StringSplitOptions.None);
var row = table.NewRow();
for (var colNum = 0; colNum < values.Length; colNum++)
{
var value = values[colNum];
if (i == 0)
{
table.Columns.Add(value, typeof (String));
}
else
{
row[table.Columns[colNum]] = value;
}
}
if (i != 0) table.Rows.Add(row);
}
catch(Exception ex)
{
errors.Add(ex.Message);
}
i++;
}
}
return table;
}
}
回答by Nepa
I came across this piece of code that uses Linq and regex to parse a CSV file. The refering article is now over a year and a half old, but have not come across a neater way to parse a CSV using Linq (and regex) than this. The caveat is the regex applied here is for comma delimited files (will detect commas inside quotes!) and that it may not take well to headers, but there is a way to overcome these). Take a peak:
我遇到了这段使用 Linq 和正则表达式解析 CSV 文件的代码。参考文章现在已经有一年半的历史了，但还没有遇到比这更简洁的使用 Linq（和正则表达式）解析 CSV 的方法。需要注意的是，此处应用的正则表达式适用于逗号分隔的文件（将检测引号内的逗号！）并且它可能不适用于标题，但有一种方法可以克服这些问题）。取一个峰值：
Dim lines As String() = System.IO.File.ReadAllLines(strCustomerFile)
Dim pattern As String = ",(?=(?:[^""]*""[^""]*"")*(?![^""]*""))"
Dim r As System.Text.RegularExpressions.Regex = New System.Text.RegularExpressions.Regex(pattern)
Dim custs = From line In lines _
Let data = r.Split(line) _
Select New With {.custnmbr = data(0), _
.custname = data(1)}
For Each cust In custs
strCUSTNMBR = Replace(cust.custnmbr, Chr(34), "")
strCUSTNAME = Replace(cust.custname, Chr(34), "")
Next
回答by Thomas
this is the code i use it but your apps must run with net version 3.5
这是我使用的代码，但您的应用程序必须使用 net 3.5 版运行
private void txtRead_Click(object sender, EventArgs e)
{
// var filename = @"d:\shiptest.txt";
openFileDialog1.InitialDirectory = "d:\";
openFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*";
DialogResult result = openFileDialog1.ShowDialog();
if (result == DialogResult.OK)
{
if (openFileDialog1.FileName != "")
{
var reader = ReadAsLines(openFileDialog1.FileName);
var data = new DataTable();
//this assume the first record is filled with the column names
var headers = reader.First().Split(',');
foreach (var header in headers)
{
data.Columns.Add(header);
}
var records = reader.Skip(1);
foreach (var record in records)
{
data.Rows.Add(record.Split(','));
}
dgList.DataSource = data;
}
}
}
static IEnumerable<string> ReadAsLines(string filename)
{
using (StreamReader reader = new StreamReader(filename))
while (!reader.EndOfStream)
yield return reader.ReadLine();
}
回答by Neo
The best option I have found, and it resolves issues where you may have different versions of Office installed, and also 32/64-bit issues like Chuck Bevitt mentioned, is FileHelpers.
我发现的最佳选择是FileHelpers，它解决了您可能安装了不同版本的 Office 以及Chuck Bevitt 提到的32/64 位问题的问题。
It can be added to your project references using NuGet and it provides a one-liner solution:
可以使用 NuGet 将其添加到您的项目引用中，并提供单行解决方案：
CommonEngine.CsvToDataTable(path, "ImportRecord", ',', true);
回答by Smeiff
For those of you wishing not to use an external library, and prefer not to use OleDB, see the example below. Everything I found was either OleDB, external library, or simply splitting based on a comma! For my case OleDB was not working so I wanted something different.
对于那些不希望使用外部库并且不想使用 OleDB 的人，请参阅下面的示例。我发现的一切要么是 OleDB、外部库，要么只是基于逗号进行拆分！对于我的情况，OleDB 不起作用，所以我想要一些不同的东西。
I found an article by MarkJ that referenced the Microsoft.VisualBasic.FileIO.TextFieldParser method as seen here. The article is written in VB and doesn't return a datatable, so see my example below.
我发现MarkJ了一篇文章，引用的Microsoft.VisualBasic.FileIO.TextFieldParser方法，看到这里。文章是用VB写的，不返回数据表，所以看我下面的例子。
public static DataTable LoadCSV(string path, bool hasHeader)
{
DataTable dt = new DataTable();
using (var MyReader = new Microsoft.VisualBasic.FileIO.TextFieldParser(path))
{
MyReader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited;
MyReader.Delimiters = new String[] { "," };
string[] currentRow;
//'Loop through all of the fields in the file.
//'If any lines are corrupt, report an error and continue parsing.
bool firstRow = true;
while (!MyReader.EndOfData)
{
try
{
currentRow = MyReader.ReadFields();
//Add the header columns
if (hasHeader && firstRow)
{
foreach (string c in currentRow)
{
dt.Columns.Add(c, typeof(string));
}
firstRow = false;
continue;
}
//Create a new row
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
//Loop thru the current line and fill the data out
for(int c = 0; c < currentRow.Count(); c++)
{
dr[c] = currentRow[c];
}
}
catch (Microsoft.VisualBasic.FileIO.MalformedLineException ex)
{
//Handle the exception here
}
}
}
return dt;
}
