这个入库CSV文件时会把这个数据的文件中的remoteIp这个字段内容
117.182.220.34看成是一个小数;然后入库数据就变成了这个117.1822;我应该如何解决这个问题呢;而且我的入库文件列数与数据库设计字段数是一样的;现在就是不知道怎么搞这个入库CSV文件数据变成小数问题;请各位懂得技术人支下招;非常感谢。
private void NODLimport(string tablename, string datebasename, string buttonname)
{
string xxx = "";
string Tag = "";
string connString = "server=192.0.0.1;uid=sa;pwd=xyz@1230;database=" + datebasename;
System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();
fd.Multiselect = true;
if (fd.ShowDialog() == DialogResult.OK)
{
foreach (string file in fd.FileNames)
{
xxx += file + "\n";
}
DialogResult dr = MessageBox.Show("是否将以下文件导入到【" + buttonname + "】\n" + xxx, "导入文件确认", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (dr == DialogResult.OK)
{
//label1.Visible = true;
richTextBox1.Text = null;
richTextBox2.Text = null;
foreach (string file in fd.FileNames)
{
richTextBox2.AppendText(DateTime.Now.ToString("HH:mm:ss ") + System.IO.Path.GetFileName(file) + "数据读取中...\n");
Tag = TransferData(file, tablename, connString);
richTextBox2.AppendText("--------------------\n");
richTextBox1.ScrollToCaret();
richTextBox2.ScrollToCaret();
}
if (Tag == "TAG") { MessageBox.Show("导入完成!"); }
else MessageBox.Show("导入失败!");
}
}
}
public string TransferData(string excelFile, string tablename, string connectionString)
{
DataSet ds = new DataSet();
// int yichang = 0; //异常标记
try
{
String codename = FileEncoding.EncodingType.GetFileEncodeType(excelFile).CodePage.ToString();
string fileName = excelFile.Remove(0, excelFile.LastIndexOf("\\") + 1);
string strConn = null;
string strExcel = "";
OleDbDataAdapter myCommand = null;
switch (fileName.Split('.').Last())
{
case "xls":
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
break;
case "xlsx":
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
break;
default:
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile.Remove(excelFile.LastIndexOf("\\") + 1) + ";Extended Properties='text;HDR=YES;FMT=Delimited;CharacterSet=" + codename + ";';";
strExcel = "select * from " + fileName;
break;
}
//获取全部数据
// string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
//string strConn = string.Format(@"Provider=Microsoft.Ace.OLEDB.12.0;Data Source={0};Extended Properties='Text;'", AppDomain.CurrentDomain.BaseDirectory);
// string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";Extended Properties='TEXT;HDR=Yes;FMT=Delimited;'"; // csv file:HDR=Yes-- first line is header
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = dt.Rows[0][2].ToString().Trim();
if (strExcel == "")
{
if (tablename == "TS_MYD_2019")
strExcel = string.Format("select * from [{0}A3:CE1048576]", sheetName);
else strExcel = string.Format("select * from [{0}]", sheetName);
}
conn.Close();
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, sheetName);
myCommand.Dispose(); //关闭对象
int rowsnum = ds.Tables[0].Rows.Count;
progressBar1.Maximum = rowsnum;//设置进度条的最大值为总条数
using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))
{
bcp.BulkCopyTimeout = 666666666;
bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
bcp.BatchSize = 100;//每次传输的行数
bcp.NotifyAfter = 100;//进度提示的行数
bcp.DestinationTableName = tablename;//目标表
bcp.WriteToServer(ds.Tables[0]);
bcp.Close();//关闭对象
}
using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
{
string luanlai = "";
sqlconn.Open();
System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
command.CommandTimeout = 6000; //设置相应时间
if (tablename == "TS_MYD_2019")
{
command.CommandText = "EXECUTE pro_update_ts_myd; ";
command.ExecuteNonQuery();
luanlai = "存储过程已执行!";
}
sqlconn.Close();
richTextBox1.SelectionColor = Color.Blue; richTextBox1.AppendText(System.IO.Path.GetFileName(excelFile) + "导入完成!!!!\n" + luanlai + "\n");
return "TAG"; //返回结果控制弹窗
}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
richTextBox1.SelectionColor = Color.Red;
richTextBox1.AppendText(System.IO.Path.GetFileName(excelFile) + "导入失败!!!!" + "\n");
richTextBox2.SelectionColor = Color.Red;
richTextBox2.AppendText(DateTime.Now.ToString("HH:mm:ss ") + "导入异常" + "\n");
if (ex.Message == "给定的 ColumnMapping 与源或目标中的任意列均不匹配。")
{
richTextBox1.AppendText("详细失败原因如下:导入的EXCEL的单元格列数比设定的列数多,请检查EXCEL的数据是否异常\n");
}
else richTextBox1.AppendText("详细失败原因如下:" + ex.Message + "\n");
Application.DoEvents();//重点,必须加上,否则父子窗体都假死
return "";//返回结果控制弹窗
}
}
private void btn_csvtest_Click(object sender, EventArgs e)
{
NODLimport("CSV_TEST", "RBZZY", "CSV_TEST临时增量导入" + this.btn_csvtest.Text);
}