Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Accessibility review: alt text added



Excerpt


Steps

  1. Go to https://www.geneseo.edu/is/spreadsheet-merge-utility/Geneseo's Merge Tabular Data page.
  2. Insert excel spreadsheet data into the two text areas labelled "Dataset 1" and "Dataset 2".
    1. This can be done through either copying and pasting data directly from an excel spreadsheet, or dragging and dropping an Excel csv or xlsx file into the desired text area.
      1. Note that currently only csv and xlsx files are accepted via drag and drop.
    2. Note that both datasets must have column headers, with at least one column header that is identical (case-insensitive), for the program to function properly.              Dataset 1 and 2 fieldsImage Modified
  3. Determine the delimiter used in the datasets, either tab for regular excel files (Most common and default value) or comma for comma-separated values (CSV).
                                                            Delimiter dropdown menuImage Modified
    1. Note that this may be determined either before or after placing the data in the dataset text areas.
    2. Both datasets must have the same delimiter formatting for the program to work.
    3. If the Merging column(s) field isn't displaying the column headers properly, try changing the delimiter. If this doesn't fix the problem, make sure both datasets are formatted the same. If the problem persists, please contact CIT.
  4. This will then populate the Merging columns field, which allows you to select the column or columns that should be used as a basis to join the two spreadsheets. 
                                                           Merging columns fieldImage Modified
    1. Once data is entered and the program determines they have a column header that is the same, it will populate this field to look similar to this. There may be more than one column the datasets share.
                                                   Merging columns field with gnum enteredImage Modified
    2. The column(s) will be highlighted when they are selected.
                                                   Merging columns field with gnum entered and highlightedImage Modified
      1. Hold the Crtl-button on Windows and the ⌘-key on MacOS while selecting to choose more than one column.
  5. Once the the column or columns have been selected, push the blue "Merge" button to initiate the merging. 
    1. Note that data may be reordered row-wise upon merging in order to align similar rows
      1. It will list all matches first, followed by unmatched values in the first set and then unmatched values in the second set.
                                                     Merge buttonImage Modified
  6. This will then populate the Results text area towards the bottom once the merge has completed.
    1. Note that after the merge has been completed, the program will insert a "_1" suffix to the columns from the first dataset and a "_2" suffix to the columns from the second dataset. This is to differentiate which data came from where after the merge, particularly in the case of identical column names that store different data.
      Results fieldImage Modified

  7. To retrieve the data from the Results text area, it may be copy and pasted into a spreadsheet, drag and dropped into a spreadsheet, or downloaded as a csv file via the blue "Download Results" button underneath the text area.
                                                               Download Results buttonImage Modified
    1. Note that the data within the Results text area can not be modified directly from within the page by the user. To modify this data, download or move it into a local file on your computer to make the desired changes.
    2. This result can be merged with a third dataset and so on, but this would require removing the "_1" or "_2" suffix from the desired merging column in the result dataset at the least.
  8. The "Reset Values" button must be pressed before another merge may be performed.
                                                                           reset values buttonImage Modified
    1. During this step, the checkboxes to "Preserve Dataset 1" or "Preserve Dataset 2" may be unchecked to clear its respective dataset from the text area. This can facilitate entering a new dataset in the area.
      1. Note that leaving the checkbox checked will keep the current dataset.
                                                 Preserve Dataset 1 and dataset 2 options checkedImage Modified
  9.  For example: 
    1. Paste the following data from an excel spreadsheet into Dataset 1 and Dataset 2 respectively
      1.           Dataset 1 

        GnumName
        G001Joe Smith
        G002Frank Love
        G003Jane Doe


                   Dataset 2


      2. This will yield a page looking like this: 
        dataset 1 and 2 fields containing information from tables in previous stepsImage Modified

    2. Then Select the merging column, in this case "Gnum"
                                             Merging columns field with gnum highlightedImage Modified
    3. Hit the "Merge" button and the Results field will be populated as follows:
      Results field showing merged information, with download results button at bottomImage Modified
      1. Once downloaded the table will appear as follows:

                                  Resulting Dataset

        Gnum_1Name_1GNum_2Email_2
        G001Joe SmithG001js1@geneseo.edu
        G003Jane DoeG003jd@geneseo.edu
        G002Frank Love



        G004as1@geneseo.edu


        1. Notice that the two records with the same Gnum from each dataset appear first in the result with their columns joined on the same row.

          1. Values in the datasets that have a match will always appear first in this manner, with the columns from the first dataset listed first, followed by the columns of the second dataset.
        2. Then the record from the first dataset that has no match in the second dataset is listed by itself - the columns from the second dataset are empty.
        3. Similarly, the record from the second dataset without a match in the first is alone with the columns from the first dataset empty.

...