using dynamic input on excel files with different tab / worksheet names

[simon]

Here's my dilemma. I have 200 excel spreadsheets but they all have different/unique worksheet names (so not all Sheet1). I would like to create one output from all 200 sheets.

I used the directory tool to get fullpath which feeds into the dynmaic input tool. The DI tool works great but ONLY when worksheet name or tab is the same in all files (sheet1). Same is true when just using input tool and doing *.xlsx.

How can I get input / dynamic input tool to work when all excel files contain different worksheet names (LA, NY, ATL, etc). Has anyone found a workaround for this? (I am not about to drag 200 sheets to my canvas and merge them since this will become an app.)

Thanks,

Simon

[Niladri]

Hi Joe,

My ask is to load 10 excel sheets ( 7 xlsx and 3 xls) into an Oracle table. In each of these excels, there are three worksheets - A , B and C. I need to load B from each of them. The structure of B is exactly same.

Your solution asks us to use Dynamic XLSX. When I put the sheet names that I do not want to use, ( A and C ) , it is giving me this error - The field ' Sheet names' is not contained in the record.

Please help.

Niladri

[Maxholi]

Hi Joe,

Is there a way to adjust this macro to be able to read in xls files as well?

The xls connection doesn't allow List of Sheet Names.

Thank you,

Max 

[SkyW]

I am also having a problem with .xls files. I have a few workbooks that each contain a single sheet but the sheet name varies from month to month. Is there a way to bring in whatever sheet is available for this file type as well?

[Abhi_C]

Joe,

Your solution looks interesting. Would it be possible to provide a sample of your solution ?

Thanks !

[aoxomoxoa]

Hello

two things from an Alteryx newbie :

first, is it possible for the Dynamic Input tool to relay the tab name, not the file name - as a field in the output file ?

I saw a solution online that mimics this, but it only does so by adding a column to each tab's dataset first, with the individual tab name shown in every cell..

far from optimal, especially if the tab data is automatically generated and you want to avoid an intermediate step/formula

second, I have set up a dynamic input tool and get the schema error everyone talks about

It seems that either Alteryx incorrectly assigns a string type to V_WString when the same field on all the other tabs reflects V_String

Initially I though there were character(s) in that one column that Alteryx just does not like

however, that seems strange since everyone seems to get the same issue with a Dynamic Input 

any ideas ?

[jcardoso]

Hi, i was going through the Joe's macro and couldn't find where,

Question: it's possible with this macro to update the input Directory automatically, which means, make the input Directory dynamic?

[carmenprozesky]

In the new version of alterex (2018.2) the query editor option is no longer available. How do you import multiple sheets without this option. For my example I have certain sheet names that I want to import (using input data sheet name and a filter to select the sheets I want to import. But I am not sure how to use this as an input into dynamic input in order import the different sheets. 

[carmenprozesky]

Hi, 

Can someone please assit. 

In the new version of alterex (2018.2) the query editor option is no longer available. How do you import multiple sheets without this option. For my example, I have certain sheet names that I want to import (using input data sheet name and a filter to select the sheets I want to import. But I am not sure how to use this as an input into dynamic input in order import the different sheets. 

[itahir04]

Hi,

But that option doesn't work with .xls file :(

I am trying to get the tab name in my file which has xls extention. Dynamic input tool doesn't show <List of all Sheet Names> Tab.

Is there any simple solution to get Sheet name from .xls file?

Thanks,

Rana

[rachcamp7]

Hi Joe,

I am trying to combine two workbooks together using your macros, but I don't see the attached 2 macros you reference in your post. Am I missing something? I downloaded the Crew package but there seem to be a long list of macros inside the Macros folder. I tried using the XLSX input macro but it gives me an error Error: "Wildcard XLSX Input (1): Tool #6: Cannot find macro "CReW_WildcardXLSXInput.yxmcbatch"."

Can you help with this? I feel like maybe this is overkill for what I need, as it seems like it should be something really simple. I just have one sheet on one workbook and one sheet on another workbook and I just need it to combine them and save them as a new file.

Thanks for any and all help!