Collection Manipulation

[matthew.cable]

Hi, I have a process where we will ingest a spreadsheet that contains over 300 columns and possibly a couple of 100 rows. What would be the quickest and easiest way to either remove the columns I do not need, or only collect the columns that I do need, I only require around 60 of the 300 collections.   I can use the delete columns but this will take some time, I cannot find anything anywhere that would allow me to move columns I do require.   Thank you, Matt

[Mustafa_UlasYig]

Hi Matthew.Cable I would definitely use the MS Excel VBO action ""Get Worksheet as Collection"" and get the entirely worksheet as Collection 1. 1) You could use the Utility - Collection Manipulation action ""Split Collection"" to split into two collections, in this you specify the columns that are whitelisted and blacklisted in the Collection templates of the action, this could take a while, especially when you have 300 columns, to specify two collections with their respective column names and types. But once specified, this action is fast to execute. But if the columns differ from time to time, you have to change your Collection templates, and then I wouldn't recommend this solution. 2) I would instead use Utility - Collection Manipulation action ""Get Collection Fields"" to get the Collection Fields/Columns into a collection, e.g. Collection 2. You then delete the fields/columns you want to keep from Collection 2 and then loop Collection 2, where you use either Utility - Collection Manipulation action ""Delete Field"" or ""Delete Column"", where you specify the collection/field name (Collection 2.Column1) you wish to delete from Collection 1. You should the be left with Collection 1 with only the columns you wanted to keep in the first place. Hope it makes sense, this should be fairly fast as well and not require you to specify a whitelist and blacklist collection and is a better solution if columns change over time. Good luck BR, Mustafa