Help formatting/parsing CVS file

[GothamRob]

Hello!

I'm very new to Alteryx and have been tasked with formatting a CSV file from a survey. The issue that I'm running into is all of the data is in one column when I use Text to columns (split to rows) it parses the data as expected, but I can't seem to wrap my head around how to get the data separated out of the one column and neatly presented with the relevant data under each column.

Tools used so far: Text to columns, Text to columns (split to rows), Dynamic rename, Select, Sample N Records.

Alteryx version 2019.4.8.22007

I've checked the help files and various other posts on here without any luck, hoping that someone can point me in the right direction! 🙂

Thank you!

[AbhilashR]

@GothamRob - does something like the attached solution work?

[DavidP]

Something like this?

[DavidP]

Slightly better version

[GothamRob]

This works perfectly, thank you!

I'm trying to understand the workflow, are the two multi row formulas grouping/organizing the data together and the cross tab moves the data into the correct columns?

[GothamRob]

This works, thank you!

I'm trying to understand the workflow of it, what are the create new column and the basic or custom filters doing?

[AbhilashR · Accepted solution]

@GothamRob - are you looking to understand David's workflow or mine? If mine, following is the order of operation: 

1) Read the source data (in this case its your text input file)

2) Split the data into multiple rows based on blank space available after every row (\n delimiter lets you do that). The symbols highlighted in the image below are an indicator that you can split rows based on blank spaces.

3) Once split across rows, split each new row into multiple columns based on comma delimiter. I chose to break it into 15 columns since thats what your download has.

4) RecordID gives every row in the data set a unique sequential number.

5) Filter tool helps get rid of the extra row with the bunch of V's

6) Dynamic Rename takes the column name from row 1 of the filtered dataset and assigns them as new column headers.

7) Select tool lets you choose the final universe of columns you want to output.

Hope this helps! Please mark this post as solution accept if it satisfy's your requirement.

[Philip]

Here's how I would solve it.

[DavidP]

In my solution, I did the following:

1. Use text to rows with the comma separator

2. Create Column identifiers for each of the 15 columns

3. Create Row identifiers for each set of 15 columns

4. Remove the newline (\n) with a regex replace function 

5. I can now use a CrossTab tool (Group by [Row number] with [Column number] as Column names and [DownloadData] as values to get the data in the right tabular format.

6. Tidy up column headers

As you can see, with Alteryx there are different ways to approach each problem.