Find empty cells and write data in that cells

Help Studio
, ,
1

I have a question what is that is , i have an excel in that so much of data, in the excel somewhere empty cells, how to find that empty cells and how to write data in that empty cells only , please help anyone …
Note:- if there is no empty cells it has to say there is no empty cells, if empty cells wite data in that cells

2

send your sample excel sheet

3
Names digits id’s Status office
ram 39 infosys
chotu 30 capgemini
surya 933 4873 working Tcs
sandya 944 9893 working virtus
kondalu 847 4895 working pride
dinesh 333 7387 working bata
vamsi 123 9823 working texus
4

Hi @Aregala_Yedukondalu

Try below steps.

  1. Use Read Range activity to read the data from excel file and will give you output as DataTable and say ‘inputDT’.
  2. And then use ForEach Row activity to iterate the data one by one row.ForEach row in inputDT
    IF row(“ColumnName”).Tostring.Length = 0
    Then row(“ColumnName”) = “Value need to enter in empty cell”
  3. Finally use Write Range activity to write into Excel file.

Regards
Gokul

2 Likes
5

Hey!

Try like this:

  1. Read range - Dtout
  2. Create one integer variable and assign it with 1
Assign intCounter = 1
  1. For each row in data table pass the Dtout
  2. Take one if condition and pass the expression like this
CurrentRow("ColumnName").ToString.Trim.Equals(" ") OR CurrentRow("ColumnName").ToString.Trim.Equals("")

In then block take one Write cell and pass the value what you wants to fill in the empty CELL

→ In the range field mention like this

"E2:E"+(intCounter+1).ToString

I am writing the data in E Cell you can modify this based on your requirement

next to write cell take one assign activity and pass the expression like this

intCounter = intcounter+1

This will keep on fills the data which cell is empty

Regards,
NaNi

6

I completed process and execution. In it i got error in the output at last stage and mentioning it as “Cannot write to the target range because it has hidden rows. Remove any filters or unhide rows and try again.”

7

Hi @Aregala_Yedukondalu

Can you try ExcelApplcationScope-WriteRange (or UseExcelFile - WriteDtaTableToExcel).

Or

ExcelWriteRange throws exception if there is hidden column or row, as the specification of Excel activities package 2.8.5 or above.

Downgrade Excel package or lower might resolve this issue

Regards
Gokul

8

i tried that excel application scope- write range only…

9

You can try to downgrade the package or use Workbook activities

Regards
Gokul

10

use if condition
row(ids).tostring=nothing then write cell
else
dont write

11

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