Incorrect data split

[Duszko]

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)?

[ShankerV · Accepted solution]

@Duszko 

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

[ShankerV · Accepted solution]

Hi @Duszko 

Another way is to use Regex function also like below.

(.+)\s(.+),(.+)

Many thanks

Shanker V

[Duszko]

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?

[ShankerV]

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.

[ShankerV · Accepted solution]

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

[ShankerV · Accepted solution]

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

[ShankerV · Accepted solution]

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

[Duszko]

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).

[ShankerV · Accepted solution]

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