How to upload only non-empty rows of Excel spreadsheet using oledb in C#?

Question

I am importing excel sheet to DataTable using oledb connection as below.

private static DataTable UploadExcelSheet(string fileName)
    {
        DataTable uploadDataTable;
        using (OleDbConnection objXConn = new OleDbConnection())
        {
            objXConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName +
                                            ";Extended Properties=\"Excel 12.0;IMEX=1\"";

            objXConn.Open();

            OleDbCommand objCommand =
                new OleDbCommand("SELECT * FROM Template$ ", objXConn);
            OleDbDataAdapter objDataAdapter = new OleDbDataAdapter();

            // retrieve the Select command for the Spreadsheet
            objDataAdapter.SelectCommand = objCommand;

            // Create a DataSet
            DataSet objDataSet = new DataSet();

            // Populate the DataSet with the spreadsheet worksheet data
            objDataAdapter.Fill(objDataSet);
            uploadDataTable = objDataSet.Tables[0];
        }

        return uploadDataTable;
    }

Everything is working fine but problem comes when user delete content of few rows before uploading the excel. It reads those empty rows as well along with non empty rows, and saving data in database fails because of business rule violation (mandatory field missing). What I tried is putting where condition in query :

"SELECT * FROM  WHERE  not [CandidateId*] = 0 or not [Firstname*] = '' or not [Lastname] = '' or not [type*] = '' or not [DOB*] =" + DBNull.Value

So it will select only those rows which has data. But I am not able to compare non string field i.e. Date, Integer etc. Which are comming as DBNull when empty. Can any one please suggest the way to do it, I dont want to use DataReader.

Veach

Answer 1

Use

".. WHERE NOT ([Lastname] = '' OR [DOB*] IS NULL OR ... )

Capillarity

+1, This solution will filter it first before importing. Saving a lot of time if your data contain a lot of empty data. – Limn

Thank You. This worked for me as well. But why do i get an error every time a OR-relation in WHERE-clause is used WHERE (([Cond1a] = '' & [Cond1b] = '' ) OR ([Cond2a] = '' AND [Cond2b] = '')). – Horlacher

@Horlacher - Check the &; if that does not help, post a new question describing your problem in detail. – Capillarity

@Capillarity How about if I want to filter to not include rows that has empty on all its columns? *All the columns' name is dynamic – Contraband

Answer 2

Expanding on vc's answer, 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 System.DBNull || string.Compare((field as string).Trim(), string.Empty) == 0)).CopyToDataTable();

Tardif

I was getting a null reference exception during the trim operation, so I had to include || (field as string) == null before the string.compare, and it works. – Recognizance

Answer 3

How about filtering the rows after the query has executed using Linq to object:

var filteredRows = uploadDataTable.Rows.Cast<DataRow>().Where(
  row => row.ItemArray.Any(field => !(field is System.DBNull)));

Contributory

Thanks vc, this is correct way, i thought to do this, i dint try but it should work fine. But I was thinking to do it at the time of reading data from excel only, to avoid unncessery extra work/filtering after reading. – Veach

@Rahul, I'm not looking for reputation but if you think Ekkehard's answer or mine was helpful, consider voting up + accepting the answers – Contributory

Answer 4

Expanding on the previous answers, this worked for me. Delete rows where all fields are null.

Dim deleteRows = From row In result.AsEnumerable
                 Where row.ItemArray.All(Function(field) Equals(field, DBNull.Value))

For Each deleteRow In deleteRows
    deleteRow.Delete()
Next

Deficient