How to remove empty rows from DataTable

Question

I am working on importing data from an Excel sheet to database. The Excel sheet contains few empty rows and I want to remove those empty rows, then insert cleared data into database.
I have written a code by referring other code, this is the code for inserting values:

OleDbConnection cnn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + txtExcelFile.Text + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'");
//DataTable dt = new DataTable();

try
{
    cnn.Open();
    OleDbDataAdapter data = new OleDbDataAdapter("select * from [Customers$]", cnn);
    data.Fill(dsExcel);
    dgvCustomers.ColumnHeadersVisible = false;

    SqlConnection connection = new SqlConnection("Data Source=COMPUTER-8EB749;Initial Catalog=KITS;Integrated Security=true");
    connection.Open();
    for (int i = 0; i < dsExcel.Tables[0].Rows.Count; i++)
    {
        string ID = ds.Tables[0].Rows[i][0].ToString();
        Int16 CustID = Convert.ToInt16(ID);
        string CustName = dsExcel.Tables[0].Rows[i][1].ToString();
        string CardScheme = dsExcel.Tables[0].Rows[i][2].ToString();
        string Outlet = dsExcel.Tables[0].Rows[i][3].ToString();
        string TerminalNum = dsExcel.Tables[0].Rows[i][4].ToString();
        Int32 Terminal = Convert.ToInt32(TerminalNum);
        string Date1 = dsExcel.Tables[0].Rows[i][5].ToString();
        DateTime Date = Convert.ToDateTime(Date1);
        string Time = dsExcel.Tables[0].Rows[i][6].ToString();
        DateTime DateTime = Convert.ToDateTime(Time);
        string Amount1 = ds.Tables[0].Rows[i][7].ToString();
        double Amount = Convert.ToDouble(Amount1);

        SqlCommand com = new SqlCommand("insert into Customer(CustID,CustName,CardScheme,Outlet,TerminalNum,TranDate,TranDateTime,Amount) values ('" + CustID + "','" + CustName + "','" + CardScheme + "','" + Outlet + "','" + Terminal + "','" + Date + "','" + DateTime + "','" + Amount + "')", connection);
        com.ExecuteNonQuery();
    }
    connection.Close();
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}
finally
{
    MessageBox.Show("Data Inserted Successfully.");
}

Can anyone say me how can I remove empty rows so that i can insert only data?!

Solution 1

Try this.

public bool InsertRowsToDataBase()
{
    try
    {
        DataTable excelTable = new DataTable();

        string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + txtExcelFile.Text + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";
        using (OleDbConnection cnn = new OleDbConnection(connString))
        {
            string query = "select * from [Customers$]";
            using (OleDbDataAdapter data = new OleDbDataAdapter(query, cnn))
            {
                data.Fill(excelTable);
            }
        }
        dgvCustomers.ColumnHeadersVisible = false;

        connString = "Data Source=COMPUTER-8EB749;Initial Catalog=KITS;Integrated Security=true";
        using (SqlConnection connection = new SqlConnection(connString))
        {
            connection.Open();
            for (int i = 0; i < excelTable.Rows.Length; i++)
            {
                //takes from the 3rd row
                if (i > 1)
                {
                    DataRow row = excelTable.Rows[i];
                    object ID = row[0];
                    if (ID != null && !String.IsNullOrEmpty(ID.ToString().Trim()))
                    {
                        Int16 CustID = Convert.ToInt16(ID);
                        string CustName = row[1].ToString();
                        string CardScheme = row[2].ToString();
                        string Outlet = row[3].ToString();
                        string TerminalNum = row[4].ToString();
                        Int32 Terminal = Convert.ToInt32(TerminalNum);
                        string Date1 = row[5].ToString();
                        DateTime Date = Convert.ToDateTime(Date1);
                        string Time = row[6].ToString();
                        DateTime DateTime = Convert.ToDateTime(Time);
                        string Amount1 = row[7].ToString();
                        double Amount = Convert.ToDouble(Amount1);

                        string columnNames = "CustID,CustName,CardScheme,Outlet,TerminalNum,TranDate,TranDateTime,Amount";
                        string query = String.Format("insert into Customer(0}) values ('{1}', '{2}','{3}','{4}','{5}','{6}','{7}','{8}')",
                            columnNames, CustID, CustName, CardScheme, Outlet, Terminal, Date, DateTime, Amount);
                        using (SqlCommand com = new SqlCommand(query, connection))
                        {
                            com.ExecuteNonQuery();
                        }
                    }
                }
                //this is your last row. do whatever you want with this
                DataRow lastRow = excelTable.Rows[excelTable.Rows.Count - 1];
            }
        }
        return true;
    }
    catch (Exception exception)
    {
        Elmah.ErrorSignal.FromCurrentContext().Raise(exception);
        return false;
    }
}

Please note that I am just checking if ID is null and not inserting any such rows as ID will be the PK in your table.

Solution 2

This will remove all rows that which each of it's columns contain either nothing or white space:

dataTable = dataTable.Rows
    .Cast<DataRow>()
    .Where(row => !row.ItemArray.All(field => field is DBNull || 
                                     string.IsNullOrWhiteSpace(field as string)))
    .CopyToDataTable();

Solution 3

This will remove all empty rows from datable:

DataTable dt = dt.Rows
                 .Cast<DataRow>()
                 .Where(row => !row.ItemArray.All(f => f is DBNull))
                 .CopyToDataTable();

OR

DataTable dt = dt.Rows
                 .Cast<DataRow>()
                 .Where(row => !row.ItemArray.All(f => f is DBNull || 
                                  string.IsNullOrEmpty(f as string ?? f.ToString())))
                 .CopyToDataTable();

Solution 4

To check Empty Rows

Foreach(DataRow as row in datable.Rows) {
    var isEmpty = row.ItemArray.All(c => c is DBNull);
    if(!isEmpty) {
        //Your Logic
    }
}

Solution 5

try
{
    OpenOleDBConnection();
    OleDbDataAdapter dataAdapter = new OleDbDataAdapter("select * from [" + SelectedSheet + "]", Connection);

    dataAdapter.Fill(DataTable);

    if ((DataTable != null) && (DataTable.Rows != null) && (DataTable.Rows.Count > 0))
    {
        List<System.Data.DataRow> removeRowIndex = new List<System.Data.DataRow>();
        int RowCounter = 0;
        foreach (System.Data.DataRow dRow in DataTable.Rows)
        {                            
            for(int index = 0; index < DataTable.Columns.Count; index++)
            {
                if (dRow[index] == DBNull.Value)  
                {
                    removeRowIndex.Add(dRow);
                    break;
                }
                else if (string.IsNullOrEmpty(dRow[index].ToString().Trim()))
                {
                    removeRowIndex.Add(dRow);
                    break;
                }
            }
            RowCounter++;
        }
        // Remove all blank of in-valid rows
        foreach (System.Data.DataRow rowIndex in removeRowIndex)
        {
            DataTable.Rows.Remove(rowIndex);
        }
    }
}
catch(Exception e)
{
    WPFMessageBox.Show(e.Message, Globalization.GetValue("Import_ImportOption_FormHeader"), WPFMessageBoxButtons.OK, WPFMessageBoxImage.Error);
}
finally
{
    CloseOleDBConnection();
}

Here I m also skipping the rows if they have blank entry in any of the row.

View more solutions

Comments

[Shadow The Kid Wizard]

As a side note, you really better dispose properly of the objects by using using block e.g. using (OleDbConnection cnn = new OleDbConnection(...))

[Nazima]

May i know which part are you indicating to continue.. 'if(string.IsNullOrEmpty(ID + CustName + CardScheme /*.. and so on */)) { continue; }'

[Andreas Ågren]

@Nazima Your for-loop, right before this statement: SqlCommand com = new SqlCommand("insert .... That way you will not insert empty data.

[Nazima]

i have tried the way u said to place the for loop in if stmt..it is showing me an error..

[Andreas Ågren]

@Nazima I have updated my answer to indicate what you should update in your for-loop.

[Nazima]

excel sheet contains headers in 2nd row..when i am trying to excute the app it is showing error that "input string is not in correct format"..this error is occurring due to column names..may i know how can i go thru col names first and then the values..what i have to change in if condition..

[naveen]

and is the first row empty? and where are you getting the error. which line?

[Nazima]

first row contains subheading i.e. Date: which is to be removed..thts working fine..second row contains column names and from third row onwards the data starts...i know why the error is coming because in second row the col name is of type string not int..that is why it is showing error..error is shown in this line Int16 CustID = Convert.ToInt16(ID); beacuse the row contains the column names not values..so i want to know how can i go thru first column names and then values..hope u understood

[Davide Piras]

mmm to my knowledge a PK field cannot be NULL!

[Zenwalker]

I just said it could bea PK field. Not literally it is. Some thing like in a student table, name column cant be null. So not xactly an PK as in DB style

[Nazima]

Thank u..it worked..and i need one more favour..i am new in .net and would like to improve in coding..Can u suggest any books or sites or any tips and tricks for coding..I Will be glad if u suggest.

[naveen]

books like C# in a nutshell, C# in depth if you have intermediate knowledge. Head first C# if you are beginning. And if you have a good and cheap internet connection go to asp.net/general/videos. read blogs of scott gu, jon skeet, dave ward, eric lippert and so on... all the best

[Nazima]

Naveen may i know how can i retireve last row value from datatable.The last row of excel sheet contains the total no.of records and Total amount which i need to insert it to another table.i have posted an image also so u can get a clear idea..

[Nazima]

hi..may i know how can i insert only new data from excel sheet to database without inserting previous data..i am trying to use NewRow() method of datarow..am i correct..say me few steps how to do it..

[naveen]

please post it as a new question nazima.

[rsenna]

Why would someone (with some sense) make a connection to a remote database server, iterate over all records and remove empty lines?? Why don't do that directly on the database server, using TSQL code (since you obviously do have write access to it)? This is clearly a smart line of LINQ code - but it would not be that smart to use it.

[Josh M.]

@tEsTA - you assume the data comes from a database. It could come from CSV, Excel, etc. This is great for clearing out extra/empty lines when you pull in data from Excel to a DataTable. Excel leaves empty lines at the end of worksheets all the time, and they you have to write/run a macro to truncate the worksheet - so yes, this is useful!

[rsenna]

@JoshM. You're right - after all, this SO question is about an Excel spreadsheet. But I'll keep my advise, since it could be used for a real database table (by some novice developer who doesn't know better).

[jmattheis]

Could you format your code and add an explanation to it?

[Ray Koren]

This only worked for half of my rows of empty strings. I Used IsNullOrWhiteSpace as opposed to IsNullOrEmpty and it worked for all my cases.

[shA.t]

Note that field as string will result as null for a value type, so I suggest using field as string ?? field.ToString() ;).

[karunakar bhogyari]

I have got below exception when there are only headers and no data for columns. "The source contains no DataRows. "

[Elikill58]

Can you edit your answer to explain why your post is different that other 14 answers ? And why it fix the question ?

[PCM]

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value.

[General Grievance]

Why return when you have no function definition?

[Casey Crookston]

10 years later, worked like a charm!