Need help-copy date from Excel file 1 and paste in transpose in Excel file2

Help StudioX
,
1

I need help for how to copy data from specific column H, R, Z, AH, AP from Excel file 1 and paste value transpose in another excel file 2 as below picture.
Please suggest how to use UI path studioX to it.

image
image1366×768 152 KB

image
image1366×768 249 KB

2

@Pat

Welcome to the community

Read the data using read range activity…whole data

Then use filtwr datatable and in filtwr winxard columns tab…give the column names you want to keep

Then write range to write the data…if you need transpose then use copy paste range and use transpose option in it

Cheers

1 Like
3

Thank you so much for your suggestion.
I can’t attach a picture on this forum.
There are some complexities that I can’t figure out. :smiling_face_with_tear:
Are you convenient to give me a contact ? then I will share you more detail.

4

To copy specific columns (H, R, Z, AH, AP) from Excel file 1 and paste their values transposed into Excel file 2 using UiPath StudioX, follow these steps:

Step-by-Step Guide:

  1. Open Excel file 1 (Source File):

    • Use the “Use Excel File” activity and choose Excel file 1 as the source file. Make sure to check the option “Is Visible” if you want to see the file being opened.

  2. Read Data from Excel file 1:

    • Use the “Read Range” activity to read the entire data from Excel file 1, specifying the range (e.g., A1:AP100) or simply leave it empty to read the entire sheet.
    • Store the output in a DataTable variable, say dtSource.

  3. Extract Specific Columns:

    • You need to extract only the required columns (H, R, Z, AH, AP) from the dtSource DataTable.
    • Use the “Assign” activity to create a new DataTable with just the selected columns:
      dtSelectedColumns = dtSource.DefaultView.ToTable(False, "H", "R", "Z", "AH", "AP")

  4. Transpose Data:

    • In UiPath StudioX, there isn’t a direct activity to transpose data. However, you can achieve this by looping through the rows and columns to create a transposed DataTable.
    • Use a For Each Row loop to iterate over the dtSelectedColumns.
    • Inside the loop, use a Write Cell activity to write the data transposed in the destination Excel file (Excel file 2).
    • You can calculate the cell to paste by transposing the row index and column index.

  5. Open Excel file 2 (Destination File):

    • Use the “Use Excel File” activity to open the destination Excel file (file 2).
    • You may specify the starting cell where you want to paste the transposed data (for example, starting at A1).

  6. Write Transposed Data to Excel file 2:

    • Use the “Write Cell” activity to write the transposed data in the correct cell locations.
    • Use a counter or index to keep track of the positions to paste the data in a transposed manner (i.e., flipping rows and columns).

Example Workflow:

  • Step 1: Use Excel File → Read Range to get the data from Excel file 1.
  • Step 2: Extract required columns from the DataTable.
  • Step 3: Use a For Each Row to loop through each row in the selected columns.
  • Step 4: Use Write Cell to write the values transposed into Excel file 2.

Alternative Method: Using DataTables in Excel

If you’re familiar with DataTables, you can use “Write Range” after modifying the DataTable programmatically to transpose it before writing to the destination file. However, for simplicity, manually looping through the DataTable might be the best approach for StudioX.

5

@Shakib Please help to suggest how to map data for copy ?

image
image1468×821 169 KB

image
image1470×565 241 KB