.csv File to Mysql

*********************************************************************
*******sql sentence in mysql
*********************************************************************
LOAD DATA LOCAL INFILE 'C:\\Users\\1382919\\Desktop\\1.csv' 
INTO TABLE terimal 
CHARACTER SET utf8
FIELDS TERMINATED BY ',' 
ENCLOSED BY '' 
LINES TERMINATED BY '\r\n' 
IGNORE 3 LINES

UPDATE terimal SET terimal.Test_Date = STR_TO_DATE(terimal.Test_Date,'%m/%d/%Y %H:%i') WHERE 1=1
*********************************************************************
*******THIS SQL SENTENCE HAS SOME ERROR OF FAIL PATH IN C#
*******SO WE UES MySqlBulkLoader FOR CSV TO DATABASE
*********************************************************************
//----------------------SQL--------------------------//
string sql = "LOAD DATA LOCAL INFILE'" + file + "'";
sql += "INTO TABLE terimal CHARACTER SET utf8 FIELDS TERMINATED BY ',' ";
sql += "ENCLOSED BY ''";
sql += "LINES TERMINATED BY '\r\n' IGNORE 3 LINES";
//---------------------- MySqlBulkLoader--------------------//
using System.IO

string file = fi.FullName;
FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read);
StreamReader sr = new StreamReader(fs);
string csv = sr.ReadToEnd();
int insertCount = 0;
sr.Close();
string tmpPath = Path.GetTempFileName();
File.WriteAllText(tmpPath, csv);
try
{
    using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["ictnpf"].ConnectionString))
    {
        conn.Open();
        MySqlBulkLoader bulk = new MySqlBulkLoader(conn)  //数据批量导入:datatable转CSV->导入库
        {
            FieldTerminator = ",",
            FieldQuotationCharacter = '"',
            EscapeCharacter = '"',
            LineTerminator = "\r\n",   //行结束标志
            FileName = tmpPath,
            NumberOfLinesToSkip = 3,   //前几行不读取
            TableName = "terimal",
        };
        insertCount = bulk.Load();
    }
}
catch (Exception e){
    MessageBox.Show(e.Message);
    return false;
}
File.Delete(tmpPath);
return true;
  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值