使用SqlBulkCopy将CSV文件导入SQL Server [英] Import a CSV file to SQL Server using SqlBulkCopy

问题描述

我具有创建表然后接收CSV文件的功能。我需要一个ID列，该列会自动递增，以供以后使用。因此，我使用ID字段运行以下查询。在开始运作之前，因为CSV文件最初没有ID列，所以当将其发送到数据库时会出现错误。因此，我的下一个想法是将没有值的空白ID列添加到CSV文件，然后再次尝试查询。仍然有问题。我的C＃代码中的错误是：从bcp客户端收到针对colid 1的无效列长度。猜测是ID列。有没有一种方法可以同时插入此ID列并自动递增？

I have this function that creates a table and then receives a CSV File. I need an ID column in it that auto increments which would be used for later use. Therefore I ran the below query with the ID field. Before it wasn't working because initially the CSV File had no ID column so when time came for it to be sent to the database there would be an error. So my next idea was to add a blank ID column with no values to the CSV file and then attempt the query again. Still having an issue. The error in my c# code is: "Received an invalid column length from the bcp client for colid 1." Which am guessing is the ID column. Is there a way to have this ID column inserted and auto increment at the same time?

private void button2_Click(object sender, EventArgs e)
    {
        string connectionString = "Data Source=LPMSW09000012JD\\SQLEXPRESS;Initial Catalog=Pharmacies;Integrated Security=True";
        string query = "CREATE TABLE [dbo].[" + textBox1.Text + "](" +"ID int IDENTITY (1,1) PRIMARY KEY," + "[Code] [varchar] (13) NOT NULL," +
       "[Description] [varchar] (50) NOT NULL," + "[NDC] [varchar] (50) NULL," +
        "[Supplier Code] [varchar] (38) NULL," + "[UOM] [varchar] (8) NULL," + "[Size] [varchar] (8) NULL,)";


        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(query, connection);
            command.Connection.Open();
            command.ExecuteNonQuery();
        }

        SqlConnection con = new SqlConnection("Data Source=LPMSW09000012JD\\SQLEXPRESS;Initial Catalog=Pharmacies;Integrated Security=True");
        string filepath = textBox2.Text; //"C:\\Users\\jdavis\\Desktop\\CRF_105402_New Port Maria Rx.csv";
        StreamReader sr = new StreamReader(filepath);
        string line = sr.ReadLine();
        string[] value = line.Split(',');
        DataTable dt = new DataTable();
        DataRow row;
        foreach (string dc in value)
        {
            dt.Columns.Add(new DataColumn(dc));
        }

        while (!sr.EndOfStream)
        {
            value = sr.ReadLine().Split(',');
            if (value.Length == dt.Columns.Count)
            {
                row = dt.NewRow();
                row.ItemArray = value;
                dt.Rows.Add(row);
            }
        }
        SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
        bc.DestinationTableName = textBox1.Text;
        bc.BatchSize = dt.Rows.Count;
        con.Open();
        bc.WriteToServer(dt);
        bc.Close();
        con.Close();

    }

推荐答案

我想您在SQL Server中有一个以这种方式创建的表：

I suppose you have a table in SQL Server which you created this way:

CREATE TABLE [dbo].[Table1] (
    [Column1]   INT           IDENTITY (1, 1) NOT NULL,
    [Column2]   NVARCHAR (50) NOT NULL
);

包含以下值的文件：

Column1,Column2
1,N1
2,N2
3,N3

因此，要将值批量插入表中，可以使用 SqlBulkCopy 这样：

So to bulk insert values to the table you can use SqlBulkCopy this way:

var lines = System.IO.File.ReadAllLines(@"d:\data.txt");
if (lines.Count() == 0) return;
var columns = lines[0].Split(',');
var table = new DataTable();
foreach (var c in columns)
    table.Columns.Add(c);

for (int i = 1; i < lines.Count() - 1; i++)
    table.Rows.Add(lines[i].Split(','));

var connection = @"your connection string";
var sqlBulk = new SqlBulkCopy(connection);
sqlBulk.DestinationTableName = "Table1";
sqlBulk.WriteToServer(table);

这篇关于使用SqlBulkCopy将CSV文件导入SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！