To Sort based on Last Character Data

Help Studio
, , , , , , , ,
1

Hello team,

We have a column defined in Below format

E_no
IN3
INa
IN2
IN1
INc
INb

Then
Reorganize in Below format
(The last character first in numerical ones are sorted and then alphabetical one)
i.e in E_no the last character (1,2,3,a,b,c as below given as example)

E_no
IN1
IN2
IN3
INa
INb
INc

Thanks Team in advance

2

Hi @NISHITHA ,

1. Use Excel Application Scope to read the data from the Excel file.

2. Use Read Range activity to read the data into a DataTable variable.

3. Use Assign activity to create a new DataTable variable to store the sorted data:
   - Use LINQ query to order the rows based on the last character of the 'E_no' column.
   - Example LINQ query:
        sortedData = dt.AsEnumerable() _
                      .OrderBy(Function(row) If(Char.IsDigit(row.Field(Of String)("E_no").Last()), 0, 1)) _
                      .ThenBy(Function(row) row.Field(Of String)("E_no").Last()) _
                      .CopyToDataTable()

4. Use Write Range activity to write the sorted data back to the Excel file.

Make sure to adjust the column name (‘E_no’) in the code snippet according to the actual column name in your Excel file. This script uses LINQ to sort the data based on the last character as specified in the format.

Also, ensure to handle exceptions and validate the data to ensure it meets the expected format before sorting and writing it back to the Excel file.

Thanks,
Vinit Mhatre

3

Hi @NISHITHA

Read range

YourDataTable = YourDataTable.AsEnumerable().OrderBy(Function(row) If(Char.IsDigit(row("E_no").ToString().Last()), 0, 1)).ThenBy(Function(row) row("E_no").ToString()).CopyToDataTable()

Write range

image
image1269×673 32.3 KB

Hope it helps!!

4

Hello @NISHITHA

  1. Read Range (Read the Excel or CSV file into a DataTable)

    • Input: Path to your file
    • Output: DataTable (let’s call it dt)

  2. Add Data Column (Add a new column to store the sorting key)

    • DataTable: dt
    • Column Name: SortKey
    • Data Type: String

  3. For Each Row (Loop through each row in the DataTable)

    • Input: dt
    • Output: row

    a. Assign (Calculate the sorting key based on the last character)

    • To: row(“SortKey”)
    • Value: If(Char.IsDigit(row(“E_no”).ToString.Last), “0” & row(“E_no”).ToString.Last, “1” & row(“E_no”).ToString.Last)

  4. Assign (Sort the DataTable based on the SortKey column)

    • To: dt
    • Value: dt.AsEnumerable().OrderBy(Function(row) row(“SortKey”)).CopyToDataTable()

  5. Remove Data Column (Remove the temporary SortKey column)

    • DataTable: dt
    • Column Name: SortKey

  6. Write Range (Write the sorted DataTable back to the file)

    • Input: Path to your output file
    • DataTable: dt

Thanks & Cheers!!!

1 Like
5

Hi,

you can try below query

dt = dt.AsEnumerable().OrderBy(Function(row) If(Char.IsDigit(row(“E_no”).ToString().Last()), 0, 1)).ThenBy(Function(row) row(“E_no”).ToString()).CopyToDataTable()

image
image707×425 18.3 KB

check below zip file
forum_6122023.zip (9.8 KB)

6

we can exploit that the asc code from a number is below of a char. So we directly use OrderBy on the last char
grafik
And Also:
grafik

7

@NISHITHA

try this

 sortedData = yourDataTable.AsEnumerable().OrderBy(Function(row) If(Char.IsDigit(row.Field(Of String)("E_no").Last()), 0, 1)).ThenBy(Function(row) row.Field(Of String)("E_no").Last).CopyToDataTable

cheers…!

8

@Vinit_Mhatre , @ppr , @Kartheek_Battu , @sai_gupta , @pravallikapaluri , @neha.upase

Thanks for the quick responses

Any other approaches can try

1 Like
9

Does it mean you ask for alternates to:

  • LINQ - Order by and its variations
    OR
  • Populating a sort columns and its variations

?

10

Hi

Extract the last characters: Use a loop to iterate through the data and extract the last character from each E_no value. Store the extracted characters in a separate list.

Sort the last characters: Sort the list of extracted characters. For numerical characters, sort them in ascending order, and for alphabetical characters, sort them lexicographically.

Reorder the data: Create a new list to store the reorganized data. Use the sorted list of last characters as a reference to determine the order in which to add the corresponding E_no values to the new list.

Update the original data: Replace the original data with the reorganized data.