Problem with extracting tables

Help Studio
1

Hello,

I have an excel file, which contains more data, tables.
I give an example of the same. I need to sort columns from tables with names in alphabetical order. How to extract only the necessary tables and perform sorting for each of them and save them in the same file

20230725 - MC Debit.xlsx (25.4 KB)

2

To extract and sort tables with names in alphabetical order from an Excel file using UiPath, and save the data into a variable, you can follow these detailed steps:

  1. Use the “Excel Application Scope” activity to open the Excel file. Specify the file path or use a variable to hold the file path.
  2. Use the “Read Range” activity to read the data from the Excel file into a DataTable variable. Specify the range or leave it blank to read the entire sheet.
  • Create a DataTable variable, let’s say dtExcelData, to store the extracted data.
  • In the “Read Range” activity, provide the sheet name or index, and select the range to read.
  • Assign the output of the “Read Range” activity to the dtExcelData variable.
  1. Use the “Filter Data Table” activity to filter the DataTable based on the table names. Set the filter condition to include only the necessary tables.
  • Create a new DataTable variable, let’s say dtFilteredData, to store the filtered data.
  • In the “Filter Data Table” activity, assign the dtExcelData variable to the “Input” property.
  • Set the “Filter Wizard” to “Keep Rows”.
  • Specify the filter condition in the “Filter” field. For example, you can use "[Table Name] <> ''" to filter out empty table names.
  • Assign the output of the “Filter Data Table” activity to the dtFilteredData variable.
  1. Use the “Sort Data Table” activity to sort the filtered DataTable by the column containing the table names. Set the “Sort Order” to “Ascending” to sort the names in alphabetical order.
  • In the “Sort Data Table” activity, assign the dtFilteredData variable to the “Input” property.
  • Specify the column name or index to sort by.
  • Set the “Sort Order” to “Ascending”.
  1. Use the “Write Range” activity to save the sorted DataTable back to the same Excel file, overwriting the original data.
  • In the “Write Range” activity, assign the dtFilteredData variable to the “DataTable” property.
  • Specify the range or leave it blank to write to the same sheet.
  • Make sure to check the “Overwrite” option to overwrite the original data.
  1. To save the sorted data into a variable, you can use the Assign activity to assign the dtFilteredData variable to another variable.
  • Create a new DataTable variable, let’s say dtSortedData, to store the sorted data.
  • Use the Assign activity, and in the “To” field, specify the dtSortedData variable.
  • In the “Value” field, assign the dtFilteredData variable.

Now, you have extracted only the necessary tables from the Excel file, sorted them by table names in alphabetical order, and saved them back to the same file. The sorted data is also saved in the dtSortedData variable.

I hope this helps.

3

Hi, it can be a bit tricky. First, you need to determine the range within which you want to search. If you know the exact range, it’s a straightforward task. However, if the range is dynamic or uncertain, you should use the “Lookup Range” activity. This activity will provide you with the range you need based on the value from where you want to start your search. Once you have the range, you can pass it to the “Read Range” activity.

image

For the sort table please refer to the below video.