Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Input Multiple Xls files at once.

khanShahbaz
5 - Atom

Hi All,

 

I receive around 120 Excel XLS files each month. I need to input all of those at once. There is only one sheet in all the excels, but names are different for all.

 

Please help me with this to Input the XLS files

6 REPLIES 6
ChrisTX
15 - Aurora

There are many historical discussions about importing multiple Excel files.  Please search the community and review some of the prior questions and answers.

 

Here is one:

 

The Ultimate Input Data Flowchart

Community > Designer > Designer Knowledge > The Ultimate Input Data Flowchart

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/t...

 

khanShahbaz
5 - Atom

Yes i did went through those but those are related xlsx extension. But the files which i have are of xls. Batch macro are not picking up those.

ChrisTX
15 - Aurora

Can you post your workflow that is not picking up xls files?

khanShahbaz
5 - Atom

Hi Chris

 

The below is the attached Workflow.

 

ChrisTX
15 - Aurora

If all of your XLS files are in the same folder, you can use the Input Data tool with a wildcard for the file name, instead of using the Directory tool.

 

If you looked at the post for The Ultimate Input Data Flowchart, which path did you follow in that flowchart?

 

For the problem you're trying to solve, each specific piece of information is important and will help you find the right posts in the forum.

  - are files XLS or XLSX?    because we can't easily get <List of Sheet Names> for XLS files

  - are all sheet names the same

  - do all sheets have the exact same layout (same column names)

  - etc

 

Is the sheet name the same in all XLS files?

If yes, you need a formula tool before calling the macro to add "E" (your sheet name) to the FullPath    (FullPath + "|||E")

 

If no, you will need to get every sheet name from every XLS file and use a formula tool to append the sheet name to the FullPath.  Search the forum for XLS sheet name.

 

You'll see posts with info like this:

   Table or Query = <List of Sheet Names>

   but as far as I know that only works with XLSX files.

 

The first thing you'll need is a full path including Sheet Names, if you have varying sheet names.

 

The app in this post may have a method to get sheet names from XLS files, not sure....

  https://community.alteryx.com/t5/Data-Sources/Error-opening-an-XLS-not-xlsx-file/td-p/47085/page/2

  see the comment about....a fantastic app that takes a directory path and converts all xls files to csv or .xlsx respectively.

  The post is located all the way at the bottom.

 

Chris

 

khanShahbaz
5 - Atom

Thank You 🙂 it was a great help.

Labels