Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Matching fields when importing multiple excel sheets.

JPMurphy91
6 - Meteoroid

Hello,

 

I'm running a workflow that utilizes the Excel Load File Macro to pull multiple similar excel sheets into one.  These excel sheets are similar, but not exactly the same.  Some sheets have more fields than others.  The problem I'm having is that the fields aren't lining up and thus data is being stacked in the improper field.  The macro is set to auto-configure by name.  See below for example.

 

clipboard_image_0.png

 

Any help with this would be greatly appreciated.  

 

Thanks!

5 REPLIES 5
DiganP
Alteryx Alumni (Retired)

@JPMurphy91 you can create named range per 'sheet' and bring the data in accordingly in the input tool. 

Digan
Alteryx
DanielG
12 - Quasar

Hi,

 

 

 

Something that has "Auto Config by Name" set up in the union should be able to handle the samples you posted.  Though that is a really basic sample, to be honest. 

 

I dont know anything about the "Excel Load File Macro" (I looked on the Public Gallery but couldnt find anything with that name).  Was that something developed in-house?

 

If the macro is just joining two inputs together, then crack it open and take a look at the union tool in there to see how it is working.

 

 

Perhaps there is a setting that needs to be adjusted within the macro?  If the sheets are always going to differ, but will each individually remain stagnant you can manually configure the headers by changing the setting in the union.

 

If they will change each time this is run, the manual config will not be useful to you and the auto config by name will work assuming your cow counts will always be under "Cow" and not changed to "Bovine" or something like that...  🙂

JPMurphy91
6 - Meteoroid

Hi Dan,

 

Thank you for the response.  

 

I understand that the example I provided is extremely simple, but the actually data I'm using shouldn't be different.  Just finance related categories instead of animals, more fields, and 12 sheets instead of 2.  

 

I think the main cause of this problem may be that each sheet has about 5 rows of trash that I need to skip in order to get to the column headers that I want for each field.  Any idea how to work around this?  Below is a screenshot of the front end of the workflow where the data is being brought in.  (The blank blue circle is the macro and the formula tool is just the full file path of each sheet)

 

clipboard_image_0.png

 

 

I've also attached the macro for your reference.  

 

Thanks again for the help.

JPMurphy91
6 - Meteoroid

Hey Dan,

 

Please ignore my last reply.  I figured it out.  Edited the macro to start pulling from row 5, and then also added a dynamic rename tool in the macro.  

 

Thanks!

DanielG
12 - Quasar

Awesome!

 

Nice work.

 

Labels