Import values from multiple defined Named Range from multiple sheets

[SidUK]

Hi,

I am looking to input data into Alteryx from an Excel which has multiple defined named ranges on multiple sheets. I want to use the defined name range to pull in the data.

I understand how it works when it's not defined (e.g. A1) then its Full path + '|' + SheetName$A1:A1'.

Using the link below I am able to pull down the list of the defined named ranges in the Excel...just don't know how to use it to pull the associated data

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-do-I-get-a-list-of-all-named-range...

Attach is a test file with the following defined named range.

CS_CompanyName

CS_CompanyAddress

CD_NoOfEmployees

CD_AnnualRevenue

Note, the actual live data source has 20+ defined named ranges

Any help/guidance much appreciated

[grossal · Accepted solution]

Hi @SidUK,

I think it's probably possible to do this with the Dynamic Input, but I wasn't able to figure it out, so I made a simple macro.

Main Workflow: 

Macro:

Output:

Input 1:

Input 2:

You will simply need to adapted the second input and add all ranges you want.

Workflow and Macro attached. Let me know what you think.

Best 

Alex

[atcodedog05]

Hi @SidUK ,

A dynamic input tool since the datatype of Value field changes over sheets (Number/String). Hence it would create schema mismatch issue.

Instead if a batch macro is used to read each sheet at a time and datatype of value field is forced to String using select tool so that it prevents schema mismatch.

Select the file from which you want to read the sheets.

Set Sheet name column and select same file to set read from.

The batch macro iterates over the sheet names and appends the results and provides you the output in single table.

And generates the output like below

Since column can have only one datatype it needs to be considered as String.

Here a reference reading a weekly challenge on reading files.

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-180-Thousand-File-Challenge/td-p/461356

I hope this helps. If there is anything else i can help you with please reply back.

If it solves your use-case please like the reply and mark the reply as accept solution.

Thank you for sharing this scenario. It was a great exposure.

Cheers and Happy Analyzing : )

[SidUK]

@grossal - Does the job. Thank you!

@atcodedog05  - Thank you for your answer, however i needed to utilise the defined named range within the sheet.

[atcodedog05]

Hi @SidUK ,

Thats great no issues. A good opportunity to learn how @grossal approach the use-case.

Great job @grossal  : )

Cheers and Happy Analyzing : ) 

[Jamesrich]

Hi @grossal 

Is it possible for you to upload your workflow in a slightly older version of Alteryx? Unfortunately I can't open it 😞

Thank you! James

[grossal]

Hi @Jamesrich,

I have good news for you, you can easily do it yourself! Rightclick the workflow in the windows explorer and open it with notepad++ or another text-editor.

The first two lines should look very similar:

Just change the version to the version you are using and you should be good to go!

Best

Alex

[Jamesrich]

Hi @grossal 

Thanks very much - I managed to change the version and the workflow opens but the macro is not working.

Do you know why that might be? Thanks!

[grossal]

Hi @Jamesrich,

what version do you use? I can repackage it for you tonight 🙂

Best

Alex

[Jamesrich]

Thank you! @grossal 

Version 2019.3

Although the full version number says 2019.3.5.17947