CODEWITHSUNDEEP
rsqldf
Benjamin
Benjamin

Reputation: 17279

read.csv.sql filter for fields with columns

I haven't been able to solve this one using the answers in this question or from the sqldf FAQ's. 

LOC_NAME,BIRTH_DTTM,MOM_PAT_MRN_ID,EMPI,MOM_PAT_NAME,MOM_HOSP_ADMSN_TIME,MOM_HOSP_DISCH_TIME,DEL_PROV_NAME,ATTND_PROV_NAME,DELIVERY_TYPE,PRIM.REPT,COUNT_OF_BABIES,CHILD_PED_GEST_AGE_NUM,REASON_FOR_DEL,REASON_DEL_COM,INDUCT_METHOD,INDUCT_COM,AUGMENTATION
HOSPITAL,1/1/2000 10:00,abc,Eabc,"Surname1, Given1",1/1/2000 10:00,1/3/2000 10:00,"Doctor, First","Doctor, First","C-Section, Low Transverse",Repeat,1,38,,,1) None,,1) None
HOSPITAL,1/2/2000 11:00,def,Edef,"Surname2, Given2",1/2/2000 11:00,1/5/2000 11:00,"Doctor2, First2","Doctor2, First2","C-Section, Low Transverse",Primary,1,36,Ruptured Membranes;Labor;Other (see comment),"PPROM, Preterm labor",1) None,,1) None
HOSPITAL,1/3/2000 12:00,ghi,Eghi,"Surname3, Given3",1/3/2000 12:00,1/6/2000 12:00,"Doctor3, First3","Doctor3, First3","C-Section, Low Transverse",Repeat,1,31,Other (see comment),,1) None,,1) None
HOSPITAL,1/4/2000 13:00,jkl,Ejkl,"Surname4, Given4",1/4/2000 13:00,1/7/2000 13:00,,"Doctor4, First4","Vaginal, Spontaneous Delivery",,1,28,Other (see comment),Fetal anomaly,1) oxytocin (Pitocin),,

To read in the data, I have tried:

read.csv.sql(file) 

read.csv.sql(file, filter = 'tr.exe -d ^" ')

read.csv.sql(file, filter = list('gawk -f prog', prog = '{ gsub(/"/, ""); print }'))

read.csv.sql(file, 
             filter = "perl -e 's{(\"[^\",]+),([^\"]+\")}{$_= $&, s/,/_/g, $_}eg'")

I'm working in R 3.0.0 with R Studio Server on a Ubuntu OS.

Unfortunately, changing the delimiter isn't an option (nor would it be very effective for some of the files I need to query. Some of my files are pathology reports, so no matter what delimiter I use, I'm going to run into this problem.

Any hints on what I'm missing to get this to read in?

Upvotes: 1

Views: 1163

Answers (1)

G. Grothendieck
G. Grothendieck

Reputation: 270248

Try csvfix as in sqldf FAQ #13 but use the write_dsv's default | symbol rather than ; since there are semicolons in your file:

read.csv.sql("myfile.csv", sep = "|", filter = "csvfix write_dsv")

Upvotes: 1

Related Questions