1、把csv文件中的数据读到datatable中
public static DataTable ImportCSV(string filePath)
{
DataTable ds = new DataTable();
using (StreamReader sw = new StreamReader(filePath,Encoding.Default))
{
string str = sw.ReadLine();
string[] columns = str.Split(',');
foreach (string name in columns)
{
ds.Columns.Add(name);
}
string line = sw.ReadLine();
while (line != null)
{
string[] data = line.Split(',');
ds.Rows.Add(data);
line = sw.ReadLine();
}
sw.Close();
}
return ds;
}
2、把csv的数据导入到数据库中
public static void ImportCSVToDB(string filePath, string type, string dbTable)
{
string sqlString = "BULT insert " + dbTable + " from '" + filePath + "' WITH(FILEDTERMINATOR=',',ROWTERMINATOR='\n')";
using (SqlConnection conn = GetConn(conStr))
{
conn.Open();
SqlCommand command = new SqlCommand(sqlString, conn);
return command.ExecuteNonQuery();
}
}
3、excel的数据读入datatable中
public static DataTable ImportXLS(string filePath)
{
string fileName = System.IO.Path.GetFileName(filePath);
string path = System.IO.Path.GetDirectoryName(filePath);
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties = 'Excel 8.0;HDR=YES;IMEX=1;'";
DataTable ds = new DataTable();
using (OleDbConnection conn = new OleDbConnection(strCon))
{
conn.Open();
OleDbDataAdapter adpt = new OleDbDataAdapter("select * from [Sheet1$]", conn);
adpt.Fill(ds);
}
return ds;
}
4、导出数据到csv文件中。(这种方式速度快)
public static void ExportToCSV(DataTable ds, string filePath)
{
using (StreamWriter sw = new StreamWriter(filePath, false, Encoding.Unicode))
{
for (int i = 0; i < ds.Columns.Count; i++)
{
sw.Write(ds.Columns[i].ColumnName + "\t");
}
sw.Write("\n");
for (int i = 0; i < ds.Rows.Count; i++)
{
for (int j = 0; j < ds.Columns.Count; j++)
{
sw.Write(ds.Rows[i][j].ToString() + "\t");
}
sw.Write("\n");
}
sw.Close();
}
return ;
}