The list of files that we can read and write from Alteryx is pretty comprehensive; especially considering we can also read/write from/to many different databases and web datasources too; but sometimes we may come across a file format that does not appear to be on the list of sources available to read from/write too in Alteryx.
In the post we will walk you through a common process that we will apply in order to read data from or write data too, other file types you may wish to work with.
Reading Non-standard File Formats
Lets say I want to perform a count of the number of tools that exist within an Alteryx Workflow, but I want to use Alteryx to perform this process on itself, rather than manually determine the number via counting.
Now if you didn't know, an Alteryx workflow file (.yxmd), is an XML file, which Alteryx Designer translates into the beautiful masterpiece that is your workflow canvas. As you drag, drop and configure tools, this information is getting written to your saved .yxmd file in the background.
Now a .yxmd file is not a typical data file, and as a result is not included in our list of valid file formats within either the 'Input data' or 'Output data' tools; but, with both of these tools, there is an additional option at the bottom of the dropdown, 'All Files', we want to select this option from the dropdown; this will then make, literally every file, visible in the filebrowse.
I can now select anyone of these files and Alteryx will attempt to read the file into Alteryx; I'm going to select the 'AllCombos.yxmd' file as an example.
Immediately Alteryx will bring up a prompt asking you to 'Resolve File Type'; I will almost always use the 'Read as a delimited text file' option, with the 'Delimiter' set to 'None, and the 'First Row Contains Field Names' option unchecked. This process will bring any data the file may have into a single column list.
You may want to check out the configuration pane at this point, and you will see the options you have selected in this panel are then reflected. As a note, the default column width for a .csv file is given as 254; this will often lead to truncation if you are working with long text data (such as complex XML files in the case of a .yxmd or .twb file), in such cases you may want to significantly lift this value, but if truncation is occurring then you should notice it as a warning message.
And when I hit run my data stream will look something like...
Now at this point I just want to clarify this is a workflow built for a community solution and does not contain sensitive data (despite the 'Salary' field).
We can now parse the file as we wish. In this instance, I will simply do a 'Contains' statement to identify rows which have the field 'Node ToolID' in (which indicates a tool on the canvas) and then use the 'count records' tool to identify the number of tools.
A great example of this type of transformation can be found in the sample workflows included in the Alteryx install; available by going to 'Help' > 'Sample Workflows' > 'Use Scripting and Automation tools' > 'Build a Macro' > 'Identify Tools and Macros used in a workflow'
Writing Non-standard File Formats
Writing to a non-standard file format is just as simple as reading from.
When you bring an output data tool onto the canvas and navigate to the filebrowse, you should choose the file type Comma Separated Value ('.csv') from the dropdown; then give your file a name and hit 'save'.
On the configuration panel, where the path and name of the file are given, you should simply amend the extension to be that with which you want to save to, in this case I want to create an Alteryx Workflow file, so I will change the extension to '.yxmd'. If you rename the extension to a filetype which Alteryx does write too, Alteryx will automatically 'resolve' the configuration panel and adjust it from a .csv configuration panel, if it does not, then the .csv configuration panel will 'hold'.
Now these default settings for a .csv file contrast from what we selected when we inputting the file (see above); we want to adjust these settings to match how we inputted the file. So I will set the delimiter to be none (\\0) and uncheck the option 'First Row Contains Field Names'. You will also want to change the 'Quote Output Fields' option from 'Auto' to 'Never'.
Now when you run your workflow, you will generate a .yxmd file (or whatever file type you chose), and it should then work successfully in your desired programme (in this case, open as a workflow in Alteryx Designer!).
If you have any questions relating to this post; or would like further examples of different filetypes then please do not hesitate to comment!
Ben