Skip to main content

Introduction to the Foundational File Set for GC Volunteer Management

Updated

GC Volunteer Management Implementation

 

 

Introduction to the Foundational File Set

The “Foundational File Set” is the group of .CSV files that are used to populate the initial data in GC Volunteer Management (VM). Additionally, these files are used as the template for future update files and will guide the creation of your school’s importer configuration that will ingest data automatically when you upload .CSV files to your SFTP. 

Your VM Configuration is created using the exact column headers you choose to send in your data during your implementation. Columns should not be added or removed nor should the column headers change in the file you drop in your SFTP after your VMS has been configured without previously coordinating with GiveCampus. If the files you are sending include any changes in format (you have added or removed columns, changed column headers, or changed file names), please email support@givecampus.com to notify the GiveCampus team and coordinate a change in your importer configuration. Once your VM Importer Configuration has been set during Onboarding, GiveCampus offers complimentary configuration updates once every 90 day period. More frequent configuration changes may be able to be accommodated based on the guidelines in your contract. Most schools will request no more than one configuration update per year and many will not need a configuration update after Onboarding completes.

Sample File

Once you've completed the 'Data Export Questionnaire' step in your VM Onboarding tab, you'll download a customized sample file to model your export on. It's important to match each column in this file with the export you're creating from your database. Extra columns or data that isn't anticipated by the VMS can lead to challenges creating your initial data import. Matching this sample file will ensure a smooth onboarding process. 

 

File Formatting Guidelines

The “File Set”:

We strongly recommend separating your data into several different files - referenced as your “File Set” or “Data Set”. Doing so ensures that your data is more easily managed and updated. Which files you will send will depend upon the information that you provide during the implementation process. Every school will send a Constituent File, which contains biographical information about your constituents. Additionally, you may send the following files:

  • Detailed Gift File
  • Gift Summary File (not necessary if sending Detailed Gift File)
  • Activities File
  • Degree File
  • Giving Societies File
  • Relatives File

It is suggested that each file be structured with one row per item - for example, the Constituent File will have one row per constituent while the Detailed Gift File will have one row per gift transaction (per constituent).

Once the File Set is established, you will need to send all files in your File Set when you send updates to your VM data. Only sending some of the files from your file set will prevent use of some data management functionalities once fully onboarded. 

File type and encoding:

Your file set should be sent to GiveCampus in .CSV format. We strongly recommend the use of UTF-8 formatting for the .CSV files that you send to GiveCampus. Using UTF-8 allows for the widest range of characters to be used, including accents common in constituent names. Using a file formatting other than UTF-8 can result in some unexpected characters when a character not supported by the file encoding is sent across. File encodings should be consistent. Sending a file with an encoding that is different from the encoding expected in the importer configuration will result in a failed import. 

File naming:

When creating your importer configuration during the implementation process, we will set an expected file name for each file that you send across. It is critical the file name and formats for files submitted via SFTP remain consistent once your configuration is written. If the file name and/or format is altered, the file will not be imported, our ingestion process will be disabled, and any subsequent files will not process. 

We strongly recommend adding the date to the file name(s). This helps both our team and your team keep an eye on the data history in case we need to look back at previous formats or content. If a date is not included in the file name, the files will be overwritten every day, erasing the history of data that you’ve sent to GiveCampus. While the date is not required, most partners find this to be helpful to keep a more detailed, clean history of any updates. 

Important note: the date in the file name must match the date on which the file is uploaded to your SFTP. Files uploaded with a different date stamp than the date they are uploaded will not import.

For example, if your standard name format for the constituent file is ‘SCHOOLNAME_GiveCampus_ConstFile_YYYYMMDD.csv’, then all constituent file refreshes must follow this format. A file that is uploaded on December 14, 2020, must have the file name ‘SCHOOLNAME_GiveCampus_ConstFile_20201214.csv’ in order for the importer to recognize it; a file uploaded on December 15, 2020 must have the file name ‘SCHOOLNAME_GiveCampus_ConstFile_20201215.csv’; and so on.  

 

Data Formatting Guidelines

Multiple Datapoints (activities, relatives, etc.)

For constituents with multiple entries for a specific datapoint (e.g. multiple activities, multiple relatives), we recommend the following:

  • When entering multiple datapoints in their respective files (e.g. multiple activities in the activities file), create one row per entry. This means that if a constituent has five activities, they should have five separate rows in the the activities file.
  • When entering multiple datapoints in the constituent file, create a separate column for each one (i.e. 'Activity 1', 'Activity 2'). This means that if a constituent has five activities, they will have entries in five separate activity columns.

Separating out these datapoints by column or by row ensures that you are able to use all of the VMS' sorting and filtering capabilities.

Commas and Quotation Marks

It is best practice (and in some cases, necessary) for every field -- excluding column headers -- to be surrounded by quotation marks (e.g. “Biology,Computer Science”). 

  • Data with quotation marks within a cell require double quotes around the entry.
  • Data with commas within a cell require either double quotes around the entry OR that all commas be replaced with semicolons. 

Double quoting is generally an export option for export files in commonly used databases. We recommend double quoting because this one simple step is a blanket solution that will help prevent both the comma-related and quotation-related issues during import, as double-quotes identify the boundaries within a cell that should be read by the importer. Please see below for more information and examples:

Quotation Marks:

If quotation marks are part of a data entry (e.g. an employer is named The “Inch-by-Inch” Foundation), then the quoted character(s) must be surrounded by an additional set of quotation marks, resulting in two pairs of double quotes around the character(s) so that the quoted entry can be imported properly. For example, the data entry would need to be as follows: “The ““Inch-by-Inch”” Foundation” . 

Commas: 

When a field includes a comma, the comma is considered a separator and counts data items as independent entries, which breaks the importer. To prevent this, you can either replace all commas with semicolons OR surround the content with double quotes. 

For example, in order for the VMS to import an entry such as soccer team,math league under your “Activities” column, the entry should be formatted either with double quotes as “soccer team,math league” OR delimited by semicolon as soccer team;math league. 

While double quotes are only required for those individual cells with commas and/or quotation marks, best practice is to double quote every cell throughout all files and delimit by semi-colon so no entries are missed. Please note: double quotes should not be included in the column headers.

Line Breaks

Line breaks within a cell will cause an import run failure for the row in which there is a line break. The remainder of the file will process, but data from the affected row(s) will not be ingested.  Please see below for an example:

The following entry includes line breaks and will fail to import:

Below is the same entry without line breaks, which will successfully import: 

Blank Space

The VM will remove leading and trailing blank spaces when displaying data within the Constituent Record, Snapshot, and Search pages. However, a leading or trailing blank space can affect deduplication logic. A blank space will be read as a character when deduplicating data. Adding a blank space before or after an external ID would recognize it as a new external ID and would therefore creating a new constituent record. 

How to Format Dates and Years

Dates: 

Below are the accepted formats for date fields (e.g. ‘Constituent_LastGiftDate’, ‘Gift_Date’, etc.). Please be sure to include 4-digit years as listed. You must select one format and remain consistent throughout your data:

  • MM/DD/YYYY
  • MM-DD-YYYY
  • YYYY/MM/DD
  • YYYY-MM-DD
  • YYYYMMDD

Class Years: The class year field can only have one entry and it must be an integer. The recommended class year format is a 4-digit year (e.g. “1995”). If you cannot send class years in a 4-digit year format, please reach out to us at vms@givecampus.com 

Sending Affiliation Data

Within VM there is an opportunity to send affiliation data (such as alumni, parent, etc.). It is strongly recommended that each affiliation be sent in a separate column (e.g. Constituent_Affiliation, Constituent_Affiliation2, Constituent_Affiliation3, etc.). If you plan on sending more than one Affiliation, you will also need to send across a column that associates a class year with each affiliation. For example:

If you are sending affiliation years, you should also send a preferred class year. Each constituent can only have one preferred class year, which will determine for which year the constituent’s gifts will count towards class-based reporting. Reporting is also available for each role to Administrators as well as for a Cohort to both Administrators and Volunteers. Affiliation years can be used to determine which constituents a volunteer should see in the Search constituents page as well as other settings with VM.

How to Format True/False Fields:

Fields such as ‘Constituent_Selectable’ and ‘Constituent_Deceased’ are True/False fields. The following options are accepted entries for “True” (these fields can be sent as upper case or lower case, so long as they remain consistent). All other data is imported as “False”.

  • TRUE
  • T
  • Y
  • YES
  • 1

 

Sending the Foundational File Set

You will send the Initial Foundational File Set to GiveCampus upon completion of Step 3 in the Onboarding Process - the Data Export Questionnaire. Following Step 3, you will have access to a Sample File Set that outlines the recommended files and columns that you send to GiveCampus based on the information and decisions that you made during the Onboarding experience.

Once you have generated your Initial Foundational File Set, you should upload the file set to the “private” folder of your SFTP. SFTP login credentials will be provided by GiveCampus during the Onboarding process. Until you have completed the Onboarding process, sending files does not trigger an import. When you drop files in the SFTP during the Onboarding process, please reach out to vms@givecampus.com to alert us to the new files. 

GiveCampus will then review the Foundational File Set. It is common for there to be a few questions upon reviewing the Foundational File Set. GiveCampus will share these questions with you and may ask for some revisions to the Foundational File Set. Once you land on a final version of the Foundational File Set, GiveCampus will use these files to build out your VM Importer Configuration and then ingest your data into your VMS. 

Data Updates

Once you’ve completed Onboarding, you will begin sending GiveCampus data updates. Your data update files should match the file name, structure, and format of your Foundational File Set. You will continue to send across all Constituents (and associated information) that you send in your Foundational File Set with updates to the provided information. 

Most schools send data updates daily. The VMS Importer will look for files hourly, at the 40th minute of the hour. However, depending on the size of the files, it may take several hours to ingest the data into your VM. 

When sending update files, if you remove certain pieces of data (namely Constituents, Gifts, Gift Summaries, Activities, Relatives, Degrees, or Gift Societies) they will persist in your VM. We persist this data to ensure data stability. If these data objects are “outdated”, meaning they were previously sent, but not included in your most recent update file set, that information will appear on your Data Management tab within your VM for you to review and delete. If data is re-added to your update file set, those data objects will get re-created within your VM, and no longer appear as “outdated” on your Data Management tab. 

 

Related articles