We are celebrating the 10-year anniversary of the Alteryx Community! Learn more and join in on the fun here.
Start Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
fireworks
SOLVED

how to convert rows in a text file to columns

chvizda
8 - Asteroid

Hi all

 

I have .yml file in the format below with many many more entries in the same structure. I need to transform these to columns with the names before the : (i.e. -name , folder, state,...)

I can import the file as csv, use a text to columns tool and split it by the :. Now I thought I can crosstab it by the new column and concatenade the values. But now I don't know how to go forward to have the correct values in the new columns.

 

I hope you can help me.   

 

Many thanks in advance

Steffen

 

new needed format

-name:folder:state:attributes:alias:assetid:ipaddresstag_period
tst015/test/network/switchpresent tst015xlsx_2773ac5114a55164f71c3721821a46802c4652b210.10.3.35718x5
tst11bg......     

 

 

existing format:

- name: tst015
folder: /test/network/switch
state: present
attributes:
alias: tst015
assetid: xlsx_2773ac5114a55164f71c3721821a46802c4652b2
ipaddress: 10.10.3.35
tag_period: 718x5
- name: tst11bg
folder: /test/network/switch
state: present
attributes:
alias: tst11bg
assetid: xlsx_5970346a48e1f5d5e7a1c4ca4863bf491857d18d
ipaddress: 1.1.93.51
tag_period: 718x5
- name: tst12bg
folder: /test/network/switch
state: present
attributes:
alias: tst12bg
assetid: xlsx_2ba50980f63f7b5c4c2a20913b58c00742bd4967
ipaddress: 2.2.93.52
tag_period: 718x5

 

3 REPLIES 3
gawa
16 - Nebula
16 - Nebula

@chvizda 

In your dataset, I see two challenges:

1) The name of columns repeats every 8 rows, so 1 record will have 8 columns after cross-tab. Before cross-tab, it needs grouping of records somehow.(in the given dataset, row 1-8, row 9-16, row 17-24 => total 3 records)

2) The name of columns contains special character like a whitespace, - that will be renamed to _ forcibly by Cross-tab tool.

 

Here is the WF to tackle these challenges. First,  grouping by every 8 rows using Record ID, Sort, and Tile tools.

image.png

 

Second, replace column name to serial number(001, 002,,,) by Find&Replace tools and Cross-tab. At last, retrieve original column name by Dynamic Replace tool, and you will get the desired output.

I was not sure you want to concatenate all records or distinguish it so I prepared two patterns for your reference.

image.png

 

Pls check the attached WF how it works. 

Raj
16 - Nebula

@chvizda one way of doing this
short and simple 
hope this helps.

jdminton
13 - Pulsar

Another way to do this. This way is a little less complex.

Snag_3d6f062.png

Labels
Top Solution Authors