Import & merge, unique IDs.

Hi,


I suppose this has been raised before, but it appears that when importing data you can only either add rows or merge rows.





That means that if you have a file containing a mixture of data you would like to merge and data you would like to add, you'd have to query your database first in order to see what's already in there and then split your data and import it in 2 files.





I would find very convenient to have an import mode that merges data when it can find the specified ID in the database and add it if the ID isn't already there...





For example let's say we have 2 compounds in the database with:


ID Purity


1 92


2 100





I have a file that contains updates for the purities of those 2 compounds AND some new compounds.


It'd be great if the new compounds could be added at the same time the exisitng compounds are updated.





Would that be possible?





Another thing: is it possible to specify a field to contain a unique value (like a primary key, but without actually being a primary key)?





Regards,





Geoffroy.

Quote:

I would find very convenient to have an import mode that merges data when it can find the specified ID in the database and add it if the ID isn't already there...

Interesting point. We envisaged the merge mode primarily for merging biological data (e.g. in a text file) with structure data (e.g from a SD file). In this case it makes no sense to add as new rows. You presumably have two separate structure files, where it would make more sense to do this.





Currently you could do this by loading both files into separate tables and using the Overlap analysis function to identfy what was in common. The results of the overlap analysis can be used to filter the second file so that it can be exported in 2 parts, ones in common and ones that are unique. Then each can be imported separately as you suggest.





We'll look at your suggestion to see whether it can easily be done.

Quote:

is it possible to specify a field to contain a unique value (like a primary key, but without actually being a primary key)?

We have this in our current developmen version. It allows you to create an unique index on a column, thus precventing duplicates. This version should be available in couple of weeks.

Hi. I came across the same issue. When updating a table with new structures which also contain new data for oldercompounds I could not figure out how to add the new structures and merge (or even overwrite) data for compounds that were already in the database. The overlap anaylsis/export suggestion seems to work, but it sure would be nice to do this in one shot with an SD import.

We have various improvements we want to make to import. We will look at this as part of that process.