Challenge #2: Preparing Delimited Data

[Inactive User]

@TaraM  thank you, the article is very useful!

[KOBoyle]

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.

[JoeM]

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

[KOBoyle]

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

[kconner]

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

[mceleavey]

A simple solution:

Spoiler

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.

[Max06270]

Pretty simple, thank you for the challenge!

Spoiler

[KatieA]

Solution attached!

[SeanAdams]

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

Spoiler

- 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

[JoshK_dup_167]

Going for gold.