The problem you are facing, is that on each iteration, you are writing on the A2 Cell, therefore overwriting what the 1st iteration did.
You need to find the last row with data, and update the “2” with that value on each iteration:
In short, it would look like this:
1- Already in place, loop each file
2- Already in place, Inside the for each, read text file
3- Find last row with data (0 on the 1st iteration, 2 in the 2nd…)
4- Select Range (Instead of Always A2, A+last data with row index, +1)
5- Send Ctrl V
Another option you can use is convert the value from the Text file into a datatable, and instead of pasting from the clipboard, you can use the Append Range activity to let UiPath automatically detect the last row, and add your new data right below.
Edit: The solution does not contemplate a rerun, therefore make sure to delete the rows in the “Manage” Sheet, before running. Also it is always adding the headers as your example. If you want the same result but without repeated headers, make sure to mark this property. Also cleaned up a bit and reuploaded the example.
Sorry, one more question. In below step, since they are within for each file in folder, does it put the 3 txtfile data in dt all at once or one by one?
It is looping each file, the same as you had, the only difference is that I changed the order of your excel app scope and the for each, to avoid opening and closing excel 3 times:
What you had:
1- For each file
2- 3x Open Excel
3- 3x Write data
4- 3x Close Excel
New design:
1- 1x Open excel
2- 3x Write data
3- 1x Close Excel
Just to avoid possible problems related to opening/closing excel
Long story short - Not all at once, but looks like it
Hm, I was just checking and looks like you have 2 tabs on the text file, therefore that’s why the shift in the columns:
before going back to the copy-paste solution, do you think it might be a problem on the text file? Also the solution might be to find those 2x tabs in the text file string, replace them with a single tab, and would work, but has its risks.
Okay, bit of a workaround, and for sure we have linq gurus that will find a better approach, but its sorted.
The Generate Datatable activity was identifying the double tab as a single one, and that’s the reason for the missing column and the shift.
Solution: Regex replace the 2 tabs with the same 2 tabs + a keyword in the middle, that will generate the right number of columns, and then remove the keyword.
(another approach could be to remove the keyword with an excel macro)
I did also try to convert it to CSV, and change the Tabs to commas, but your Address field can contain commas, and would produce the same error without further development to take that out. Test Sample_IP.zip (166.1 KB)
I have a call with my requester to learn about the txt files. He told me we can’t change anything in it base on our customers’ guidance and the best way to do so is copy and paste
So I keep debugging in last one hour and get the solution as below:
I add one sheet named “Trans” and paste the txt data in it in each loop then paste to the sheet"manage"
And it seems works though it looks quite troublesome
