Delete rows from DataTable at once without loop where a column has Null value

Question

I'm populating a DataTable object from an Excel worksheet. I'm not counting on the user entering data correctly, and I'd like to delete rows that have a null value in column A. I've searched around quite a bit, and it looks like everyone is doing this with a for loop. Here's the problem: 757,000 rows. If data is not formatted properly that DataTable gets filled up with Excel's max of 1048575. I don't need to check each value individually. In SQL server you can write:

DELETE table WHERE columnA IS NULL

I considered excluding nulls on the DataTable fill, but I couldn't get this to work reliably as I don't know the name of column A until it's in the DataTable and sometimes the column name has a \n character in the middle which throws syntax errors.

How can I accomplish the same sort of thing without a loop?

Answer 1

How about creating a new, clean DataTable from your first one?

DataTable t = new DataTable(); //actually your existing one with bad records
DataTable newTable = t.Select().Where(x => !x.IsNull(0)).CopyToDataTable();