关于批量文本数据导入的问题
我用oledb访问一个文本文件,当它是普通的ASCII格式的文件时,用SqlBulkCopy可以正常导入,但是只要变成unicode格式的文本时,导入进去的就全变为NULL了,是不是连接串写的有问题,怎样访问Unicode的文本?
public void TransferData(string excelFile, string sheetName, string connectionString)
{
DataSet ds = new DataSet();
try
{
//获取全部数据
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source="+ excelFile + ";" + "Extended Properties=\"text;HDR=YES;FMT=Delimited;Encoding=UNICODE;\";";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = string.Format("select * from [{0}.csv]", sheetName);
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, sheetName);
//用bcp导入数据
using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))
{
bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
bcp.BatchSize = 100;//每次传输的行数
bcp.NotifyAfter = 100;//进度提示的行数
bcp.DestinationTableName = sheetName;//目标表
bcp.WriteToServer(ds.Tables[0]);
}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
}