Challenge #2: Preparing Delimited Data

[GeneR]

We hope you enjoyed last week's challenge. The solution has been posted here. For the second challenge lets look at removing characters and splitting data into columns based on delimiters.

Many products will export textual data with delimiters such as quotes. This is done so that strings can contain delimiters or control characters within them. Having more than one type of delimiter can be hard for ETL programs to interpret.  In the input text file, there are two different delimiters (double quotes, single quotes) and they surround different data types.

Use Alteryx to strip out the delimiters as superfluous and format the data as represented in the output.

You may notice that we have started classifying the exercises into beginner, Intermediate and advanced.  This classification is used by Alteryx internally to sequence exercises as users advance. 

Update 11/23/2015:

The solution has been uploaded.

[kevinbird15]

Interesting solution.  I did something similar, but I used the DateTime converter to get into a date format and parsed out all of the quotes and commas on the front end.  Is it possible to have people post solutions one the solution is put up so that we can see how other people are solving the issues?

[TaraM]

Great idea @kevinbird15! Our ultimate goal with these exercises is to give users the ability to try  to solve a myriad of problems and practice using Alteryx. We will have a training section soon that will encourage more participation and collaboration. In the meantime, feel free to post in one of the discussion boards (this one would fit in Data Prep and Blending) and link to the article you are working on. I am sure other users in the community would be very interested in how their peers are solving these problems. 

[marapatricia]

Hi! I cannot open this file because I'm using a previous version. Do you have other versions of this file? Can you post it too? Thanks!

[TaraM]

@marapatricia - what version are you on? I'll see what we can do.

[marapatricia]

It's 9.5.11. Attaching the image. Thanks so much Tara! Really appreciate!

[mix_pix]

Hi,

I've used the Text to Columns tool before and definitely see why it makes sense for this exercise.  How would you configure the Text to Columns tool if there weren't a defined number of fields you wanted to produce?  In other words, in this example we know that there are three distinct components of the text string we want to parse out and those three components are separated by commas.  What if there were a variable number of components in each row (i.e. one had 3, one had 4, etc)?  Is there a way for Alteryx to determine how many components exist in each row (based on a known delimiter) and then feed that number into the # of Columns parameter?

-Mike

[MarkN]

Just did this exercise. I believe that by definition if you have a delimited file, all of the delimiters should be present for each row. If you have a delimited file that does not have all of its delimiters per row, that's an issue that would occur when exporting or creating the data.

[Inactive User]

Can Alteryx generate a new file with special delimiters?

I have an excel file with lots of characters , URL's, apostrophes, parenthesis, commas, etc.  When I send to others, some people said their system can not read my file because their system take these characters as delimiters, so they suggest me to create a new file with delimiters? How to use Alteryx do that?

[TaraM]

Hi @Inactive User - this article explains using alternative characters as delimiters:

http://community.alteryx.com/t5/Alteryx-Knowledge-Base/The-How-to-Guide-to-Writing-Delimited-Files-comma-pipe-other/ta-p/31641