How to export data for a pivot table/plot?

User 8f0e0cac1a

06-09-2012 19:14:58

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.


Thank you


Hoon

ChemAxon 60ee1f1328

06-09-2012 22:38:41

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.

ChemAxon 60ee1f1328

07-09-2012 11:50:00

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 !

User 8f0e0cac1a

07-09-2012 14:28:49

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.


Best


Hoon

ChemAxon 60ee1f1328

07-09-2012 15:20:56

OK then...


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.


Thanks,


Daniel.

User 8f0e0cac1a

07-09-2012 17:36:20

Thank you ,


You are awesome!


Hoon