Filter Excel and add data to empty fields dynamically

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 :smiley: )

I hope you can help me out :blush:

@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 :smiley:
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.
https://linqsamples.com/linq-to-objects/conversion/Cast

Also Learning the basics of Linq using tutorials available in the web, will help you understand more, one such example is below :
https://www.tutorialspoint.com/linq/index.htm