Data Parse from single row

Question

Hi, I came across a data which is in same row with the columns, but defiantly need some input or help in any way possible. 
I have attached the desired output which is my end goal and the text file with all the data in single row.

Thank you in advance.

Desired Output.JPG

trial.txt

nick_ceneviva · Accepted Answer

The attached workflow should get you 95% of the way there.  I couldn't quite nail down what all of the column headers should be.

Essentially what you should do is read your text file in as a fixed width file with one column.  Have a second text input that has all of the names of the expected column headers so (Company Representatives, Territory Covered, Address, Phone, etc.).  Assign a RecordID for sorting purposes to know the order that the records are read.

I then use two different multi-row formula fields.  The first one is used to assign a GroupID, which really will be the final RecordID.  This basically assigns the same number to the records until the first column is met (Company representatives).  At this point it adds one and continues until the next Company Representatives.  The second multi-row formula is used to assign the column headers.  All this formula does is create a field equal to the previous row.

I then join the Text file to the text input tool that has the column headers on field_1 and the column headers.  I then sorted on theTecordID.  A crosstab is used to pivot the data where the column headers are the column header field and the values are Field_1.  You can then use a select tool to order the columns as you would like.

Hopefully that helps but let me know if you have questions!

DultonM · Accepted Answer

Hi @yashd,

Attached is another way to approach parsing your data. It splits the data between headers and non-headers, handles when there are multiple data values per header, and separates the data by section (COMPANY INFORMATION, BROKER INFORMATION, and SITE SELECTION CRITERIA). I added documentation in the workflow so you can follow along with what is happening. Hope this helps!

Joe_Mako · Accepted Answer

Here is another similar route:

- top three tools (summary, sort, browse) are for pulling out the headers to populate the Text Input tools below
- Record ID for sorting
- two Join Multiples bring in the two header fields
- Sort on Record ID
- Multi-Row Formula to fill in Field Header
- Multi-Row Formula to fill in Section Header
- Multi-Row Formula to create a Row ID
- Formula to create a unique Field Name based on Filed Header and Prefix (from the Section Header Text Input)
- Filter to remove header records
- Summary to get the min Record ID per Field Name for use as a Field ID, so the fields can be correctly named and sorted (because just a Cross tab messes stuff up)
- Join to bring in the created Field ID
- Unique to drop repeated values
- Cross Tab to reshape
- Dynamic Rename to correctly name the fields