Bulk data upload via server: OrgVue Upload Utility

It is possible to script data to be pushed to OrgVue. This is a case of using HTTPS POST to push CSV data to a specific dataset. We have an upload tool to perform this, or you may wish to write your own integration to perform the HTTPS POST.

The functionality detailed in the main article is no longer supported and entirely superceded by the OrgVue Blender utility.  The OrgVue Blender utility is not generally distributed and is delivered only as part of an OrgVue Consultancy engagement. 

OrgVue Blender is a command line utility for Windows. Its purpose is to:

  • Retrieve and manipulate data from a wide variety of sources
  • Load the resultant data to OrgVue
  • Automate scheduled uploads from a Windows Server to OrgVue

Currently supported sources include:

  • CSV (Comma-separated variable) files
  • XLS/XLSX (Excel) files
  • SQLite databases
  • Online resources

Data may be transformed in a wide variety of ways including:

  • Built-in standard transforms
  • User-defined transforms
  • SQL queries
  • R (statistical computing) scripts

If you would like further information on the OrgVue Blender utility, please contact us by submitting your details here

 

You will require Admin rights on the dataset to run this utility.

An alternate, but subsidiary use of the OrgVue Upload Utility is to upload large amounts of data to the OrgVue server direct, which are impractically large to do via the browser.

Overview

This article details the usage of the OrgVue Upload Utility. The purpose of the Upload Utility is:

  • To load data to OrgVue from the commandline or via double click from a Windows Desktop / Laptop
  • To automate scheduled uploads from a Windows Server to OrgVue  

 
Pre-requisites:

  1. Data to be uploaded must be in CSV format*.
  2. The first row of data in the CSV file must contain the column headings
  3. The CSV file must be contiguous block of data (continuous rows and columns). The data should have no empty rows or columns, and no subheadings or sections.

 
* CSV (comma-separated values) format is a simple, plain text format for data. It is the most common format for data interchange between systems and all common databases/datasources can export to CSV format. For example, data in Excel can be saved in CSV format using Save As and choosing Save as type: CSV (Comma delimited).   

Accessing the Upload Utility

The latest version of the tool and this document can always be downloaded from:

https://orgvue.concentra.co.uk/content/upload.zip


Once downloaded, you must unzip the entire contents of this zip file to a folder from which to run the Upload Utility.  

 

Configuration

The upload tool contains a file called config.txt which should look something like this:

Please note that in the section accounts, {id} and {from} are placeholders for the application only and should not be completed manually.

{  
	"accounts": {
		"default": {
			"tenantId":"{Enter Tenant Id}",
			"baseUrl":"https://orgvue.concentra.co.uk",
			"itemEventsUrl":"/csv/{id}/events/{from}",
			"csvUrl":"/csv/{id}",
			"datasetsUrl":"/datasets",
			"datasetUrl":"/dataset/{id}"   
			}  
	}, 
	"transforms": {
		"default": [                
					{
						"operation": "loadCsvFile",
						"source":"{Enter csv filename}.csv",
						"targetRef": "dataset"
					},    
					{                     
						"operation": "postCsv",
						"account":"default", 
						"sourceRef": "dataset",
						"datasetName": "{Enter target dataset name}", 
						"key":"{Enter dataset key property}" 
					}
						
				]  
	} 
}

The first thing you will need to configure is the Tenant Id.  You will need to replace the tenantId text {Enter Tenant Id} with the Id for your tenant account. 

Please note, the tenant ID is not typically published, if you do not know your tenant ID you can interrogate OrgVue itself for this information see instructions here, otherwise  please contact the team at support@orgvue.com.

  
For a single file you will need to change the source {Enter csv filename}.csv to the name of the csv file you wish to upload, be sure to include the file extension which is most likely to be .csv, however you can use any file extension you wish. 

You will also need to change the field datasetName value {Enter target dataset name} to the name of the dataset in OrgVue you wish the csv content to merge with.  Finally, you will need to replace key {Enter dataset key property} with whichever property value in the csv file represents the merge key, or unique id value for the data. 

Note:

  • the script will not create the dataset for you as part of its execution, it must already exist
  • baseUrl must include the full https:// path i.e. either https://orgvue.concentra.co.uk or https://orgvuedemo.concentra.co.uk

 

 
For multiple files, there are three different cases

 
One off upload operation

If you do not plan to automate the process, then apply the configuration and run the upload tool for each csv/dataset combination.

Batch uploading operation

If you plan to automate the process for the simultaneous uploading of a collection of files then you can duplicate the two operation blocks within default as many times as needed, setting the appropriate values for the source, datasetName, and key fields.  For each block the targetRef, and sourceRef fields must be unique. For example:  

		"default": [                
					{                      
						"operation": "loadCsvFile",
						"source":"Employees.csv",
						"targetRef": "dataset1"  
					},    
					{                     
						"operation": "postCsv",
						"account":"default",
						"sourceRef": "dataset1",
						"datasetName": "Employees Dataset",
						"key":"Employee_Id" 
					},                
					{                      
						"operation": "loadCsvFile",
						"source":"Roles.csv", 
						"targetRef": "dataset2" 
					},    
					{                     
						"operation": "postCsv", 
						"account":"default",
						"sourceRef": "dataset2",
						"datasetName": "Roles",
						"key":"Name"
						}  
  ] 

Also note the use of the comma to separate each operation block.

 
Distinct uploading operation

If you plan to automate the uploading of several files, or file collections, which will get run independently, you can add new transform blocks.   You must give each block a unique name.  For example:

	"transforms": {
			"employees": [ 
						{                      
								"operation": "loadCsvFile", 
								"source":"Employees.csv",
								"targetRef": "dataset1"
						},    
						{                     
								"operation": "postCsv",
								"account":"default",
								"sourceRef": "dataset1",
								"datasetName": "Employees Dataset",
								"key":"Employee_Id"
						},   
			],   
			“roles”:[ 
						{                      
								"operation": "loadCsvFile",
								"source":"Roles.csv",
								"targetRef": "dataset1" 
						},    
						{                     
								"operation": "postCsv",
								"account":"default", 
								"sourceRef": "dataset1",
								"datasetName": "Roles",
								"key":"Name"                
						}  
			]    
			
	} 
} 

Also note the use of the comma to separate each operation block.

Usage

Step 1: Place the data you want to upload into the same folder as Upload.bat (the folder to which the zip file was extracted. If uploading data held in Excel, you will need to use Save As CSV (Comma delimited) to save the data in the correct format. You can place as many CSV files in this directory as you wish to upload.

 

    
Step 2: Ensure you have correctly set up the config.txt file as per section above.  


Step 3: Double click on upload.bat. A command window will open, and you will be prompted to input your username and password.  If you wish to run a transform block that is not the default transform block, you will need to run from the command line, upload.bat “Name Of transform”.  


      
Step 4: On completion, press any key to continue. The upload process will have completed and the console window will be closed.

Troubleshooting

A successful upload will be reported in the command dialogue along the following lines:

C:\OrgVueUtilities\OrgVueUploadTool>upload.bat
Login for account 'default': myaccount@myemaildomain.com
Password for account 'default': ***********
2016-03-04 12:20:22 INFO: Executing target default
2016-03-04 12:20:22 INFO: Loading csv file 'content_to_populate.csv'
2016-03-04 12:20:22 INFO: Posting csv dataset 'destination_dataset'
Data transformed successfully
Press any key to continue . . .

Within OrgVue, the Properties dialogue in the dataset should be updated with the column headings from the CSV file.

At first sight the dataset may appear blank as OrgVue defaults to 'Label by' Label

 

It is likely therefore you may have to adjust the Label by option to an alternate property using the appropriate dropdown to ensure the node labels display as you wish

Alternatively you might want to add a column Label and configure this in the CSV, but there is no requirement to do so.

If you want absolute confirmation of the records updated, you can do this via the audit function.

Advanced Options

Overwriting existing data, or uploading data to an empty dataset for the first time

The Upload Utility by default works in merge mode. That is, every upload performed is merged into the dataset and no deletes occur. If you expressly want to overwrite all existing data then you must change the operation from “postCsv” to “putCsv”.

		"operation": "putCsv", 
		"account":"default",
		"sourceRef": "dataset",
		"datasetName": 
		"Refreshed Dataset", 
		"key":"Name" 

    
Uploading data from a data source other than CSV

Data must first be exported to CSV format. upload.bat does not support any other format. A JSON API is available for programmatic access. Contact support@orgvue.com for more details.  


Avoiding having to provide your credentials every time you upload In order to automate uploads without requiring username and password either interactively or on the command line, you can use the write back (wb) switch which will save your credentials in a secure form in the file Config.txt

 

upload -wb yes

 


You can also include your credentials in the config.txt as plan text.  However the credentials are not encrypted so it is not recommended that you use this option. 

 

		"accounts": {   
				"default": {    
								"username":"myusername@company.com",
								"password":"mypassword",
								"tenantId":”MyTenantId",
								"baseUrl":"https://orgvue.concentra.co.uk",    "itemEventsUrl":"/csv/{id}/events/{from}",
								"csvUrl":"/csv/{id}",
								"datasetsUrl":"/datasets",
								"datasetUrl":"/dataset/{id}"
				}  
		} 

      
Merging multiple CSV files into a single dataset

It is possible to merge multiple csv files into one a single dataset, they can even merge on different key values.  Simply ensure that the appropriate postCsv operations have the same datasetName value, and the appropriate key value

	"default": [
				{                   
							"operation": "loadCsvFile",
							"source":"Employees.csv",
							"targetRef": "dataset1"
				},   
				{                    
							"operation": "postCsv",
							"account":"default",
							"sourceRef": "dataset1",
							"datasetName": "Employees Dataset",
							"key":"Employee_Id"
				},               
				{                    
							"operation": "loadCsvFile",
							"source":"RoleDetails.csv",
							“targetRef": "dataset2"
				},   
				{                    
							"operation": "postCsv", 
							"account":"default",
							"sourceRef": "dataset2",
							"datasetName": "Employees Dataset",
							"key":"Role" 
				}  
	] 

Using the Upload Utility behind a proxy

If your access to the web is through a proxy server, Upload.bat will try to detect this automatically. If you need to explicitly specify your proxy settings, this can be done with the Config.txt file. Just add the following proxy configuration into the account setting with the appropriate values for host, port with user and pass if required.   

	"accounts": { 
				"default": {
							"tenantId":”MyTenantId",
							"baseUrl":"https://orgvue.concentra.co.uk",
							"itemEventsUrl":"/csv/{id}/events/{from}",
							"csvUrl":"/csv/{id}",
							"datasetsUrl":"/datasets", 
							"datasetUrl":"/dataset/{id}",
							“proxy”: {
										“host”: “127.0.0.1”,
										“port”: “8888”,
										“auth”: {      
												“user”:”username”,
												“pass”:”password”
												}
									}   
							}  
				} 

           
Inserting historical data

When data is added, changed or removed in OrgVue, the date and time of these changes is recorded as well.  Using the upload tool you can override this date time stamp so that you can build up the history of a dataset.  Data should only be built up going forward in time.  You should not insert historical record changes earlier than the last change on that record, or you risk corrupting the record. There are two ways to override the event date time stamp.  The first is in the Config.txt file.  If you are doing a putCsv or postCsv operation, you can add the date under the key “date” as shown.

	"default": [ 
					{
						"operation": "loadCsvFile",
						"source":"Employees.csv",
						"targetRef": "dataset1"
					},  
					{                   
						"operation": "postCsv",
						"account":"default", 
						"sourceRef": "dataset1",
						"datasetName": "Employees Dataset",
						"key":"Employee_Id",
						"date":”2013-04-30"
					} 
				] 

 

  You can also set the date in the command line when running the upload using the argument –date followed by the date.  This will also override the “date” value if it has been set in the Config.txt. The date should always be in the format yyyy-MM-dd as shown in both examples.  

upload –date 2005-04-15 

 
Getting further help

Executing:

upload -h

will return a list of the all the command line switches that the Upload Utility supports. If you experience any difficulties using the Upload Utility please contact support@orgvue.com.

 

Have more questions? Submit a request

Comments