ClearPoint Data Loader
The ClearPoint Data Loader allows user to bulk upload quantitative and qualitative data from Excel, CSV, or SQL queries to all elements in ClearPoint. Data is uploaded in a matter of seconds and can even be set to upload on set schedules or updated automatically when a file is updated. The Data Loader was built to eliminate as many hours of data entry as possible, so you don’t have to copy and paste information from one file to another.
In This Article
- Installing the Data Loader
- Logging into the Data Loader
- Selecting files to upload with the Data Loader
- Two functions of the Data Loader
- Using the Data Loader Command Line Interface (CLI)
The Data Loader is a great tool for bulk uploading information to your ClearPoint account. The Data Loader is available at all plan levels with certain restrictions. See Data Loader capabilities by plan level below:
- Basic Plan: allows for 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 watches your files for new changes.
If you need to download the Data Loader, please reach out to us at firstname.lastname@example.org and we’ll send you the installer! Click on the link to the installer for the Data Loader and open the installer from your browser. Choose the folder in which to install ClearPoint Data Loader. By default it will install the program to the Program Files on your computer. Once installed, click Finish and the Data Loader will open and be ready for use.
To log into the Data Loader, you will need to get Data Loader keys. To add Data Loader keys to a user, log into your ClearPoint account. Open System Settings in the control panel and click on Admin Options.
Click on the Data Loader tile. In the Data Loader API Keys tab, click on the plus icon next to the user’s name who you would like to have Data Loader keys. In the pop up window, click Add. This user now has Data Loader keys. The keys will automatically be set to expire after a year. To edit the expiration date, click on the pencil icon next to the users name and adjust the date.
To remove a user’s access to the Data Loader, click the trash icon next to the users name. This will delete the Data Loader keys so that the user can no longer log into the Data Loader.
Click on the key icon to view your Data Loader Keys. Copy and paste the Access Key and Secret Key to log into 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.
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 logged into the Data Loader, you will 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’ll see the location of the folder and all of the files listed below.
Once logged into the Data Loader, you will 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. For more detailed instructions on setting up a SQL connection, please see Uploading SQL files with the Data Loader 2.0.
Function One: Updating existing elements in ClearPoint. Upload quantitative and qualitative data to existing elements in ClearPoint. You can map to objectives, measures, and initiatives and upload data or text to any update field. Additionally, you can update measure series data. See how to do this below.
Function Two: Adding new elements in ClearPoint. You can add new scorecards with elements like categories, objectives, measures, and initiatives to ClearPoint. You can also populate fields like Owner and Description. See how to do this below.
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. This might need to be adjusted based on the Excel file. 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. Select Legacy format if you are using mappings from the Data Loader 1.0. See Transitioning to the New Data Loader for more information on the Legacy format.
After we’ve 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 here.
If you don’t auto-map or want to change the mapping, you can by clicking 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 this is checked.
Once the file is mapped, you can click into the Import Options tab. You can configure import options and scheduling options on this tab. To just 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 can choose to run uploads manually from the Data Loader. You can also choose to have changes to be pushed to ClearPoint as soon as they are saved to the file. Lastly, you can choose to run the package on a schedule so it updates each day, week, month, or year.
When you click into 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 complete and how many elements were updated.
You can also 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. The only difference is 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.
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 then exists. 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.