2016年4月27日 星期三

C# CSV 匯入、匯出

C# 做CSV之匯入匯出,透過以下方式:

CSV 格式之各欄位可以透過逗點區分 ","

Example:
Jill Smith 50
Eve Jackson 94

CSV Format:
Jill,Smith,50 \n
Eve,Jackson,94

匯入方法 (以從 CSV匯入至資料庫中):


bool ImportCSV(string path) {
    Encoding utf8bom = new UTF8Encoding(true); //必須使用 UTF-8 BOM 開啟,否則會有亂碼

    StreamReader sr = new StreamReader(path,Encoding.Default); //path 就是 .CSV 檔案的路徑
    SqlCeCommand cmd = conn.CreateCommand();

    sr.ReadLine();
    while (sr.Peek() >= 0) {
        string ss = sr.ReadLine();
        string[] line = ss.Split(',');
        cmd.CommandText = "INSERT INTO Game(ID,Game,Home,Away,Score,Success,Date) VALUES(@A,@B,@C,@D,@E,@F,@G)";
        cmd.Parameters.Add("@A", line[0]);
        cmd.Parameters.Add("@B", line[1]);
        cmd.Parameters.Add("@C", line[2]);
        cmd.Parameters.Add("@D", line[3]);
        cmd.Parameters.Add("@E", line[4]);
        cmd.Parameters.Add("@F", line[5]);
        cmd.Parameters.Add("@G", line[6]);
        cmd.Prepare();
        cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
    }
    MessageBox.Show("SUCCESS!");
    return true;
}

匯出方法 (從SQL中匯出):

StringBuilder sb = new StringBuilder();
while(ds.Read()){
    sb.Append(String.Join(ds.GetString(1),","));
    sb.AppendLine()
}
var filePath = @"C:\Export.CSV";
Encoding utf8withoutbom = new UTF8Encoding(true);
TextWriter tw = new StreamWriter(filePath,false,utf8withoutbom);
tw.WriteLine(sb.ToString());
tw.Close();

沒有留言:

張貼留言

© ERIC RILEY , 自由無須告知轉貼
Background Japanese Sayagata by Olga Libby