将 CSV 文件中的 IP 地址导入 SQL 服务器时出现问题

本文关键字：服务器 SQL 问题 导入 地址 文件 CSV IP | 更新日期: 2023-09-27 17:56:49

我正在研究SQL批量复制窗口应用程序，我需要将csv文件导入SQL服务器。但是IP地址数据似乎存在问题。

csv 文件数据以逗号分隔，示例数据如下

Email,FirstName,LastName,Company,Address1,Address2,City,State/Province,Zip/PostalCode,Country,Phone,SecondaryPhone,Fax,DateEntered,OriginalIP,LatestIP
ab@outlook.com,,,,,,,,,,,,,2/29/16 15:56,12.251.217.166,12.251.217.166
bc@gmail.com,,,,,,,,,,,,,2/29/16 15:57,70.57.244.202,70.57.244.202

但是当导入时，除了最后两列，即lastip和latestip之外，一切都很好。

正在导入的值是

12.2512,12.2512 for 1st row and same for 2nd row ie. 70.5724    ,70.5724

代码如下

DataSet ds = new DataSet();
            try
            {
                // Creates and opens an ODBC connection
                string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + this._dirCsv.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
                string sqlSelect;
                OdbcConnection conn;
                conn = new OdbcConnection(strConnString.Trim());
                conn.Open();
                //Creates the select command text
                if (numberOfRows == -1)
                {
                    sqlSelect = "select * from [" + this.FileNevCsv.Trim() + "]";
                }
                else
                {
                    sqlSelect = "select top " + numberOfRows + " * from [" + this.FileNevCsv.Trim() + "]";
                }
                //Creates the data adapter
                OdbcDataAdapter objOledbDa = new OdbcDataAdapter(sqlSelect, conn);
                //Fills dataset with the records from CSV file
                objOledbDa.Fill(ds, "csv");
                //closes the connection
                conn.Close();
            }
            catch (Exception e) //Error
            {
                MessageBox.Show(e.Message, @"Error - LoadCSV", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            return ds;

. 架构.ini

如下 
[CONTACTS Jan Feb 2016 New.csv]
ColNameHeader=True
Format=Delimited(,)
MaxScanRows=25
CharacterSet=ANSI

和架构编写代码如下

private void WriteSchema()
        {
            try
            {
                FileStream fsOutput = new FileStream(this._dirCsv + "''schema.ini", FileMode.Create, FileAccess.Write);
                StreamWriter srOutput = new StreamWriter(fsOutput);
                string s1, s2, s3, s4, s5;
                s1 = "[" + this.FileNevCsv + "]";
                s2 = "ColNameHeader=" + chkFirstRowColumnNames.Checked.ToString();
                s3 = "Format=" + this._strFormat;
                s4 = "MaxScanRows=25";
                s5 = "CharacterSet=" + this._strEncoding;
                srOutput.WriteLine(s1.ToString() + "'r'n" + s2.ToString() + "'r'n" + s3.ToString() + "'r'n" + s4.ToString() + "'r'n" + s5.ToString());
                srOutput.Close();
                fsOutput.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, @"writeSchema");
            }
            finally
            { }
        }

将 CSV 文件中的 IP 地址导入 SQL 服务器时出现问题

谢谢@les-h和@deroby将列数据类型添加到架构文件解决了这个问题

这是更新的架构文件

[CONTACTS Jan Feb 2016 New.csv]
ColNameHeader=True
Format=Delimited(,)
MaxScanRows=25
CharacterSet=ANSI
Col1="Email" Text
Col2="FirstName" Text
Col3="LastName" Text
Col4="Company" Text
Col5="Address1" Text
Col6="Address2" Text
Col7="City" Text
Col8="State/Province" Text
Col9="Zip/PostalCode" Text
Col10="Country" Text
Col11="Phone" Text
Col12="SecondaryPhone" Text
Col13="Fax" Text
Col14="DateEntered" Text
Col15="OriginalIP" Text
Col16="LatestIP" Text
相关文章：