How To: Read XLS files – with several tabs that contain diacritics

[VianneyM]

Some time ago, there was a nice writeup: The Ultimate Alteryx Holiday gift of 2015: Read ALL Excel Macro: Part 2. This amazing macro allowed me to read any excel file, regardless of the number of tabs.

Until I start working with users that use diacritics on the sheet names.

For example:

If we try to use the mentioned macro, you will receive an error like this: ‘not a valid name’

I decided to approach this as a macro (2 levels) and use the Directory functionality to read all possible xls files within a folder.

Level 1: Read xls Tabs

This macro will read all the tabs for a single xls file. I used an R tool that includes the library readxl.

This library allows us to read xls files. I used the excel_sheets function to extract the sheet name and compile the sheet name with the name file path. You will receive a column per tab that the xls file has. I cleaned these two values and passed them as Path and Tab.

This data is sent to the Read xls file macro.

Level 2: Read xls Files

This macro gives structure to the full path (Path + Tab) using the structure needed in xls files. It uses the Dynamic Input tool to dynamically choose the data and output its content.

Note:

1. Update the XXXX for your corresponding paths
2. Don’t forget to install the R library readxl under your %Program Files% path g. C:\Program Files\Alteryx\R-3.5.3\library

[apathetichell]

FYI - I'm not seeing read Read_xls tabs macro in the workflow after downloading and extracting...

Once you have readxl installed in your R library path it's all fairly straight forward and a good way to get .xls sheet names since out of the box input data doesn't. If anyone else needs help on this (and has readxl already working) let me know and I can whip something up... I'd also recommend setting the director in the outer macro for .xls and then using a filter with regex_match([filename],".*xls$") to filter out the .xlsx files before converting the director locations to R style locations (ie "\" and "/" swapped)...