For each excel row, fill up to cell 15th if cell is empty

Help Activities
, , ,
1

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

螢幕截圖 2022-10-07 上午10.59.27
螢幕截圖 2022-10-07 上午10.59.272384×614 70.5 KB

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?

3

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.

4

螢幕截圖 2022-10-07 下午1.44.13
螢幕截圖 2022-10-07 下午1.44.132384×614 71.5 KB

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:

螢幕截圖 2022-10-07 下午1.49.54
螢幕截圖 2022-10-07 下午1.49.542384×614 71.9 KB

Therefore, i want it to be like:
for each row, if it is not filled up to cell 15, fill it with

5

@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 ?

6

No, the cells in between will always be filled, so we just have to worry about the end of each row.

7

@Ben_siu ,

Could you check with this Method as shown in the Image :

image
image773×461 16.2 KB

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.

8

Hi @Ben_siu,

You can try the following steps to get the required output:

  1. Read a excel file using read range activity.
  2. Use for each loop to get each row of a DataTable and create index variable in foreach activity.
    Note: For Each Row index variable will help to identify the row throw index value.
  3. Use while loop activity under For Each Row. In while loop put a condition so that it run 15 times. Create index variable in while also.
    Note: While Loop index variable will help to identify the col throw index value.
  4. Use If activity under the While loop and check whether the cell value is blank or not. Pass Col index variable in current row to read the cell value.
    5 If the cell is blank then write the cell using write cell activity.

image

Regards,
Rohit

9

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
image

image
image644×526 13.8 KB

image
image907×435 17.3 KB

Hope This Helps

Regards
Sudharsan

10

After this if you write range this datatable in the excel you need you can get your output @Ben_siu

11

hi, i cannot use this workflow, it threw an error of “cannot access the file because it is used by another process”

12

Try like this @Ben_siu

Regards
Sudharsan

14

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.