Create and Sort Datatable

I have an Excel spreadsheet with two columns and no headers. The data is not formatted as a table in Excel. Also, it has a dynamic number of rows (rows can be added or deleted over time). For example:

49    aaa
42    bbb
83    ccc 
13    ddd
2     eee
8     fff

What I’m trying to do is simply pull the data into a DataTable and then sort the datatable by the first column and then, using a for each loop, display the items in the second column. How can I do this?

(in reality, I’m trying to shuffle the datatable in a random order - so the numbers in col1 are randomly generated when the Excel file opens)

  1. Read range activity - specify the sheet name, but leave the range blank so it reads the entire workbook. Save output as dt1 (or name however you’d like)
  2. Assign activity - dt1.DefaultView.Sort = “[Column1] Asc”
  3. Assign actiity - dt1 = dt1.DefaultView.ToTable
  4. For each row activity (in dt1)
    a. Write line activity - row.item(1).ToString

I also have created a workflow that performs a Knutes Shuffle / Fisher Yates shuffle on a datatable if you’re interested. It takes a datatable input, shuffles it in a random order using the knutes shuffle, sorts the datatable by the shuffle order column, then returns the sorted datatable as an output

2 Likes

THANK YOUUUUUU, YOU SAVED MY LIFE <3

hi @Dave,
I tried what are the steps you said bot i am facing an error… can you expalin where i did a mistake…
Thanks Advance…sorting

Replace Column 1 with the name of the column from your datatable that you want to sort. I believe it is case sensitive. Not 100% sure, but that’s most likely the issue

1 Like

hi @Dave

Column is Numbers,
i replace the name but facing the same issue… hlp me…

I dont see a column header in your screenshot. Can you upload your .xaml and the excel? I can take a look

@Dave there is no column header…Book1.xlsx (8.5 KB) Main(Autosaved)(Autosaved).xaml (5.7 KB) project.json (1.0 KB)

@tejaswi_nerella right now you have the property “AddHeaders” checked in your read range activity. That means it is taking the first row in excel and assigning that as the column name. You want to uncheck that. Then change the column name inside the [square brackets] so it is [Column0] instead. You can also choose it by index instead if you’d prefer that, so you’d change the assign to be DT1.Columns(0).ColumnName + " Asc"

@Dave Thanks…
if i want put the sorted result in excel

You can use a write range activity to write it back into excel

1 Like

@Dave thank you…