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?

Do you want to delete the rows form the source workbook or just not show them in the datatable? How are you reading from excel? — D Stanley
– D Stanley, Commented Feb 11, 2015 at 20:09
can you not implement the Select() Method of the DataTable to filter down and perhaps populate a DataTable from there..? also IN SQL Server Your syntax is incorrect.. it's DELETE [FROM TABLE NAME] WHERE Clause — MethodMan
– MethodMan, Commented Feb 11, 2015 at 20:12
@DStanley really I just want them to not be in the datatable. — stphnlwlsh
– stphnlwlsh, Commented Feb 11, 2015 at 20:34

DrewJordan · Accepted Answer · 2015-02-11 20:33:34Z

4

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

answered Feb 11, 2015 at 20:33

DrewJordan

5,3341 gold badge30 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Justin Russo · Accepted Answer · 2015-02-11 20:35:01Z

0

Just use Linq...

var newData = (from d in DataTable //this would be your datatable. 
              where d.columnA != NULL
              select d.*).CopyToDataTable<DataRow>();

Then just use your newData variable, which holds your newly populated datatable.

answered Feb 11, 2015 at 20:35

Justin Russo

2,2141 gold badge22 silver badges27 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related