Monday, 15 March 2010

\0 delimited

Ever wondered what the point of \0 delimited files would be?  Ever wished you could output delimited files with no quote qualifiers?  I had done both and having found the answers thought I would share them here.

Last week I ran into a problem I have had on a few occasions over the years I've worked with Alteryx.  This time round some of the tricks I had used before weren't going to work so I decided to post the problem on the SRC forum.  A big thanks to Margarita at SRC, as the same day I got a reply which let me build a working solution!

My problem is a seemingly simple one to do with the delimited output file which Alteryx produces.  What I wanted to output was a delimited file with a pipe delimiter and no qualifiers.  Alteryx does pipe delimited no problem, but when you come to options on qualifiers all you have is the tick box "Always qualify fields" which is either true or false. But there is no option to "Never qualify fields" which is what I want.

So as you might have guessed from the title of the post the solution comes from using \0 as the delimiter.  What \0 does is produce an output file with no delimiters and no qualifiers, which at first glance might not seem so useful.  But what it allows you to do is build up your output file exactly as you want it using the functionality of Alteryx.

For example if you have a dataset with three fields some of which contain quotes and you want to output a pipe delimited, non-qualified file; then you can just use the formula tool to build a new field like so:

[Field1] + "|" + [Field2] + "|" + [Field3]

and then a select to output only that new field to a \0 delimited file.

The below module shows a few techniques of how you could use this: