CODEWITHSUNDEEP
rcsv
Data Science Analytics Manager
Data Science Analytics Manager

Reputation: 439

how to read delimiter "|" vertical line in R external csv file

In R, how does one read delimiter or and also convert delimiter for "|" vertical line (ASCII: | |). I need to split on whole numbers inside the file, so strsplit() does not help me.

I have R code that reads csv file, but it still retains the vertical line "|" character. This file has a separator of "|" between fields. When I try to read with read.table() I get comma, "," separating every individual character. I also try to use dplyr in R for tab_spanner_delim(delim = "|") to convert the vertical line after the read.delim("file.csv", sep="|") read the file, even this read.delmin() does not work. I new to special char R programming.

read.table(text = gsub("|", ",", readLines("file.csv")))

dat_csv <- read.delim("file.csv", sep="|")

x <- cat_csv %>% tab_spanner_delim(delim = "|")

dput() from read.table(text = gsub("|", ",", readLines("file.csv")))

",\",R,D,|,I,|,7,8,|,0,1,0,|,0,0,1,2,|,8,8,1,0,1,|,1,|,7,|,1,0,5,|,1,1,6,|,1,9,9,9,1,2,2,0,|,0,0,:,0,0,|,|,A,M,|,6,|,|,|,|,|,|,|,|,|,|,|,|,|,\",", 
",\",R,D,|,I,|,7,8,|,0,1,0,|,0,0,1,2,|,8,8,1,0,1,|,1,|,7,|,1,0,5,|,1,1,6,|,1,9,9,9,1,2,2,6,|,0,0,:,0,0,|,4,.,9,|,|,6,|,|,|,|,|,|,|,|,|,|,|,|,|,\","

dput() from dat_csv <- read.delim("file.csv", sep="|")

"RD|I|78|010|0012|88101|1|7|105|116|19991220|00:00||AM|6|||||||||||||", 
"RD|I|78|010|0012|88101|1|7|105|116|19991226|00:00|4.9||6|||||||||||||"

dput(dat_csv)

"RD|I|78|010|0012|88101|1|7|105|116|19991220|00:00||AM|6|||||||||||||", 
"RD|I|78|010|0012|88101|1|7|105|116|19991226|00:00|4.9||6|||||||||||||"

Upvotes: 0

Views: 2070

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 389355

We can read the data line by line using readLines. Remove unwanted characters at the end of each line using trimws, paste the string into one string with new line (\n) character as the collapse argument and use this string in read.table to read data as dataframe.

data <- read.table(text = paste0(trimws(readLines('file.csv'), 
                   whitespace = '[", ]'), collapse = '\n'), sep = '|')

Upvotes: 1

Related Questions