How to add zero to empty cells, if the output datatable is dynamic

Sheet Name : Input
I would like to add “0”, whereever there are empty cells, starting from B4, C4, D4…U4 to entire row…, the output is dynamic but all starts from B4.
Sheet Name : Output.
Required Output.
Demo.xlsx (11.9 KB)
please help

Hi @Sudhir_P ,

Try this one:
FillEmptyDt.xaml (8.5 KB)

Notes:

  1. put your Demo.xlsx in /Input/
  2. Ensure Output sheet is empty before running

Hi @Sudhir_P

You can use the below LINQ expression.

(From r In dtData.AsEnumerable
let ra = r.ItemArray.Select(Function (x) x.ToString.Trim.Replace(" ",“0”)).toArray()
Select SampleDT.Rows.Add(ra)).CopyToDataTable()

Use statement from above within an assign and assign it to an empty dataTable: SampleDT

Hope this will be helpful. Thank you.

Hi,

dt_Data refers to datatable of Input sheet ??
Sample DT refers to what datatable?
Sorry, i new to Linq query.

Hi @Sudhir_P

Read Range the data from A1 :X3 as per your Data in Excel and Save it as Dt2

Read Range the data from A4:X13 as per your data in Excel and save it as Dt1

Now use LINQ as per @Jobin_Joy Suggested!

Like below

Assign newDtVar = (From r In Dt1.AsEnumerable
let ra = r.ItemArray.Select(Function (x) x.ToString.Trim.Replace(" ",“0”)).toArray()
Select Dt2.Rows.Add(ra)).CopyToDataTable()

Then write range a newDt.

Regards

1 Like

Thanks @pravin_calvin for clarifying his doubts.

1 Like

thank you for clarifying my doubt, but the output is dynamic, Sometimes in X Column and another time U Column, it varies, how should i address this in Read Range please?

Hi @Sudhir_P

Please refer to xaml below !

Main.xaml (6.6 KB)

Hope the above Helps you!

Regards

Sorry Jobin for late response. Thank you for your help.

No worries. Hope @pravin_calvin is already solved the issue.

Thank you.