Import a CSV file to SQL Server using SqlBulkCopy

Question

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();

    }

Solution

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
);

file containing such values:

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

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);

Comments

[Hogan]

The id column should be auto created for you and you don't have insert anything in it. What exactly was the code that didn't work when you are trying to do the first step -- focus on that-- that should work fine.

[Javy26]

Same thing Received an invalid column length from the bcp client for colid 1.

[John D]

This link might help: stackoverflow.com/questions/6651809/… With a normal insert, you don't add the auto-identity column to the DataTable at all - the database doesn't need it unless you indicate you want to supply explicit values.

[Javy26]

I need it though, it will be used as the unique identifier if duplicates are entered and I need to delete it(from my c# application that is. And that link doesn't address what I want because his issue was the SQLBulk Copy Options being set to keep identity. @JohnD

[John D]

I'm a bit confused - do you want to supply your own auto-increment numbers, or do you want the database to generate them for you? If you want the database to generate them for you, don't mention the column in the DataTable - the database doesn't expect to see it because it assumes responsibility for generating the auto-incremented numbers.

[Javy26]

@RezaAghaei If I do that then that means the data admin would have to edit that in SQL each time a user creates a file through the query in the application. That would become a bit of a hassle to the admin

[Reza Aghaei]

What's the problem exactly? You created a Table with an Identity column and now you want to insert data of a csv file to that table?

[Javy26]

@JohnD ok let's say I use the database values it generates for me. How would I access them in terms of a delete by row number operation?

[Reza Aghaei]

You should first turn on identity insert SET IDENTITY_INSERT YourTable ON and do the insert and then turn it off SET IDENTITY_INSERT YourTable OFF. Also you can insert values without passing identity column value.

[John D]

It's only during INSERT that auto-increment columns are special. To delete a row with a specific ID: delete from table where ID = 12345.

[Javy26]

string query = "SET IDENTITY_INSERT [dbo].[" + textBox1.Text + "] ON [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,)"; like this? @RezaAghaei

[Javy26]

@RezaAghaei turning the identity on then create the table

[Javy26]

cmd.CommandText = "SET IDENTITY_INSERT dbo.MyTable ON";

[Reza Aghaei]

I'll post an example for you.

[Reza Aghaei]

Just as a side note: 1) When posting a question, please consider posting a Minimal, Complete, and Verifiable example. Just compare my answer with your question to see how you can write a code which other can use simply. 2) Code formatting is really important, so always try to format your code and make it as readable as you can. • I hope these tips help you to use the site better :)

[Hogan]

very nice answer.