Convert multiple files/file types from a folder to a single file type

[sajast]

Pretty new to Alteryx here.

So, say I have a folder with 20+ files that vary from .csv to .xlsm to .xlsx, etc (all Excel readable). Is there a way to create a workflow where I can tell Alteryx to look in this entire folder and convert all files to a specific file type, such as .xlsx? Rather than trying to create 20+ individual inputs? That's really all I need the workflow to do at this point - look in this folder, convert all files to .xlsx. I'm having trouble with the "look in this folder" and grab everything part ha.

Any advice is greatly appreciated!

[Bluebird_Tim]

Hi Sajast! So the "look in folder tool" will be the directory tool.  Hope this jumpstarts things for you.  

https://help.alteryx.com/20231/designer/directory-tool

Tim

[Bluebird_Tim]

Hi Sajast - I just noticed you put this post in the connect part of the discussions.  I would repost any further questions in the "Designer Desktop" section as thats the product your questions will pertain to.

[ArnaldoSandoval]

Hi @sajast 

I did some research on how to handle scenarious like yours, and found this topic here in the community The Ultimate Input Data Flowchart which is based on the directory tool, batch macro and Input Data Tool 

There are some limitations introduced by the Input Data Tool that we should be aware, they are:

• We have to define a template file when implementing the Input Data Tool in the macro.
• The template file is a dummy, CSV, XLSX, XLS file, the problem is that once we initialise the Input Data Tool with a file type, it is not easy to change it to a different file type dynamically at run time.
• We should keep in mind that CSV files by example could have fields separated by commas, pipes, tabs, etc, so once we configure the tool to handle CSV files with pipe separated fields, it wont be easy for the tool to handle comma or tab separated fields, this constraints force us to implement an Input Data Tool per specific CSV configuration.

• The solution implements Batch Macros per file types because of the Input Data Tool limitations named above, these batch macros workflows are very generic.

• If we look at the Input Data Tool configuration, you will notice the "Dummy.csv" file located at the solution's Templates folder, and it 5. Delimiters field defined as PIPE. You may add extra feature to your macro, like returning the file name as part of the data.

• The Batch Macro's action tool taget the ".\Templates\Dummy.csv" file, but if we look at its configuration, we may be able to dynamically change the FormatSpecificOptions, but we are not doing that here, that will require a more sophisticated macro accepting parameters to modify the FormatSpecificOptions.

• The Macro's interface designer assign a Custom Icon to the macro and define the "Auto Configure by Name (Wait Until All Iterations Runs)" property. This property is very important to get the expected results.

Input Any File macro:

Now that we understand how to create a batch macro to Input CSV  files with pipe separated fields, and based on the limitation listed above, we know that each file type requires its own Input batch macro, e.g. one for XLSX, other for XLS, another one for CSV files with commad separated fields, etc. We created another batch macro managing that scenario.

This macro receives the full file name to process (load), get the file type, base file name, and file name from the Control Parameter; with the file type is trigger the execution of the appropiated macro, e.g. Iinput CVS pipe, Input XLSX, Input XLS and so on.

We introduced a new limitation with this macro, if the file type for comma separated files is in lower case (csv) the file is pipe separated, and if it is all capital letter (CSV) the file contains comma separated fields.

This macro configuration is also similar to the individual Input XXX macros.

Load-Multi-File-Types-01 solution

• The workflow read all the files from the .\Folder directory.
• It does some parsing to isolate the file type and base name, in order to create the TargetFile field.
• It pass the TargetFile field to the Input Any File macro.
• The Input Any File load the target file based on its file type using the appropiated macro.

Attachment:

Once you download and open that attached package, it creates the folder structure shown below: