Nested for each loop to read rows and column in excel sheet

[Waka (Andy NG)]

Hi

i need some help over here to read each row value and column to be copied over to a original list of excel sheet.

example, i would need to copy values from B2:E9 based on the screenshots and each row going to copy and paste into a file that holds monthly original list… meaning i would received a daily report in excel A file consisting of header 1 to 4 values and i would like to make the UiPath to copy and paste the daily report to a original listing which going to compound into monthly listing.

however, based on my flow i only managed to get the first row value and the flows stopped and it does not copied over to the original list and to move to the next row(completed row B but not moving to row C and closed the flows)

[NutchanonSwnl (Nutchanon Suwanalerk)]

Hello @Waka

Can you show raw data of excel and Result excel that you expected it to be?

Best Regard,

[Waka (Andy NG)]

@NutchanonSwnl

im trying to use the nested for loop to copy and paste the value from Daily report to Monthly report

Screenshot 1

screenshot 2

[Srini84 (Srinivas Kadamati)]

@Waka

Instead of Creating a new variable ColCounter for incrementing you can declare a variable in For Each row properties Index, that will act as your Counter

Hope this may help you

Thanks,
Srini

[vishal.kp (vishal)]

Hi @Waka ,

You can use the merge datatable activity to append your daily report datatable to make monthly report and then write it to the excel.

regards,

[NutchanonSwnl (Nutchanon Suwanalerk)]

In case if header name from DailyReport and MonthlyReport are same

you can use append activity like this

DailyReport Data:
DailyReport.xlsx (8.9 KB)
MonthlyReport Data:
MonthlyReportBefore.xlsx (8.9 KB)
Result after using flow
MonthlyReport.xlsx (9.2 KB)

Hope this one help you but if it’s not the same header name we can discuss about this later

[Waka (Andy NG)]

@Srini84

may i know how does the properties index works?

[Waka (Andy NG)]

@NutchanonSwnl

the headers are the same in both files, however the daily report rows is dynamic and it could in range from 5-20 rows in the daily report. can the append range work for this ? i tested at my file and the cell range is hard coded.

[NutchanonSwnl (Nutchanon Suwanalerk)]

When you use Read Range you can set like this

Change “Sheet1” into your sheet name but leave Range(“”) to be the same
it will read all column and row and save to DailyReportData
then we use append to set all row of Daily Report into monthly Report by next available slot

Best Regard,

[Waka (Andy NG)]

Hi @NutchanonSwnl

i can’t use range(“”) and would need to specific the range because it will append other things liner or words into the monthly report.

[NutchanonSwnl (Nutchanon Suwanalerk)]

In your Daily Report Template does it have decoration text below after lastrow of data?

If not you can write like this (For example Specific column of data is A to D)

What i mean is something like this

Best Regard,

[Waka (Andy NG)]

@NutchanonSwnl

yes, my excel file have decoration text at the first and last row.

when i use the read.range(“A1:D”)

there is an error message showed "Could not read range A1:D

[NutchanonSwnl (Nutchanon Suwanalerk)]

Workflows :
FlowForWaka.zip (31.2 KB)

Assume that your daily report is having first 3 row as decorate
and last 2 row as decorate like this

after use this workflow will process excel into this

then we use append as normal to end our flow

Ref:

for description each activity will be like this

Hope this one help
Best Regard,

[Waka (Andy NG)]

Hi @NutchanonSwnl

i follow your code and use it on my script, the code giving me this error showing “Invoke Code:Exception has been thrown by the target of an invocation.”

while i run your example shared with me, and i dont have this error message

any possible solution for this?

[Waka (Andy NG)]

Hi @vishal.kp

Does this merge datatable remove the header from the daily report and merge the data to monthly report?

[NutchanonSwnl (Nutchanon Suwanalerk)]

Hello @Waka

Could you check at edit arguments? might be missing with arguments or variable
Some screenshot would be nice also.

Best Regard,

[Waka (Andy NG)]

@NutchanonSwnl

i followed your code or i could i missed it some where

[NutchanonSwnl (Nutchanon Suwanalerk)]

@Waka

TodayDT might not get a correct type of data or maybe inside of invoke code is not right
Can you debug and check if it pass ‘TodayDT’ datatype or data to invoke code correctly or not?

Best Regard,

[Waka (Andy NG)]

@NutchanonSwnl

TodayDT datatype is “DataTable”

May i know how to interpret this line of code?

[NutchanonSwnl (Nutchanon Suwanalerk)]

@Waka

May i know how to interpret this line of code?

First we read range into “DT_Daily”(assume this is my workflow) then .AsEnum.skip will skip first 3 row(work like delete first 3 row) then copy to “DT_Daily” as datatable(Just like after modify data we copy into same variable datatable)
if you have change your variable name into TodayDT please also change “DT_Daily” to “TodayDT”
Just like this

TodayDT.AsEnumerable().Skip(3).CopyToDataTable()

Best Regard,