Challenge #2: Preparing Delimited Data
- S'abonner au fil RSS
- Marquer le sujet comme nouveau
- Marquer le sujet comme lu
- Placer ce Sujet en tête de liste pour l'utilisateur actuel
- Marquer
- S'abonner
- Sourdine
- Page imprimable
- Marquer comme nouveau
- Marquer
- S'abonner
- Sourdine
- S'abonner au fil RSS
- Surligner
- Imprimer
- Signaler au modérateur
@TaraM thank you, the article is very useful!
- Marquer comme nouveau
- Marquer
- S'abonner
- Sourdine
- S'abonner au fil RSS
- Surligner
- Imprimer
- Signaler au modérateur
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.
- Marquer comme nouveau
- Marquer
- S'abonner
- Sourdine
- S'abonner au fil RSS
- Surligner
- Imprimer
- Signaler au modérateur
@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:
- Marquer comme nouveau
- Marquer
- S'abonner
- Sourdine
- S'abonner au fil RSS
- Surligner
- Imprimer
- Signaler au modérateur
That is what I was looking for. Thank you for the quick reply.
- Marquer comme nouveau
- Marquer
- S'abonner
- Sourdine
- S'abonner au fil RSS
- Surligner
- Imprimer
- Signaler au modérateur
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.
- Marquer comme nouveau
- Marquer
- S'abonner
- Sourdine
- S'abonner au fil RSS
- Surligner
- Imprimer
- Signaler au modérateur
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.
- Marquer comme nouveau
- Marquer
- S'abonner
- Sourdine
- S'abonner au fil RSS
- Surligner
- Imprimer
- Signaler au modérateur
- Marquer comme nouveau
- Marquer
- S'abonner
- Sourdine
- S'abonner au fil RSS
- Surligner
- Imprimer
- Signaler au modérateur
- Marquer comme nouveau
- Marquer
- S'abonner
- Sourdine
- S'abonner au fil RSS
- Surligner
- Imprimer
- Signaler au modérateur
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
- Marquer comme nouveau
- Marquer
- S'abonner
- Sourdine
- S'abonner au fil RSS
- Surligner
- Imprimer
- Signaler au modérateur