How to delete Empty Columns in Collections

[AparnaMenthum]

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

[AmiBarrett]

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

[MichalSzumski]

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

[SivaGelli]

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!

[AmiBarrett]

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

[SivaGelli]

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.

[AparnaMenthum]

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.  

[AmiBarrett]

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.

[AparnaMenthum]

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

[AmiBarrett]

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