Question

C#导入CSV数据

以前用以下代码来导入EXCEL表格中的数据，现在需要导入CSV文件中的数据，应该怎样修改：
public static DataSet ExcelToDataSet(string pathname)
{
DataSet ds = new DataSet();

try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + pathname + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();

string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = string.Format("Select * from [Sheet1$]");
myCommand = new OleDbDataAdapter(strExcel, strConn);

myCommand.Fill(ds, "Sheet1");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}

return ds;
}

Answer 1

/// <summary>
/// 将指定的CSV文件还原为DataTable
/// </summary>
/// <param name="dataTableFrame">目标数据表格的结构</param>
/// <param name="CsvFilePath">需要还原的CSV文件</param>
/// <returns>还原的DataTable</returns>
public static System.Data.DataTable CsvFile2DataTable(System.Data.DataTable dataTableFrame, string CsvFilePath)
{

string conStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
conStr += Path.GetDirectoryName(CsvFilePath);
conStr += ";Extended Properties=\"Text;HDR=Yes;FMT=Delimited\"";
OleDbConnection oleCon = new OleDbConnection(conStr);

OleDbCommand oleCom = new OleDbCommand("select * from [" + Path.GetFileName(CsvFilePath) + "]", oleCon);

DataTable redt = dataTableFrame.Clone();

object[] records = new object[redt.Columns.Count];//存放记录的数组

try
{
oleCon.Open();
OleDbDataReader oledr = oleCom.ExecuteReader();
//oledr.Read();
while (oledr.Read())
{
oledr.GetValues(records);//取出一行记录
try
{
redt.Rows.Add(records);
}
catch { continue; }
}
oleCom.Dispose();
oleCon.Dispose();
oledr.Dispose();

return redt;
}
catch { return null; }
finally
{
oleCom.Dispose();
oleCon.Dispose();
}
}

这种方法有个小问题，就是需要一个架构文件：Schema.ini
这个文件记录了CSV文件的结构:

[CSV文件名.csv]
ColNameHeader=True
MaxScanRows=0
Format=CSVDelimited
CharacterSet=ANSI
Col="字段名1" int//类型
Col="字段名2" text//类型