Remove empty columns

[VratHimbo]

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

[david.l.morris]

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

[VratHimbo]

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

[zdenek.kabatek]

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

[VratHimbo]

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

[VratHimbo]

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

[zdenek.kabatek]

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