Hello,
Within my dataset some cells contains quotation marks within its values (I guess it stands for inches). It's like:
sthsthsth 1.22",1"
due to this quotation mark Alteryx cannot recognize comma as a column delimiter and incorrectly splits columns. How can I fix this if input is an excel file (not csv)?
Solved! Go to Solution.
If I understand your requirement correctly. Alteryx will read the excel file in one column.
Post which you can use Text to columns like below to get the desired result.
Many thanks
Shanker V
Hi @Duszko
Another way is to use Regex function also like below.
(.+)\s(.+),(.+)
Many thanks
Shanker V
My input is more like this:
So there are around a million rows; among them are some with this incorrect split. I need to fix the split so that all columns are mapped correctly.
Should I filter out those incorrect rows, use one of your solutions and then Union or is there any other way?
Hi @Duszko
Can you give 10 samples in the excel format with all permutation and combination, so will propose the solution for the huge dataset to work accurately.
It will be easy to load and test from my end also.
Hi @Duszko
Check whether it works for you. Hope you are expecting the result like this.
Output:
Input was:
If this helps, please like this post and mark it as a solution. If you have any other questions, please let us know.
Many thanks
Shanker V
Hi @Duszko
Step 1: Input
Step 2: Record ID
This will help to keep the records in order post we do the modifications and union the result.
Step 3:
If this helps, please like this post and mark it as a solution. If you have any other questions, please let us know.
Many thanks
Shanker V
Hi @Duszko
Step 4:
Step 5:
The below order is more important to display the exact result.
Step 6: Select tool
If this helps, please like this post and mark it as a solution. If you have any other questions, please let us know.
Many thanks
Shanker V
Hi @ShankerV
The data is confidential so I cannot give you an exact sample. The file I've attached is created by me to present the issue.
Luckily there is only one permutation (if I understand correctly what permutation is 😅 => pattern that causes the error).
Hi @Duszko
Step 7:
Final output:
If this helps, please like this post and mark it as a solution. If you have any other questions, please let us know.
Many thanks
Shanker V