Find Last Row in Data and Paste Excel Table Copied From Another File

Help StudioX
,
1

Hello everyone,

I am trying to make the bot (on UiPath StudioX)

  • read data from a specific tab on an Excel file1 and find the first empty row (after last row of data). The tab already contains data
  • copy data from a different file2
  • paste data to the first empty row on file1
  • I have to repeat this process for 6 different files (i.e. copying data from 6 different files and pasting on the same tab on file1)

I am very new to UiPath and Automation so if you please have a straightforward solution that would be great.

Below is a screenshot of what I’ve been trying to do:

image
image647Ă—842 22.8 KB

1 Like
2

@alberto.lombardi

Use a read range activity to read data from another sheet then use append range activity to append the read data into the first sheet

Cheers

1 Like
3

Hi @alberto.lombardi,

Use the “Assign” activity to find the first empty row. You can do this by using the Rows.Count property of the DataTable. For example:

emptyRowIndex = dataTable.Rows.Count + 2

Here, +2 is used because the row index is 1-based, and you want to go to the next empty row.

Here’s a the workflow:

  1. Read Data from File1
  2. Find First Empty Row
  3. Copy Data from File2
  4. Paste Data to File1 (at emptyRowIndex)
  5. Copy Data from File3
  6. Paste Data to File1 (at emptyRowIndex)
    … Repeat for File4 to File7
4

Hello @alberto.lombardi

  1. Read Range:

    • Input: File1.xlsx, SheetName (e.g., “Sheet1”), Range (e.g., “A1:C100”)
    • Output: DataTable (e.g., File1Data)

  2. Assign:

    • To: emptyRow
    • Value: File1Data.Rows.Count + 2

  3. For Each (File in Files):
    a. Read Range:

    • Input: CurrentFile.xlsx, SheetName, Range
    • Output: DataTable (e.g., CurrentFileData)

    b. Append Range:

    • Input: File1.xlsx, SheetName, StartingCell (e.g., “A” & emptyRow.ToString())
    • DataTable: CurrentFileData

    c. Assign:

    • To: emptyRow
    • Value: emptyRow + CurrentFileData.Rows.Count

  4. End For Each

Thanks & Cheers!!!

6

Thank you Anil it worked. I would like UiPath to paste the data on the last row. What could I write in “Append after range” on “Append Range” activity to paste the data on the last row of the sheet?

image

7

Hi @ABHIMANYU_THITE1 & @Kartheek_Battu thanks! I couldn’t find “Assign” on my UiPath. Is there another way that I could find the last row of the sheet and use the “Append Range” activity?

8

image

9

You don’t have to find the last row. Just use the Append Range activity and it does that for you.

I suggest doing the free training on the UiPath Academy web page.

10

@alberto.lombardi

Nice…Now in append after range give only the sheetname where you want o append and not range

Excel.Sheet("SheetName")

cheers

11

This is great thanks Paul!

12

Thank you it works @Anil_G !

1 Like
13

Also, @postwick, do you know if you can copy and paste data from csv files or do I have to necesarily export to excel? I don’t see use activity for csv

14

Sorry, I did find a Read CSV activity actually. My issue here is that I have to select a certain range within the CSV files (to be copied and then pasted on the main excel file) and I can’t find this option. I also don’t see any “Use Csv File” resource.

Any advice would be greatly appreciated :slight_smile: @postwick @Anil_G @Kartheek_Battu @ABHIMANYU_THITE1

Regards

image

15

You don’t need any “Use CSV File” scope activity. Just use the Read CSV activity on its own.

When you say select a certain range, what do you mean? You can read the whole CSV into a datatable and then remove the data you don’t want from the datatable. Then Write Range or Append Range to Excel.

If what you mean is the file isn’t just a raw CSV file but contains other unwanted text, you’ll have to show us the CSV file so we can recommend how to do that.

16

@alberto.lombardi

For read csv you can use read csv to read into datatable and then use append range workbook activity to append the datatable to the excel you want

Cheers

17

@postwick & @Anil_G thank you for the replies. Unfortunately, I can’t show the CSV file since it contains confidential information. The issue is indeed that the CSV file contains unwanted text in several columns. I would only like to extract columns A:G and exclude cols H:K. Was wondering if this could be done with the Read CSV activity or any other activitities? If this is not possible, then as you said Paul, I can just make the bot read the whole datatable, remove the unwanted data and append on the main file.

18

I didn’t mean unwanted columns. What I meant was some sort of, say, report format where there is a title and other information at the top before the CSV data actually starts.

If it’s just a raw, pure CSV file then just use the Read CSV activity to get it into a datatable. Then you can manipulate it however you want. For example, you can use the Filter Data Table activity to remove columns you don’t need.

19

@alberto.lombardi

You know what you can do that…use excel file and append range will workf or csv as input as well…just give csv in use excel and use append range and give the same for csv but the sheetname will be same as filename for csv always

so use Excel.Sheet("FilenameWithoutExtension")

cheers

1 Like
20

Hey @Anil_G thank you for the response. I’ve been using your suggestion. So in short, I told UiPath to read 3 csv files using Read CSV activity and then “Output to” 3 different sheets in my UiPath notebook. I now would like to copy these 3 datasets (only including columns A:N) from the notebook, and then append them on a separate excel file (file 2) sheet that already contains some data. I’ve been trying to use the append range activity but I’m not able to make the robot to copy the data from column A to N until the last row of data. I’ve been trying to use this formula FileName.Sheet(“Sheet1”).Range(“A:N”) but it gives me an error. Any suggestions? Thank you very much

21

@alberto.lombardi

May I know what error you are getting?

Cheers