Crosstabbing a .txt file with field names in rows instead of columns

[arthurmauk]

Hi all, I have a .txt file that I need to transform and I thought the Crosstab tool would do it but I've been having trouble with it.

One record is across multiple rows instead of along one row, separated by null lines. The field names are at the front, then tab and colon :, then the record value. 

I've been able to get somewhere by using Crosstab after I add a RecordID and then CEIL the ID divided by the total number of fields, however the result doesn't come out perfectly because the number of field rows is occasionally different across records...

I've added an example to show you guys what I mean exactly, hope that helps? Thanks in advance! :)

[GiuseppeC · Accepted solution]

Hi @arthurmauk,

the workflow attached should give you what you want. I used the MultiRow formula to create the record ID.

Hope this helps, but let me know if I got your use case wrong!

Best,

Giuseppe

[LordNeilLord · Accepted solution]

Hey @arthurmauk 

You'll need to identify the where each of the records starts first, for example using multi row formula to create a new RecordID everytime it sees "LogonName" in the field.

I've mocked you up a quick example.