Writing output to a "CSV" file with a line terminating delimiter

[ColinG]

Hi All,

Once again Alteryx is being too smart for its own good on me. I need to write a pipe delimited text file of data. I've got two separate problems doing this, but I'll stick to the first issue. 

In this file I must not have fields quoted, and it must have a line terminating pipe delimiter before the carriage return and line feed.

So setting up the Output Data tool for File Format=CSV and Delimiters=|, and Quote Output Fields=Never is no problem. The problem is Alteryx does not end the record line with the delimiter too, and offers no option to specify a line delimiter.

My next attempt was to add the pipe with a formula tool to the end of the last field in each record. If you do this, then Alteryx errors out saying "When Quote Mode is Never, field cannot contain a delimter character". Hey Alteryx maybe I do want to do that, due to your tool limitations, and I don't need you checking my underpants. Usually you don't and I have to be super careful.

I tried changing it to "Auto" and of course then certain fields including that one did get double quoted as isn't acceptable for the tool that will take this file as an input.

I've run out of ideas besides using the Python tool to write all the data raw, which I can do, but is yet another tool to write some code and debug. Are there any other options I haven't found on the forums or thought of yet?

Thanks.

[BS_THE_ANALYST]

Hey @ColinG, 

Just had a quick look at the problem. 

Not sure this is the best route/solution but I think this gives a little more flexibility somehow. With Summarize tools we can leverage 3 things when we concatenate values, Start, Middle, End. That got me thinking perhaps you can have the | character (and others) in the "end" placeholder - I guess this meets the ending in a pipe requirement and you can remove the quotes.

We could try not using a .csv file and instead use a .txt file? Would that be an issue? I'll provide the little idea I put together. It's definitely not the best - just a theory.

 

All the best,

BS 

[ColinG]

Thanks for the ideas. I may be able to do it with the workflow as currently set up just exchanging the output tool, if I can figure out how to do a "raw" txt ouput file with no extra logic inserted or assumed by the output tool. I was already using the .csv option choosing the pipe delimiter, but still writing the actual file name as myfile.txt because the application that ingests this file requires the file extension to be .txt

Where would I find that .txt option? It isn't available in my 2021.4.2 version in the Output Data Connections selector, or in the File Format dropdown box. I only see .csv and .flat as options. 

I tried .flat option too, but that writes two files of the same name with two different extensions. A myfile.txt version of the file that has the data but thousands of extra spaces that do not exist within the actual data. I knew it didn't but I put a data cleansing tool on all fields just to be sure with no change. Then it also creates a myfile.flat schema file with the metadata of the .txt file fields.

[BS_THE_ANALYST]

@ColinG any chance you could download my workflow that I previously attached? Perhaps it provides some guidance. Please note I also found myself concatenating all the rows into a single cell in the example I provided but after running a test now, it seems fine to have them as individual rows (check the 2 data streams in the picture below). You'd have to think about the "end" character in the summarize tool if you're having them on separate rows i.e. -> |\n

 

In order to output a .txt file I selected .flat for my output. However, I just ensured that the full path terminated with .txt ..

Let me know how that goes.

All the best,

BS