extra delimiter and extra text qualifiers in Text File

[AJadhav45]

Hi Alteryx Community,

I’m encountering an issue with date formatting in Alteryx where there’s an extra | appearing before certain date fields within my dataset. I’d like to remove this extra | while keeping the rest of the structure intact.

Example of the Issue:

1. Current Format:
   ABC - XYZ System"|"20/04/2025"|"Country"|"123456"|"John | Doe"|"John Doe"|"Agency"|""|""|""|"Coun  | try"|""|""|""|""|"N1"|" |15/06/2024"|""|"jane.doe"|"Review"|""|""|""|"N"|"X"

2. Expected Format:
   ABC - XYZ System"|"20/04/2025"|"Country"|"123456"|"John Doe"|"John Doe"|"Agency"|""|""|""|"Country"|""|""|""|""|"N1"|"15/06/2024"|""|"jane.doe"|"Review"|""|""|""|"N"|"X"

Alteryx Formula Tried:

I’ve attempted to fix this using the following REGEX_Replace formula:

alteryx

CopyEdit

REGEX_Replace([YourField], '\|"\s*\|(\d{2}/\d{2}/\d{4})"\|', '|"$1"|')

Issue Description:

In the data, there are cases where an extra | appears between fields, such as "|"  This is causing issues with parsing and data alignment. The goal is to remove these extra characters without disturbing the overall field structure.

[GMG0241]

Hi Ajadhav45,

I've created a solution which removes the extra | from the input (assuming the '¬' character doesn't appear anywhere in the string, but this can be fixed as needed), but doesn't deal with the excess whitespace. I think you'll need to decide on exactly how you want to fix the excess whitespace on a case by case basis e.g. if it's in the 'name' column, we are expecting a first name and a last name so format it so it removes all whitespace except a space in between the first and last name. On the other hand with Country or the date columns, we might expect there to never be a space and can just remove all whitespace in that column. I think it's up to you to work out exactly how you need your logic to process this extra whitespace 

[AJadhav45]

Input data contains extra | characters that need to be removed if they appear within the "|"...........anytext...."|" pattern.
Also all data is getting merged into one cell after using the Summarize Tool. Data want next to each other instead of merging into one cell.

[GMG0241]

To fix the data getting merged, add a groupby to the summarise tool on recordID. I don't completely understand your extra | comment, from what I can see the workflow is correctly removing all occurrences of | in-between "|"..anything..."|", with the only errors occurring when the data isn't formatted correctly (e.g. rows 2 and 3 which have the 1st and 2nd columns separated by |" instead of "|"). Please may you provide an example of where the logic isn't working correctly with regards to the removal of |?

[OllieClarke]

Hi @AJadhav45 

If this is data coming into Alteryx, then to me it looks like your input data tool is incorrectly configured. If you specify a pipe as your delimiter and to ignore delimiters in double quotes, I'd expect your data to come in as a properly formatted table.

Ollie