molconvert gives unwanted "sorting" of columns

As mentioned in another thread, I want to convert text files to sd files containing headers and data attached and per suggestion I use

molconvert sdf test.txt{smiles} -o test.sdf

This works in principle, *but*

if there is a colum with partial data, or no data, this column gets moved to the end upon conversion.

I would like to retain the order of the columns. Is this a bug or a feature, am I missing a switch?

Hi,

If in the column there is no data then we don't export that property at all.

Regards,
Domi

Okay, but what about the fact that partially empty columns (data not in every row) end up in a different order?That is kind of odd, wouldn't you agree? compare column order in my enclosed examples.

And with regards to empty columns: would be nice to have control over them being exported or not. They could become important in e.g. other contexts.

May I ask from where the problem comes? Did you find this during using JchemForExcel, or sth other application? How can we reproduce it?

As mentioned in my opening question, using molconvert

molconvert sdf test.txt{smiles} -o test.sdf

(resp test1.txt / test1.sdf with the provided files). i.e. convert the txt file (which is a smiles file with data attached), then look at the sdf file output (in table formate). I add a picture file to show this.

Thank you for the detailed answer, we will investigate the issue and return soon.

Hi,
The situation is that we don't import empty attributes from smiles and not export empty attributes to sdf. Therefore in the exported sdf file every molecule has only its relevant properties. Consequently if you open the sdf file in table mode then the attributes are collected from the sdf(!) file in the order as they occur there.
One workaround is if we import the empty attributes from smiles and export the empty attributes to sdf. Would it be good for you, or you would prefer some other solution?
Regards,
Domi

If I understand you suggestion correctly, yes, that would be a possible work around.

To be able to give the best answer for now, or possibly a workaround that helps, could you tell what is the root of the column order change problem? Does it related to visualization or something else?

main problem would be for me if I wanted to merge multiple files. since I don't use advanced packages such as spotfire, and basically do it manually in e.g. excel, it causes a problem. if there were a good way of mergin in knime, it would help me on the way, but I am not very experienced in the usage of knime either.

"visualization", or, "readability" in excel is another problem thus, if e.g. a qualifier column suddenly is somewhere else, it is a pain to read. this is basicallay the case since I am using text files as "databases".

does this help?

Thanks for the details. As far as I know JchemForExcel when you open an .sdf file reads the found properties just in the same column as they were in the previously read files. Doesn't JchemForExcel helps this problem?

unless it is a setting that I have missed, Jchem for Excel does not import it as you suggest, see the above picture file where you can see the files before and after conversion with molchem, and imported into Jchem/Excel.

Hi,

So if I understand your problem, you want to merge multiple files(smiles format) with the same order of the properties in the excel? Why can't you just sort the columns in excel to the right order or merge the smiles files in a text editor and after convert to sdf? Please explain the whole process how you merge this files.

Thank you,
Mate 

You have to envision sheets with 60 or even 100+ columns. And thousands of entries/rows.

Now take just 2, not even mentioning 5 or more sheets you want to merge. And each of them has a different column order. That is pretty annoying, very time consuming and unproductive to have to find and sort the columns to get them into a correct order. And a big source for errors.

If Jchem4Excel is able to do that automatically (maybe I am stupid)  upon importing several sheets into one, then I haven't noticed. Also, JChem4Excel is only on the PC. As soon as I work on a Mac, that option isn't available.

In addition, the origin of the  files may not be of the same file type, one of many reasons to convert them into the same type for latter merging.

Hope this helps, not sure how else I can explain it.

Have the source files the same order of the properties?

as a simplistic answer for now: yes.

 

 

that aside, back to the origin of the thread: I appreciate you wanting to understand my work-flow, but for me, I would simply like you to see the export of empty fields into sd. Other sd writing programs do it, at least I am used to it from previous experience, e.g.  Accord for Excel, Isis Base, Isis for Excel, Schroedinger Suite and others. And definetly not in a different order than the origin.

Rhenium wrote:

that aside, back to the origin of the thread: I appreciate you wanting to understand my work-flow, but for me, I would simply like you to see the export of empty fields into sd. Other sd writing programs do it, at least I am used to it from previous experience, e.g.  Accord for Excel, Isis Base, Isis for Excel, Schroedinger Suite and others. And definetly not in a different order than the origin.

Hi,

You are right we wanted to understand the workflow to suggest some workaround. Actually it is not possible to export empty/null properties,  or properties with empty string to sdf format. On the other hand I do not see a reason to support it, but I'm open to discuss it.

As far as I understand your question is not related to JChem4Excel (you mentioned that you may work on Mac).

I guess you want to generate sdf file with different properties and use this sdf files for some reason. 

As I mentioned the empty properties will not be exported to the sdf, but if you import the generated sdf file to Chemaxon software, I'm quite sure that the missing properties can be visualized in such a column order as you want, if you want  to use an other software, than I have no idea how to solve this problem.

I have attached a picture as a result of 

mview -f Compound:Property1:Qualifier_Property2:Property2:Qualifier_Property3:Property3:Qualifier_Property4:Property4:Ruleset1:AnotherProperty:Ion_Class:n_Rings:Comment ~/Downloads/test1.txt