The ClearPoint Data Loader is a great tool for bulk uploading quantitative and qualitative data from Excel, CSV, or SQL queries to all elements in ClearPoint. The Data Loader is available at all plan levels with certain restrictions.
Data Loader capabilities by plan level
Basic Plan: allows for the creation of new elements and Scorecards.
Professional Plan: allows for the above, as well as updating Measure series data, and running on a schedule.
Enterprise Plan: allows for all of the above, as well as qualitative field updates, and monitors your files for any updates.
Installing the ClearPoint Data Loader
From the Control Panel click on Automation
Select Data Loader under Data Integrations
Click the Download Now button
Choose the folder in which to install ClearPoint Data Loader.
By default, the program will be installed in the Program Files on your computer.
Once installed, click Finish and the Data Loader will open and be ready for use.
Logging into the ClearPoint Data Loader
Managing Data Loader Keys
The following steps will help you get the Data Loader API keys.
From the Control Panel click on System Settings
Click on System Setup
Select Data Loader under Standard Features
On the Data Loader API Keys tab, click the Plus icon next to the user's name for whom you would like to create Data Loader Keys.
You will be asked to confirm that you wish to grant access to this user to perform updates and changes to your ClearPoint account using the Data Loader.
Once you have added the API Keys you will have three options:
View API Key will allow you to see the Access Key and Secret Key
Edit API Key will allow you to modify the expiration date
The keys will automatically be set to expire after a year
Delete API Key, this will delete the Data Loader keys so that the user can no longer log into the Data Loader.
Log in to the Data Loader using Advanced Mode
When you download the Data Loader and launch the software you will be prompted to log in using your ClearPoint credentials, we recommend switching to Advanced Mode using the dropdown menu and logging using your API Access Key and API Secret Key.
Important: To ensure the safety of your information, it's strongly recommended that only the users working with the Data Loader have access to API Keys, and that they are not shared with anyone outside of your organization, by keeping this information secured, you can prevent unauthorized access.
Selecting files to upload with the Data Loader
There are a few different types of files you can upload with the Data Loader. Below you can see how to select Excel, CSV, and SQL files to upload.
Uploading Excel and CSV files
One important thing to note before getting started, the Data Loader will watch folders on your computer which makes it possible for data to be automatically uploaded when files are saved.
Because of this, you must choose a folder for the Data Loader to watch that contains the files you want to upload. We suggest having a Data Loader folder on your Desktop that contains all files that will be run through the Data Loader.
Once you log into Data Loader
Click on Watch New Directory
This will open up a window that shows the folders on your computer
From the Window, choose the folder where the files live that you want to upload
Once you have selected the folder, click Select Folder
You will see the location of the folder and all of the files listed below
Uploading SQL files
Once you log into Data Loader
Click on Add Connection under Default Database Connections
The Data Loader offers several different SQL Dialects: My SQL, Microsoft SQL Server, Postgres, Maria DB, ODBC, OLE DB, and Oracle.
Please visit our Help Center article on Uploading SQL Files with the Data Loader for more information.
Two functions of the Data Loader
Updating existing elements in ClearPoint
Click Add Mappings for the Excel file you want to map
Each tab at the top of the window represents a sheet in your Excel file
Click on the sheet name and select the checkbox to include that sheet in the upload
The Header Row and First Data Row will automatically fill in
Check Multiple Scorecard if there is a Scorecard column in your Excel file
If there is not a Scorecard Column in your Excel file check Single Scorecard and Choose the Scorecard where the elements live that you are updating from the dropdown menu
After we have selected which Excel sheets to include in the upload, we will need to make the mappings on each sheet.
On the left hand menu, click into the first sheet tab
You will be asked to auto-map the columns.
Auto-mapping can save a lot of time so we would suggest clicking Yes
If you don’t auto-map or want to change the mapping, click Edit for each Column
From the first dropdown, you can select the element you want to map to
From the second dropdown, you can select the field you want to map to, which will include all default and custom fields in your account
By default, Copy down values to fill blank cells will be checked
If you have rows that apply to multiple rows in the following columns, like a Measure and its series, you want to make sure its checked
Note: If you make any changes to your Excel file while mapping in the Data Loader, you can click Refresh Worksheet to refresh the Data Loader Mappings
Once the file is mapped, you can click into the Import Options tab
You can configure import options and scheduling options on this tab
If you want to update data in ClearPoint, choose Enable updates only
To create new elements in ClearPoint, choose, Enable creation of new elements and edits of existing elements.
For Scheduling you are presented with three options
Run uploads manually from the Data Loader: you will perform the uploads manually
Automatically push changes when file changes: you will change the changes to be pushed to ClearPoint as soon as they are saved to the file.
Push changes on a Schedule: you can choose to run the package on a schedule so it updates each day, week, month, or year.
When you click on Preview changes, you will see all of the periods that are being mapped.
If you scroll down, you can see all of the Measures and each section has new data highlighted in yellow.
Once you have confirmed the new data, you can either click Save to save your mappings or Run to add the new elements to your ClearPoint account.
Clicking Save or Run will bring you back to the main page of the Data Loader
If you run a Package, you will see a toaster appear letting you know it is completed and how many elements were updated.
Adding new elements in ClearPoint
You can use the Data Loader to create new elements in ClearPoint. This feature is helpful for setting up many elements in your account quickly.
The steps for adding new elements in ClearPoint via the Data Loader are the same as the steps in the above section with a few differences that we will explain.
On the Import Options tab, you should make sure to select Enable creation of new elements and edits of existing elements. This will allow you to both create new elements in ClearPoint and update existing elements.
Using the Data Loader Command Line Interface (CLI)
The Data Loader Command Line Interface (CLI) is a powerful tool that allows you to run the imports to ClearPoint without needing to interact with the Data Loader graphical user interface (GUI).
When using the CLI, the background scheduling and directory monitoring features of the GUI are not leveraged. Instead of running in the background, the application runs one task and then exits. This allows it to be simply controlled by Windows Task Scheduler or other scheduling and automation tools.
A complete guide for using the CLI are included with the Data Loader, in the same folder where you have installed it.
Accessing and reviewing Revision History and Data Loader import
Select Settings from the Control Panel
Click on Revision History under Utilities
You will find an entry in the Revision History indicating Data Loader Import
