如何将CSV文件加载到System.Data.DataTable
中,根据CSV文件创建数据表?
常规的ADO.net功能是否允许这样做?
我一直在使用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;
}
}
这里有一个使用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
一旦填好,你可以对数据表的属性进行估值,如ColumnName,以利用ADO.Net数据对象的所有功能。
在VS2008中,你可以使用Linq来实现同样的效果。
注意:这可能是一个重复的this SO问题。