Hi all,
I want to know if it is possible to implement this:
for each rows in the excel file, if the row is not filled up to cell 15, then fill it up with an empty string “” .
My data looks like this
However, I want the workflow to work in any data types, not necessarily formatted such. So may I know if there’s anyway to do this?
Hi @Ben_siu ,
Could you maybe provide us with an Expected Output for the presented Input ?
This way we can avoid much confusions and get the logic right for the implementation. Maybe also highlight the changes in the Output.
sure! so basically, the cells with alphabet are data that I have originally. There’s 15 cell in a row. And I want to fill up all the remaining cells, highlighted here, with whatever string i want.
The most important part is that the data might not be presented this way, it could be like:
Therefore, i want it to be like:
for each row, if it is not filled up to cell 15, fill it with
@Ben_siu ,
Is there a possibility for a value to be empty in between the rows ? In the above example we have only the values being empty at the end of the row.
If there is a possibility that a value can be empty in between, then do we need to fill that as well ? or do we need to ignore it ?
No, the cells in between will always be filled, so we just have to worry about the end of each row.
@Ben_siu ,
Could you check with this Method as shown in the Image :
After reading the Excel sheet as a Datatable, use a For Each Row in Datatable Activity and inside it put the below Expression in an Assign Activity.
CurrentRow.ItemArray = CurrentRow.ItemArray.Select(Function(x)if(String.IsNullOrWhiteSpace(x.ToString),"YourString",x.ToString)).ToArray
Then use a Write Range Activity to write the updated datatable back to excel sheet.
Hi @Ben_siu,
You can try the following steps to get the required output:
Regards,
Rohit
HI @Ben_siu
Check with this expression
Before using this expression
you need to assign another dt
Dt2 = dt.Clone
(From r In dt.AsEnumerable
Let ra = r.ItemArray.Select(Function (x) If(String.IsNullOrEmpty(x.tostring),"you can give whatever string you need",x.ToString)).toArray()
Select Dt2.Rows.Add(ra)).CopyToDataTable()
It will replace all the empty value to whatever string you need
Screenshots
Hope This Helps
Regards
Sudharsan
After this if you write range this datatable in the excel you need you can get your output @Ben_siu
hi, i cannot use this workflow, it threw an error of “cannot access the file because it is used by another process”
Try like this @Ben_siu
Regards
Sudharsan
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
