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.

score 18 · Answer 1 · answered Feb 10 '12 at 19:31

18

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();

answered Feb 10 '12 at 19:31

Levitikon

7,749
9
56
74

1

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. – Paul Zaczkowski Aug 12 '14 at 18:05

vc 74 · Answer 2 · 2014-11-21T18:36:47.090

13

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)));

edited Nov 21 '14 at 18:36

answered Feb 17 '11 at 08:41

vc 74

37,131
7
73
89

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. – Rahul R Feb 17 '11 at 09:00
@Rahul, I'm not looking for reputation but if you think Ekkehard's answer or mine was helpful, consider voting up + accepting the answers – vc 74 Feb 18 '11 at 08:53

score 9 · Accepted Answer · edited Jun 06 '17 at 12:14

9

Use

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

edited Jun 06 '17 at 12:14

shA.t

16,580
5
54
111

answered Feb 17 '11 at 07:50

Ekkehard.Horner

38,498
2
45
96

1

+1, This solution will filter it first before importing. Saving a lot of time if your data contain a lot of empty data. – Septian Primadewa Dec 16 '15 at 02:19
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] = '')). – Ulpin Oct 31 '16 at 10:49
@Ulpin - Check the &; if that does not help, post a new question describing your problem in detail. – Ekkehard.Horner Oct 31 '16 at 13:57
@Ekkehard.Horner How about if I want to filter to not include rows that has empty on all its columns? *All the columns' name is dynamic – Yusril Maulidan Raji Jul 23 '19 at 14:04

score 0 · Answer 4 · answered Mar 11 '13 at 20:43

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

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

4 Answers4

Linked