Challenge #2: Preparing Delimited Data
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@TaraM thank you, the article is very useful!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
That is what I was looking for. Thank you for the quick reply.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you for the exercise - mostly the same as the other responders except:
- 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator