New Sheet Name Every Month in the Source File

I have an Excel Binary File which is downloaded from the System every month. Each file contains only one Sheet and the Sheet Name varies month on month. For example

FILE #	Sheet Name in April	Sheet Name in May
File 1	903017	962135
File 2	874564	845752
File 3 .... and so on

When I built the workflow, I built it using the April Files loading April Sheet Names and it works well.

Now when I replace the source folder with the May files, the May Sheet names are different from the April ones as depicted in the above table and therefore the Input Tool does not read them.

How to I force the Input Tool to read the only sheet available in each of the files irrespective of the changing sheet names.

Datasets

Developer Tools

Input

Accepted answers

mceleavey

Hi @surajmthomas ,

I don't understand. It works perfectly for me.

Replace that second tool with the xlsb tool attached and change the directory tool accordingly.

Import Multiple Schema XLSB.yxmc

Multi Sheet Import Example.yxmd

All comments

Emil_Kos

Hi,

You can skip reading the headers and use dynamic replace tool to change the name of the columns. I hope this helps:

Workflow.yxmd

surajmthomas

Dear Emil,

The table that I depicted above is not the contents of the file. It is how the sheet name in the file changes every month. The sheet name is not consistent like "Sheet 1". The sheet name is depicted as "841256" in April and "874562" in May. Since the sheet name keeps varying the Input Tool is unable to read the refreshed input month on month.

Emil_Kos

Hi,

In each of those sheets, the column places are the same? Or they also move around. If they are in the same place my workflow should be able to help you. I have skipped loading the headers and then you could use some kind of generic headers that would work for each of the months and use them by dynamic replace.

mceleavey

Hi @surajmthomas ,

I built a couple of tools to do exactly this.

I've attached the two tools and example workflow. The first tool reads in the sheet names in each file and appends them to the filename. Then the second tool dynamically loads them all in and unions them together.

I hope this helps,

EDIT: You'll need to change out the second tool in the example to the XLSB version attached.

Multi Sheet Import Example.yxzp

Dynamic Multi File Sheet Names.yxmc

Import Multiple Schema XLSB.yxmc

surajmthomas

To be more specific My Configuration Pane for the Input tool looks like this. Since this is a monthly job run, the below files are available for download from the system at the end of each month.

The file name for April is -- "ID CREATED LAST MONTH Apr.xlsb" - can be downloaded only on April 30th from the system

April Configuration -- Notice the Sheet Name is '939970' - This runs fine

The file name for May is -- "ID CREATED LAST MONTH May.xlsb" - can be downloaded only on May 31st from the system

When the May files are downloaded, I go to the same path and replace the April file with the May File and try to Run Alteryx.

Notice the May File Sheet Name is '994590' which is different from April Sheet Name '939970'. This has changed because I have manually pointed the path to the right file and selected the right sheet

Therefore, by just merely replacing the April file with the May File in my local folder and running the workflow is going to throw me an error as the Sheet Name is different. Everytime it requires my manual intervention to change the path and select the right sheet name in the Input Configuration of the Input tool month on month for the workflow to run with the refreshed data.

Is there a way where Alteryx reads the input without bothering about the sheet name. This way, I just have to replace the files in the local folder on my computer month on month and click Run in the Alteryx window.

Emil_Kos

Hi @surajmthomas,

I believe I was confused by your table and I was thinking you have a problem with headers. Can you check @mceleavey post I think he got the answer that you need.

surajmthomas

I get a fresh xlbs file every month with refreshed raw data. The structure of the xlbs file is fixed and does not change month on month. The workflow that I built, has to accept the latest file and run. There is only one sheet in the file. But the sheet name keeps changing month on month. In the April file, if the sheet name is "12345", the May file will have its sheet name as "67890".

I want to default the Sheet Name to "Sheet1" before the input tool can read the raw data and run the workflow.

By this way, I can just replace the April Files with the May Files in my local folder and hit RUN in Alteryx.

surajmthomas

Emil_Kos Your first solution is a good case for columns which have values of mixed data type (Numeric and String). For example out of 10000 records, 9950 are Numeric and 50 of them are String. Since most of the rows are Numeric, Alteryx identifies the column as Numeric Data Type. By the solution you provided, you can force the column headers to be the first line of data and by default Alteryx would assign F1,F2 ....... Fn as column names and the data type would be defaulted to String. Once this is brought into the workflow as string you can use the Dynamic rename and Formula tools to change the data type to suit your workflow.

surajmthomas

mceleavey This is great but, I am unable to achieve what I am looking for.

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335