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

    }

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. — Hogan
– Hogan, Commented Nov 17, 2016 at 20:19
Same thing Received an invalid column length from the bcp client for colid 1. — Javy26
– Javy26, Commented Nov 17, 2016 at 20:22
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. — John D
– John D, Commented Nov 17, 2016 at 20:26
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 — Javy26
– Javy26, Commented Nov 17, 2016 at 20:33
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. — John D
– John D, Commented Nov 17, 2016 at 20:39

Reza Aghaei · Accepted Answer · 2019-10-11 05:11:51Z

16

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

edited Oct 11, 2019 at 5:11

answered Nov 17, 2016 at 21:15

Reza Aghaei

126k18 gold badges239 silver badges447 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Reza Aghaei Over a year ago

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 Over a year ago

very nice answer.

Collectives™ on Stack Overflow

Import a CSV file to SQL Server using SqlBulkCopy

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related