When I import Excel, an extra row is added in database by C#

Question

I'm importing an Excel file by C# through below code. But when I check at database I see one null row is added.

Can you help me to change this code to delete this null row?

public static class ExcelTools
{
    static DataSet result = new DataSet();
    public static DataTable ExcelToDataTable(string path)
    {
        var pck = new OfficeOpenXml.ExcelPackage();
        pck.Load(File.OpenRead(path));
        var ws = pck.Workbook.Worksheets.First();
        DataTable tbl = new DataTable();
        bool hasHeader = true;
        foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
        {
            tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
        }
        var startRow = hasHeader ? 2 : 1;
        for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
        {
            var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
            var row = tbl.NewRow();
            foreach (var cell in wsRow)
            {
                row[cell.Start.Column - 1] = cell.Text;
            }
            tbl.Rows.Add(row);
        }
        pck.Dispose();
        tbl.TableName = System.IO.Path.GetFileNameWithoutExtension(path);
        return tbl;
    }
}

I go to database select that table and click on select top 1000 rows and check the values — Shafieh F, Nov 12 '16 at 10:00
You can [edit] your post and add a screenshot by pressing `Ctrl` + `G` — Thomas Ayoub, Nov 12 '16 at 14:37
why not using oledbcommand to import excel into datatable. if interested visit this [link](http://stackoverflow.com/questions/18511576/reading-excel-file-using-oledb-data-provider) — Hadi, Nov 12 '16 at 22:49
Please see the picture I uploaded. one null row is added after each import. — Shafieh F, Nov 15 '16 at 07:13

score 1 · Answer 1 · answered Nov 12 '16 at 14:56

1

You might have been fooled by the last NULL row:

that allows you to add a row manually. Which means that it isn't in your Database. To be sure, you can count null rows:

select sum(case when [COL] is null then 1 else 0 end) count_nulls
from [TABLE]

answered Nov 12 '16 at 14:56

Thomas Ayoub

29,063
15
95
142

Please see the picture I uploaded. one null row is added after each import. – Shafieh F Nov 15 '16 at 07:11

When I import Excel, an extra row is added in database by C#

1 Answers1