I'm trying to get a .csv file with this exact format http://puu.sh/ubMVZ/6d7b9968d5.png to be used in a statistical analysis in metaboanalyst (this is a list of concentrations of metabolites from different patients)

When I save my .xls with my own data in it as a .csv file in excel it gives me this http://puu.sh/ubN3m/24981307b8.png as you can see the delimiters and separators are different, I want commas and quotes but the file I have doesn't give me that. Does anyone know how I can mimic the first example? (which is an example dataset taken from metaboanalyst website) I'm a novice in manipulating things like this with software.



Assuming that I've understood your requirement correctly, you will need to do the following.

  1. Open Excel and go into the "Data" tab. Open "From Text" in the "Get External Data" section and open your CSV file.

  2. Ensure that Delimited is selected at step one.

  3. At step two, set the "Text Qualifier:" field to none and select comma as the delimiter. This should leave the quote marks intact and split the file up as required.

  4. On step three, set the relevant data formats for your spreadsheet.

When saving the file, you should bear in mind that the default delimiter is set elsewhere. See here for more info.

  • Hi, I tried this but the result looks like this puu.sh/ubPtD/88fd65ab08.png Meaning the semicolons are still there and nothing is actually enclosed by quotation marks like in the example file, they're just present at the left hand side. – Alan Dazay Feb 20 '17 at 16:34
  • @AlanDazay Is this a publicly available file? Difficult to see what exactly has happened there. – LiamC Feb 20 '17 at 16:43
  • I'll share a link to the file on google drive drive.google.com/open?id=0B7ereD5vZo4JU3NqOXVEZkJYUDg this is after I applied the suggested changes (semicolons are there and some compounds names got erased) vs the template i'm trying to make it look like metaboanalyst.ca/resources/data/human_cachexia.csv – Alan Dazay Feb 20 '17 at 16:52
  • Please stick a copy of the original file on there if possible and I'll have a look. – LiamC Feb 20 '17 at 16:55
  • Thank you very much, here is the .xls file with all the data that i'm trying to convert into .csv with the proper format drive.google.com/open?id=0B7ereD5vZo4Jcm92MXlQREJkdEk It's only the first sheet, the others can be ignored – Alan Dazay Feb 20 '17 at 17:37

Downloaded your google drive file and managed to convert it correctly.

Have you checked your Region and Language settings? (refer: Excel save-as CSV options -- possible to change comma to pipe or tab instead?)

It could be that you have set your "List seperator to semicolon instead of comma, which is what is causing this to happen

  • I have already fixed these settings and it does not make any difference on the result, the separator is still semicolons. I haven't gotten to trying and get quotation marks around all the non-variables yet. – Alan Dazay Feb 21 '17 at 10:58
  • Nvm I got it to work if i saved the file using OpenOffice, Excel still doesn't work though. Thanks for all the help. – Alan Dazay Feb 21 '17 at 11:16
  • I guess that works, still curious what is causing it though :) – teikjoon Feb 23 '17 at 0:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.