Click here to Skip to main content
14,774,521 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,

I am trying to import values from CSV to SQL server 2008 Database

I am inserting 3 columns to table.
Two values from CSV file. One value is from program.
When I run my code, rows are inserted with null values

But my column count is showing as 3.

Below is my code

i = 0;
fil="Desktop";
string line=string.Empty;
StreamReader sr = new StreamReader(filepath);
line = sr.ReadLine();
line = sr.ReadLine();

string[] value = line.Split(',');
DataTable dt = new DataTable();
DataRow row;
i=0;
foreach (string dc in value)
{
    if (i == 0)
    {
        dt.Columns.Add(new DataColumn(fil));
       // dt.Columns.Add(new DataColumn(DateTime.Now.ToString()));
        dt.Columns.Add(new DataColumn(dc));
        i++;
    }
}
string value1;
while ( !sr.EndOfStream )
{
    value1 = sr.ReadLine().Split(',')[0];
    value = new string[] { value1 };
    dt.Columns.Add(new DataColumn(value1));
    if(value.Length == (dt.Columns.Count)-3)
    {
        row = dt.NewRow();
        row.ItemArray = value;
        dt.Rows.Add(row);
    }
}
SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString,    SqlBulkCopyOptions.TableLock);
bc.DestinationTableName = "mssp_eps_compliance_report";
bc.BatchSize = dt.Rows.Count;
con.Open();
bc.WriteToServer(dt);
bc.Close();
con.Close();

Please help.

Thanks in advance.
Posted
Updated 11-Sep-13 20:56pm
v3
Comments
CHill60 12-Sep-13 16:35pm
   
Haven't got the software here at the moment to check this, but try putting a breakpoint before your bc.WriteToServer and have a look at the actual contents of dt. I don't think you should be adding the column to dt within the loop ... setup the datatable outside of the loop but populate its rows within the loop

1 solution

I guess you forget to add ColumnMappings to your SqlBulkCopy object.
   
Comments
Richard C Bishop 17-Sep-13 13:09pm
   
Solutions are not for guesses, comments, or questions.
Malte Klena 24-Sep-13 8:00am
   
Have you even tried SqlBulkCopy with or without ColumnMappings?
Probably not.
You're more of the nitpicker, who is bothered by single words or phrases.







This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900