how to convert rows in a text file to columns

[chvizda]

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:	ipaddress	tag_period
tst015	/test/network/switch	present		tst015	xlsx_2773ac5114a55164f71c3721821a46802c4652b2	10.10.3.35	718x5
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

[gawa]

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

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.

Pls check the attached WF how it works. 

[Raj · Accepted solution]

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

[jdminton]

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