Creating a Merged Spreadsheet from Multiple Files

This procedure is part of the Excel Book. A newer version of the Book is available via the BDK (v2).

Overview

This procedure merges multiple spreadsheet files into one. You can rename columns, ignore specific columns, and choose how the files are joined.

Syntax

create a merged spreadsheet
  the files
  the join is "{join type}"
  the similar columns are "{original column name}", {new column name}"
  the ignored columns are "{ignored column}"

Inputs

1. the files: The spreadsheet files to be merged. Supported formats are .csv and Excel. (required)

2. type: The type of join to use when merging the files. (optional) Options:

   • outer (default)

   • inner

   • left

   • right

3. original column name, new column name: A pair of column names that should be considered the same across spreadsheets. The first in each pair is the original column name, and the second is the new column name all occurrences will be renamed to.You can specify multiple column pairs, separated by commas. Example: "Name", "Full Name", "Date", "Event Date". (required)

4. ignored columns: A list of columns to be excluded from the final merged spreadsheet. (optional)

Examples

1. Merging Two Files

the first file is "https://onedrive.live.com/file1.csv"
the second file is "https://s3.amazonaws.com/bucket-name/file2.xlsx"
create a merged spreadsheet with
    the files
    the join is "inner"
    the similar columns are "Name", "Full Name", "Date", "Event Date"
    the ignored columns are "ID", "Timestamp"

2. Merging Multiple Files

the spreadsheet files are "https://s3.amazonaws.com/bucket-name/sheet1.csv", "https://s3.amazonaws.com/bucket-name/sheet2.csv"
create a merged spreadsheet
    the spreadsheet files
    the similar columns are "Product ID", "ID"