Take the 2-minute tour ×

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\"";


            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
            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.

share|improve this question

4 Answers 4

up vote 3 down vote accepted


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

share|improve this answer
Thanks Ekkehard for your reply, but it is not working. –  Rahul R Feb 17 '11 at 8:13
My apologies, but it seems your way is also working. –  Rahul R Feb 17 '11 at 9:03
+1 I have used this with Excel. –  Fionnuala Feb 17 '11 at 9:33

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();
share|improve this answer
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 at 18:05

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)));
share|improve this answer
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 9: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 8:53

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
share|improve this answer

Your Answer


