Merging Data

Merging data is a powerful feature, but it risks overwriting your data inappropriately if used incorrectly. It is therefore strongly recommended that you read and understand this section before using the feature. Also, it is recommended that you back up your data (e.g. export it to file so that it can be re-imported) if the data cannot be easily recreated.

When you select a field for merging, you are specifying that the field in the file and the field in the database table contain a common ID, and the data for each row in the file will be used to update the corresponding row in the database (rather than adding it as a new row). Any fields that are specified as new fields will be added as usual, but any mapped fields will have their values in the database overwritten with any value found in the file.

Example: You have a SD file containing structures and an ID field, and a data file (e.g. a CSV file exported from Excel) containing additional data for those structures, and one of the fields in this file contains the ID of the structure. To merge this sort of data you would use the following procedure:

Import the SD file into a new JChem table as usual.
Start to import the CSV file into the same table (see Importing data for details).
When you get to Step 3, Field Details, map the two fields that contain the common ID by removing the field from the list of new fields using the Remove button (by default all file fields are added as new fields). The field will become enabled in the list of file fields and can be added back to the list of database fields.
Specify that this field is to be used as a merge field by selecting the field in the list of file fields and the field in the database that it will be merged with from the right hand list. With both fields selected click the 'Merge' button. Merge fields are signified by the double-headed arrow symbol.
Add all other fields that you require from the CSV file as new fields. If you specify any fields as mapped fields then the data in the file will overwrite the values already in the database.
Continue the import as usual.

Important points to remember about merging:

If an ID from the file is not found in the database then that entry will not be imported.
Data in your database can be irreversibly overwritten.
The order of the IDs is not important.
Only certain field types can be used for merging data (text and integer).
If the ID field of the database can be merged but not mapped.
If the database contains multiple rows with a given ID, then ALL will be updated. Think carefully that this is what you want.
Multiple merge fields can be specified, in which case all IDs must match.
The previous two features are not expected to be used commonly but do provide a very powerful capability to selectively update data. Use this with caution.

Current limitations on merging data:

When merging data the structure field cannot be mapped.
The structure field cannot be used as a merge field.
Merging may be slow if you have lots of data. Adding an index to the merged column before importing is recommended.