Saturday 26 October 2013

4.EXCEL FULL OUTER JOIN OR CARTESIAN JOIN FOR EXCEL TABLES

Excel Full Outer Join or Cartesian Join for Excel Tables

How to get this Script

Please send Email with Subject "Full Outer Join"  to vijkid143@gmail.com for Zip File

Note: Customization can be done on Request

Problem
Generally Data from two tables can be joined in Databases using Join Statements. But to join the data from two tables present in Excel Sheets is a bit challenging. 

Solution
This VBScript will join the data from two different Excel Tables and give you the the FULL OUTER JOIN or CARTESIAN JOIN Result

How to Use it

1) Download the zip file (Please send Email with Subject "Full Outer Join"  to vijkid143@gmail.com for Zip File)
2) Unzip the Files
3) Copy the Data from your 1st Excel Table (Just leave the Column Names) and Paste it in File1.txt. Save the File1.txt and Close it. For Example i have copied the Data from EMP_TABLE.xls to File1.txt
4) Copy the Data from your 2nd Excel Table (Just leave the Column Names) and Paste it in File2.txt. Save the File2.txt and Close it. For Example i have copied the Data from DEP_TABLE.xls to File2.txt
5) Now Run the VBScript "EXCEL TABLES FULL OUTER JOIN.vbs"
6) In the Popup Box "Enter the Column Number to be Joined in Table2". Here enter the Table2 Column number which has to be Joined. In this Example we have EMPID present at Column 3 in DEPT_TABLE.xls. So Enter "3" and Click OK
7) In the Popup Box "Enter the Column Number to be Joined in Table1". Here enter the Table1 Column number which has to be Joined. In this Example we have EMPID present at Column 7 in EMP_TABLE.xls. So Enter "7" and Click OK
8) VBScripts Starts the Execution and Completes the Execution which you will see as Messages on the Screen.
9) Now Open the OUTPUT.TXT File and you will see the FULL OUTER JOIN or CARTESIAN JOIN Records of Excel Table1 and Excel Table2
10) Simply Copy the Data from OUTPUT.TXT back into your Excel Sheet to view the Data in Tabular format for easy understanding

Advantages
1) No Usage of Macros in Excel.
2) Output File shows Total Number of Records present in 2 Tables along with Total Number of displayed after Joining.


Visit My Other Blogs

http://datacompare.blogspot.in/
http://wordcount-vbscript.blogspot.com/

Solutions
1) Single Column Data Compare between two files
2.Multiple Columns Data Compare between two Files
3.Identify the Duplicate Values and Distinct Values in a Flat File
4.Extract Records with Multiple Columns by searching Full String or a Partial String on any Column in a Flat (Excel) File

Developed By: Vijay Uppara (Vijaya Bhasker M Uppara)

Please send your comments or Feedback to vijkid143@gmail.com



1 comment:

  1. This is the best and most convenient solution I've found online. The instruction is very precise. Excellent job Vijay and thank you very much for the help!

    ReplyDelete