Help formatting/parsing CVS file

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!

DataPrep.yxmd

Workflow

Datasets

Parse

Best Practices

Predictive Analysis

Documentation

Behavior Analysis

Accepted answers

AbhilashR

@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.

All comments

AbhilashR

@GothamRob - does something like the attached solution work?

DataPrepModified.yxmd

DavidP

Something like this?

parse survey data.yxmd

DavidP

Slightly better version

parse survey data.yxmd

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?

Philip

Here's how I would solve it.

DataPrep_Philip.yxmd

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335