The fourth post is also a continuation of the first three posts, so this time I would like to introduce loading multiple sheets in Excel.
Note that this tip is only possible in XLSX and XLSM formats, not in XSLB (binary) or XLS (old Excel) formats. The reason why is that it uses the “Import only the list of sheet names” function.
For XLSB format, please try my macro using Python tools available in the Community Gallery.
About Excel files loading
So, I’m sure everyone has known the following “Select Excel Input” screen when loading an Excel file.
Normally, you would select the option “Select a sheet” here, but if you select “Import only the list of sheet names” at the bottom here, you can get the sheet names as shown below.
Using this function, it is possible to get sheet names and load all sheets by specifying the file path as I did in week 3.
Load multiple sheets of one book at once
When you want to load multiple sheets in a single Excel file, you can load the sheet list and read all of them in combination with the Week 3 macro as follows
In the first Data Input tool, I load the file list with full path. To do so, the following settings will be use.
I increase the size in the field where the retrieved full path is stored in the next Select Tool. This is to ensure that the number of characters reserved is not exceeded when adding the sheet name to the file path in the next Formula tool.
The next Formula tool adds the sheet name to the full path. Note that the file name obtained here has the string “” added instead of the actual sheet name. Here is what we would like to do.
\Book2.xlsx|||<List of Sheet Names>To replace this “” with the Sheet Names field, enter the following formula in the Formula tool
Replace([FileName], "<List of Sheet Names>", [Sheet Names])
After that, using the week 3 macro, we can load the multiple sheets in a single Excel Book.
*Of course, you can make it one formula tool by creating a new field without using the select tool.
Load multiple books and sheets in a specific folder at once
To batch read from multiple sheets of multiple Excel books under a specific folder, you should change the above procedure into macro.
I change the red section above into macro as follows. This is made under the assumption that the file paths are entered using the Directory tool as in week 3.
Now let’s take a look at the configuration of the Action tool. We have made the settings a little more complex to make it more resistant to debugging. That is, we have set “Update Value with Formula” as the “Action Type”. Because if you use the “Update Value”, when you change the file in the Data Input tool, you have to change the value in the Action tool, however, often forget to do so and thereby make mistakes.
The expression is as following.
[#1]+"|||<List of Sheet Names>"The other tools will be set up the same as when loading multiple sheets of a single book at once. This results in the following workflow
What about XLSB?
When you want to do the same things in XLSB files, you can use the pyxlsb library with Python tools. As mentioned at the beginning of this post, I have uploaded a macro to Community Gallery. XLSB file type is difficult to use because of its limitations on the number of columns, etc. Although XLSB type files are smaller file size and attractive, I don’t recommend to use in Alteryx. Because Alteryx does not support standard loading, and Microsoft drivers are required)
Sample workflow download
Contents of the next blog
The next tip is using the “Select a named range” in Data Input tool.
コメント