topic Hi, With using of existing… in Product Forum

How to delete Empty Columns in Collections

AparnaMenthum — Thu, 07 Feb 2019 23:14:00 GMT

Hi All, I am Working on Excel data, Copying all Excel data in to collections. Once I copied all data in to collections it is copying extra empty columns also, I want to delete column extra columns in collections. I tried business object Utility-Collection manipulation using Delete column action. By using this action I can able to delete only one column, I want to delete 3 columns. If any one knows solution Please help me.. Thanks in Advance..

Found a fairly simple…

AmiBarrett — Thu, 07 Feb 2019 23:27:00 GMT

Found a fairly simple approach on Stack Overflow. https://stackoverflow.com/questions/1766902/remove-all-columns-with-no-…

Hi, With using of existing…

MichalSzumski — Fri, 08 Feb 2019 17:12:00 GMT

Hi, With using of existing objects You can simply create Your own action that will delete all columns except ones that You need. My object just takes input collection and column names in text variable (separated with dots). Then action checks columns in provided collection and delete in loop only those that were unmatched. Still solution provided by amibarrett is more elegant. Best regards Michal

hi Michal, I was not…

SivaGelli — Tue, 19 Feb 2019 20:23:00 GMT

hi Michal, I was not successful in executing the code that was in the SO link. I have added all the Linq related namespaces but still could not manage to delete the empty columns. Could you share your code snippet to get this done? Thanks!

The biggest problem with…

AmiBarrett — Wed, 20 Feb 2019 01:11:00 GMT

The biggest problem with this is that Blue Prism will not allow for nulls once it gets sent into the code stage, if the type is anything other than Text. I've commented out the else in the below code, because no cell will be set to null. (Tested in 5.0.24, but I'm assuming this applies to newer versions as well.) If you want to remove multiple non-text columns at once, then @mszumsk's solution is probably best. (Though running an action to drop a single column three consecutive times shouldn't really have an impact on performance, either.)

DataView dv = Collection.DefaultView; 
//Label to prevent 'Collection was modified' errors     
Out: 
foreach(DataColumn col in Collection.Columns) {
	if(col.DataType == System.Type.GetType(""System.String""))
		dv.RowFilter = col.Caption+"" =''"";
	//else
		//dv.RowFilter = col.Caption.ToString() + "" = null"";
	if(dv.Count == Collection.Rows.Count)
	{
		Collection.Columns.Remove(col);
		goto Out;
	}
}
New_Collection = Collection;

Thanks amibarret! I was…

SivaGelli — Wed, 20 Feb 2019 10:43:00 GMT

Thanks amibarret! I was interested in one stop solution to delete the empty columns using Code stage and, i agree that the other solutions suggested are valid and works well without hindering performance.

Thanks for all !! I tried…

AparnaMenthum — Fri, 22 Feb 2019 00:00:00 GMT

Thanks for all !! I tried below code, but the code stage is throwing compiler error, is there anything do I need to change the code. Function RemoveEmptyColumns(Datatable As DataTable) As Boolean Dim mynetable As DataTable = Datatable.Copy Dim counter As Integer = mynetable.Rows.Count Dim col As DataColumn For Each col In mynetable.Columns Dim dr() As DataRow = mynetable.Select(col.ColumnName + "" is Null "") If dr.Length = counter Then Datatable.Columns.Remove(col.ColumnName) Datatable.AcceptChanges() End If return true end function Thanks ! Aparna.

I seem to recall that…

AmiBarrett — Fri, 22 Feb 2019 02:02:00 GMT

I seem to recall that functions need to be placed within the global code section of the initialize stage. You should then be able to call RemoveEmptyColumns(Collection) in your code stage, assuming your inbound collection is named Collection.

When I try to place this…

AparnaMenthum — Fri, 22 Feb 2019 03:13:00 GMT

When I try to place this code within the Global code section of the initialize stage, it is throwing the error. Page: Initialise Stage: Stage1 Type: Error Action: Validate Description: Compiler warning at line 12: Function 'RemoveEmptyColumns' doesn't return a value on all code paths. Are you missing a 'Return' statement? Repairable: No Page: Initialise Stage: Stage1 Type: Error Action: Validate Description: Compiler error at line 5: 'For' must end with a matching 'Next'. Repairable: No

Place a new line just saying…

AmiBarrett — Fri, 22 Feb 2019 03:24:00 GMT

Place a new line just saying 'Next' above the End If.

Placed 'Next' below the End…

AparnaMenthum — Fri, 22 Feb 2019 08:04:00 GMT

Placed 'Next' below the End If, It's working but how to use initialize stage in the process studio.

You don't call it like an…

AmiBarrett — Fri, 22 Feb 2019 20:55:00 GMT

You don't call it like an action, it's just built in. If it compiled fine in the global code section, then you should be able to invoke the function RemoveEmptyColumns in any code stage on any action you create in that same object.

Sorry @ amibarrett I am…

AparnaMenthum — Sat, 23 Feb 2019 00:32:00 GMT

Sorry @ amibarrett I am unable to catch your point.

If that code is in your…

AmiBarrett — Mon, 25 Feb 2019 20:44:00 GMT

If that code is in your global code, you should be able to call RemoveEmptyColumns(Collection) in any code stage.

Got it, Thank you…

AparnaMenthum — Thu, 28 Feb 2019 20:07:00 GMT

Got it, Thank you..@amibarrett