Input multiple sheets from multiple '.xls' excel files

[dataminerchris]

Hello all

I have submitted my query to the history of posts before posting myself and found many very cool looking solutions, but did not solve my issue.

My situation :

I have 27 identically configured excel files with each having three same configured sheets I want to input "in one go". I also want to be able to trace sheet name.

They are "unfortunately" .xls formats. The file names are like 'PE - 1.xls'. When I try to apply 'dynamic input' I get awkward string error messages (unable to see sheets - see below). When I wish to  select multiple sheets in the 'input data' I cannot select multiple sheets, only one at a time.

Is there anything I am basically doing wrong ?

Is it linked to the "-" symbol in file name ?

Thank you for your tips.

Chris

[afv2688]

Hello @dataminerchris ,

This is a workflow I did a while ago. It selects all the sheets from the specified xls. Tell me if you need some assistance :)

Cheers

[dataminerchris]

Hello afv2688

thanks for the prompt reponse. I will try these asap.

Being really new to Alteryx, I have not yet tried dynamic macros...so this is a good first test for me.

regards

Chris

[dataminerchris]

Hello again,

How should I use the three files you attached ?

I downloaded all three and opened them in Designer. I tried adapting various configurators to my situation (directories, file names). I  tried to parameter the union1 macro to my situation :  I get the error notification "the Microsoft Access engine cannot find the object 'list of sheet names' " and the routine goes no further.

So I am still stuck indeed.

Thanks for any further hint.

rgds

[afv2688]

Hello @dataminerchris ,

Are we talking about excel files or access database files? You said at first xls files and the error you give is from another software.

As you can see here. The workflow works perfectly for excel files. You have to only configure the directory tool (mine is currently set to take only < *.xlsx > files).

Cheers

[dataminerchris]

Thanks again for response.

the files are indeed all .xls

They were initially all named as follows : "PE - 1.xls" through to "PE - 27.xls". I renamed them all to remove the "-" and it helped a little.

They all contain three same name sheets : M1 through to M3

All sheets (27 x 3 = 81) are configured exactly the same : same # of columns and same column names

What I need to do is cleanse each sheet then stack them all on top of the other. I have solved the individual sheet cleansing operation in one sheet of one file, but now have to repeat for all the other sheets.

From reading various topics in forum history I understand this should be quite straightforward, especially having such similar files, but I still get stuck. I clearly do not master the art of action macros but will give it a further try.

When using 'input tool' or 'dynamic input tool' it seems I can never parameter a range of sheets to find : i can either get only one sheet or an error in reading if I try to use "PE*.xls"

rgds

[mceleavey]

Hi @dataminerchris ,

When using the directory tool with the dynamic input and the .xls files have multiple sheets, you need to amend the fullpath using a formula tool to append the sheet:

[fullpath]+"|Sheet1$"

This should then allow you to use this method.

M.

[dataminerchris]

Hello

thanks for this further tip. I can use it to add sheet name field in my table.

I am still struggling to import the separate sheets of a full file.

rgds

[mceleavey]

Ah, ok, you need to use an import tool and set it to "Import only the list of sheet names":

Then you can create fullpaths with all the sheet names. You may need to then use the macro to cycle through them all

[afv2688 · Accepted solution]

Hello @dataminerchris ,

I have seen now the problem. You have to do a litte modification on the workflow. On the macro 'multidynamic_union_1 you have to change and erase the action tool on the 'list of sheet names' and change it for an text input with the name of your sheets: M1, M2, M3.

That is all,

Cheers