How to read quoted text containing escaped quotes
Solution 1
One possibility is to use readLines()
to get everything read in as is, and then proceed by replacing the quote character by something else, eg :
tt <- readLines("F:/temp/test.txt")
tt <- gsub("([^\\]|^)'","\\1\"",tt) # replace ' by "
tt <- gsub("\\\\","\\",tt) # get rid of the double escape due to readLines
This allows you to read the vector tt in using a textConnection
zz <- textConnection(tt)
read.csv(zz,header=F,quote="\"") # give text input
close(zz)
Not the most beautiful solution, but it works (provided you don't have a " character somewhere in the file off course...)
Solution 2
read_delim
from package readr can handle escaped quotes, using the arguments escape_double
and escape_backslash
.
read_delim(file, delim=',', escape_double=FALSE, escape_backslash=TRUE, quote="'")
(Note older versions of readr do not support quoted newlines in CSV headers correctly: https://github.com/tidyverse/readr/issues/784)
Related videos on Youtube
Max
Updated on May 31, 2022Comments
-
Max almost 2 years
Consider the following comma separated file. For simplicity let it contain one line:
'I am quoted','so, can use comma inside - it is not separator here','but can\'t use escaped quote :=('
If you try to read it with the command
table <- read.csv(filename, header=FALSE)
the line will be separated to 4 parts, because line contains 3 commas. In fact I want to read only 3 parts, one of which contains comma itself. There quote flag comes for help. I tried:
table <- read.csv(filename, header=FALSE, quote="'")
but that falls with error
"incomplete final line found by readTableHeader on table"
. That happens because of odd (seven) number of quotes.read.table()
as well asscan()
have parameterallowEscapes
, but setting it toTRUE
doesn't help. It is ok, cause fromhelp(scan)
you can read:The escapes which are interpreted are the control characters ā\a, \b, \f, \n, \r, \t, \vā, ... ... Any other escaped character is treated as itself, including backslash
Please suggest how would you read such quoted csv-files, containing escaped
\'
quotes. -
Joris Meys almost 13 years@Marek : I'm not completely following. Where exactly should I replace that to get the correct output?
-
Marek almost 13 yearsI mean
tt <- readLines(file); tt <- gsub("\\\\'","''",tt); read.csv(textConnection(tt),header=FALSE,quote="'")
cause double quotes are read correctly (see?scan
-> Details -> quotes). -
jnas almost 10 yearsObviosly this works for files quoted with
"
, too usingtt <- gsub("\\\\\"","\"\"",tt);
-
rakensi over 9 yearsAlthough this solution works for small files, for larger files it becomes very slow and uses lots of memory.A streaming solution (holding only one line of the file in memory before it goes to the table) would be better, but I do not (yet) know how do do that.
-
Joris Meys over 9 years@rakensi You can use readLines() to read in the file in chunks and process those chunks, but any solution in R that involves large files or datasets will become slow and will use a lot of memory. R isn't the most memory-friendly language by design.
-
rakensi over 9 years@Joris Meys: Thank you for your remark. I think a good solution would be to use a pipe connection, transforming each line before feading it into read.csv. Unfortunately, the pipe uses a shell script, which makes this dependent on the environment you run it in. A pipe that transforms each line with an R function would be better, but I have not seen anything like that.