topic RE: Remove empty columns in Product Forum

Remove empty columns

VratHimbo — Wed, 10 Mar 2021 14:10:00 GMT

Hello guys,
i need some help with a collection. I need to perform the action "Set columns name from first row" but i've some empty field, and i need to delete this column before without the use of my own code stage. Maybe with Oledb that will be great but i haven't the DELETE function.
How can i do that?

Thanks in advance

------------------------------
Vrat Himbo
------------------------------

RE: Remove empty columns

david.l.morris — Wed, 10 Mar 2021 18:29:00 GMT

The way I usually do this is like this:

Utility - Collection Manipulation action 'Get Collection Fields' (let's call the output [Fields]
Use Loop stages to loop over [Fields]
Inside the Loop, use Utility - Collection Manipulation action 'Read Collection Field' with inputs of Row Index = 0, Field Name = [Fields.Field Name], and Collection = [Your Data Collection] -- this should output the value into a text data item
Inside the Loop still, use a Decision stage to check if that text data item is empty: [Text Data Item]<>"" and if it is empty then use Utility - Collection Manipulation action 'Delete Column' using the [Fields.Field Name] as input for the name of the Column.

It'd look something like this:

Edit: I should mention that there are better ways to do this, and I'm specifically suggesting in the case that you only have a few empty columns. If you have dozens of empty columns to remove, then it'd be better to do it another way.

------------------------------
Dave Morris
Cano Ai
Atlanta, GA
------------------------------

RE: Remove empty columns

VratHimbo — Thu, 11 Mar 2021 09:15:00 GMT

Hi Dave,
first of all thanks for your support!
I think i wasn't clear to show my problem. I've an excel file where i need to remove the first 3 rows, because my column name are in the 4th row. And at this point, no problem. When i remove these rows and i've my column names at position 1, i've tried to use "Set first row as column name" but instead of some blank value i'm not able to do this operation.
I want to say what's the best way to manage that situation even if with oledb, in order to avoid another loop (if it's possible).

Thanks in advance

------------------------------
Vrat Himbo
------------------------------

RE: Remove empty columns

zdenek.kabatek — Thu, 11 Mar 2021 16:05:00 GMT

Hi, Vrat,

did you check the action in MS Excel VBO Get Worksheet as Collection Offset. This gives you everything you need. You start with offset and specify that it contains header line.

Regards,

------------------------------
Zdeněk Kabátek
Head of Professional Services
NEOOPS
http://www.neoops.com/
Europe/Prague
------------------------------

RE: Remove empty columns

VratHimbo — Thu, 11 Mar 2021 16:32:00 GMT

Hi @Zdeněk Kabátek,
thanks for the help.
Yes, now i'm closer to my objective, but i've 20 columns named Column6,Column11,Column22,Column23.
How can i remove that? Is a good practice to use a loop with "Remove column" action and in input put "Column"&"*"? Or there is another solution?

Thanks

------------------------------
Vrat Himbo
------------------------------

RE: Remove empty columns

VratHimbo — Fri, 12 Mar 2021 10:50:00 GMT

This is the solution provided, there is a best approach to do that? I loop through the column collection to remove.
Now i need to remove some rows at the end of the collection starting from a particular string, how can i do that?
Thanks

------------------------------
Vrat Himbo
------------------------------

RE: Remove empty columns

zdenek.kabatek — Fri, 12 Mar 2021 11:04:00 GMT

Hi, Vrat,

well, there are several approaches how you can do that. To recommend the best one I would need to know more about the collection. One approach would be to use Filter Collection action which is part of Utility - Collection Manipulation. You build a filter the way that the particular column will not contain the strings which you need to remove. Then you get a new collection which contains all rows except the ones which you don't want.

Regards,

------------------------------
Zdeněk Kabátek
Head of Professional Services
NEOOPS
http://www.neoops.com/
Europe/Prague
------------------------------