topic RE: Remove Null/Empty Columns From a Collection in Product Forum

Remove Null/Empty Columns From a Collection

vinodchinthakin — Thu, 14 Jul 2022 06:26:00 GMT

Hi All,

Do we have any action or code stage to Remove Null/Empty Columns from a Collection.

@devneetmohanty07. Any insights here.

------------------------------
vinod chinthakindi
------------------------------

RE: Remove Null/Empty Columns From a Collection

Joshna_16 — Thu, 14 Jul 2022 10:08:00 GMT

Hi Vinod,

Try VBO:Utility-Collection manipulation, action-remove empty rows, that should work.

------------------------------
Joshna Dammala
RPA Developer
Asia/Kolkata
------------------------------

RE: Remove Null/Empty Columns From a Collection

vinodchinthakin — Mon, 18 Jul 2022 12:31:00 GMT

Hi Joshna,
The action you have stated simply deletes Empty rows, but my requirement is to delete Empty Columns

------------------------------
vinod chinthakindi
------------------------------

RE: Remove Null/Empty Columns From a Collection

ManpreetKaur1 — Mon, 18 Jul 2022 15:20:00 GMT

Hi @vinod chinthakindi,

You can use "Delete field" action of Utility-Manipulation and provide the column name to be deleted. It will ask for field name as input to be deleted. Please let me know if it doesn't work for you.

------------------------------
Manpreet Kaur
Manager
Deloitte
------------------------------

RE: Remove Null/Empty Columns From a Collection

vinodchinthakin — Tue, 19 Jul 2022 04:58:00 GMT

Thanks Manpreet for your reply. Delete field works only when we know the column name. In my case, the columns names are dynamic. We don't know which columns are Empty.

------------------------------
vinod chinthakindi
------------------------------

RE: Remove Null/Empty Columns From a Collection

MikkoKamppila — Tue, 19 Jul 2022 07:20:00 GMT

There's no such functionality in Blue Prism Utility library. You have to create a custom functionality with a Code Stage, such as following:

Dim bRemove as Boolean
Dim iColumn as integer
' Loop through each column
For iColumn = Collection_In.Columns.Count - 1 to 0 step -1
'For each Row as DataRow in Collection_In.Rows
	bRemove=True
	' Loop through every row for this column
	For each Row as DataRow in Collection_In.Rows
		if Trim(Row(iColumn).ToString()) & "" <> "" then
			bRemove=False
			exit for
		end if
	Next

	if bRemove=True then
		Collection_In.Columns.Remove(Collection_In.Columns(iColumn))
	end if
Next

Collection_Out = Collection_In.Copy

It's VB code. For Code Properties settings:
* Inputs, a collection named "Collection In"
* Outputs, "Collection out".

Please note, the code above works only for Collection having String fields only. Please develop further if your Collection contains Flags, Numbers etc. It's basically a fork of "Remove Empty Rows" provided by Blue Prism.

Note: no guarantees given.

------------------------------
Mikko Kamppila
IT analyst
Kela
Europe/Helsinki
------------------------------

RE: Remove Null/Empty Columns From a Collection

vinodchinthakin — Wed, 20 Jul 2022 14:06:00 GMT

Thanks. It worked for me for Text Type Fields!

------------------------------
vinod chinthakindi
------------------------------

RE: Remove Null/Empty Columns From a Collection

LinkenZaney — Wed, 20 Jul 2022 15:10:00 GMT

What are the names of the blank columns? From the text of the question as it currently stands, it's unclear what happens when you attempt to leverage the appropriate Action in the Utility - Collection Manipulation VBO.

Thanks for the reply.. I am copying data Excel to collections with out headers data is having 5 columns but it is copying 8 columns.

By using utility- collection Manipulation and the action delete column I can able to delete only one column, but I want to delete all empty columns in collections.

On-Hold Marketing