New Sheet Name Every Month in the Source File

[surajmthomas]

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.

[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:

[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,

M.

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

[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.