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)
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)
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
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
@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"