SQLBulkcopy - Header of datatable getting inserted in the database table

Question

The header of the datatable is getting inserted as a record in the table. I am incrementing the row number but still the header gets inserted. Could someone tell me what the issue is ? If you see the code below the I am doing rows.MoveNext.

        XSSFWorkbook xssfwb;
        using (FileStream file = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
        {
            xssfwb = new XSSFWorkbook(file);
        }


        var sheet = xssfwb.GetSheetAt(0); // Change this to the worksheet you want to import.
        var rows = sheet.GetRowEnumerator();

        var dtExcelData = new DataTable();
        var linenumber = 0;
        DataRow dr;


        dtExcelData.Columns.AddRange(new DataColumn[3] { 
        new DataColumn("AccountNumber", typeof(string)),
        new DataColumn("Amount", typeof(decimal)),
        new DataColumn("Sedol",typeof(string)) });



        while (rows.MoveNext())
        {
            IRow row = (XSSFRow)rows.Current;
            linenumber++;

            row.GetCell(4).SetCellType(CellType.Numeric);

            if (row.GetCell(0) != null)
            {
                dr = dtExcelData.NewRow();
                dr["AccountNumber"] = row.GetCell(1).ToString();
                dr["Amount"] = decimal.Parse(row.GetCell(4).ToString());
                //dr["Amount"] = (decimal)row.GetCell(4).NumericCellValue;
                dr["Sedol"] = row.GetCell(11).ToString();


                dtExcelData.Rows.Add(dr);
            }
        }

        //DealingContext.ExecuteCommand("TRUNCATE TABLE [dbDealing].[MESSAGING].[Rebate]");
        DealingContext.ExecuteCommand("TRUNCATE TABLE [dbo].[Testxyz]");




        //Set the database table name
        using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(DealingContext.Connection.ConnectionString))
        {
            //sqlBulkCopy.DestinationTableName = "[dbo].[Testxyz]";
            sqlBulkCopy.DestinationTableName = "[dbo].[Testxyz]";

            //[OPTIONAL]: Map the Excel columns with that of the database table

            sqlBulkCopy.ColumnMappings.Add("AccountNumber", "AccountNumber");
            sqlBulkCopy.ColumnMappings.Add("Amount", "Amount");
            sqlBulkCopy.ColumnMappings.Add("Sedol", "Sedol");
            sqlBulkCopy.WriteToServer(dtExcelData);


        }

score 0 · Answer 1 · answered Jan 29 '15 at 17:22

The header is the first row returned from rows, just skip that row when you import.

//(snip)
    dtExcelData.Columns.AddRange(new DataColumn[3] { 
    new DataColumn("AccountNumber", typeof(string)),
    new DataColumn("Amount", typeof(decimal)),
    new DataColumn("Sedol",typeof(string)) });


    //Makes the header row the current row.
    rows.MoveNext();

    //Moves to the first row with data and loops till done.
    while (rows.MoveNext())
    {
        IRow row = (XSSFRow)rows.Current;
        linenumber++;

        row.GetCell(4).SetCellType(CellType.Numeric);

        if (row.GetCell(0) != null)
        {
//(snip)

SQLBulkcopy - Header of datatable getting inserted in the database table

1 Answers1