How to delete columns in a collection

[jassi_123]

Hello Team,

I would like to retain only Column 1 and delete the remaining columns in the following Collection:-



And also I need to delete the Name Value in the first Column and I need this data in a data item with , i.e Hong Patricia, Siromani, Ramesh.

Can you someone please advise on it.

 

[VJR]

Hi jassi_123,

I'm sure you would like to make your first row of the collection as the column headings? Is that true?
If so there is a ready made action in Blue Prism called as 'Set Column Names From First Row'.
How to use it is explained in Post #9 here.

For Deleting a column simply use the Delete Column action.

 

[junjie]

hi VJ
the above screenshots showing to delete only 1 single column ID2
if i want to delete many columns ?
what will be in the expression ?
is it : "ID2" & "ID3" and ...
or "ID2"; "ID3"; ...
please help advise.

 

[VJR]

Hi junjie,

The code stage of Blue Prism's 'Delete Column' action is designed to delete one column at a time.

So there are below ways you can attempt to delete multiple columns.
Option i. Have multiple 'Delete Column' actions for each of the columns you want to delete.
May not be your obvious choice.

Option ii. Maintain the list of columns to be deleted inside another Collection (say colColumnsToDelete).
Then loop through colColumnsToDelete and pass the column name to the 'Delete Column' action.
Thus deleting all required columns at the end of the loop. There will be only one 'Delete column' action inside the loop.

Option iii. Make a copy of the 'Delete Column' Code stage and modify it to delete as per requirements.

 

[junjie]

hi VJR ~
thanks so much for your kind and prompt advices : )
i also guess the deletion is only 1 column at a time... in case i cannot find the ways how to put into the expression to delete many columns at a time
so, the option 3, this one i don't know much about coding ...haha
option 2, how to filter into a collection of having all unwanted columns ?
let's say my collection 1 having columns named A, B, C , D and E
how do i create another collection containing only unwanted columns B, C and D ?

 

[junjie]

hi VJR ~
i also have another question that i do not know how to resolve ...
i have 2 excel files
file1 having columns A, B, C, D
file2 having columns A, B, E, F

columns A and B in both files: have the same header
columns A in both files: have the same value (staff ID number)

i want to combine this 2 files into 1 file, and this combined file will have columns A, B, C, D, E, F
and the added columns E and F will be based on Vlookup of the staff ID on column A in file 1 to look up at file 2 to add values of columns E and F accordingly.

what can i do?
can help advise steps by steps and what to write inside the properties of them ?

 

[junjie]

hi VJR ~
to add on the above question, that , when adding the columns E and F in the existing file1, the header of columns E and F will also add in.
please help ... ~

 

[VJR]

hi VJR ~
thanks so much for your kind and prompt advices : )
i also guess the deletion is only 1 column at a time... in case i cannot find the ways how to put into the expression to delete many columns at a time
so, the option 3, this one i don't know much about coding ...haha
option 2, how to filter into a collection of having all unwanted columns ?
let's say my collection 1 having columns named A, B, C , D and E
how do i create another collection containing only unwanted columns B, C and D ?

For Option 2, you just need to create a Collection data item and list down names of all columns that you don't want.

colColumnsToDelete
B
C
D

 

[junjie]

Hi VJR ~
we are still working on collection right?
meaning that, business object is collection manipulation, and which action to select in the drop box ?

 

[VJR]

Hi VJR ~
we are still working on collection right?
meaning that, business object is collection manipulation, and which action to select in the drop box ?

Deleting multiple columns of collection. Drag and drop a Collection data item. Give 'colColumnsToDelete' as the collection name. Give a column heading - meaning Field. Add rows and manually write B, C and D in those rows since these are the columns you said you want to delete.

 

[junjie]

Hi VJR
this is at the collection data item creation. (add fields and put the names of the fields to group them in this collection )
how about what action to take? and what to write inside the property of this action?

 

[VJR]

Hi junjie,

I've mentioned the action to take in the above steps.
I suggest you to read thoroughly and go step by step and make amendments if necessary.

Option ii. Maintain the list of columns to be deleted inside another Collection (say colColumnsToDelete).
Then loop through colColumnsToDelete and pass the column name to the 'Delete Column' action.
Thus deleting all required columns at the end of the loop. There will be only one 'Delete column' action inside the loop.

You have created colColumnsToDelete collection. So what would be the rest of the steps based on the above instructions.
[colColumnsToDelete.ColumnName] is to be passed to the action where ColumnName is whatever you gave to the new collection.

 

[junjie]

Hi VJR ~
Got it and let me try to figure out next steps
How about the other question?
Await your kind advices
Thanks ~

 

[junjie]

hi VJ !~
i created the collection of those unwanted columns.
after that, i loop the original collections (of all columns) through the unwanted columns collection, using Action - Delete column, the Inputs are the unwanted columns collection. But at the column name, what should i put there ? i did try leave it blank so it will delete whatever it has in the unwanted columns collection, but it results as empty in the result collection.

 

[VJR]

hi VJ !~
i created the collection of those unwanted columns.
after that, i loop the original collections (of all columns) through the unwanted columns collection, using Action - Delete column, the Inputs are the unwanted columns collection. But at the column name, what should i put there ? i did try leave it blank so it will delete whatever it has in the unwanted columns collection, but it results as empty in the result collection.

View attachment 990

- Buddy, you will need to have a calm mind, be patient, read the instructions thoroughly. Every word, line ,sentence has some significance.
I have provided you the format for what needs to be passed.

[colColumnsToDelete.ColumnName] is to be passed to the action where ColumnName is whatever you gave to the new collection.

-Also, I mentioned loop through colColumnsToDelete and not the original collection .

Maintain the list of columns to be deleted inside another Collection (say colColumnsToDelete).
Then loop through colColumnsToDelete and pass the column name to the 'Delete Column' action.

-In your 'Input Collection' parameter pass the name of the Original collection because you need to delete the columns of that collection.
In 'Column Name' parameter use as stated below-
[colColumnsToDelete.ColumnName] is to be passed to the action where ColumnName is whatever you gave to the new collection.

 

[junjie]

Hi VJ
thanks for your kind explanations.
i encounter this error msg pop up, how should i fix it ?

 

[VJR]

How does your Delete Column property look like?

 

[junjie]

here ...

original data : is the collection having all columns.
columns unwanted: is the collections containers those i don't want.
and here inside the collection columns unwanted:





and my loops property :

 

[VJR]

Columns Unwanted is incorrectly configured.
It must have only 1field (or column) and the Initial Values in that column must be the names of those unwanted columns.

 

[junjie]

oh oh ...thanks
i make corrections.
now, it runs alright.
but i notice it only deleted the columns has value 0. ? (and this column is the one i put at the last row in the initial value)
the other columns still remain ...

shall i need to add any value in ?