WeeklyAlteryxTips#2　Multiple file read #2

The second Weekly Blog is a continuation of the first Blog on loading multiple files in the Data Input tool.

In the previous post, I explained how to load the multiple files with the same schema, and I would like to explain about loading files with the same schema again this time.

Read multiple files with the same schema at once, excluding some files

There are some files with the same schema, you may want to exclude files from being read based on some condition. For example, you only want to read files that do not have “_old” at the end of the file.

In such cases, the you can use Directory tool to read the file path information once, and then you can use the Filter tool or other tools to exclude unnecessary file paths before using the Dynamic Input tool.

In other words, the workflow is as follows:

The settings for the Dynamic Input tool are as follows (this setting does not work for Excel or Access files).

The “Input Data Source Template” is a required setting. You need to specify the file that will be the template to be loaded here. If the files with a different schema will be skipped and not loaded.

“Field” option in the “Read a List of Data Sources” section is the field that contains the file path. If you are using the Directory tool, “Full Path” is the appropriate field. And also “Action” should be set to “Change Entire File Path”.

Note : For Excel files

For Excel files, the sheet name must also be specified when specifying the file path within the Dynamic Input tool.

For exmaple, the following path does not work.

C:\\MyDocAYXWeeklyBlog\001_MultipleFiles\Book1.xlsx

In this example, the three pipes and sheet name should be added after the file name as follows.

C:\\MyDocAYXWeeklyBlog\001_MultipeFiles\Book1.xlsx|||Sheet1

Once this is done, the rest can be set up just like a CSV file.

The workflow is as follows:

The Formula tool settings are as follows:

FileAddPaths([Directory], [FileName]+"|||Sheet1")

In this workflow, the Filter tool is used to remove the files from file list, but you can freely create your own logic, so try creating logic that is not limited to the Filter tool (for example, you can use the Join tool to exclud the files from the excluded file list). Alteryx does not require a specific configuration to work, but rather allows you to flexibly create workflows to do a variety of things.

Sample workflow download

Contents of the next blog

The third post will be a multiple file loading, too.