Fill blank data with '-'

[Pranay_Kumar_Bose]

Hello All,
I have few excel files which has almost 200 lines of data each, with some blank cells as well…
when I am consolidating them into one, the rows which has even one blank cell is not getting consolidated. So, the whole row is getting removed.

So, I want to put either “-” or a “0” into those blank cells automatically before consolidation… !
Thanks…

[Palaniyappan (Forum Leader)]

Hi

Check this thread for a xaml sample on the same note

Find/replace Value: I want to find empty cellls in a excel file and replace with 0

as you are lookin for “” Empty string it will not catch a space The provided Excel contains space on the cellls which are looking blank it is risky but you can try first run - replace " " with “” second run - replace “” with 0 When doing this on Datatable level, then you would have a better control on it, as you can use the trim method and would not harm spaces within texts

Cheers @Pranay_Kumar_Bose

[Anil_G (Anil Gorthi)]

@Pranay_Kumar_Bose

You can use find/replace activity and replace all the blanks with -

cheers

[Kartheek_Battu]

Hello @Pranay_Kumar_Bose

Assign (ConsolidatedDataTable = New DataTable)

ForEach (ExcelFilePath in ListOfExcelFiles)

Excel Application Scope (Specify ExcelFilePath)

Read Range (Read data into DataTable)

Loop through rows and columns in the DataTable
ForEach (Row in DataTable.Rows)

ForEach (Cell in Row.ItemArray)

If (String.IsNullOrEmpty(Cell.ToString()))

Replace blank cell with “-” or “0”
Modify the logic based on your requirements
Cell = “-”

Append DataTable to ConsolidatedDataTable
Merge DataTable (Source: DataTable, Destination: ConsolidatedDataTable)

Write ConsolidatedDataTable to the output Excel file
Excel Application Scope (Specify OutputExcelFilePath)

Write Range (ConsolidatedDataTable)

Thanks & Cheers!!!