Perform Joins or Minus Queries on 'Excel' Files

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.


joinXL

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()

Installation

joinXL is only on github as of August 22, 2016, but has been submitted to CRAN.

devtools::install_github("yvonneglanville/joinXL")

Usage

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

  1. Preview of source file-minus-target file
  2. Preview of target file-minus-source file
  3. Preview of source file-minus-target file + target file-minus-source file

Output in working directory 'sourcemMINUStarget.xlsx' 'targetMINUSsource.xlsx' 'rowsNOTduplicated.xlsx'

News

joinXL 1.0.0

  • Added a NEWS.md file to track changes to the package.

Reference manual

It appears you don't have a PDF plugin for this browser. You can click here to download the reference manual.

install.packages("joinXL")

1.0.1 by Yvonne Glanville, a year ago


http://github.com/yvonneglanville/joinXL


Report a bug at http://github.com/yvonneglanville/joinXL/issues


Browse source code at https://github.com/cran/joinXL


Authors: Yvonne Glanville [aut, cre]


Documentation:   PDF Manual  


GPL-3 license


Imports readxl, openxlsx, timeSeries, data.table, rChoiceDialogs, R.utils, rJava, graphics, Rcpp, grDevices, stats, timeDate

Suggests knitr, rmarkdown


See at CRAN