R read data with records sometimes on two lines

Question

I need to read data from text files (many of them and very large), that typically look as follows:

---

#2013#3090050010#CCOU#01#022#1#N#16/03/2015 #2013#3090050010#CCOU#01#023#1##16/03/2015 #2013#3090050010#CCOU#02#005#1#1692528#16/03/2015 #2013#3090430110#CCOU#15#504#2#blablablablablablablablablablablablablab labla#01/10/2014

#2013#3090430110#CCOU#15#505#2##01/10/2014

---

So "#" is the separator and sometimes long records use two lines. I have a workaround whereby I ignore lines that do not begin with "#", using grep:

x<-readLines("data.txt")
y <- grep("^#",x)
app<-x[y]
NamesForCols<-c("..",...) 
myDat<-read.table(text=app,header =F,sep="#",quote="",col.names = NamesForCols, colClasses=c("NULL", "factor", NA,NA,NA,NA,NA,"character","NULL"), fill=T,blank.lines.skip=T,comment.char = "",allowEscapes = T)

But I am not happy with this solution (there is loss of significant data). Is there a way to read the data.txt file so that each record necessarily entails meeting the "#" symbol exactly 8 times even though this implies sometimes visiting two lines? Any other suggestion would be welcome. Thank you!

Answer 1

You could do the following:

text <- "#2013#3090050010#CCOU#01#022#1#N#16/03/2015 
#2013#3090050010#CCOU#01#023#1##16/03/2015 
#2013#3090050010#CCOU#02#005#1#1692528#16/03/2015 
#2013#3090430110#CCOU#15#504#2#blablablablablablablablablablablablablab 
labla#01/10/2014

#2013#3090430110#CCOU#15#505#2##01/10/2014"

Now combine columns that dont start by # with the preceeding one:

x <- strsplit(text, "\n")[[1]]
# starts with # or is empty
ind <- cumsum(pmax(grepl("^#",x), x==""))
x_collapsed <- vapply(split(x, ind), paste0, character(1), collapse = "")
x_collapsed <- paste(x_collapsed, collapse = "\n")

Now you can read that e.g. via:

require(readr)
read_delim(x_collapsed, delim = "#", col_names = FALSE,
       col_types = cols(X9 = col_date("%d/%m/%Y")))[, -1]

Which results in:

# A tibble: 5 × 8
     X2         X3    X4    X5    X6    X7                                             X8         X9
  <int>      <dbl> <chr> <chr> <chr> <int>                                          <chr>     <date>
1  2013 3090050010  CCOU    01   022     1                                              N 2015-03-16
2  2013 3090050010  CCOU    01   023     1                                           <NA> 2015-03-16
3  2013 3090050010  CCOU    02   005     1                                        1692528 2015-03-16
4  2013 3090430110  CCOU    15   504     2 blablablablablablablablablablablablablab labla 2014-10-01
5  2013 3090430110  CCOU    15   505     2                                           <NA> 2014-10-01

Answer 2

Taking advantage of floo0's and epi99's answers I arrived at my own solution, which is as follows:

text <- readLines("data.txt")
text_string <- paste0(text, collapse="")
result <- gsub("(#[^#]*#[^#]*#[^#]*#[^#]*#[^#]*#[^#]*#[^#]*#[^#]*)","\\1\n", text_string, perl = TRUE)
df <- read.delim(text = result, header=FALSE, sep = "#")[2:9]

So it differs from epi99's because it looks for a pattern where the right sequence of "#" symbols appear, possibly intertwined with other characters.

Answer 3

text <- readLines("data.txt")
text_string <- paste0(text, collapse="")

# assuming every line ends in a date, put back line breaks
# by matching and capturing
result <- gsub("(\\d{2}/\\d{2}/\\d{4})\\s?", "\\1\n", text_string, perl = TRUE)

# read from string
df <- read.delim(text = result, header=FALSE, sep = "#")[2:9]

df

gives

    V2         V3   V4 V5  V6 V7                                              V8         V9

1 2013 3090050010 CCOU  1  22  1                                              N 16/03/2015
2 2013 3090050010 CCOU  1  23  1                                                16/03/2015
3 2013 3090050010 CCOU  2   5  1                                        1692528 16/03/2015
4 2013 3090430110 CCOU 15 504  2 blablablablablablablablablablablablablab labla 01/10/2014
5 2013 3090430110 CCOU 15 505  2                                                01/10/2014