How to find and replace all null cells with certain string? eg "Data Not Found"

[samir (Samir Mohite)]

I’m trying to replace all null cells from the spreadsheet with a string like “Data Not Found”
i’m able to replace all those null cells with a string within a specific column so when i want to replace all null with a string i have to run it again and again by changing the column name,

so after that i have tried using while loop and incremented that column name char A by using this…
input = Convert.ToChar(Convert.ToUInt16(input) +1)
–(where input is my char OR column name start with A)
the problem is it performs well on 1st column but for the rest of the columns it writes that “Data Not Found” according to the 1st column’s cell positions where it written previously.
and it goes into the loop till last col.

• Excel is not installed in my system.
• So tried all this with Workbook activities

here’s the spreadsheet.
Untitled 2.xls (6.5 KB)

here’s my workflow (One column at a time)
Main.xaml (8.0 KB)

workflow with while loop.
practice.xaml (12.2 KB)

spreadsheet (Result)
Untitled 2.xls (12.5 KB)

Error screenshot.

[etss1016 (Tao Tang)]

Can put the process in Try and Catch block, when “Data Not Found” is happened could identify the error cell then replace it.

[MAHESH1 (MAHESHKUMAR JV)]

@samir

You follow these steps it will work.

Read the SpreadSheet and store it in a dataTable dta
Create one more datatable variable dtb
Then use Below Query in Assign Activity

dtb = (From q In dta.Select
        Let x=String.Join(",",From p In dta.Columns.Cast(Of System.Data.DataColumn) Select If(Not 
       String.IsNullOrEmpty(q(p).ToString),q(p).ToString,"Data Not Found"))
       Select dta.Rows.Add(x.Split({","},StringSplitOptions.None))).ToArray.CopyToDataTable

Now you Write dtb in Excel by using Write Range Activity

Regards,
Mahesh