Hi, I would like to export structure/data for generating a graph in Excel. Because I test same compounds in many assays, I get this type of Exel file after I export.
However this is not good for me to do any analysis because so many data are in one cell.
Is there any better way to export data for further analysis; pivot table etc..
I would like each row has compound name, assay data so I can use,sort,modify easily.
So here you have exported some relational data into a XLSX format and it has bundled some child records into single cells.
One possible solution :
Export Structure and Data as separate CSV files and as you suggest attempt some merge/re-format from within VBA i.e. It could be possible with a simple macro.
Another is export your relational data into an SDF file with additional fields and then import using JChem 4 Excel functions thus giving you proper chemaxon structures in your spreadsheet as well as your graphing data.
Another alternative has been suggested to me and so I pass it on to you !
If you reverse your data tree and then complete a normal export, you should get the data in the format that you require !
If you need some assistance in doing this - please tell us on here and we will contact you !
I think this is a good idea since I don't have JChem for Excel.
I appreciate your help in make my data tree reverse.
Lets assume that you have Structures -< Data (1 : n) using a join column called ID.
1. Promote the Data entity to it's own data tree using your choice of primary key during promotion (this is not the ID column).
2. Create a (n : 1) / (Many to one) relationship FROM Data.ID to Structures.ID - The "Data" node is the parent and "Structures" is the child.
3. Add the edge (right click on Data datatree) and add edge, icon should transform and you see n:1 appear.
This should have reversed your original data tree, so an export from here should give you somthing like the data format your after (try CSV or XLSX) please.