Merge Datatable - Unnecessary column displayed


#1

Hello Everyone,

I am trying to merge two datatable but when I merge it, an unnecessary column (Column1) is displayed. Please find the output in screenshot below. I have attached the xaml file as well. Please note “View” column is from different excel sheet and rest of the columns(S.no, Name and Field) are from another excel sheet

3 2 ,

1 Main.xaml (9.1 KB)


Datatable.rows.count returns blank output
#2

@Selvasathappan your workflow looks fine. I guess it is the problem with the excel sheets.
Try opening the excel sheet and press Ctrl + End. This will tell you which is the last used row and column in the sheet.
Sometimes in Excel, if some content is deleted from the cells or if even a blank space that has been entered on any of the cells it would still be considered as used row / column. This would be read by Read Range activity as it reads the entire set of used cells. I beleive that could be your problem here. Can you check?

If that is the problem, delete the unused rows / columns by selecting the entire row / column, save the sheet and try Ctrl + End to verify once again. If that is fixed, your workflow should work fine!


#3

hi @Selvasathappan,
This usually happens when any cell having Some blank Spaces or have deleted data from excel cell.

To Solve this, either you can delete that column in excel or you have to put some logic in UiPath.

Logic for UiPath:
If you have fixed number of columns in Excel sheet, then you can remove extra columns from data table before Merging.

I have made two logic to remove unnecessary columns, check in the attached workflow, You can use whichever is suitable for you.

Main (3).xaml (18.2 KB)


#4

@Santan_Barnwal, @kaderms - Guys, Thanks for your response. Can you also please share me your views/suggestions on how I can do this? This seems to unreliable but I can’t afford to go live with this scenario


#5

@Santan_Barnwal @kaderms - Guys, I tried both of your suggestions but still I am getting kind of similar output when I merge the datatables. I used CTRL+END to verify the cell range and it is correct. Moreover I also verified the column count and row count to ensure it is all correct using @Santan_Barnwal workflow but still the output is not as expected. I have attached my excel sheets and xaml file and screenshot as well. Kindly help

2 q1.xlsx (8.4 KB)
q2.xlsx (9.2 KB)
smartly.xaml (10.8 KB)


#6

I am confused with your problem again.

This doesn’t appear anymore. So I guess problem solved! Unless you were facing some other issue.
Were you expecting an output like this?

image

If yes, then I am not sure if merge data table would be the right activity to do this.


#7

@kaderms - I am expecting an output like shown in your screenshot. How can I achieve that? Did you get to see my workflow? If merge datatable doesn’t help with this, should I only be using for each to achieve this which is like kind of a lengthy way to do this.

My usecase is like, Name, Age and Job columns might differ but View column will always remain same but just repeat for every 3 rows. From excel row number 5, again the view column value would be ‘1’.

I will extract data from an web app for the columns Name, Age and Job everyday and store it in dt1 and then merge it with dt2 which only has view column.

Finally append the dt1(destination) table data to another excel sheet which has previous day records of dt1(dt1+dt2 merged)

Can you please help?

Thanks
Selva


#8

How about doing it this way?

After the above step
Add Data Column - “View”
Assign counter = 0
For Each Row in dt1
row(“View”) = (counter mod 3) + 1
counter = counter + 1

Your dt1 has all the data like how it is in my screenshot above.
Now you can do


#9

@kaderms. Thanks for the response. However before I could try that, I had another issue, can you please help me with this?


#10

@kaderms and @Santan_Barnwal - Guys, Have you experienced this issue? Can you please help me with this