Skip to main content

The Knowledgebase

Study Abroad: Data Import/Export Utilities

The Data Import/Export Utility is a powerful tool which allows administrators with sufficient permissions to export application, profile, itinerary, program, and course data. It also allows administrators to import those same types of data through an interface that inserts and updates the data in the system. 

The Import/Export Utility was designed with the following use cases in mind:  

  • Batch import of the basic elements of user profiles, the program catalog, home/foreign courses, and applications from legacy systems during the initial implementation of Terra Dotta software 
  • Export of program, profile, and application data for use in other systems for trending, analytic, and statistical information.

Important Note: As noted on the application itinerary template document linked below, the application itinerary import is intended for importing legacy itinerary information. The use of this feature is not intended for use with current applications as the import does not create itinerary start and end dates at the database level. The Application Finder search uses information in the database table to pull results when filtering on itinerary dates. 

This document will cover the following topics related to the Import/Export Utility:

  1. Access to the Maintenance > Data Import/Export Page
  2. How to Use Data Templates
  3. Importing Data
  4. Bulk Exporting Data
  5. Exporting Data from Search Results
  6. Getting Started with the Import/Export Utility

IMPORTANT PRELIMINARY STATEMENT:  
Data import and export functions require a great deal of caution because of the security and/or volatility of the data being handled. The features in this toolset provide enormous power to any end-user with access to use them. For that reason, great care must be taken to assign permissions for these utilities only to trusted and qualified users. Anyone intending to use these features must read this document carefully before proceeding. 

Imports: While the import functionality does have many safety mechanisms to prevent improper data from being imported into the database, the data import can result in very widespread data changes that are not the intended results and could necessitate a re-import or even recovery from a backup. Note that SaaS customers will not be able to roll back their changes using a database backup.   For this reason, it is always recommended to run a test import with a small sample of rows before importing a large file.

Exports: The exports generated by this tool are NOT restricted based on the user's permissions - including Data Access Objects. For example, if an administrative user has program data access object restrictions and is given permission for this tool, that administrative user will have access to all program and application data - not just to the program and application data assigned by her/his permissions. Access to the Data Import/Export Utility should be granted only to trusted and qualified administrative users.

 

1. Access to the Maintenance > Data Import/Export Page

The Data Import/Export Utility is found in the Maintenance section of the administrative tools, where privileged users can download data templates, perform data imports, and export records in bulk.

Since the Data Import/Export tool is a maintenance level permission, it can only be assigned to groups and users by an account’s superuser   As with all Maintenance permissions, Hosted/SaaS customers must open a support case to request the Data Import/Export Utility permission for specific administrators because your site does not have a superuser. Because of the powerful nature of this tool, the primary admin user must submit the request should other staff members require access to it.

 

2. Data Templates

The data templates are nothing more than empty Excel spreadsheets containing column headers to aid in the process of importing data. Each template is organized and formatted for importing a specific type of data. These data types include programs, program dates, applications, home/foreign courses, profiles, program itinerary records, and application itinerary records. 

Each column within each data template represents one or more data elements. By following the outline provided by the templates, in addition to structure provided by the template documents, you will be able to accurately import and export data to and from your site.

When creating a file for import, it is important to leave the column header names exactly as they were produced when the data template was generated, however, it is ok to delete columns that are not required for your import.  

To generate a template, go to Maintenance > Data Import/Export and click on the name of the desired template in the ‘Data Templates’ panel at the top of the page. An Excel file will open with the header row populated with column names. Use the links below to access specific and more detailed information/instructions about working with each type of data template:

 

3. Importing Data

The data import functionality of the tool uses the following interface for importing a tab-delimited text file into your database:

Data 4.png
 [Upload form used for importing and updating data]

To upload a file, click on the 'Browse...' button and select the file from your local drive. 

This file must be a tab-delimited text file with the .txt extension. 

NOTE FOR MAC USERS: If you have used Microsoft Excel to edit the file, you will need to save the file using the 'Windows Formatted Text' file format. Microsoft Excel for Mac OS X saves tab-delimited files in a way that cannot be parsed by the import utility, but the Windows Formatted Text format will be formatted with expected tab-delimiters and line breaks. 

After selecting the file to be uploaded, mark the radio button to indicate which type of data structure is being imported. 

Then, click on the 'Import/Update Data' button to upload the file and begin the import process. 

During the import process, if there are any difficulties in parsing the file received, warnings will be displayed to the administrator. These warnings and errors will provide information that can be used to correct the issues with the submitted file. It will identify records that could not be imported and the data that was problematic.
 
Warnings and errors do not necessarily mean that data was not imported into the database. The results report will indicate if there were any records that were either created or updated during the import:
 

import2.png
   [Upload results from import/update form.]


When running an import routine, it is important to remember that this function will perform one of two actions for each record it receives. It will either import the record into the database (create a new program/application/profile/course record), or it will update an existing record with the new information provided. 

When sending a file through this tool, it is not necessary to have all columns in the template present. However, for each template, there are some required fields and dependent fields, as explained in the template documents above. Note that the presence or absence of some fields determines whether the data is to be imported as a new record or as an update to an already existing record.

 

4. Bulk Exporting of Data

At the bottom of the Maintenance > Data Import/Export page is a section to perform a bulk export of your site's data – open the drop down list and select the type of data you’d like to export: 

Data 5.png
   [Bulk data export options]


Bulk exporting data will deliver a populated, tab-delimited file of all your site's current data for the option selected: 

  • Programs: All programs in your catalog with their configuration settings and their program parameter values (see "Finding Program ID#s" section below)
  • Program Dates: All program date information for programs/application cycles that have program date records (see "Finding Program ID#s" section below)
  • Applications: All applications in your account including user, applicant parameter, and questionnaire response data 
  • Home Courses: All home courses in your account 
  • Foreign Courses: All foreign courses in your account 
  • Profiles: All users in the account that have a non-Null profile status
  • Program Itinerary Records: All program itinerary records in your account
  • Application Itinerary Records: All application itinerary records in your account 

Please note that these can be extremely large files (especially the applications export) and may result in long delivery/downloading times. Please be patient while the bulk export files are generated.  For programs, applications, and profiles, it may be more feasible to use the ability to export from search results of a program/application/profile search instead of using the bulk export. This will result in a smaller file.

When performing a bulk export, it is important to remember that not all records and/or columns included in the export file are necessary when re-importing that data. It is possible to perform the bulk export, sort the results in a spreadsheet, remove unneeded records/columns, make edits, then re-import just the edited records. 

Note: The bulk exports are not to be treated as an exhaustive resource for backing up critical data.

 

5. Exporting Data from Search Results

For administrative users with access to the Data Import/Export utility, there are options available when running application, profile, and program searches that allow the search results to be exported into a tab delimited file that can be edited offline and re-imported. 

For application and profile searches, the search results page will have an option under the 'Options' drop-down menu called 'Full Export':

Data 1.png
  [Full Export selection from application/profile search results options menu.


After selecting this option, the administrative user will be taken to the Maintenance > Data Import/Export tool where there will be a panel at the top of the form indicating that submitted data was detected. The page will indicate the number of applications/profiles that were passed over to it and provide an 'Export Now' button that can be clicked to generate the tab-delimited file:

Data 2.png
  [Data detected for exporting]


When the export has completed, a notification will appear indicating this and the browser will send the export as a downloadable file.  This can be used to export only a portion of the applications on the site. This is recommended for sites as an alternative to the bulk export of all applications/profiles especially for live sites with a lot of applications/profiles in the system from many application cycles. 

Similar to this application export, there are also options for administrators with access to the Data Import/Export tool when running a program search from Program Admin > Search:
Data 3.png
  [Export checkboxes from program search results page]


After running a program search, there will be a list of checkboxes on the left side of the results screen. The admin can select the desired programs for the export and then click on the 'Export Selected' button at the bottom of the results page.  

Note: This option is not available from Program Admin > List All - this export can only be done from the results of a program search. If you need to export all programs, it can be done via the bulk export option.

Like with the application search export, the admin user will be brought to the Maintenance > Data Import/Export tool with a message at the top of the page indicating that the selected programs were detected (see Figure 5). The page will provide a button for initiating the data export. 

Note: This option does not provide the program date records for the selected programs. Program date records can only be obtained through the bulk export utility or through the general reporting functionality.

 

6. Getting Started with the Import/Export Utility

To get started with this utility, it is best to review the data template documents provided above and test it out on a small scale with only a few inconsequential records being exported/imported. That way, you can get used to the methods of how the data moves in and out of your system without using production data. If you have access to a staging/test server, it is recommended that you begin working with this tool on that server first to avoid any unintended changes in your application, program, or user records. 

There are two things that are necessary when working with exports from your site:

 

Maintaining the tab-delimited file format

While the initially exported file has an .xls file extension, it must be re-saved with a proper .txt file extension. The file cannot be edited and saved as an Excel document as this will change the data of the file, and it will not be able to be re-imported into your site unless re-saved in the proper file format. 

For Mac OS X users that are editing the file in Microsoft Excel, there is an option when saving for 'tabdelimited file', but this is not the option that should be used. Instead, the option 'Windows Text File' should be selected. 

If you are having difficulty importing a file, attempt to open the file using a plain-text editor to make sure that the data is appearing properly in that application with tabs between units of data. If the file doesn't open in a plain-text editor or is displaying as a bunch of unreadable characters, it is likely that the file has been re-saved in an improper format. 

 

Maintaining the data formatting of the export

It is important that data formats do not get unintentionally changed when working with the export files. This is a common occurrence when working with Excel - especially where dates and time stamps are used.

You may find that your records are not properly re-importing even though they look correct when viewed in Excel. This is because when the file was opened by Excel, the date/time data was translated to an Excel formatted date (which is not the same format used by TDS).

If you find that date fields are not re-importing, it may be necessary to use a different method of opening the file in your spreadsheet application.
For example, Mac OS X users who use Microsoft:Excel 2008 will not be able to double-click on the exported files as this auto-translates dates and numeric fields. Instead, a new spreadsheet must be opened and the export data brought into that spreadsheet via the File > Import ... option from the menu. During this import, all columns must be specified as 'text' columns. 

 

Scrubbed characters:

Another aspect of the export to be aware of is that certain characters are incompatible with the export/import utility due to their inability to be edited in most spreadsheets and text editors. These characters will not be exported and can result in unexpected changes when you re-import that data.

The export will not be able to export any control characters (like tabs). It is unlikely that these would actually be in any of the records being exported, but if you are working with columns that have had content pasted from other applications (like Microsoft Word or a plain-text editor), it is possible that your data has tabs embedded. 

When getting started with this utility, double-check the exported data with the data on the site to make sure it is consistent and not removing characters that had been used for formatting of the content.

 

Finding Program ID#s

Using the Data Import/Export tool for program information requires you to know the Program ID number.

Program ID#s are easiest to find by running the bulk export for 'Programs'. To run this report, go to Maintenance > Data Import/Export and scroll to the bottom of the page. Click on the Programs tab under Bulk Export of Data. This may take awhile to run if you have a lot of programs on your site. The first two columns in the resulting spreadsheet will be the program ID# and the program name.
 
These ID#s will not change over time so you can save this list for later reference if you want.
 
If you only need a few Program ID#s, you can find them individually by going to the program builder for each program in turn and clicking on the Brochure tab. The Program ID# can be seen as the string of numbers at the end of the URL for this page.