topic Re: Challenge #2: Preparing Delimited Data in Weekly Challenges

Challenge #2: Preparing Delimited Data

GeneR — Fri, 05 Jul 2024 21:11:55 GMT

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.

Re: Alteryx Weekly Exercise - Week #2 DataPrep_Delimiters (Beginner)

kevinbird15 — Wed, 25 Nov 2015 16:10:11 GMT

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?

Re: Alteryx Weekly Exercise - Week #2 DataPrep_Delimiters (Beginner)

TaraM — Wed, 25 Nov 2015 16:24:24 GMT

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.

Re: Alteryx Weekly Exercise - Week #2 DataPrep_Delimiters (Beginner)

marapatricia — Thu, 10 Dec 2015 08:07:22 GMT

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!

Re: Alteryx Weekly Exercise - Week #2 DataPrep_Delimiters (Beginner)

TaraM — Thu, 10 Dec 2015 21:55:39 GMT

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

Re: Alteryx Weekly Exercise - Week #2 DataPrep_Delimiters (Beginner)

marapatricia — Sun, 13 Dec 2015 23:11:57 GMT

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

Re: Alteryx Weekly Exercise - Week #2 DataPrep_Delimiters (Beginner)

mix_pix — Mon, 14 Dec 2015 19:55:51 GMT

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

Re: Alteryx Weekly Exercise - Week #2 DataPrep_Delimiters (Beginner)

MarkN — Fri, 17 Jun 2016 04:03:39 GMT

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.

Re: Alteryx Weekly Exercise - Week #2 DataPrep_Delimiters (Beginner)

Inactive User — Tue, 11 Oct 2016 21:52:37 GMT

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?

Re: Alteryx Weekly Exercise - Week #2 DataPrep_Delimiters (Beginner)

TaraM — Tue, 11 Oct 2016 21:59:50 GMT

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

Re: Alteryx Weekly Exercise - Week #2 DataPrep_Delimiters (Beginner)

Inactive User — Tue, 11 Oct 2016 22:10:12 GMT

@TaraM thank you, the article is very useful!

Re: Challenge #2: Preparing Delimited Data

KOBoyle — Tue, 10 Jan 2017 00:11:00 GMT

I don't see how this solution retains delimiters or control characters that are present within a field. If I change the first input from "Mary had a little lamb whose fleece was white as snow" to "Macy's, Inc. had a little lamb whose fleece was white as snow", where an apostrophe (text qualifier delimiter) and comma (field separator delimiter) are present in the text, the output is not correct. Can someone clarify how to implement a comparable solution when the delimiters are not superfluous, and are used for their intended purpose? Does Alteryx make a distintion between what spreadsheet software refers to as text qualifiers and field separators? Thanks.

Re: Challenge #2: Preparing Delimited Data

JoeM — Tue, 10 Jan 2017 15:53:14 GMT

@KOBoyle, that's a great thought. In your example, where we transform the underlying data value: "Mary had a little lamb whose fleece was white as snow" to "Macy's, Inc. had a little lamb whose fleece was white as snow", the solution will fail. However, there are some options in the text to columns tool to help with cases like this. In the advanced options, we can choose when to ignore the specified delimiter when it is in 1) quotes, 2) single quotes 3) parentheses and 4) brackets. These options essentially operate as text qualifiers via the text to columns tool. If you reconfigure your solution in the text to column tool to appear as the following, your example will work:

Also, note that this functionality is available for certain file types in the input data tool. For example, if I import a .txt file, I will have the following as a configuration option:

Re: Challenge #2: Preparing Delimited Data

KOBoyle — Tue, 10 Jan 2017 16:37:39 GMT

That is what I was looking for. Thank you for the quick reply.

Re: Challenge #2: Preparing Delimited Data

kconner — Tue, 17 Jan 2017 13:33:33 GMT

Great exercise. I really like these challenges to help learn the tool and get familiar with its abilities.

My solution is below. Basically I ran Text to columns on the ',', then stripped the quotes from the two fields. Converted the date and then selected the values for the final display.

solution display

Re: Challenge #2: Preparing Delimited Data

mceleavey — Mon, 23 Jan 2017 12:06:10 GMT

A simple solution:

I used the Text to Columns tool to seperate on commas, ensure "Ignore delimeters in quotes" is checked. This will ignore the commas in the text.
Then simply remove the quotes and convert the date.

Re: Challenge #2: Preparing Delimited Data

Max06270 — Thu, 09 Feb 2017 20:45:02 GMT

Pretty simple, thank you for the challenge!

Re: Challenge #2: Preparing Delimited Data

KatieA — Fri, 24 Feb 2017 04:33:24 GMT

Solution attached!

Re: Challenge #2: Preparing Delimited Data

SeanAdams — Sun, 26 Feb 2017 18:40:31 GMT

Thank you for the exercise - mostly the same as the other responders except:

- Used the new Alteryx 11 custom date capability in the DateTime component just to see how it works
- Used select tools throughout to remove unused data at each step (not needed for 2 rows, but good habit
- Added a simple tester to do a field-for-field check on the results vs. expectations

Re: Challenge #2: Preparing Delimited Data

JoshK_dup_167 — Fri, 03 Mar 2017 16:30:59 GMT

Going for gold.