Input multiple excel files

[sallyjeon124]

I built workflow that works for one excel file, but want to know if there is any way I can use this workflow for multiple excel files.

The files are in a folder and have different names (e.g. XYZ, ABC, ACO...). Each file has 3 tabs (sheets) and the sheet names are consistent (Tab A, Tab B, and Tab C). To run the workflow, I only need to use the Tab A (I do not need Tab B and Tab C).

I tried the wildcard input, but it didn't work since it combines the multiple sheets into one sheet.

The workflow I built looks like this.

The final output should look like this: (each file goes through the workflow and become one line - it works for one file if I input one file.)

For example,

Input ABC file -> go through the workflow -> create row 2 in the final output

Input XYZ file -> go through the workflow -> create row 3 in the final output

and so on for all the files in the folder.

[grossal]

Hi @sallyjeon124,

I think you are doing something wrong with the wildcards. I have just tested it, and it worked properly.

I have two files:

111_1.xlsx

111_2.xlsx

Both containing two sheets and I always want the first sheet.

Can you post post the settings of your input tool? Mine looks like this:

I'll also attach the super minimal sample (worfklow and files), so you can check it out yourself.

Best

Alex

[AbhilashR]

Hi @sallyjeon124, I generally use a combination of Directory tool and Macros to read multiple excel file, and their corresponding tabs into Alteryx. I have attached an example for you to look at and test out. Note, this works only with .xlsx file formats. We will have to tweak this solution if you are looking to read .xls files. 

[markcurry]

If the files contain consistent sheet names, you should be able to use the *.xlsx wildcard.  If the schema is different between files, you might get an 'different schema error', the usually happens if a field is empty in one file and not the next.  If that's the case you might need to use a Batch macro, see this post:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Import-Multiple-Excel-Sheets...

Another option might be the 'Wildcard XLSX Input' tool within the Crew macros, available here: http://www.chaosreignswithin.com/

Hope that helps.

[sallyjeon124]

@AbhilashR

It seems like most of my files are .xls format.

Is it possible to build one for .xls format or combination of .xlsx and .xls?

[grossal]

I have adapted my workflow to suite your use case.

You won't be able to use it for both, but you can easily achieve it with two Input, each with a wildcard and a union tool behind it.

Example attached. Let me know if this works for you.

Best

Alex

[AbhilashR]

@sallyjeon124 - I have broken down the workflow to process xlsx and xls files separately. Hope this helps.

[sallyjeon124]

@AbhilashR, thanks for your help, but it keeps giving me error message.

I built a sample model with sample excel files.

Workflow explanation:

1. Excel file name is not consistent (e.g. Google, Apple, Amazon...)

2. Excel file is xls format

3. Sheet name is consistent (e.g. there are two sheets in each excel file - Cash flow statement and Income, but only "cash flow statement" sheet will be used, but the order of tab is inconsistent)

4. The format of excel file is consistent (if I put individual excel file into the workflow model I built, I will give me the same result)

5. For now, the output only shows one company. For example, if I input amazon file, it shows row 2. But, what I want is putting multiple files all together (batch input from a folder) and generate 200 rows (with 200 excel files).

Will it be possible?

Current Output:

The output needed:

[AbhilashR · Accepted solution]

@sallyjeon124 - I modified your workflow and put together two approaches you can take to read multiple files at one go. This solution assumes all 200 of your files will have .xls file extension.

Approach 1 - reads all files in the folder using the .\.xls notation

Approach 2 - leverages Directory tool, to which I manually append your sheet name (in this case 'Cash Flow Statement$') and pass it through a macro which reads all files at one go.

I identify and segregate the records being read using the FileName that gets pulled into Alteryx by the Input tool. This option in the Input tool allows you to pull it in:

Hope this solution works out for you.

[sallyjeon124]

Thank you so much. it works!!!!