- Posts: 7
- Thank you received: 0
Welcome to the LimeSurvey Community Forum
Ask the community, share ideas, and connect with other LimeSurvey users!
CSV upload with double quotes around values
- crebeck
- Topic Author
- Offline
- New Member
Less
More
3 years 8 months ago - 3 years 8 months ago #203920
by crebeck
CSV upload with double quotes around values was created by crebeck
Hi everyone,
I am trying to link LimeSurvey to our Sona Systems subject pool by (amongst others) uploading a participant list form Sona Systems to LimeSurvey. The participant list Sona Systems provides is a CSV file with double quotes around the values. According to LimeSurvey this should work:
However, if I try to upload the file, I always get the following error:
As soon as I remove the double quotes around the values the upload works without any problem. I also tried this with a minimal CSV that only includes first name, last name and email address (see attached files), which leads to the same problem.
Has somebody else encountered this problem or can replicte it? Or am I missing something else here?
I am trying to link LimeSurvey to our Sona Systems subject pool by (amongst others) uploading a participant list form Sona Systems to LimeSurvey. The participant list Sona Systems provides is a CSV file with double quotes around the values. According to LimeSurvey this should work:
File should be a standard CSV (comma delimited) file with optional double quotes around values (default for most spreadsheet tools)
However, if I try to upload the file, I always get the following error:
(the participant list I used as a test has only one entry).1 lines had a mismatching number of fields.
As soon as I remove the double quotes around the values the upload works without any problem. I also tried this with a minimal CSV that only includes first name, last name and email address (see attached files), which leads to the same problem.
Has somebody else encountered this problem or can replicte it? Or am I missing something else here?
Last edit: 3 years 8 months ago by crebeck.
The topic has been locked.
- tpartner
- Offline
- LimeSurvey Community Team
Less
More
- Posts: 10110
- Thank you received: 3593
3 years 8 months ago #203924
by tpartner
Cheers,
Tony Partner
Solutions, code and workarounds presented in these forums are given without any warranty, implied or otherwise.
Replied by tpartner on topic CSV upload with double quotes around values
LimeSurvey version?
Cheers,
Tony Partner
Solutions, code and workarounds presented in these forums are given without any warranty, implied or otherwise.
The topic has been locked.
- Joffm
- Offline
- LimeSurvey Community Team
Less
More
- Posts: 12944
- Thank you received: 3979
3 years 8 months ago - 3 years 8 months ago #203927
by Joffm
Volunteers are not paid.
Not because they are worthless, but because they are priceless
Replied by Joffm on topic CSV upload with double quotes around values
Your csv_quotes seems to contain this.
So I should consider this as only one entry because of the surrounding quotes
"""John"",""Doe"",""jdoe@test.com"""
After having removed these surrounding quotes, and leaving the double double quotes I got
""John"",""Doe"",""jdoe@test.com""
remaining double double quotes.
Cleaning the file to only "double quotes"
"John","Doe","jdoe@test.com"
So, in my opinion this is correct behaviour.
This phrase
Double quotes work, single quotes do not.
A file with single quotes
'John','Doe','jdoe@test.com'
is imported as
Joffm
Code:
firstname, lastname, email """John"",""Doe"",""jdoe@test.com"""
So I should consider this as only one entry because of the surrounding quotes
"""John"",""Doe"",""jdoe@test.com"""
After having removed these surrounding quotes, and leaving the double double quotes I got
""John"",""Doe"",""jdoe@test.com""
remaining double double quotes.
Cleaning the file to only "double quotes"
"John","Doe","jdoe@test.com"
So, in my opinion this is correct behaviour.
This phrase
seems to mean:File should be a standard CSV (comma delimited) file with optional double quotes around values (default for most spreadsheet tools
Double quotes work, single quotes do not.
A file with single quotes
'John','Doe','jdoe@test.com'
is imported as
Joffm
Volunteers are not paid.
Not because they are worthless, but because they are priceless
Last edit: 3 years 8 months ago by Joffm.
The following user(s) said Thank You: crebeck
The topic has been locked.
- crebeck
- Topic Author
- Offline
- New Member
Less
More
- Posts: 7
- Thank you received: 0
3 years 8 months ago #203930
by crebeck
Replied by crebeck on topic CSV upload with double quotes around values
That makes sense. I guess opening it in the txt editor first instead of Excel would have helped. For future reference if anybody stumbles into this: CSV files automatically escape manually placed quotes when saved (
en.wikipedia.org/wiki/Comma-separated_values#Basic_rules
). This added the extra quotes in this example.
That also goes for my original participant list from Sona Systems: Sona Systems exports the CSV with an extra line on top saying "Participants". I opened it in Excel to delete the first line, but this lead Excel to drop the quotes for the fist value. Excel then subsequently tried to escape all other quotes in the above manner. Opening and editing the file in txt prevents this.
Thanks again Joffm for your help!
That also goes for my original participant list from Sona Systems: Sona Systems exports the CSV with an extra line on top saying "Participants". I opened it in Excel to delete the first line, but this lead Excel to drop the quotes for the fist value. Excel then subsequently tried to escape all other quotes in the above manner. Opening and editing the file in txt prevents this.
Thanks again Joffm for your help!
The topic has been locked.
- Joffm
- Offline
- LimeSurvey Community Team
Less
More
- Posts: 12944
- Thank you received: 3979
3 years 8 months ago #203933
by Joffm
Volunteers are not paid.
Not because they are worthless, but because they are priceless
Replied by Joffm on topic CSV upload with double quotes around values
Therefore we do not recommend EXCEL to handle csv-files.
Better OpenOffice, LibreOffice, etc..
Joffm
Better OpenOffice, LibreOffice, etc..
Joffm
Volunteers are not paid.
Not because they are worthless, but because they are priceless
The following user(s) said Thank You: holch
The topic has been locked.