Import values from multiple defined Named Range from multiple sheets

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-ranges-from-an-Excel-workbook/m-p/185301/highlight/true

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

ClientA.xlsx

Workflow

Accepted answers

grossal

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

2020-04-19-name ranges.yxzp

All comments

grossal

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

2020-04-19-name ranges.yxzp

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 : )

ReadSheet-WF.yxzp

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.

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15872

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703