Reading .csv files with unquoted newlines

[prockenschaub]

Hi all,

Sometimes I receive .csv files from external data providers that include no quotes (presumably because the data manager forgot to click the right button when exporting them from SQL), which makes it really hard to read in files with free text comments that can contain newlines. Does anyone have a good way to deal with those files, particularly when they are >1GB?

An example:
test.txt

library(readr)
library(data.table)

read.csv("test.txt")
read_csv("test.txt")
fread("test.txt", fill=TRUE)

[drhodesbrc]

I suggest fixing in bash first. I'm sure there's a more elegant way of doing it, but this works:
sed -i 's/,/","/g' test.txt # change ',' to ' "," '

sed -i '1s/^/"/' test.txt # add a '"' to first line

sed -i '$s/$/"/' test.txt # add to end line

tr '\n' ' ' < test.txt > temp.txt && mv temp.txt test.txt # Then remove any erroneous newlines

[alhenry]

I agree it would be easier to fix the file using shell command first

Another option with sed and some regex matching:

sed -E -e :x -e '/^([^,]*,){2}[^,]*$/N; s/\n/ /; tx' test.txt

Also, data.table::fread() can pre-process input file with invoked system command so if the OS has sed installed we can do:

#write temporary file from the uploaded txt file
file <- "https://github.com/ucl-ihi/CodeClub/files/3481300/test.txt"
tmp <- tempfile(fileext = ".txt")
httr::GET(url = file, httr::write_disk(tmp))

library(data.table)
# sed commands: note that the EOL characters \r\\n may differ depending on the OS

command <- "sed -E -e :x -e '/^([^,]*,){2}[^,]*$/N; s/\r\\n/ /; tx'"
fread(cmd = paste(command, tmp))
#>    A B                                                  C D
#> 1: 1 a                       commment over multiple lines 1
#> 2: 2 b                                     only one lines 5
#> 3: 3 c                                             single 8
#> 4: 4 d again multiple  lines that are not quoted properly 9