Separate Datatable based on empty data and Rename Column Name

[aliaga (Ali Aga Mustofa)]

Hi All,

Currently i’m developing RPA with extraction data from excel/pdf with following data :

From above data, i need to do some massaging data to :

• Rename Column Name with new name based on their increment column name.
  ex : column 10,5,20,10,5 will be rename become 10,15,35,45,50 (10+5=15, 15+20=35 and so on)
• Separate on new datatable based on column 10-50 that has data (remove column with blank data

Expected output :

I don’t know hot to do this, can we achieve this using VBA macro/UiPath Studio?

Regards,

[Kartheek_Battu]

Hello @aliaga

1. Read Excel Data:

• Use “Excel Read Range” to read data into a DataTable.

2. Initialize Variables:

• Increment (Int32) for incrementing column values.
• NewColumnNames (List(Of String)) to store new column names.

3. Data Transformation:

• Loop through each row:
  • Update column names based on Increment.
  • Add new column names to NewColumnNames.
  • Increment Increment by 5.

4. Remove Blank Columns:

• Use “Remove Data Column” to remove blank columns based on NewColumnNames.

5. Create New DataTables:

• Loop through NewColumnNames:
  • Create new DataTable.
  • Loop through original rows, add to new DataTable based on column value.

6. Write Output:

• Use “Excel Write Range” to save new DataTables to files.

Thanks & Cheers!!!

[aliaga (Ali Aga Mustofa)]

Hi @Kartheek_Battu ,

Thankyou for your reply, i really appreciate.

First of all, i’m little bit confuse about step no 2-4, could you please elaborate or have an sample of xaml?

Thankyou

[Yoichi]

Hi,

Can you try the following sample?

Sample20230817-6L.zip (9.5 KB)

Regards,