Table columns to rows

Hello,

I could not get this in a performat way:

I split a text using Cell Splitter and receive a table

            Col1   Col2   Col3   Col4

Row1   text1  text2    text3 text4

Row 2   text5  text6    text7 text8

 

I would like to get a result with only one column containing all texts and one column which refers to the original row (in order to know which text parts belong together)

My approach was to loop over each row

rename the column

transpose the table

collecting the result in the loop end.

 

This solution works, but is slightly slow for our 20ths records.

 

Thanks for any suggestions.

 

Seems like a use case for Unpivoting (without extra columns). (I am not sure there should be a node for this kind of functionality in the text processing extension as it is in the base package. Did I miss an important requirement from your question?)

Cheers, gabor

Hi,

if I understand correctly, once you've split the data into multiple columns, simply use the column aggregator node. Choose to group on cols1-4 and in the aggregate tab, choose to aggregate by list method. Then do an Ungroupby node on this column with the list cells. Does this achieve what you want.

Alternatively in the cell splitter node, tick the option which says Output as List. This will now show all texts in a single cell too in this list format. Again, simply use the ungroupby node now.

Hope this helps.

simon.

Thanks for the fast answers!

I will give it a try

Bernd

How to Split Text into Different Columns?

A feature of Microsoft Excel ‘text to columns’ option under the Data menu allows information in a cell or range of cells to be split into different columns. We find this tends to occur on exported lists of address information.

Following are the steps to split data into different columns using ‘Text to columns’ :

1 Select the range of data you need to work at.
2 Select Data from menu and select Text to Columns in the Data Tools group.
3 Check Delimited in the dialog box (1 of 3) and click Next.
In Step 2 of 3, check the box related to the specific separator in the information.
If the separator is tab, check tab or if the separator
is semicolon, check semicolon and so on.
If the separator is other than the specified check boxes given in the wizard, check Other box and mention the separator in the box for Other:
4 Select Other in the dialog box (2 of 3) and enter the delimiter used in your data (-) in the box for Other: and click Next.
5 Enter the Destination (C3) to paste the data after splitting and Click Finish.
6 Click Ok in the dialogue box.

excel vba programmer

How to Split Text into Different Columns?

A feature of Microsoft Excel ‘text to columns’ option under the Data menu allows information in a cell or range of cells to be split into different columns. We find this tends to occur on exported lists of address information.

Following are the steps to split data into different columns using ‘Text to columns’ :

1 Select the range of data you need to work at.
2 Select Data from menu and select Text to Columns in the Data Tools group.
3 Check Delimited in the dialog box (1 of 3) and click Next.
In Step 2 of 3, check the box related to the specific separator in the information.
If the separator is tab, check tab or if the separator
is semicolon, check semicolon and so on.
If the separator is other than the specified check boxes given in the wizard, check Other box and mention the separator in the box for Other:
4 Select Other in the dialog box (2 of 3) and enter the delimiter used in your data (-) in the box for Other: and click Next.
5 Enter the Destination (C3) to paste the data after splitting and Click Finish.
6 Click Ok in the dialogue box.

excel vba programmer