Here is a sample data from a large data table that consists of about 25-30 columns. The columns of interest are shown below.
I need to filter out the blank rows and then sort this data table by Column headers A, B and then C. Notice that one of cells in “C” is highlighted in yellow. There is a missing value in there that causes my sort to fail, when I use the following command.
Assign dt = (From x In dt.AsEnumerable Order By cint(x(“A”).ToString), cint(x(“B”).ToString), cint(x(“C”).ToString)).CopyToDataTable
Error: Assign: Conversion from string “” to type ‘Integer’ is not valid.
When the blank cell is replaced with a value of 2 the results are displayed as follows:
Any help overcoming this issue? I do not want to loop through the table but would prefer to use LINQ and do it all in one step, if that is possible.
Assign dt = (From x In dt.AsEnumerable
Where Not String.IsNullOrEmpty(x("A").ToString) And
Not String.IsNullOrEmpty(x("B").ToString) And
Not String.IsNullOrEmpty(x("C").ToString)
Order By CInt(x("A").ToString), CInt(x("B").ToString),
If(String.IsNullOrEmpty(x("C").ToString), 0, CInt(x("C").ToString))
Select x).CopyToDataTable
Explanation:
From x In dt.AsEnumerable: This part of the query converts the data table (dt) into an enumerable sequence of rows, and we assign each row to the variable x.
Where Not String.IsNullOrEmpty(x("A").ToString) And Not String.IsNullOrEmpty(x("B").ToString) And Not String.IsNullOrEmpty(x("C").ToString): This is a filtering condition. It ensures that only rows where columns “A,” “B,” and “C” are not blank (empty or null) are considered for sorting.
Order By CInt(x("A").ToString), CInt(x("B").ToString), If(String.IsNullOrEmpty(x("C").ToString), 0, CInt(x("C").ToString)): This is the sorting part. It orders the selected rows based on the values in columns “A,” “B,” and “C.” For column “C,” it checks if the value is empty. If it’s empty, it assigns a value of 0 to make the sorting process valid.
Select x: This part of the query selects the rows that satisfy the filtering condition and arranges them in the sorted order.
.CopyToDataTable: Finally, this function is used to convert the result of the LINQ query back into a data table, and the sorted, non-blank rows are stored in the dt variable.
I m coming up with this statement that it can have any number of columns
To make it even more dynamic I kept sorting considering that there can be any number of columns
use this in a assign activity
dt = (From x In dt.AsEnumerable
Where Not x.ItemArray.Any(Function(c) String.IsNullOrWhiteSpace(c.ToString()) OrElse Not Integer.TryParse(c.ToString(), Nothing))
Order By x.ItemArray.Cast(Of Integer)()
Select x).CopyToDataTable
Thanks for your response. Great explanation of the solution. However, like any SQL, when AND is used in the “where” clause, the row with blank cell in column “C” is eliminated first. By the time the rows are passed to the “Order by” clause, there would be no need for the IF condition in it.
I added couple more rows to the table (shown within the box) that I posted earlier to see a better outcome of our sort result.
With this sample data, when I run your query as-is, you will notice that the blank cell row is ignored.
(From d In dt.AsEnumerable
Let chk = {0,1,2}.All(Function (x) isNothing(d(x)) OrElse String.IsNullOrEmpty(d(x).toString.Trim))
Where Not chk
Let aco = {"A","B","C"}.Select(Function (p) If(Int32.TryParse(d(p).toString.Trim, Nothing), Int32.Parse(d(p).toString.Trim),0))
Order By aco(0), aco(1), aco(2)
Select r = d ).CopyToDataTable
ashokkarale
Yoichi
Anil_G
sudster
Kismet_Tosun
anand.t
k.sakaguchi0804
SorenB
Akash_Javalekar1
Sisay_Dinku