import problem

I am trying to import an excel created csv file into a new table. My first problem that I cannot choose ';' as a field separator, but I can manage this problem. However still I am unable to import correct data.





My (; -> , converted) csv file look like this


ide,transporter,category,subcategory,drug,type,remarks


1,MRP1 COOH terminal region mutants ,Eicosanoid ,Cysteinyl leukotriene, Pro-inflammatory ,Leukotriene C4, LTC4 ,substrate ,


2,MRP1 D(K)436E ,Sex hormone ,Estrogen, Glu-conjugate ,Estradiol 17beta-D-glucuronide, E(2)17betaG,substrate ,Km=2.9 microM





The key can be that the program find 21 columns, when it is 6.


So, how can I tell the end line note to JChem?





I have also tried to save my table in tab separated format and tell JChem that this is tab separated, but this time JChem found only 1 column.





Thanks,


Zsolt

Well, as you suggested this looks like an end of line character problem. Different operating systems use different characters to signify the end of line in text files.





Can you provide me with the actual file, and tell me which operating system you are using.


I've tried to reproduce this by copying the data you provided from the forum web pages, but this imported fine, so I need the original data.





We may need to provide an option in Instant JChem to specify the end of line character if the file does not use the defult for the operating system.


We will add addtional characters for the delimiters, including ';'





Tim

The problem is casued by additional comma characters in one of the fields, causing the parser to split this field into multiple fields, hence why you get 16 fields when you were expecting 8.





Colon (:) and semi-colon (;) have now been added as additional separators so that your file can now be imported directly. This updated development version should be available in the next few days.





In the meantime you should be able to write the data as a tab separated file, which should allow it to be imported without any conflicts with the additional commas.





Tim

I created a new database and added a field called IUPACName as text. When I go to the file import menu the field mapping dialog does not know about the new field.

If I rename the table name in the tree, when I get to the import file dialog I only see the old table name not the new one.

Andy, Please can you define how you are doing this. It works for me.


1. Create a new empty JChem table.


2. Open the table definition editor to edit the table.


3. Add a new field (e.g. type=text, name=name)


4. In project explorer right click on table's node and choose "Import from file.."


5. Work throght to step 4 (Map/Create fields). On this screen the new field should be in the list of exisitng fields available from the combo boxes in the Mapping column.





Can you try this out, and identify if you are doing anything different.





Tim





p.s can you post each different item as a new forum topic so that we can keep different issues separate.

The table node in the project explorer is not acutally the same as the database table. Let me explain.





We have a concept called a DataTree that defines the data structure that will define the master detail nature of the data in the database tables (e.g. traversing the foreign key relationships). A DataTree allows the user to define a particular view (or perspctive) of the database (e.g. a "compound view" or an "assay view"). Think of a DataTree as a bit like an ISIS HView, but one that the user can uderstand and define. Master detail tables and forms will be associated with a particular DataTree.





What appears in the project exporer is the DataTree, and currently a DataTree can only contain a single table, so is effectively synonymous with that table (but this will soon change) so we used the same icon as a table to re-inforce this.





So for now it really makes little sense to rename the node in the project explorer as it currently just represents the single table it contains, but in the future it will be useful to give it a meaningful name that reflects the purpose of the particular DataTree (e.g. Registered Compounds). Maybe for now we should disable renaming it to avoid any confusion?





Tim

I have a lot of problems importing SDfiles to create tables in Instantjchem0_6_0 for WIN. Even if I export a file with IJC, when I try to import it back, there is an error message and an empty table. I have attached a problematic example. Any idea?





Zsolt





org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [ALTER TABLE A28 ADD cd_id INTEGER NULL]; SQL state [s0021]; error code [-58]; Column constraints are not acceptable in statement [ALTER TABLE A28 ADD cd_id INTEGER NULL]; nested exception is java.sql.SQLException: Column constraints are not acceptable in statement [ALTER TABLE A28 ADD cd_id INTEGER NULL]


java.sql.SQLException: Column constraints are not acceptable in statement [ALTER TABLE A28 ADD cd_id INTEGER NULL]


at org.hsqldb.jdbc.Util.sqlException(Unknown Source)


at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)


at org.hsqldb.jdbc.jdbcStatement.execute(Unknown Source)


at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:280)


at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:254)


at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:287)


at com.im.df.dao.dynamic.jdbc.DFJdbcDaoSupport.addColumn(DFJdbcDaoSupport.java:422)


at com.im.df.core.impl.DatabaseSchemaManager$4.doInTransactionWithoutResult(DatabaseSchemaManager.java:323)


at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:33)


at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:114)


at com.im.df.core.impl.DatabaseSchemaManager.insertField(DatabaseSchemaManager.java:314)


at com.im.df.core.impl.StandardDatabaseEntity.addField(StandardDatabaseEntity.java:257)


at com.im.workbench.ijc.io.fileimport.ImportRunner.addField(ImportRunner.java:128)


[catch] at com.im.workbench.ijc.io.fileimport.ImportRunner.performAddFields(ImportRunner.java:199)


at com.im.workbench.ijc.io.fileimport.ImportRunner$Worker.run(ImportRunner.java:338)


at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:493)


at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:926)

I think this is because you are trying to import the field in the file called "cd_id". This is one of the standard database columns in JChem, and when you import a file that has been exported from Instant JChem this cd_id field is present in the exported file and so when you import it back into Instant JChem your are trying to create a database column using a name of a column that already exists, which is not allowed.


Try excluding this field (and probably the molweight and molformula fields as these will be created by JChem), or if you do need these fields rename them to a name that is not already in use.





We will enhance Instant JChem to provide an error message during the import wizard to warn the user of this potential problem.





Tim

I have a hundreds of separated molfiles in a directory. Is there any way to create a table or SDfile in 1 step? Or should I import molecular structures one at a time?





thanks,


Zsolt

I think you can do that by using


molconvert sdf *.mol -o sdf


(I found it in molconvert -h)


Good Luck,


Nicolas

Instant JChem does not currently provide a way to do this, other than importing each file individually.





I think the molconvert trick is the best option. Then importing the SDF you generate should be simple





Tim

Thanks for this trick. Now I have another problem. I have an excel file with data and an SDF file with structures. I imported the data first and tried to import structures to the same table but instead of updating the relevant fields, IJC added new rows. I have also tried the structure-data order without success. Is it any way to merge the structure and data tables?





Thanks, Zsolt

This is not currently possible in IJC, but is an obvious thing to want to do.


We will add this feature. I assume there is a single field in each file that contains a common ID that links the data in each file?





Tim

The latest version of Instnat JChem now allows you to merge data when importing based on common IDs. For instance you can import an SD file with structures and then import a CSV file with data into that table using the common IDs to identify the into record to which each record of the CSV file should be merged. Data in the CVS file can be used to create new fields or over-write values in existing fields.





Please let us know if this meets your needs.





Tim