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

Need urgent help on dynamic selection of tow input files and compare the sheet

alt_tush
9 - Comet

Hello Friends,

 

I have two files File1.xlsx and File2.xlsx. Each file have 3 different sheets name as "Sheet1" , "Sheet2" and "Sheet3".

 

My requirement is I have to compare sheets columns between two files where column structure are same in both the files.

This should be work dynamically like workflow will first compare file1.sheet1 with file2.sheet1 , file1.sheet2 with file2.sheet2 accordingly

 

I use two input box for file1 and file2 and got below details using field info :-

 

File1_Sheet_Name    File1_Full_Path                               File2_Sheet_Name           File2_Full_Path

Sheet1                       C:\Users\File1.xlsx|||Sheet1            Sheet1                              C:\Users\File2.xlsx|||Sheet1
Sheet2                       C:\Users\File1.xlsx|||Sheet2            Sheet2                              C:\Users\File2.xlsx|||Sheet2
Sheet3                       C:\Users\File1.xlsx|||Sheet3            Sheet3                              C:\Users\File2.xlsx|||Sheet3

 

Now I want to open both the files for sheet1 and save the different entries in output file.

So above table there are 3 entries will execute one by on and save the output for each sheet.

 

Please help me for further logic.

 

Appreciate your help.

 

Thank you.

5 REPLIES 5
SamDesk
11 - Bolide

Hello @alt_tush,

 

Does every sheet have the same column structure? I.e. file1 sheet1 has columns AB, and C and so does file2 sheet1 and so does file1 sheet2... etc.


Sam :)

alt_tush
9 - Comet

Yes structure is same.

 

Just I want to find the different data from both the files at sheet level

 

If sheet1 has col1 col2 then sheet2 have col1 col2 too. So just want to find out mismatch row from both the files. same for sheet2 and sheet3

 

Thanks for your reply :)

 

DavidP
17 - Castor
17 - Castor

Ok, have a look at the attached.

 

I created 2 files with 3 sheets each. The first part of the workflow loads the filenames with a directory tool, then loads the list of sheet names for each and then uses a simple batch macro to load all the sheets from both files.

 

The second macro then compares sheet by sheet and writes the differences to a new file called differences.

 

You'll probably have to modify the unique tool and join tool in the find differences macro to work with the column names in your files. I didn't look into making this dynamic. Perhaps replacing these 2 tools with the "Only Unique" Crew Macro will make it more robust.

 

find diffs1.png

find diffs2.png

DavidP
17 - Castor
17 - Castor

Update: I've rewritten the seconds macro that finds the differences to no longer use a unique tool, so this should now work for different column names.

 

Let me know if you have any issues getting it to work.

 

find differences.png

alt_tush
9 - Comet

Thank you So Much David :)

 

It works. :)

 

 

Labels