topic Re: Imort multiple sheets from file, Look for number beside string(vlookup) in Alteryx Designer Desktop Discussions

Imort multiple sheets from file, Look for number beside string(vlookup)

wonka1234 — Mon, 11 Jul 2022 13:45:31 GMT

Hi,

1) I am trying to import all sheets from file that start with "CHT1".

2) I am only wanting to extract values next to "Current Month Principal for Reinvestment". Almost like doing a vlookup in each CHT1 sheet.

For example, looking in each sheet for this, regardless of cell position.

How can I import these values for each CHT1 sheet?

Thanks.

Re: Imort multiple sheets from file, Look for number beside string(vlookup)

binu_acs — Mon, 11 Jul 2022 14:00:46 GMT

@wonka1234 One way of doing this

1. Update your input tool with the below configurations

2. Update your custom filter like below

3. Update formula tool

4. Update dynamic input tool like below

once all sheets in your output you can filter out the data you want

Re: Imort multiple sheets from file, Look for number beside string(vlookup)

wonka1234 — Mon, 11 Jul 2022 14:24:25 GMT

@binu_acs

1) For the dynamic input, do I still do <List of Sheet Names>

2) Is there a way to search all these sheets? all the sheets im reading in doesnt have the same structure :(

However they all have the small image I showed above. "Current Month Principal for Reinvestment" and 4 columns over. But could be in any cells!

Re: Imort multiple sheets from file, Look for number beside string(vlookup)

binu_acs — Mon, 11 Jul 2022 14:48:31 GMT

@wonka1234 If the schema is different you need to use the batch macro, attaching one for your reference

Re: Imort multiple sheets from file, Look for number beside string(vlookup)

wonka1234 — Mon, 11 Jul 2022 15:03:13 GMT

@binu_acs I dont have this version of alteryx, do you have a previous version?

I use 2021.2.1.35394

Re: Imort multiple sheets from file, Look for number beside string(vlookup)

binu_acs — Mon, 11 Jul 2022 15:09:29 GMT

@wonka1234 open the given .yxmd file into notepad or notepad++ then change the version to your version and save.

Re: Imort multiple sheets from file, Look for number beside string(vlookup)

wonka1234 — Mon, 11 Jul 2022 15:12:08 GMT

@binu_acs

cant seem to open above file in notepad without it looking like weird characters.

Re: Imort multiple sheets from file, Look for number beside string(vlookup)

binu_acs — Mon, 11 Jul 2022 15:18:09 GMT

@wonka1234 the attached file is .yxzp which is a zipped file, first you need to unzip this file by double clicking the file, once you unzipp the workflow and the macro will be opening in a seperate folder with the same name as the zip file after that update the .yxmd file as mentioned above.

I updated the version to 2021 and attached here. double click the file and open in the designer

Re: Imort multiple sheets from file, Look for number beside string(vlookup)

binu_acs — Mon, 11 Jul 2022 15:19:22 GMT

@wonka1234 the above file is not working then go through the below post and download the given files

The Ultimate Input Data Flowchart - Alteryx Community entrée de données eingabedaten

Re: Imort multiple sheets from file, Look for number beside string(vlookup)

wonka1234 — Tue, 12 Jul 2022 13:48:30 GMT

@binu_acs can you explain what the macro does?

Re: Imort multiple sheets from file, Look for number beside string(vlookup)

binu_acs — Tue, 12 Jul 2022 22:05:22 GMT

@wonka1234 the input tool only returns the sheet names from the given excel sheet and feeding to the macro, the macro just uses the full path and the sheet names and read each sheets and stacks to one another like a union tool. Since you have different schemas only the bactch macro is able to read those records. if you look at the below post you will get much idea on this

The Ultimate Input Data Flowchart - Alteryx Community entrée de données eingabedaten