R data.table fread how to deal with embedded ^@ character?

Question

I dont manage to read this csv with fread:

➜  Downloads cat t2.csv 
47616412|76-398-12||||7639812
47616413|53-1696-18||||53169618

i think it's because in vim I see those characters:

47616412|76-398-12||^@||7639812

and fread puts a linebreak at column 4. how to deal with that?

edit 1

notice that the standard console output does not show those characters:

➜  Downloads cat t2.csv 
47616412|76-398-12||||7639812
47616413|53-1696-18||||53169618

I only see them in vim:

47616412|76-398-12||^@||7639812
47616413|53-1696-18||^@||53169618

edit 2

notice further that read.csv with skipNul works:

> read.csv("t2.csv", sep="|",header=FALSE,skipNul=TRUE)
        V1         V2 V3 V4 V5       V6
1 47616412  76-398-12 NA NA NA  7639812
2 47616413 53-1696-18 NA NA NA 53169618

edit 3

here is the file! dropbox download

not sure how... i set `na.strings = "^@"`, but that's not it. it's not a regular character i think. updated question. — Florian Oswald, Apr 17 '19 at 10:53
this seems related to https://stackoverflow.com/questions/45973059/how-to-handle-data-with-no-space-between-separators-when-using-fread-in-r — Florian Oswald, Apr 17 '19 at 11:01
I'd recommend removing the space characters in the shell before reading them to R. https://stackoverflow.com/questions/2398393/identifying-and-removing-null-characters-in-unix — Humpelstielzchen, Apr 17 '19 at 11:20
And/or do the 'sed/awk pipe before download' trick -- `fread` can read from a pipe so you may have to do your pre-processing there. — Dirk Eddelbuettel, Apr 17 '19 at 11:37
thanks @Humpelstielzchen you can post that as an answer if you want! — Florian Oswald, Apr 17 '19 at 14:03
oh R, come on. `system(paste("tr <",target,"-d '\000' >",tempfile()))` returns `nul character not allowed`. — Florian Oswald, Apr 17 '19 at 14:15
Only if it solved your problem. ^^ Did you get it to work somehow? Maybe doing it directly in the shell? — Humpelstielzchen, Apr 17 '19 at 17:38
yeah, well this last thing is a bit of a pain. I end up doing it directly in the shell as you said with a little script that takes the filename as an argument. it's called `nulkiller.sh`. do you think I should submit it to CRAN? :-) — Florian Oswald, Apr 17 '19 at 19:23

score 4 · Accepted Answer · answered Apr 17 '19 at 20:20

This has just been fixed in dev 1.12.3 (see NEWS) :

fread() now skips embedded NUL (\0), #3400. Thanks to Marcus Davy for reporting with examples, and Roy Storey for the initial PR.

I checked your file attached to the question indeed fails with 1.12.2 on CRAN but works in dev.

> library(data.table)   # v1.12.2 on CRAN 07 Apr 2019
> fread("~/Downloads/t2.csv")
Empty data.table (0 rows and 1 cols): 47616412|76-398-12||
Warning message:
In fread("~/Downloads/t2.csv") :
  Stopped early on line 2. Expected 1 fields but found 1. Consider fill=TRUE
  and comment.char=. First discarded non-empty line: <<>>

but in dev 1.12.3 it now works :

> library(data.table)   # v1.12.3 in development as of 17 Apr 2019
> fread("~/Downloads/t2.csv")
         V1         V2     V3     V4     V5       V6
      <int>     <char> <lgcl> <lgcl> <lgcl>    <int>
1: 47616412  76-398-12     NA     NA     NA  7639812
2: 47616413 53-1696-18     NA     NA     NA 53169618
>

R data.table fread how to deal with embedded ^@ character?

1 Answers1