Remove Newline Carriage Return from end of output

[AmandaH]

Hi,

My workflow is outputting a CSV file. At the end of the file is a Newline / Carriage return (which you can see when you open the file in notepad) that has to be removed. I've tried using a multi-field formula tool to select all text fields, using the expression Regex_Replace( [_CurrentField_], "\r*\n*", "") but am still not able to remove the Newline. Does anyone have any other suggestions on how I could get rid of this?

[JordanB]

Hi @AmandaH

Our Data Cleansing tool in Alteryx 10.5 and above has a feature to remove line breaks & Punctuation (Return carriages)

Best,

Jordan Barker

Solutions Consultant

[IB]

Hi,

I have installed version 10.5.9 and used the Data Cleanser like in the screenshot above.

However, I need to concatenate the (two or more) rows that had a false line break as well. I tried to use the RegEx tool (formula to remove any CRLF that is not followed by a digit and a semicolon: Replace: [\r\n]+^([0-9]+\s) ) but it doesn't concatenate the 2 rows back together again.

Data looks like:

60;W;;0;N;;J;T;14790002 - Handels;CRLF

62;W;;0;N;;J;T;14790002 - Handels;CRLF

63;S;;0;Z;;J;T;CRLF

14790002 - Handels;CRLF

64;W;;0;N;;J;T;14790002 - Handels;CRLF

The incorrect CRLF (line break) needs to be removed and the data that was on the next row needs to be concatenated. So the result needs to be:

60;W;;0;N;;J;T;14790002 - Handels;CRLF

62;W;;0;N;;J;T;14790002 - Handels;CRLF

63;S;;0;Z;;J;T;14790002 - Handels;CRLF

64;W;;0;N;;J;T;14790002 - Handels;CRLF

How can I do this?

Note: the data is much longer that 255 characters per row. The example above is just a small set of the data per row.

[JordanB]

Hi @IB

I have attached a workflow using regex to remove the first CRLF once I concatenated the correct lines together.

I used multi-row logic to create an ID for each row. Looking at the dummy data it seemed each new row started with a '6'. Feel free to amend the logic depending upon your larger data set.

Best,

Jordan barker

Solutions Consultant

[anotherusername]

The original question looks to me like the issue was around a trailing line-break on the very final row of the output file FROM Alteryx?  Thus can't be processed from within Alteryx - unless there was an option to "don't add a line-break to the final row", but which might break file conventions.

This was also asked (and not solved), here:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Empty-line-at-the-end-of-the-txt-outpu...

Seems like post-processing with a batch script to strip the final CRLF is likely the solution (or opening in Notepad++ and deleting).

In my case, the finance system that the file is going to be uploaded into requires that the final reconciliation count line doesn't have any final line-break on it.

[bbartlett]

As mentioned earlier you can't remove the final CRLF until after the file is written.  The method I use is to run this as a powershell script in an 'After Run Without Errors' event.  Caveat: This may not work well for large files.

Save this code to a file named RemoveLastCRLF.ps1.  It streams the file,  checking the last to bytes for CRLF and if it's there it removes them.

$stream = [IO.File]::Open('<FullPathToFile>', [IO.FileMode]::Open)
$stream.Position = $stream.Length - 2
$bytes = 0..1 | %{ $stream.ReadByte() }
$compareBytes = 13,10 # CR,LF
if ("$bytes" -eq "$compareBytes") {
$stream.SetLength($stream.Length - 2)
}
$stream.Close()
$stream.Dispose()



FYI:  I did not come up with this Powershell code on my own.  I'd give the person I took it from credit but unfortunately I lost track of their post.  Thanks anonymous Powershell person!

[MistyClark05]

bbartlet -

I am having the same issue and am interested in your solution. I have powershell, but have never used it or the Run Command feature. I copied the code and saved it as RemoveLastCRLF.ps1. I am unsure of what to put in the Command Arguments field. I tried putting the file path but it was unable to locate my file. Any advice would be appreciated!