A simple solution is to add "\t" as a second argument to the IMPORTDATA function.
=IMPORTDATA("http://opsin.ch.cam.ac.uk/opsin/"&A2&".inchi","\t")
See it in action here: https://docs.google.com/spreadsheets/d/1YQpQiPgYJvfuHE5Tm6DMVw5hzwk3Cz0jByBp6eNwGeo/copy
How can I add compound name to a google sheet? Can we have google sheet pull off compound name from PubChem or from ChemSpider or NIST. Also can we connect the two for example name or property of a compound in two columns using one set of codes.
I agree with Bob on InChIKey, but sometimes you really just want the InChI. Also problems like this just bug the heck out of me, and I just know there's a solution! So, I think the following will work - Resolver an OPSIN.
=REGEXREPLACE(ArrayFormula(concatenate(IMPORTDATA("http://opsin.ch.cam.ac.uk/opsin/"&A2&".stdinchi")&",")), "\,\z", "")
=REGEXREPLACE(ArrayFormula(concatenate(IMPORTDATA("https://cactus.nci.nih.gov/chemical/structure/"&A2&"/stdinchi")&",")), "\,\z", "")
Otis
I was able to do it without any issues - but I stayed with the video and used Inchi key.
Did you watch the YouTube video on the "Googlable InChI Key?
http://olcc.ccce.divched.org/2015OLCCModule5P1TLO5-3-2C
The jist is that InChI can be very long and contain characters that can be misinterpeted by web browsers and other software agents. That is why we used the InChI Key in our sheet.
I am not a programmer, but you may be able to protect the string with a quotation mark, or something like that.
Cheers,
Bob
If anyone wants to see it in action, I have provided a link to a copy of the spreadsheet. You can see that hexane has one column extra and 1,2-dichlorobutane has two columns extra.
https://docs.google.com/spreadsheets/d/1NqjX8B2D3OefnzBd5_FNuEyEmPWIv_CCFRz3cqsGASU/copy
Hi everyone,
My students posted a question to a sub TLO Google Sheet Connecting IUPAC name to PubChem but since nobody is "following" that thread (OLCCFac and OLCCstu are not following), I don't know if they will get an answer. I also cannot answer the question myself.
http://olcc.ccce.divched.org/comment/991#comment-991
The upshot of the post is that since inchi strings have commas in the data, the import treats that as new data and places it in a new cell. For example the inchi for hexane is InChI=1S/C6H14/c1-3-5-6-4-2/h3-6H2,1-2H3 and the 1-2H3 gets placed in a new cell.
I have taken a screen shot with the importdata line and placed that below. I am guessing there must be a precursor to add before the IMPORTDATA statement that should treat the returned data as a string variable. I just don't know what that would be. I do think that this is an interesting question as comma separated data will have this issue as spreadsheets interpret the commas as comma separated values. I tried to use =T(IMPORTDATA("http://opsin.ch.cam.ac.uk/opsin/"&A2&".inchi")) to convert it to text, but it still truncates to the first comma.
Hello, from S53, S52, and S51, We were working on a google sheet together that would import data from different sources and allow people to select which data output they wanted(IE smiles, inchi, inchikey, cas, so on). Well we were having a issue when dealing with InChI codes. It appears as though whenever you import the data from any website with an inchi string output, whenever a "," is included in the string, such as the following, InChI=1S/C4H8Cl2/c1-2-4(6)3-5/h4H,2-3H2,1H3 , the program will read those ,'s as though they are skip functions in the doc so it moves to the next cell as shown in the image provided below. If anyone has any suggestions or comments on how they might go about getting around this issue please let us know.
Thanks, S51-53.
update* it wont let me upload an image so theres no image, Just know that the cell will say "inchi=xxxxxx" "xx" "xxx" and each set of quotes is a different cell and all of the x's come from the same inchi key import.
A nice student project would be to write a web page that lets the user paste in a set of XY data and then displays it in JSpecView and also creates a JDX file. Quite doable! :)
Looks like we now have two solutions from Otis and Jordi. I think adding the\t is a little simpler. Where did you find the \t solution, and more importantly, what is the \t modifer doing? I am assuming it is a modifer for the IMPORTDATA statement that tells the IMPORTDATA to treat the data as a text string. I just couldn't find anything that suggested the modification. If you have a link to the modifications for IMPORTDATA, could you post it?
Thank you!