Multiple excel files, multiple sheets, different schema

[jdkatz1]

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!

[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.

[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/91...

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 · Accepted solution]

@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!

[dkungu]

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

[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

[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!!