R - import wrapped .txt file with custom line separator

user_16645677 0
Viewed 61

I am attempting to import a .txt file into R with the line separator denoted as "\" and the column separator as "*". However, each line is wrapped, and I cannot find a function to set a customized line separator.

The real file is my company's banking 822 edi file (so about 10,000 lines), so I cannot show the actual text. However, BofA does not unwrap these files when you download them.

The .txt file I am attempting to import looks something like this:

Example Text File
1*AAA*2*BBB\2*CC
C*3*DDD

I want it to look like this:

Col1 Col2 Col3 Col4
1 AAA 2 BBB
2 CCC 3 DDD

I have been unable to find a function to do this, so any help would be greatly appreciated. Thank you.

import r
1 Answers

Figured it out. Probably a cleaner way to do it, but since no one answered I will leave my solution in case someone else has this problem.

First I imported the entire text file as a string:

filename <- 'BOA 2020-12.txt'
import.dat <- readChar(filename, file.info(filename)$size)

Then I removed the current line breaks:

import.dat <- gsub('[\r\n]','',import.dat)

Then I replace the "\" with new line breaks

import.dat <- gsub('[\r\\]','\n',import.dat)

Now I have a clean string to parse out. Next, I split the string into a single column matrix for each line break.

import.dat <- unlist(strsplit(import.dat,split='\n'))

In this specific case, each line has a different length of columns, so I created a matrix of zeros (17 is the maximum length of columns. It's ok to plug it since these type of documents always have a max 17 columns) and ran a nested for loop for each row and filled in the columns.

  sub.dat <- as.data.frame(matrix(0,ncol=17,nrow=length(import.dat)))
  for(i in 1:length(import.dat)){
    temp <- unlist(strsplit(import.dat[i],split='\\*'))    
    for(j in 1:length(temp)){
      sub.dat[i,j] <- temp[j]
    }
  }

Boom, easy peasy.

user_166456770
Related