This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
on 09-28-201503:31 PM - edited on 03-11-201909:20 AM by SydneyF
You have a delimited file that you need to read into Alteryx but there are an unknown number of data fields. What do you do? (Aside from opening the file and counting out the delimiters by hand)
In this example all the data is coming through in one column of data and it needs to be parsed out to individual columns.
Pretending we didn’t know there are 4 fields we can follow simple workflow to dynamically parse the data into the appropriate number of fields.
Start by adding a Record ID tool to the workflow, then add a Text to Columns tool. Rather than configuring the tool to parse to columns, we’re going split to rows using the delimiter, in this case the ‘|’.
This will take all the data from the one row and put it in individual rows, notice that the RecordID groups everything from the same row together, which will come in handy a little later.
Add Formula tool and create a field call count and set it equal to 1. Now a Running Total tool and configure it to group by RecordID and Create Running Total on the Count Field. This will essentially give us the number of columns in our data.
Two more tools and we are good to go.
Now add a Cross Tab tool, this is where the ‘magic’ happens. Configure to the tool to group by RecordID, set the Head Field to RunTot_Count, Data Field should be set to F1 and then select First under Methodologies.
The Final step is to add a Dynamic Rename tool and configure it to ‘Take Field Names from First Row of Data’. Run the workflow and you’re done!
Happy Parsing! (this example workflow - created in v. 10.0 - is attached)