Add field with actual date into the sdf

Hello!

I usually use the script for sdf export, help please modify it to add field with actual date 

>  <Date> (i)


2014-12-05

 

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 = ['SID','IDN'] // list of field names to export

def STRUCTURE_FIELD = 'Structure' // field name of the structure field

def FIELD_NAMES = ['SIDR':'SIDRON', 'IDN':'IDNON'] // 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()

chooser.setCurrentDirectory(new File('D:\\Bases\\'));

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 + "-01.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 600 structures change filename to export

            if (x % 600 == 0) {

                exporter.flush()

                rr = y +1

                println "Write $rr files"

                exporter.close()

                y++

                exporter = new MolExporter(SAVE_NAME+"-"+0+y+".sdf", 'sdf')

            }

    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"

println  "bad: $bad"

Thanks!

Igor

Hi Igor,

if you have the date field in your table, just add it to  FIELDS_FROM_PARENT, be sure to match the correct upper/lower casing as in the field name.

If you want to add the date of the export, add the following to the script.

Hope it helps,
Filip

 

// in the beginning
import java.text.SimpleDateFormat 

import java.util.Date

...

// after setting properties to expMol via values.each

expMol.setProperty("Date", new SimpleDateFormat("yyyy-MM-dd").format(new Date()))

 

Hi Filip,

The code You have written is that I need!

But also interesting: I have the field with date in the DB, and when I exports sdf-file the date which in DB looks like "03.12.2014" in the sdf appears like:  "Wed Dec 03 00:00:00 EET 2014". Is way to do data format in the DB and in the sdf similar -  "03.12.2014"

Thanks!

Igor

Hi Igor,

I can't try it today it but you may want to format the value before setting it to to the molecule object.

Cheers,
Filip

 

            values.each { k,v ->

                if (v != null) {
                    if (v instanceof Date) {
                        v = new SimpleDateFormat("dd.MM.yyyy").format(v)
                    }
                    def pName = (FIELD_NAMES[k.name] == null ? k.name : FIELD_NAMES[k.name])

                    expMol.setProperty(pName, v.toString())

                }

            }

Thank you Filip!