community
cancel
Showing results for 
Search instead for 
Did you mean: 
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Weekly Challenge
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Unable to display your progress at this time. Please try again a little later, or contact an administrator if you continue to see this error.
Announcement | Get certified today - take the Alteryx Designer Core and Advanced exams on-demand now!

Challenge #2: Preparing Delimited Data

Highlighted
Alteryx Alumni (Retired)

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 excercises into beginner, Intermediate and advanced.  This classification is used by Alteryx internally to sequence excercises as users advance. 

 

Update 11/23/2015:

The solution has been uplaoded.

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?

Creative Director
Creative Director

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. 

Tara McCoy

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!

Creative Director
Creative Director

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

Tara McCoy

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

 

alteryxdesigner.JPG

Fireball

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

Atom

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.

Asteroid

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?

Creative Director
Creative Director

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

http://community.alteryx.com/t5/Alteryx-Knowledge-Base/The-How-to-Guide-to-Writing-Delimited-Files-c...

 

Tara McCoy