Automation & integration - Data Loader

Fernando Montenegro
24 steps
2 minutes

Clearpointstrategy

Scribe

Start by logging into ClearPoint Strategy at [app.clearpointstrategy.com/#/home](http://app.clearpointstrategy.com/#/home)

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 watches your files for new changes.

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 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 on the **Plus icon** next to the user’s name whom you would like to have Data Loader Keys - You will be asked to confirm 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.

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 \[INSERT LINK\] 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 Lader - 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**