Remove Null/Empty Columns From a Collection

[vinodchinthakin]

Hi All,

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

@devneetmohanty07. Any insights here.
​​

------------------------------
vinod chinthakindi
------------------------------
​

[Joshna_16]

Hi Vinod,

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


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

[vinodchinthakin]

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

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

[ManpreetKaur1]

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

[vinodchinthakin]

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

[MikkoKamppila · Accepted solution]

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

[vinodchinthakin]

Thanks. It worked for me for Text Type Fields!

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

[LinkenZaney]

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