Importing SDF from ChemOffice

Greetings,





I have two SDFiles I've exported from our chem office server. One of them is a nested SDFile where there are multiple entries for each structure (structures are unique) and the other one is the flat version of the data where there are multiple structures, but only one set of results per entry.





I'm trying to import into the database using jcman, and it the flat file seems to be ok. I have a problem if I try to add experimental results There are about 15 fields, corresponding to different experiments, so one entry could have data for one of the experiments, and no data for the other one.





I've set up the fields in the table creation as "Fieldname double NULL", etc. Since I can have nulls. The problem is that when it tries to import, I get (please excuse the typos, as I'm typing between computers )





java.lang.NumberFormatException empty String


at java.lang.FloatingDecimal.readJavaFormatString(floatingDecumal.java983)


at java.lang.Double.valueof(doble.java202)


at java.lang.double.<init>(Double.java277)


at chemaxon.jchem.db.sq.TypeConverter.convert(TypeConverter.java70)


at chemaxon.jchem.db.FieldInfo.setValue(FieldInfo.java163)


at chemaxon.jchem.db.FileToSQLHandler.setValues(FieltoSQLHandler.java147)


at chemaxon.jchem.db.FileToSQLHandler.ImportFile(FieltoSQLHandler.java261)


at chemaxon.jchem.gui.Importer.run(Importer.java136)


at java.lang.Thread.run(Thread.java136)





for almost every molecule (Some make it through, only those that have no empty values for all the fields)





Thanks!





Art

Hi,





Please let me know the following:





1. The JChem version you are using


2. The type and version of your database





It would be also useful if you could send some sample nested SDFiles to support _at_ chemaxon.com.





Szilard

Hi Art,





Thank you for sending me a sample of the file.





There have been several problems with the file:





1. The format of the data fields in an SDFile should be the following:





-Data header


-Data


-Blank line





This was not always the case in your file, I have manually corrected it.





2. Fields with NULL value should not be listed at all. Two bank lines after the data header mean an empty string (which has a different meaning than NULL).


Of course an empty string cannot be converted to a number, that's why you got the error message.





After also correcting these manually (deleting the empty data fields), the SDFile imports fine into JChem.





Please see MDL's ctfile.pdf for details:





http://www.mdlchime.com/downloads/public/ctfile/ctfile.jsp





If you have lot of these faulty molecules, probably you will need some script or program to correct these errors in them.





Szilard

Szilard wrote:

Hi Art, 1. The format of the data fields in an SDFile should be the following: -Data header -Data -Blank line This was not always the case in your file, I have manually corrected it. 2. Fields with NULL value should not be listed at all. Two bank lines after the data header mean an empty string (which has a different meaning than NULL). Of course an empty string cannot be converted to a number, that's why you got the error message. After also correcting these manually (deleting the empty data fields), the SDFile imports fine into JChem. If you have lot of these faulty molecules, probably you will need some script or program to correct these errors in them.

Hmm... thanks for the answer. This file is automatically generated from Chemoffice enterprise tools. I guess I'll have to fix it myself (most of the molecules have this problem, >18K)





Art

Fixing the file did the trick... now to try and convince Cambridgesoft to fix their format :)

Or just forget about Cambridgesoft, and use ChemAxon tools ;)





Let me know if ChemOffice has some important features that cannot be achieved with our tools. We are open to suggestions.





Szilard

Hi,





I suppose you have written some script or program code to correct these files.





Could you possibly share it with us (if it is not a problem for you), so other ChemOffice users can also use it ?





You may post it here as an attachment, or to the contribution area:





http://www.chemaxon.hu/forum/forum10.html





(please attach it as file(s))





He have also decided to modify our code, so we will accept files with one or both of these errors in the future.





Szilard

Hi,





We have made modifications to our code.


From the next major release (3.1) JChem will support files like these.





Best regards,





Szilard