exporting into sd file

Hi,





I experienced if the first row of a column is empty (in my


case it was a standard text field), then IJC ignores this column to


export into an sd file, even if it's visible in the gridview. It's


apparently easy to repare this, simply putting one single character in


the first row, then it exports nicely, but it would be nicer if it


shouldn't be done and IJC could handle empty fields in the first row as


well.








Denes

Empty values are not exported to SDF for any particular row, but if there is a value present in other rows then it will be inlcuded in the fields that are exported.


e.g. an empty value in the first row does not prevent a valuee being exported if it is present is subsequent rows.


Can you check your exported file to verify this.


Thanks





Tim

Hi Tim,





I've made a small table with 3 compounds and it worked as you said, IJC exported into an sd file the content of the whole field without any problem. Even if the first row in a text field column was empty. But I have a database with 1352 records, and if the first row is empty in this column IJC continues to ignore it during export (the given column is simply missing in the sd file). I repeated exporting the db, and I have still this error. I deleted the character: "." from the first row and the whole column disappeared from the exported sd file. I wrote it back and the column came back again. I've checked the sd files with Marvinview 5.2.0.








It's puzzling me, really.





Denes

I can't think of any obvious reason why this might be happening.

Have you checked the SD file contents directly to look for the field. e.g. using text editor?

 

 

Tim

Hi Tim,

 

I've checked as text file, and I found that even when the column was missing in the MarvinView, the info was in the textfile. Can it be the problem, that the first non-empty element is in the 221st row in that column? I've made 2 sd files to demonstrate the problem (attachments). When I open checkbase1 with MarvinView I can't see the "check" column. I put a "." character in the first row and the column is there. If I open as a text file the info is always there which is in the 221st row.

 

Denes

Yes, this appears to be an issue with MarvinView.

I will report it to the Marvin developers.

Many thanks for reporting this.

 

Tim

 

The problem with MarvinView has been fixed in Marvin 5.2.2.

When a record with additionald data is encountered the display is reloaded to show this extra column.

 

Tim

Hi Tim,

 

It's starting again the problem with sdf exporting-importing issue in IJC. Now I have a database, where the several 100 first place in some text-fields are empty except for the first row, where "99" was standing in all case. After exporing I've checked with Marvinview, it was OK. Then I tried to import into an sdf file and IJC changed the field type autonomously from text to integer. So it was not able to import just fraction of the sdf file since he waited integers for those fields and found text.

More interestingly after I renamed those '99'-s to 'A'-s the export import procedure happenned smoothly.

I attach the 2 screenshots (interesting parts in the column manager the changed field types in case of GPCR-number and PPI-number field.

Regards,

Denes

Hi Denes,

Since it found only integer values in given column for first 100 rows then newly created column is integer too. Possible solution is to read more rows in the import dialog. Please press Read more button during import as seen in the screen shot. Read as many rows as needed for fetching first row with any text characters in your example. 

Note that if it does not find any text characters, but only numbers(eg. your case for first 100 rows) in a column, it always determines that the column is of the integer type only.

Hope it helps.

Filip

Hi Filip,

 

Even I understand your reasons, please accept my opinion that changing the field type without any notice is not a very elegant solution. If there's some reason (smaller exported file size or so) that the program changes the field type, it should be asked if the user allows it. If I generated a field as text I'd like to see back as text after the export import procedure.

Is it possible to turn off this field-changing feature in IJC?

Dénes,

thanks for your opinion, but I'm affraid that there is nothing like field-type defined in exported files(tried sdf; rdf; mrv). Therefore IJC does not know what fields were there before the export when importing these files again. So there is no feature to turn off, because some field type must be used and it is usually determined from the first 100 rows.

However you can change it manually during import if you want. Select a field from Fields in database list on the right side and then change New field type from the drop-down menu according your wishes. It's is descibed on this page http://www.chemaxon.com/instantjchem/ijc_latest/docs/user/help/htmlfiles/moving_data/importing_data.html#step3 . 

I'm sorry if this is annoying for you. I don't know about any better solution then changing it manually.

Filip

Hi Filip,

Thanks for the info, I didn't know that sd files doesn't contain information about field types. In this case the importing capability of IJC is excellent.

Between two IJCs the zipped shema backup works, it's OK. Are there anyhow any database format where field types are stored as well?

Denes

Hi Denes,

your right it work perfectly fine for whole schemas by using schema backup feature on local Derby DB. I dont know about any format like that for local DB. It's definitely not possible in IJC yet. 

Maybe if you have strong DB experiences you could do it by Derby DB client, but I guess it would take more time than define it by hand. I know there are export/import commands which can be used on Oracle for it. Anyway, it is more likely work for DB admins. These database migration procedures must be usually handled manually(ie. generating SQL script for creating table with defined fields).

Filip