C# ASP.NET CSV檔案匯入資料庫)

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.OleDb;

using System.Data.Sql;

using System.Data.SqlClient;

using System.IO;

 

namespace HPRSP.CommonDataObject

{

public class CSVHelper

{

 

private string path;

private string fileName;

/// <summary>

/// z建構函式

/// </summary>

public CSVHelper(string filePath, string fileName)

{

this.path = filePath;

this.fileName = fileName;

}

 

/// <summary>

/// 從Csv中讀取資料

/// </summary>

/// <returns></returns>

public DataTable Read()

{

return Read(null);

}

/// <summary>

/// 通過檔案流的方式來讀取CSV檔案，預設第一列為標題列，列之間用逗號分隔

/// </summary>

/// <param name="files"></param>

/// <returns></returns>

public DataTable ReadCsvFileToTable()

{

return ReadCsvFileToTable(true, ',');

}

/// <summary>

/// 通過檔案流的方式來讀取CSV檔案，預設列之間用逗號分隔

/// </summary>

/// <param name="files">檔案名稱</param>

/// <param name="HeadYes">第一行是否為欄位標題</param>

/// <returns></returns>

public DataTable ReadCsvFileToTable(bool HeadYes)

{

return ReadCsvFileToTable(HeadYes, ',');

}

/// <summary>

/// 通過檔案流的方式來讀取CSV檔案

/// </summary>

/// <param name="files">檔案名稱</param>

/// <param name="HeadYes">第一行是否為欄位標題</param>

/// <param name="span">分隔字元</param>

/// <returns></returns>

public DataTable ReadCsvFileToTable(bool HeadYes, char span)

{

//檔案路徑和檔案名稱

string files = path + fileName;

DataTable dt = new DataTable();

StreamReader fileReader = new StreamReader(files, Encoding.Default);

try

{

//是否為第一行（如果HeadYes為TRUE，則第一行為標題列）

int lsi = 0;

//列之間的分隔字元

char cv = span;

while (fileReader.EndOfStream == false)

{

string line = fileReader.ReadLine();

string[] y = line.Split(cv);

//第一行為標題列

if (HeadYes == true)

{

//第一行

if (lsi == 0)

{

for (int i = 0; i < y.Length; i++)

{

dt.Columns.Add(y[i].Trim().ToString());

}

lsi++;

}

//從第二列開始為資料列

else

{

DataRow dr = dt.NewRow();

for (int i = 0; i < y.Length; i++)

{

dr[i] = y[i].Trim();

}

dt.Rows.Add(dr);

}

}

//第一行不為標題列

else

{

if (lsi == 0)

{

for (int i = 0; i < y.Length; i++)

{

dt.Columns.Add("Col" + i.ToString());

}

lsi++;

}

DataRow dr = dt.NewRow();

for (int i = 0; i < y.Length; i++)

{

dr[i] = y[i].Trim();

}

dt.Rows.Add(dr);

}

}

}

catch (Exception ex)

{

throw ex;

}

finally

{

fileReader.Close();

fileReader.Dispose();

}

 

return dt;

}

 

/// <summary>

/// 從csv中讀取資料

/// </summary>

/// <param name="colNames">列名列表，可為空白</param>

/// <returns></returns>

private DataTable Read(string[] colNames)

{

string sql = CreateSql(colNames);

return ExecuteTable(sql);

}

 

/// <summary>

/// 通過執行sql語句，從Csv中讀取資料

/// </summary>

/// <param name="sql">sql語句</param>

/// <returns></returns>

private DataTable ExecuteTable(string sql)

{

DataTable table = new DataTable();

using (OleDbConnection connection = new OleDbConnection(GetConnString(true)))

{

OleDbCommand command = connection.CreateCommand();

command.CommandText = sql;

 

OleDbDataAdapter adapter = new OleDbDataAdapter(command);

adapter.Fill(table);

}

return table;

}

 

#region 私人方法

 

private string CreateSql(string[] colNames)

{

string cols = "*";

if (null != colNames && colNames.Length > 0)

{

StringBuilder colList = new StringBuilder();

for (int i = 0; i < colNames.Length - 1; i++)

{

colList.AppendFormat("[{0}],", colNames[i]);

}

colList.AppendFormat("[{0}]", colList[colList.Length]);

cols = colList.ToString();

}

return string.Format("select {0} from {1}", cols, fileName);

}

private string GetConnString(bool IsHeaderRow)

{

return string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Text;FMT=Delimited; HDR={0}';data source={1}", IsHeaderRow ? "Yes" : "No", path);

}

#endregion

}

}

 

轉自http://www.cnblogs.com/starxp/articles/2387804.html