Formatting a flate file

[IJH34]

Not sure if this is possible in alteryx, but wondering if I would be able to format a flat file correctly.

i.e. putting field names as header across multiple columns and taking the corresponding data and placing it in the correct colum. 

Notice that the second persons information contains a field that would fall into Address 2, while the first persons address 2 field would be null. 

CURRENT FORMAT:

hhnumber   Name   Address1   Address2   City   State   Zip   OverallMatch   Age   Age_Indicator   Estimated_Income_Code   No_Mail_Flag   FILLER1   Positive_Match
2   JOHN DOE   123 LONG RD   WONDERLAND   MA   01375-9474   M   36   I

6   M
6   JANE DOE   777 FLYING CLOUD DR   APT 333   EDEN PRAIRIE   MN   55344-3834   M   30   I 

 9 M

DESIRED FORMAT:

hhnumber

Name

Address1

Address2

City

State

Zip

OverallMatch

Age

Age_Indicator

Estimated_Income_Code

No_Mail_flag

Filler1

Positive_Match

2

JOHN DOE

123 LONG RD

WONDERLAND

MA

01375-9474

M

36

I

6

M

6

JANE DOE

777 FLYING CLOUD DR

APT 333

EDEN PRAIRIE

MN

55344-3834

M

30

I

9

M

[grossal]

Hi @IJH34,

would you mind sharing a minimal version of the flat file? It's easier if we can explore and test it.

Best

Alex

[IJH34]

@grossal I've attached a sample of the file. Formatting, spacing, and field names all remain the same, however I've stripped away any PII. Enjoy the Avenger themed data set.  

[grossal · Accepted solution]

Hi @IJH34,

that's why I love sample data. The solution is just two clicks away! 

During the import Select "Read it as a delimited text file" and choose "Tab".

Output:

Workflow for prove:

I'll attache the "workflow" anyway 😉

Best and have fun with the data 😃

Alex

PS: Why does this work? First of all it's not truly multi line, it's just shown as multi-line. Second, it uses Tabulators instead of whitespaces, therefore it's easy to parse.

[IJH34]

Wow! Could it have been any easier? Thank you so much!