community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Split one column into different tables

Atom

Hello, I am receiving a .txt file that its ordenated like this:

 

1) ---------------------------------------

2) guest:!:100:100:Usuario de Sistema Operativo, guest:/home/guest:/bin/false

3) ---------------------------------------

4) SU 07/06 14:45 + pts/2 c387133-root
5) SU 07/06 18:09 + pts/1 z225029-root

6) ---------------------------------------

7) system:!:0:root,esaadmin

 

so I need the workflow to read that column and take as a delimiter the "---------------------------------------" row and generate three different tables with the data between the delimiters in order to work with them separately, so it would be like this:

 

table 1:

guest:!:100:100:Usuario de Sistema Operativo, guest:/home/guest:/bin/false

 

table 2:

SU 07/06 14:45 + pts/2 c387133-root
SU 07/06 18:09 + pts/1 z225029-root

 

table 3:

system:!:0:root,esaadmin

 

 

I would do this in the following order - there could be a simpler way to achieve the same result.

  1. Create a new column that would identify each block of data using a unique identifier - this can be done by using a Mutli-Row Formula tool to increment a counter only if the data column has your delimiter, else it would take the same value as the previous row.
  2. Transpose the resultant data with the above new column as a Key Field.
  3. Filter out the rows containing delimiters
  4. Exclude unnecessary columns from the final output using a Select tool.
  5. Finally export to a database using Output Data tool by selecting Take File/Table Name from Field > Change File/Table Name based on the identifier column from step 1. You may want to exclude this column from your final output.

 

Attaching the workflow for reference. I chose to export the data as separate worksheets of an Excel workbook; but the same would also work for a DB output.

 

Best,

Salankayana

Labels