1

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.

Thanks

2 Answers 2

0

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.

6
  • 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, 2017 at 16:34
  • @AlanDazay Is this a publicly available file? Difficult to see what exactly has happened there.
    – LiamC
    Feb 20, 2017 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, 2017 at 16:52
  • Please stick a copy of the original file on there if possible and I'll have a look.
    – LiamC
    Feb 20, 2017 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, 2017 at 17:37
0

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

3
  • 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, 2017 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, 2017 at 11:16
  • I guess that works, still curious what is causing it though :)
    – teikjoon
    Feb 23, 2017 at 0:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .