Yearly common data adjustments
After the delivery of data from the participating countries there must be done some common housekeeping to get the last data in place. This is done at the database data control panel
at http://spin2000.net/spinmy/main.php
This control panel is password protected. Use admin / admin to login and the choose the menu item Controls in the top line.
You can find all the database controls that you need in the list of controls. Most of these are straight forward and the more special controls are further described in articles on this site.
Yearly national data delivery
The yearly update from each national participant is mainly done by simply uploading some csv files with the new usedata. The upload is initiated from the upload operations panel
at http://spin2000.net/spinmy/main.php
This control panel is password protected. Contact admin(at)infoshare(dot)dk to get login information, if you cannot find them. The database operations panel gives you some definition options for your upload: You can choose any of the SPIN database tables for data maintenance – choose from the dropdown list in the field “Table”. Next choose the type of operation, you want to perform – for yearly delivery you would have a csv file with new data for the year/country combintion and you will chose Add:
- Replace: Replaces the entire database table with the content uploaded by you.
- Add: Add the linies in your csv file to the content of the database table
- Update: Updates the existing data by adding your uploaded content to the existing records.
- Restore: If for some reason you want to reverse to the data as they were before your upload.
The fast track to do an yearly update is to verify, that the column headings in your csv file is spelled the same way as it is shown on the screen when you select the table – including upper/lowercase – and the put a tick mark in the “Enable custom fields”. Remember this site is using English and expecting the decimalpoint to me a . (full stop) not a comma.
Generally you have two options for defining the contents of your csv file to the upload system: You can select the fields you wish to upload from the list of fields in the box Field Name – your csv file must contain the selected fields in the same order as shown in the box. Or you can “Enable custom fields” by the tickmark: In this case the upload system will take the first line of your csv file and interpret the content here as the names of fields. You must use same spelling as shown in the box “Field name”.
The File input button “Gennemse..” (Text is dependent on the language of your computer) is for finding the csv file on your computer.
Csv files can have slightly different syntax: If the separator between your field values is anything else than ; (semicolon) – typical , (comma) – you can enter this in the Separator field and if your text values are enclosed with a special character, typical ” (double quotation marks), you can enter this in the field Enclose value. Decimal point is always assumed to be . (full stop).
Finally, when you have everything in place, you click on submit. For large datafiles the upload can take some time. Your upload will start by making a backup copy of the database table for later restore if necessary.
If your yearly update also include an update of the substance names, you can do this exactly the same way. But, if for some reason your csv file contains both new names and existing names, this procedure will fail. You have to do it slightly different: Upload your names csv file to the table temp_spinname using the Replace function and then change to Database Controls and activate the controlfunction Copy values from temp_spinname to spinname replacing duplicates. This will both add new names of the same nametype and update the spelling of existing names.
Adding new substances: If you would like to be able to find new substances right away, you should also add the new substances to the spinstof table. You can do this by uploading a csv file with the new substances, but a faster shortcut to this is to choose the database control Add new spinstof from spinname.
And the you are all done. (Except for the yearly special updates, that isn’t done by each country).
Updating Preferred Name
Newly added substances will lack a preferred name. The preferred name should be found in the general list of names and added to the substance description. The preferred name will be the first found name in this type-priority: CAS, COM, PRD, PRN, FIN
The function for updating preferred names is part of the database operations her spin2000.eu/spinmy/main.php
Find substances with no name
When new use-data is added to the database, you might end up with some substances without any name. The database control functions can add substances with their pid / casnumber if substances found in the use data are missing in the substance table. Such records can be updated with a name from the substance name table – if it’s there. It might be missing, especially in use data from SE, as the data delivery from SE does not include a list of new (or all) names.
You can download a list of substances with no name by clicking on this link .
Updating code tables
Every codetable in the SPIN system can be updated with new values by anyone with the username / password protected access.
The update is initiated from the datacontrol panel at http://spin2000.eu/spinmy/main.php
Find missing CAS-names
Newly added substances will lack a name of type CAS. These added substances should be found and the casnumber list should be used by the casname partner to produce a list of supplemental casnames.
The database can tell us which substances need a casname in two ways: Either the simple way by finding all substances that doesn’t have a preferred name of type CAS: Download that list here.
Or somewhat more complicated by finding all substances in use-total and comparing this list with the list of cas-names in the names table. Download that list here.
This file can be sent to one or more partners for delivering ekstra names of CAS type and then the extra names can be imported in the database operations at spin2000.net/spinmy/main.php
Checking for Orphan Codes
If your uploaded data uses codes, that are not found in the codetables, you need to update the code tables. You have a database control, that can help to identify this problem: By running the “Finding orphans” database control, you get a csv file containing the records, that uses a code (or a PID), that’s not found in the code table (or the substance registry). Database control functions are here: spin2000.eu/spinmy/main.php
Updating number syntax
The SPIN substances are registered with CAS numbers, EC numbers and Index numbers. Each of these numbers are registered in the database with and without the format enhancing “-“. If the upload only contained the number in one of these formats, you have three database controls for calculating the other version. Each databse control will add any missing version of the number from the other, i.e. if the CAS number was uploaded in the format 50-00-0 and the format (PID) 50000 is missing, then this control will calculate and register it. And vice versa.
If you need to do a similar change in an Excel spreadsheet, you can remove any “-” by selecting the column and using “replace” to substitute “-” with “” (nothing).
If you need to add “-” to a PID in cell A2 in order to show the PID formatted as a cas-number you can use this formula:
=VENSTRE(A2;LÆNGDE(A2)-3)&”-“&MIDT(A2;LÆNGDE(A2)-2;2)&”-“&HØJRE(A2;1) (In a danish version of excel)
=left(A2;length(A2)-3)&”-“&middle(A2;length(A2)-2;2)&”-“&right(A2;1) (english version of Excel)
Link table for OECD
Yearly updates should be finalized with generating and delivery of a link table to OECD.
The last generated link table can be downloaded here:
SpinOECD
Procedure:
- Make sure all new substances are added to SPIN
- Update the OECD link table using the two database controls at http://spin2000.eu/spinmy (1: Update OECD table with new substances, 2: Generate link definitions for all substances)
- Download the csv table from the link above.
The downloaded csv table should be sent to: Valerie Frison with email: Valerie.FRISON@oecd.org
OECD has a general delivery document which includes a description of the SPIN database. It might be relevant to consider if earlier sent description needs to be updated. The OECD delivery document template can be found here: datatemplate-20160422
