Import worksheets (different schema) and sync/rename columns

Hi:

I need some assistance with importing 3 worksheets (within same XLSX file) where the structure (schema) is slightly different across each worksheet.

I've searched across this forum and did find some existing solutions which *partially* helped me to import the worksheets.

However, ultimately, I am not (yet) able to output the all data into the desired format (see attached JPG incl. "desired" and "current" output).

Subfolder "SourceFiles" incl. both a) source data and b) lookup table for renaming/sync. the columns.

I'd welcome any feedback which would output the six (6) records across 3 columns (Date, Deposit, AccountID).

Thank you in advance,

Tom

Import Different Schema.zip

Dynamic Processing

Accepted answers

binu_acs

@TomBock Added find and replace tool in your workflow solved the issue

Import Different Schema.yxzp

TomBock

@binu_acs -- thank you for the prompt response. I appreciate it.

Once I downloaded the version and placed the source file into the subfolder AND then ran it, I only generate a single-record output. See attached.

What am I missing?

All comments

binu_acs

@TomBock Added find and replace tool in your workflow solved the issue

Import Different Schema.yxzp

TomBock

@binu_acs -- thank you for the prompt response. I appreciate it.

Once I downloaded the version and placed the source file into the subfolder AND then ran it, I only generate a single-record output. See attached.

What am I missing?

binu_acs

@TomBock the RecordID field should be selected in the cross tab tool, can you check?

TomBock

@binu_acs -- that did the trick. It wasn't checked... but now it's outputting the data as expected. Many thanks!!

Cheers,

Tom

TomBock

@binu_acs -- sorry, one more follow up question.

In "re-review" of your solution, could you please provide some info as to what exactly the logic for the Find/Replace tool is. I would I never thought to us it in this case. Just trying to learn from this challenge. Thank you.

binu_acs

@TomBock Find and Replace tool works very similarly to Excel Vlookup function. In your use case, the Name field will be looking against your look-up file and if find a match in the BEFORE field then the Name field will get replaced with the AFTER field in the lookup table

TomBock

@binu_acs -- great -- your explanation helps out a great deal. Again, thank you for the assistance w/ solving the challenge most efficiently.

Cheers,

Tom

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703