topic Re: Is it possible to leave an entire column blank in the collection properties? in Product Forum

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

Anonymous — Wed, 03 May 2023 14:24:48 GMT

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.

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

Mukeshh_k — Wed, 03 May 2023 15:09:02 GMT

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
------------------------------

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

plnarayana777 — Thu, 04 May 2023 01:55:48 GMT

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

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

harish.mogulluri — Thu, 04 May 2023 05:06:45 GMT

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.

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

Anonymous — Thu, 04 May 2023 06:10:17 GMT

@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.