Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
Alteryx Alumni (Retired)

The other day there was an email submitted to the roundtable (an email list set up to help internal users of Alteryx with any technical questions they may have with the product). Reading the question and response made me think that there’s probably some functionality being used that perhaps people aren’t aware of and that actually could be quite useful.


The question was - how to read in multiple files from a folder (all in the same format) but one or more of the files might be empty.


The person submitting the question already had an idea of how to do this and was using the Input tool utilizing the wildcard functionality. For example if you want to read in all the text files from a specific folder you could use the following in the Input Data Source box: W:\Temp\*.txt, or if you want all the text files from that folder which start with a certain name: W:\Temp\ExampleFiles*.txt. Now this will work fine if all the files that you are trying to read in are firstly the same format and secondly contain data. (Also note that the ? symbol can be used as a substitute for a single character in a similar way as the * is used to substitute multiple characters.)

The problem is that if one of the files contains no data, and by no data I mean absolutely no information, ie. the file is totally empty, then the Input tool will error.


Ok, so this method although it would appear initially that it might do the job, doesn’t. Of course with Alteryx though there is always a different approach that can be taken.


Instead of using the Input tool how about using a Dynamic Input tool, if we can somehow give the tool a list of the files we want to read in then the job is done. So how to get a list of the files to read in… well the Directory tool seems perfect for that.

So using the Directory tool to get a list of all the files in the folder which match the same criteria used in the Input tool, W:\Temp\*.txt or similar, we get our list, except we now need to remove the files that are empty from the list. This just requires a simple Filter tool. The Directory tool not only provides a list of the file names but also includes other useful fields of information such Size and FullPath. Therefore the Size field can be used in the Filter, [Size]!=0, from the true output we will have our list of files which contain data, and then in the Directory tool we can set it up to use the FullPath field as the file path as follows:

And this is just one use, the Directory tool also outputs a CreationTime field, I can see how that might be useful. Hopefully this will have at least given a few people some ideas about how they can possibly read in multiple files either with the Input tool and wildcard option or with the Dynamic Input tool.