Combining Different Sheets with Different Schemas

[kagirbas]

Hello everyone,

Recently I had to work with pdf files. To work with them on alteryx, I use converter tool to convert multiple pdf files in to one and than I convert that ''one'' pdf file to excel worksheet to create an input for the Alteryx. PDF pages turns in to different sheets. ie pdf page 2 becomes Sheet2.

What I want to do is, find specific name and corresponding value of it in each spreadsheet and than filter them.

Problem is, since I converted these excel files from pdf every sheet has different layout. Column and row numbers are not matching and some sheets does not have the value I am searching. I try to use the workflow below;

-select input excel and list spreadsheet names

-use dynamic tool and link it to the excel file that I want to extract data

-rest is filtering

However, when I run the flow, I get  Sheet#" has a different number of fields than the 1st file in the set and will be skipped. I believe it is because column and row numbers are not matching and table layouts are different.

Is there way to find specific value in different type of excel sheets in Alteryx?

[BrandonB]

Take a look at this link: http://community.alteryx.com/t5/Alteryx-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/ta-p/20480

You will need to leverage a macro rather than the dynamic input in order to read in different schemas.

[DiganP]

@kagirbas There's an excel macro that lets you bring in excel files regardless of the schema. Take a look here. Let me know if this works. 

[kagirbas]

Hi Brandon,

Thank you for your answer. It doesn't seem to solve my problem. My documents are not in seperate excel files, they are in the same file but in different sheets. When I tried using it, macro only selected the first sheet and neglected the others.

[kagirbas]

Hello Digan,

It kind of read the other sheets but %99 of the data came out as null. Is there a way to optimize it?

Thank you for your answer

[MarqueeCrew · Accepted solution]

Try the CReW Wildcard xlsx input macro. Chaosreignswithin.com has the help you need. 

cheers,

mark

[kagirbas]

Hey Mark

Thank you for your reply, it totally solved my problem!