Matching fields when importing multiple excel sheets.

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.

Any help with this would be greatly appreciated.

Thanks!

Input

Accepted answers

JPMurphy91

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!

All comments

DiganP

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

DanielG

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

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)

I've also attached the macro for your reference.

Thanks again for the help.

excel file load macro.yxmc

JPMurphy91

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!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703