Too much delimiter on random columns

Driders

I was asked to work on Alteryx.
I have a challenge with automating data cleaning.
I want to automatically clean extra delimiters in the description columns of concatenated BSEG_BKPF tables from SAP.

I can handle cases where a description column has extra delimiters using this Formula : 

IF Length([Field_1])-Length(Replace([Field_1], "|", "")) > [NB délimiteur attendu] then Left([Field_1], FindNth([Field_1], "|", [Premier "|"])+1) + REPLACE( SUBSTRING( [Field_1], FindNth([Field_1], "|", [Premier "|"]) , Length([Field_1])-FindNth(ReverseString([Field_1]), "|", [Dernier "|"])-FindNth([Field_1], "|", [Premier "|"])),"|","")+ Right( [Field_1], FindNth(ReverseString([Field_1]), "|", [Dernier "|"])+1) else [Field_1] endif

Where : 
[Premier "|"]) = Length( Left([Field_1], FindString([Field_1], "SGTXT"))) - Length(Replace(Left([Field_1], FindString([Field_1], "SGTXT")), "|", ""))

[Dernier "|"] = Length( Left(ReverseString([Field_1]), FindString(ReverseString([Field_1]), "TXTGS"))) - Length(Replace(Left(ReverseString([Field_1]), FindString(ReverseString([Field_1]), "TXTGS")), "|", ""))

This allows me, based on the headers, to know how many normal delimiters surround my description column.

This position-based formula does not work when more than one description column is malformed.

If you have any ideas using Alteryx or another language, I would be very grateful.

NeoInfiniTech

Hi @Driders,

Currently I am away from my computer but there is an idea I can share, which assumes that the data is extracted via SQVI and there are no lines containing a text field with a newline,therefore causing a cell content to spill to multiple rows (which would need additional correction via a text editor such as EmEditor or Notepad++, especially for large text files) and all the lines are fixed width (there are trailing spaces after the end of the text fields).

If the assumptions above are not correct, you will first need to make the necessary adjustments to the data in a text editor.

What you will need to do after confirming that the data is in the expected format as explained above, you will either need to load the data in the Flat File (ASCII) format (though I am not sure if it supports UTF-8 characters or your data contains such characters), or you can load the data in as a CSV file format and specify the delimiter as \0 (no delimiter) and then either extract each field via multiple Substring formula or build a dynamic RegEx formula from the structure of the text file (you can find an example of this in this topic: Dynamic Fixed Width Text to Columns, though you might need to adjust the process to make it work with SAP output), which will help you avoid the extra delimiters in the text fields.

After splitting the text file successfully, you can either replace the extra delimiter characters found in the text fields using Multi-Field Formula or you can write the data to a YXDB file (or do both). But if your final objective is to rewrite the text file with the same delimiter characters, do make sure to remove the delimiter characters if you are not outputting the data with quotes.

One caveat: If the data is not fixed width, it is true that only one column with extra delimiter characters can be handled (see Unescaped Quote in CSV file for one such example), but beyond that I don't have a solution either.

Maybe a more experienced user here who might have come across such an issue can chime in, I would also be interested to see how a case such as this is handled dynamically if the data is not fixed width.