Importing Data

How to import your data into Solarvista and preparing for this process.

Introduction

Solarvista includes an import facility that allows you to import data in bulk from “CSV” text files. The text files can be prepared in popular tools such as Microsoft® Excel™. This is especially useful for uploading bulk information quickly when you first setting up your Solarvista system. The option to import data in bulk is available for both Work Items (“time relevant & dynamic data”) and Data Sources (“static data”).

How Import Works

It is important to understand how data is structured and stored to successfully import data into it.

Solarvista is not built on a relational database; it is built using data files with fields that record associations with other data files. For this reason, data is always imported on a file by file basis. This means that you can always import every type of data at any time, but… you must also ensure the “link” data field is correct. This normally requires some planning ahead. You can refer to this help documentation for detail and definition of each file.

The Import facility reads a CSV formatted text file and allows you to “map” the columns to specific fields within a data file.

All files in Solarvista have an ID field (usually called “Reference”) which must be unique and is subject to restrictions in content. Specifically, these ID fields must be alphanumeric characters, or underscore, with no spaces.

Most files in Solarvista have a “Status” field that must be populated correctly to become visible. This is normally set to “Enabled” or “Active” (refer to specific data source file for this).

The steps to follow to succeed in importing data are as follows:

Understand – read this documentation to ensure you understand the purpose of each file and whether you need it or not.
Plan – plan which files you will need, which fields needs to be set and the specific content of each. You should plan to prepare files and import them in the most appropriate order.
Prepare – use an external tool to build your data (or import from your existing source).
Import – use the Import facility in the web portal to import the data into your Solarvista account.
Test – review the data within the web portal once imported.

If you import data which is not correct or in error, you can select all records and delete them to enable re-importing of corrected data.

Preparing Import Files

It recommended that you prepare your data to import in an application such as Microsoft® Excel™ or similar tool. An example of each file can be found in this sample data Excel file here. The “CSV” files should be exported as “Unicode”.

You should prepare your data, depending upon the type of field as follows:

ID field - these are usually named “Reference” and there is only ever one per file. These must be unique and contain alpha or numeric characters with no spaces. Underscores are allowed, however. It is recommended to set these to be either sequential e.g. “CONT001” or indicative “SMITH_01” depending what you feel comfortable with. You can of course use unique IDs from the original data if you prefer also. Once ID fields are set, they cannot be changed. ID fields are not usually displayed (other values are more useful of course).
Text fields – these can be populated with any character.
Date/Time fields – these must be in the format of YYYY/MM/DD HH:MM.
Number fields – these fields can either be whole numbers or decimal. If a decimal is attempted to be imported into a ‘whole number’ field, the record/row will be rejected.
True/False fields – normally displayed as a check box in applications, to set the checkbox, use the text value “TRUE”

Using the Import for Data Sources

Open any view for the type of data source that you wish to import.
Click on the Import button. The Import slide in will appear with the first page Select a File to allow a file to be selected for import.
Confirm that the file is a CSV file by checking the first check box, “The file is CSV format”.
If the file contains field names in the first row, tick the second box, “The first line contains column names”. By checking this box, the mapping stage will display the labels from the top row of the CSV file; if not, it will use “sampled data” from the CSV file.
Drag and drop the relevant CSV file onto the square pad; or click/tap on the pad to display a browser dialog window to select a file.
When you have selected a file, it will be scanned for key information such as file size, name, row & column counts. This is to allow you to initially check and confirm it is as expected.
Click the “Next” button and the page will show the CSV Mapping This page allows you to map columns from your CSV file to specific fields in the data source.
LIVE will attempt to automatically assign columns to fields based upon the column names (if you selected this in the previous page).
Check each field or click on the fields to select another mapped column. You can also select “—Empty—” if you do not wish to import any data into the field.
If any field has the “Has Multiple Values” property set, you can directly import multiple values from your CSV and choose the correct delimiter character. If you choose to use commas, then you must also use double quotes in your CSV file.
When you have completed all mappings (and double checked them), click on the ‘Next’ button. The import process will start, and the screen will show progress as rows successfully read.
If any rows fail, you will have the option to download a second CSV file that is updated with the reason for error at the end of each row. This will allow you to re-edit and potentially re-import only the rows that failed.

Using the Import for Work Items

Open any view for work items.
Click on the Import button. The Import slide in will appear with the first page Select a File to allow a file to be selected for import.
Confirm that the file is a CSV file by checking the first check box, “The file is CSV format”.
If the file contains field names in the first row, tick the second box, “The first line contains column names”. By checking this box, the mapping stage will display the labels from the top row of the CSV file; if not, it will use “sampled data” from the CSV file.
The import facility can only import into one Work Item type (“Work Item Template”) at any one time. You must confirm your data is only one type in the third check box, “The data is for one Work Item type only”.
Drag and drop the relevant CSV file onto the square pad; or click/tap on the pad to display a browser dialog window to select a file.
When you have selected a file, it will be scanned for key information such as file size, name, row & column counts. This is to allow you to initially check and confirm it is as expected.
Click the “Next” button and the page will show the Template Selection Select the Work Item type that you wish to import.
Click the “Next” button and the page will show the Mapping This page allows you to map columns from your CSV file to specific fields in the selected Work Item type.
LIVE will attempt to automatically assign columns to fields based upon the column names (if you selected this in the previous page).
In the first section General Mappings, you may choose to have the ID field (“Reference”) automatically generated by checking the “Auto Generated Reference” check box. If you wish to set the Reference by data in your import data, uncheck it and select a mapped column for the “Reference Column”.
If you wish to import Tags, choose a mapped column. You can also set this manually by selecting the “---Manual Value---” option and typing a manual value into the box that appears below (this will set the same value for all records created).
In the second section Assignment Mappings, you optionally set the starting Workflow Stage and optional User that each Work Item should be assigned to.
1. For “Starting Stage”:
  1. If you select “---Empty---", Solarvista will set each Work Item created to the first default starting workflow stage it finds.
  2. If you select “---Manual Value---”, Solarvista will allow you to select another workflow stage in the box below and will set this for every Work Item created.

If you select a mapped CSV column, Solarvista expects a valid workflow stage name to be in the column of the imported CSV file (if not valid, rows will be rejected).

For “Assigned To”:
1. If you select “---Empty---", Solarvista will create each Work Item and transition to the first default starting workflow stage.
2. If you select “---Manual Value---”, Solarvista will allow you to select another workflow stage in the box below and will set this for every Work Item created.

If you select a mapped column, Solarvista expects a valid workflow stage name to be in the column of the imported CSV file (if not valid, rows will be rejected).

The next sections are based upon the design of each Work Item type so are dependent upon the previous selections and any customisations.
1. For each field:
  1. If you select “---Empty---", Solarvista will not set a value.
  2. If you select “---Manual Value---”, Solarvista will allow you to type in a value for the field which will be written to every record created.

If you select a mapped column, Solarvista expects a valid workflow stage name to be in the column of the imported CSV file (if not valid, rows will be rejected).

If any field has the “Has Multiple Values” property set, you can directly import multiple values from your CSV and choose the correct delimiter character. If you choose to use commas, then you must also use double quotes in your CSV file.
When you have completed all mappings (and double checked them), click on the ‘Next’ button. The import process will start, and the screen will show progress as rows successfully read.
If any rows fail, you will have the option to download a second CSV file that is updated with the reason for error at the end of each row. This will allow you to re-edit and potentially re-import only the rows that failed.