Performs Joins and Minus Queries on 'Excel' Files fulljoinXL() Merges all rows of 2 'Excel' files based upon a common column in the files. innerjoinXL() Merges all rows from base file and join file when the join condition is met. leftjoinXL() Merges all rows from the base file, and all rows from the join file if the join condition is met. rightjoinXL() Merges all rows from the join file, and all rows from the base file if the join condition is met. minusXL() Performs 2 operations source-minus-target and target-minus-source If the files are identical all output files will be empty. Choose two 'Excel' files via a dialog box, and then follow prompts at the console to choose a base or source file and columns to merge or minus on.
This R package performs 'SQL' type joins and minus operations on 'Excel' files. 'SQL' is a programming language, which manipulates relational tables within a database. joinXL provides 5 functions, which perform relational joins and a minus operation on 'Excel' files living on your hard drive. innerjoinXL(), leftjoinXL(), rightjoinXL(), fulljoinXL(), and minusXL()
joinXL is only on github as of August 22, 2016, but has been submitted to CRAN.
The 'Excel' files must have a related key (column) for a join or minus operation to be performed. All join operations require one file to be designated as the base file because the base file row values take precedence over those of the join file.
Once the joinXL package is installed (described below), each join can be called at the console by typing the function name with parenthesis after the name and hitting enter. There is no need to put file information within the parenthesis because the function will open a dialog box. Choose 2 files, and then follow the console prompts to designate a base file, and choose the common column in the base and join files. A column is chosen from each file to accomadate inconsistent naming conventions. A joined file is then output to the working directory.
The minusXL() function allows the direct comparison of the rows of two tables. This function is called in the same manner as the joins. A dialog window for the input of two files opens, and then there are prompts at the console for the designation of a source file and and the common column in each file. Two operations are then performed on the tables.
Operation 1. source file-minus-target file outputs rows found in source file but missing from target Operation 2. target file-minus-source file outputs rows found in target file but missing from base
Output at the console
Output in working directory 'sourcemMINUStarget.xlsx' 'targetMINUSsource.xlsx' 'rowsNOTduplicated.xlsx'
NEWS.mdfile to track changes to the package.