Multiple excel files, multiple sheets, different schema

Hello,

I have been trying to get my workflow to work whereby i am importing different excels, same sheet in each separate excel, with different schema. I have already tried everything on Alteryx forum (see below) and still cannot get it to work. i asked questions on those forums but the chat is dead and no one will assist.

How To: Import Multiple Excel Sheets or a Specific... - Alteryx Community

Read in Multiple Excel Files, with Multiple Tabs that have Different Schemas (alteryx.com)

Issue: I have 18 excel files with different schema (i attached 4 but i cannot do it manually for each one with 18 files). All excels have the same tab called Lead which has a list of investments and information about them. i am trying to pull in all investments from each excel with the name and the info in columns (Investment, Industry (FS), Unobservable input description) into my workflow. After that i was going to compile that information by investment using the name as the identifier.

Hope someone can help!

Files.zip

Workflow

Accepted answers

grazitti_sapna

@jdkatz1, another way to do this.

Attaching the workflow. Please go through it once and let us know if it is working for you.

PS: Change the directory tool input.

Thanks!

jdkatz_workflow.yxmd

jdkatz_macro.yxmc

All comments

flying008

Hi,@jdkatz1

1- First, Maybe you can tell me that result of Total_Q4.csv is your want output ?

2- So many macro of gallery can give you help to process the N files.

录制_2022_08_09_08_40_10_511.gif

Total_Q4.csv

jdkatz1

This is exactly what i am looking for! can you please provide your workflow? i will let you know if i have any further questions!

flying008

Hi, @jdkatz1

Sorry, Because security strategy so i can't upload the wf, but you can use the macro to process as it.

https://community.alteryx.com/t5/Public-Community-Gallery/Import-Multiple-Schema-Excel-Files/ta-p/919226

1- Use macro get all sheet-names from your folder.

2- Filter sheet-name only "Lead".

3- Input all data as above sheets.

4- Filter no need field and all blank rows.

5- you got it !

grazitti_sapna

@jdkatz1, another way to do this.

Attaching the workflow. Please go through it once and let us know if it is working for you.

PS: Change the directory tool input.

Thanks!

jdkatz_workflow.yxmd

jdkatz_macro.yxmc

dkungu

Here is a macro that does what you want. Additional steps may be added to capture the Date and Company details.

Dir_Read_Sheet_name.yxzp

jdkatz1

This worked and i added it at the end of the workflow! Running into an issue now where my input data doesn't show up. see picture, both files and the same (different quarters) and no info comes in from the start. not sure what i did because it was working before

Capture12.PNG

jdkatz1

Everything works until the final step of combining everything. Everything coming in in to the join from both points, but no data is present for output J. I tried to combine them again using filters and still no data shows up.

Any assistance would be greatly appreciated!

So close!!

Capture.PNG

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703