Reading a csv file with embedded quotes into R

Question

I have to work with a .csv file that comes like this:

"IDEA ID,""IDEA TITLE"",""VOTE VALUE"""
"56144,""Net Present Value PLUS (NPV+)"",1"
"56144,""Net Present Value PLUS (NPV+)"",1"

If I use read.csv, I obtain a data frame with one variable. What I need is a data frame with three columns, where columns are separated by commas. How can I handle the quotes at the beginning of the line and the end of the line?

Answer 1

I don't think there's going to be an easy way to do this without stripping the initial and terminal quotation marks first. If you have sed on your system (Unix [Linux/MacOS] or Windows+Cygwin?) then

read.csv(pipe("sed -e 's/^\"//' -e 's/\"$//' qtest.csv"))

should work. Otherwise

read.csv(text=gsub("(^\"|\"$)","",readLines("qtest.csv")))

is a little less efficient for big files (you have to read in the whole thing before processing it), but should work anywhere.

(There may be a way to do the regular expression for sed in the same, more-compact form using parentheses that the second example uses, but I got tired of trying to sort out where all the backslashes belonged.)