How can I input an excel data table on the first blank row of another excel sheet?


#1

Hi!

I am trying to input data from an excel file i receive periodically (it has a variable number of rows) into the first blank row of a master file which is meant to store all the data from all the periodical reports received.

For this, I have so far created a sequence with a read range for the received report (the range I selected goes until row 10000, so there is no risk of leaving out information) yielding a data table (DT1). Then i make another read range on the master file (yields DT2), and a foreach row of DT2, If (row(0).toString=""), a write range (DT1) in the master file, on cell “A”+DT2.Rows.IndexOf(row).ToString (i believe this gives me the first blank row).

So far, it keeps writing only the first row if the input in the master file, repeatedly until the end of the range read. I need it to input the full DT1 in the first blank row of DT2 and stop there.

I hope I was clear, this is my first post and besides my introduction days to UiPath.

Thank you for your help! Please let me know if I can provide any further info.


#2

@dcandido, you could use an Append Range activity after reading the received report to add the data of DT1 to the master spreadsheet.

Do you have a reason you are reading the master and storing it in it’s own datatable variable other than getting the first blank row?


#3

Hi Tyler,

Thank you for your reply!

No, I am only reading the master so I can find the first row with the code ““A”+DT2.Rows.IndexOf(row).ToString”, which I put in the starting cell field in the write range. Is there an alternative way of finding the first row in a sheet in the master file using the write range? What code would I have to use for the write range?

Please let me know if this is unclear.

Thank you!


#4

The activity “append range” is exactly what you want. This will find the last row used in excel, and will write a datatable to the next blank row.

One other thing to note - for DT1 instead of hardcoding to row 1000, just leave that blank and it will pick up everything in the worksheet. Only specify exact ranges if there is data you want excluded from the worksheet.

So I would 1). Read range for received report and outuput as DT1. 2). Append range to master excel file (input is DT1).

It should only be 2 activies used and very quick!