Is it possible to leave an entire column blank in the collection properties?

[Anonymous]

Hi,

I am working on use case where I need to collect data from excel file and to store in a collection properties. After that, I need to check the "Result" column. If there is any value, I need to make it clear or blank. If anyone can provide any solution, it would be a great support.

I have one idea. To Create another macro excel file and to run the macro file using Blue Prism to make the cell in excel file blank. But I am looking for a solution where I can make the result column blank from collection properties.  

[Mukeshh_k]

Hi @Ekram Shihab - You can either work on the collection by looping in and put in calculation stage as "" and store it in Data in Collection.Results  - and then use the MS Excel Action - Write collection :


OR you can directly change the values in the result coloumn of excel itself - Use Go to Cell "A1" > Go to Next Cell Empty Down > Get Active Cell - This will give you the count for last row cell - suppose Get active cell is A9 - that means its a 9 row collection - Do text functions to extract this Number - store it in variable > Use this number to set limit for iteration , Use a counter with a Initial 0 value and check the limit in the decision (Counter <= Limit)> Update the Value as in below snip and increment counter until limit reached- 



------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS, England, United Kingdom, GB
------------------------------

[LakshmiNarayan3 · Accepted solution]

Other option if you want to remove values from entire column

1. Use Utility - Collection Manipulation::Delete Column will delete entire column

here you give the collection name and column name so it will delete the column

2. Use Utility - Collection Manipulation::Append Field (Text) to append the same column with null values ( here I have given for Text type , if require you can opt for number as well )


Here you give the collection name and column name with default value as "" so it will add the column with null values

Regards
Lakshmi Narayana

[harish.mogulluri]

Hi Ekram Shihab,

Just to add with the existing options other members provided. Additional option is you can write the code either in C# or VB.Net

If  you have huge data like >30k  I generally prefer the code stage instead of Looping through the loop stage. Also as other member mentioned deleting the column and adding the column will also  be the good choice if   you have more data. 

[Anonymous]

@Mukesh Kumar thanks for your solution. It's nice. 
@Lakshmi Narayana thanks. Your solution is easy. I prefer to use your solution.
@Harish M thanks. Currently, my data volume is less. In future, I might need to use your concept. Thanks for the solution.