Hey guys,
I hope you are all healthy.
I would like to filter a column in my excel sheet for empty fields. In those empty fields I need to add some data (in this case just the number “1”). How is it possible to do that? Would the Filter Wizard change the Excel as well?
Sometimes the column has more or less rows that are empty and that’s why I need the process to be dynamic.
I think you can understand it better if you see the screenshot:
Column G is filtered for the rows that are empty (I did it so that you can understand it better. Actually the whole excel sheet is not filtered yet. I want UiPath to do it) and now I want to put “1” into the whole column G (except G1 
)
I hope you can help me out 
@RandomGuy Can you provide us with a sample excel file so that we can work and suggest better solutions accordingly, also let us know if you want the Excel format to be preserved ?
@supermanPunch
It has no real context and nothing important but the real excel file has probably the same principle. In this sample it also is column G
sample.xlsx (12.2 KB)
@RandomGuy Can you Check the below workflow :
It contains the sample excel file provided, After the Execution It should update all the Empty values or cells in Excel to 1.
Mass Update Datatable.zip (11.9 KB)
It will find the row and Column Indices of the empty values present in the Datatable.
Then using a For Each row it updates those particular values to 1.
Please revert back if this is not the solution and provide more details about what should be done exactly.
1 Like
@supermanPunch
I had to change the workbook path but then it worked out!
Can you explain the code like “DT.AsEnumerable().SelectMany(Function(row,i) DT.Columns.Cast(Of DataColumn).Where(Function(col) row(col.ColumnName).ToString.Equals(”“)).Select(Function(s){(i).ToString,s.ColumnName.ToString})).ToList()”
I can’t really see what everything actually means
Would that code work on another excel sheet when there are more rows and other data?
@RandomGuy Yes, It should generally work on the with excel having more rows and other data as well. But Basically the Whole Workflow works in Such a way that it finds the row and Column Indices of the values which are empty and then updates those values to 1 as mentioned in the previous post.
The Explanation of the Linq Query is as below :
DT.AsEnumerable().SelectMany(Function(row,i) - It Iterates over the row Collection of datatable, with the index (i) of the row,
DT.Columns.Cast(Of DataColumn).Where(Function(col) row(col.ColumnName).ToString.Equals("") - Here, We iterate over Columns present in the Datatable and filter for rows where it’s respective column value is Empty.
Select(Function(s){(i).ToString,s.ColumnName.ToString})).ToList() - Then from the Filtered rows we collect it’s row index and it’s respective Column Name in the form of an array ({i.toString,s.ColumnName.ToString}) and convert the whole Collection of Array to a List.
Hence the resultant value of the expression above would be of the Type List(Of String())
1 Like
@supermanPunch
Alright thanks.
Is there a collection of functions? Because I never would have come up with these functions. For example I don’t know what “AsEnumerable” or “Columns.Cast” means and does specifically
@RandomGuy Have a Look here on the AsEnumerable examples and it’s need.
Also Learning the basics of Linq using tutorials available in the web, will help you understand more, one such example is below :
1 Like
@supermanPunch Thanks, I’ll take a look!
Well I used your code in my real excel sheet which has about 500 rows and the robot was running for 30 minutes and then I stopped because nothing happened. With the excel file I shared with you I had no problems. I expected at least an error or something
@RandomGuy Do you have any other extra processing to be done ? I had replicated the rows to 500+ in the same excel provided by you and it is providing me the Output within seconds.
Let me know if you have any additional processing or if the Excel file is completely Different.
@supermanPunch It’s probably the fault of the Excel file but I can’t tell what the matter is. When I scroll down it goes until row 1048576 for some reason, there is not even data after row 476 lol.
I tried some other automations with that Excel file and they work (They still take some time but they work).
I would like to share the Excel file so that we can solve the problem but I am not allowed to 
@RandomGuy If you’re sure that you do not have rows after row 476, you could delete the rows after that row (Ctrl+Shift+Down and Delete) . In this way you would be able to figure out the cause and resolve the issue if that was the problem. But make sure to keep a backup of the excel file.
1 Like
@supermanPunch Yep I deleted the rows and it kinda worked out.
It filled every empty cell with the number “1” but it is only needed for one column (in this case column H).
Is there also a possibility to delete all data after the last row through automating? That would solve the first problem.
