There is a utility in Oneserve that allows data from Comma Separated Value (CSV) file to be uploaded into the application. Currently there are 3 types of data (sites, contacts and activities) supported but the process is the same for them all.
- Select the Cog icon to open the Admin menu
- Select CSV Import link from under the Tools menu section
- Select the import type – this is the type of data you wish to upload: Library Activity, Site, Contact or Resource Service
- You can also change the quote character and delimiter character on this page. The quote is the character that can be used to enclose data in the CSV file should that data include a comma (or whatever the delimiter character chosen is). For example if the Address 1 field needed to be 1,Street Name, and you have the default quote and delimiter set to “ and , then in the CSV file you would need to enter this field as “1,Street Name”.
Note: when editing uploaded data and you need to include commas, you do not need to include the quotes, this is only required for reading in from the CSV file.
- Click OK
Select File to upload
- This file needs to be on your computers file system. Click on the green Upload button.
The next page previews the data and allows you to change the column headers if required.
The rows of data are for preview only and will only show the first 10 rows of data. You cannot change any of this data at this stage.
The CSV file that is uploaded must have the column header names that correspond to the columns required for the type of data uploading, if using the import templates provided these columns should be correct.
If however they do not match you can change the headings to the data. To do this, simply click on the column header you wish to change and select the appropriate heading name. All the column headers must be unique before you continue. If you click on continue you will see an error message indicating that not all headers were set. To resolve this look for duplicate headers and change as required.
- Click Contuine to progress to the data import screen
This page shows any validation errors, which rows are Good To Go and those that have been included and excluded. By default it will show the Errors. The reason for this is to highlight any errors before continuing.
In the example above there are no errors and 50 rows good to go.
As there are no errors, select the Good To Go (and deselect all the others) and you will see all the rows of data. If there are more than 20 rows of data, it will be paginated.
You can now edit any of these cells. If you change any of the cells data and move the cursor out of the cell (into another one for example) the Import button will change to Validate. Any invalid columns will show as red, if you hover over the column it will show the error.
Clicking the Validate button will check all the rows and the button will change to Import(n) where n is the number of valid rows ready to import. If there are any invalid rows they will not be included in the import process.
After clicking on the Import button you will see a processing page that shows how many rows have been imported out of how many.
Once the import has completed you will see the page similar to the validate screen but it will show how many rows were imported.
If you want to exclude certain rows of data from the upload you can do that. On the Validate page you select a row by clicking on the far left column (with header #).
Once you have selected all the rows you wish to exclude click on the Selected records button and select Exclude. This will shows these rows with a Grey bar next to them.
If there are any errors as part of the import due to invalid data then you will see the rows shown with a Red icon next to them. If you click on the Errors option and deselect all the others you will see only the errors.
To see exactly what has caused the error hover the mouse over the red icon on the far left and a popup will indicate the cause.
Validation and Errors
There are two ways that the upload/import process attempts to check for errors in the data.
The first is Validation. When the data is uploaded it is checked for content type. All the columns for each upload type are pre-defined and have a data type assigned. This can be Text, Date, Number, True/False, and Money. If the data in that column does not match the expected type then it will show as a validation error. The cell will be red and if you hover over the cell a popup message will indicate the cause of the error. This checking is done prior to importing the data. Any rows with invalid data will not be imported. In addition to the data type it also checks that mandatory columns have some data.
The second type of check is Data Errors. These are performed as part of the import process. When the data is being imported into the system database there are various checks that are made to ensure the data is correct. For example if there is a Client column that the data needs to be associated with, then that client must exists in the database. If it doesn’t then an error will occur for that row and the import will continue. The result of this process is that all rows that can be will be imported and any errors will be highlighted at the end of the import process. The error checking will only indicate the first error found. If the row happens to contain multiple errors only the first one will be shown.
CSV Import History
- Select the Cog icon to open the Admin menu
- Select CSV Import History link from under the Tools menu section
- This allows you to see what imports have been run in the past. It lists all the previous imports in date order (newest first). You can click on any item and it will show the outcome of that import, including rows imported, errors, excluded rows etc.