User 247c00dc1d
27-03-2012 20:00:37
Hello!
Please help edit the script below in such a way that every next 60 hits were exported to a separate file (when you run the script, he asked the name of the file and where to save - accordingly if the database contains 135 hits, and after running the script and entering the file name "name" should appeared three files: "name_01", "name_02", "name_03" with 60, 60 and 15 hits in each.
import com.im.df.api.support.*
import com.im.df.api.ddl.*
import com.im.df.api.util.DIFUtilities
import com.im.df.api.util.DIFUtilities.*
import javax.swing.filechooser.*
import com.im.df.api.dml.*
import com.im.commons.progress.*
import chemaxon.formats.MolExporter
import javax.swing.*
import javax.swing.SwingUtilities
import chemaxon.struc.Molecule
import com.im.df.api.support.SelectionDescription
def FIELDS_FROM_PARENT = [ 'idnumber', 'smile'] // list of field names to export
def STRUCTURE_FIELD = 'Structure' // field name of the structure field
def FIELD_NAMES = [ 'idnumber' : 'idnumber', 'smile' : 'smile',] // rename some fields in the SD file output
// Prompt for save file location
//def SAVE_NAME = 'C:/tmp/export.sdf' // name of the file to create
def chooser = new JFileChooser()
if (chooser.showSaveDialog(null)==JFileChooser.APPROVE_OPTION) {
File fileName = chooser.getSelectedFile()
name = fileName.getCanonicalPath()
// Check to see if file has the correct extension
if (!name.endsWith('.sdf')){
SAVE_NAME = name + '.sdf'
} else {
SAVE_NAME = fileName.getCanonicalPath()
}
// See if file already exists
File existFile = new File(SAVE_NAME)
if (existFile.exists () ) {
def response = JOptionPane.showConfirmDialog (null, "$existFile exists \nOverwrite existing file?", "Confirm Overwrite", JOptionPane.OK_CANCEL_OPTION, JOptionPane.QUESTION_MESSAGE)
if (response == JOptionPane.CANCEL_OPTION) {
return}
}
}
// ------------probably no need to edit anything below here ---------------------------
// root entity
def parent = dataTree.rootVertex.entity
// ID field
def fldId = parent.idField
println "found ID field ${fldId.id}"
// mol field
def fldMol = parent.fields.items.find { it.name == STRUCTURE_FIELD }
println "found MOL field ${fldMol.id}"
// data fields from parent
def fieldsFromParent = [ ]
FIELDS_FROM_PARENT.each { name ->
def fld = parent.fields.items.find { it.name == name }
if (fld) {
fieldsFromParent << fld
println "Found parent field ${fld.id} for $name"
} else {
println "WARNING: field $name not found"
}
}
// ResultSet and VertexStates
def rs = parent.schema.dataProvider.getDefaultResultSet(dataTree, false, DFEnvironmentRO.DEV_NULL)
def parentVS = rs.getVertexState(dataTree.rootVertex)
def ids = parentVS.ids
println "Found $ids.size parent IDs to export"
// now read the data
def good = 0
def bad = 0
def exporter = new MolExporter(SAVE_NAME , 'sdf')
try {
ids.each { id ->
// stop if the script is terminated
if (env.getFeedback().isCancelled()) {
def msg = "Exporting data to file $SAVE_NAME interupted!"
println msg
throw new InterruptedException(msg)
}
try {
def data = parentVS.getData([id], DFEnvironmentRO.DEV_NULL)
// get the mol
def mol = data[id][fldMol.id]
// get the other fields
def values = [ : ]
fieldsFromParent.each {
values.put(it, data[id][it.id])
}
// println "Exporting ID $id"
def expMol
// work with a clone so we don't alter the original
if (!mol || !mol.native ) {
expMol = new Molecule()
} else {
expMol = mol.native.cloneMolecule()
}
values.each { k,v ->
if (v != null) {
def pName = (FIELD_NAMES[k.name] == null ? k.name : FIELD_NAMES[k.name])
expMol.setProperty(pName, v.toString())
}
}
exporter.write(expMol)
good++
} catch (Exception exc) {
println "EROROR Failed to load ID $id ${exc.toString()}"
bad++
}
}
} finally {
exporter.flush()
exporter.close()
}
println "Finished exporting data to file $SAVE_NAME "
println "good: $good bad: $bad"
Thanks!
Igor
ChemAxon 2bdd02d1e5
28-03-2012 11:55:43
Hello, I tried it according your description. Is it what you have expected?
Filip
import com.im.df.api.support.*
import com.im.df.api.ddl.*
import com.im.df.api.util.DIFUtilities
import com.im.df.api.util.DIFUtilities.*
import javax.swing.filechooser.*
import com.im.df.api.dml.*
import com.im.commons.progress.*
import chemaxon.formats.MolExporter
import javax.swing.*
import javax.swing.SwingUtilities
import chemaxon.struc.Molecule
import com.im.df.api.support.SelectionDescription
def FIELDS_FROM_PARENT = [ 'idnumber', 'smile'] // list of field names to export
def STRUCTURE_FIELD = 'Structure' // field name of the structure field
def FIELD_NAMES = [ 'idnumber' : 'idnumber', 'smile' : 'smile',] // rename some fields in the SD file output
// Prompt for save file location
//def SAVE_NAME = 'C:/tmp/export.sdf' // name of the file to create
def chooser = new JFileChooser()
if (chooser.showSaveDialog(null)==JFileChooser.APPROVE_OPTION) {
File fileName = chooser.getSelectedFile()
name = fileName.getCanonicalPath()
// Check to see if file has the correct extension
if (!name.endsWith('.sdf')){
SAVE_NAME = name + '.sdf'
} else {
SAVE_NAME = fileName.getCanonicalPath()
}
// See if file already exists
File existFile = new File(SAVE_NAME)
if (existFile.exists () ) {
def response = JOptionPane.showConfirmDialog (null, "$existFile exists \nOverwrite existing file?", "Confirm Overwrite", JOptionPane.OK_CANCEL_OPTION, JOptionPane.QUESTION_MESSAGE)
if (response == JOptionPane.CANCEL_OPTION) {
return}
}
}
// ------------probably no need to edit anything below here ---------------------------
// root entity
def parent = dataTree.rootVertex.entity
// ID field
def fldId = parent.idField
println "found ID field ${fldId.id}"
// mol field
def fldMol = parent.fields.items.find { it.name == STRUCTURE_FIELD }
println "found MOL field ${fldMol.id}"
// data fields from parent
def fieldsFromParent = [ ]
FIELDS_FROM_PARENT.each { name ->
def fld = parent.fields.items.find { it.name == name }
if (fld) {
fieldsFromParent << fld
println "Found parent field ${fld.id} for $name"
} else {
println "WARNING: field $name not found"
}
}
// ResultSet and VertexStates
def rs = parent.schema.dataProvider.getDefaultResultSet(dataTree, false, DFEnvironmentRO.DEV_NULL)
def parentVS = rs.getVertexState(dataTree.rootVertex)
def ids = parentVS.ids
println "Found $ids.size parent IDs to export"
// now read the data
def good = 0
def bad = 0
//define variables for tracking number of experted structures, make SAVE_NAME ready for editation
def x = 0
def y = 0
def done = 0
SAVE_NAME = SAVE_NAME[0..-5]
def exporter = new MolExporter(SAVE_NAME + "1.sdf", 'sdf')
try {
ids.each { id ->
// stop if the script is terminated
if (env.getFeedback().isCancelled()) {
def msg = "Exporting data to file $SAVE_NAME interupted!"
println msg
throw new InterruptedException(msg)
}
try {
def data = parentVS.getData([id], DFEnvironmentRO.DEV_NULL)
// get the mol
def mol = data[id][fldMol.id]
// get the other fields
def values = [ : ]
fieldsFromParent.each {
values.put(it, data[id][it.id])
}
// println "Exporting ID $id"
def expMol
// work with a clone so we don't alter the original
if (!mol || !mol.native ) {
expMol = new Molecule()
} else {
expMol = mol.native.cloneMolecule()
}
values.each { k,v ->
if (v != null) {
def pName = (FIELD_NAMES[k.name] == null ? k.name : FIELD_NAMES[k.name])
expMol.setProperty(pName, v.toString())
}
}
//every 60 structures change filename to export
if (x % 60 == 0) {
y++
done = 0
}
if (done == 0) {
exporter = new MolExporter(SAVE_NAME+y+".sdf", 'sdf')
done = 1
}
exporter.write(expMol)
x++
good++
} catch (Exception exc) {
println "EROROR Failed to load ID $id ${exc.toString()}"
bad++
}
}
} finally {
exporter.flush()
exporter.close()
}
println "Finished exporting data to file $SAVE_NAME "
println "good: $good bad: $bad"
ChemAxon 2bdd02d1e5
28-03-2012 17:14:30
There is more elegant (and right) solution which shoul be used instead. Variable 'done' is not needed and following lines should be replaced:
//every 60 structures change filename to export
if (x % 60 == 0) {
y++
done = 0
}
if (done == 0) {
exporter = new MolExporter(SAVE_NAME+y+".sdf", 'sdf')
done = 1
}
With this:
//every 60 structures change filename to export
if (x % 60 == 0) {
exporter.flush()
exporter.close()
y++
exporter = new MolExporter(SAVE_NAME+y+".sdf", 'sdf')
}
Filip
User 247c00dc1d
29-03-2012 12:52:01
Dear Filip! thank you so much - this is exactly what I need!
With respect
Igor
User 247c00dc1d
23-08-2012 13:44:22
Dear Filip!
is it possible to add a code to your script for importing not only SDF but both SDF and excel file?
Is it possible modify the script to transfer the name of current list as the name of importing file into the window where prompt for save file location?
Thanks,
Igor
ChemAxon 2bdd02d1e5
24-08-2012 05:43:39
Hi Igor!
I did not find any easy way how to export to xls files yet. (You probably meant export not import?).
As for your second question, this is not possible, import/export dialogs only deal with single files and can not accept any list of files for export/import.
I will ask somebody more experienced in this about the export to excel files and let you now if there is a simpe way how to do it.
Filip
User 247c00dc1d
24-08-2012 14:25:44
Hi Filip!
Yes, I really mean the exporting to excel.
About lists, I mean not list of files, but the name of current opened list of rows from DB, but it was not necessarily.
Thanks!
Igor
ChemAxon 2bdd02d1e5
29-08-2012 14:01:00
Hi Igor,
exporting to Excel in groovy require quite complex script and little more time at the moment. If you want to write such script I would recommend you Apache POI Java API - http://poi.apache.org/ . It is included in IJC and can be used in groovy scripts. Basically you would need to export every field in the entity and save it to excel by using Apache POI. And this is beyond the scope of this forum support.
An easier way comes in IJC 5.11. There will be an API provided wich exports to excel. The documentation will probably be located at http://www.chemaxon.com/marvin/help/developer/beans/api/chemaxon/formats/documents/jchemexcel/StructureToJC4XL.html
I'm sorry there is currently no easy and automated way how to export data to Excel by using Groovy scripting.
Cheers,
Filip