Data export problem with R

General
1

Hello. I am new to rstudio. The problem I am experiencing is that the export file is exported as below with a " in the beginning and end of each line. I would like to export this file without the beginning and ending ". Can someone assist me with resolving this?

"J180XXX|XXXYEAR|XXXFREY||XXXYEAR, XXXFREY|XXXX|NoHousingAssaigned|NA|NA|NoHousingAssaigned-NA-NA|A"

'''r

#Import Libraries

library(tidyverse)
library(lubridate)
library(readxl)
library(writexl)
library(openxlsx)
library(eeptools)
library(haven)
library(car)
library(epiR)
library(MatchIt)
library(RODBC)
library(sqldf)
library(taskscheduleR)
library(flexdashboard)
library(data.table)
library(highcharter)
library(htmltools)
library(viridis)
library(dplyr)
library(gt)
library(janitor)

####Connect to ODBC####

OMS_Connection<-odbcConnect('OMSXX',
uid = 'XX',
pwd = 'XX',
rows_at_time = 1)

####OMS Extractions####

Query_BOOKING<-sqlQuery(OMS_Connection,
'SELECT * FROM dbo.BOOKING',
as.is = TRUE)
Query_BOOKING<-as.data.frame(Query_BOOKING)

Query_HA<-sqlQuery(OMS_Connection,
'SELECT * FROM dbo.HA',
as.is = TRUE)
Query_HA<-as.data.frame(Query_HA)

####Close ODBC Connection####

odbcClose(OMS_Connection)

####Clean BOOKING_Data####

BOOKING_Data<-Query_BOOKING[,c("SYSID",
"PIN",
"LASTNAME",
"FIRSTNAM",
"MIDDLE",
"COMDATE",
"ORIG_RELDATE",
"RELDATE",
"RELSTDT",
"JLOCAT",
"OBSOLETE")]

BOOKING_Data$JLOCAT<-gsub(' ','',BOOKING_Data$JLOCAT)
BOOKING_Data$JLOCAT<-toupper(BOOKING_Data$JLOCAT)

BOOKING_Data$OBSOLETE<-gsub(' ','',BOOKING_Data$OBSOLETE)
BOOKING_Data$OBSOLETE<-toupper(BOOKING_Data$OBSOLETE)

BOOKING_Data$Blank_Reldate<-ifelse(is.na(BOOKING_Data$RELDATE),
'Blank',
'Not Blank')
BOOKING_Data$Blank_Relst<-ifelse(is.na(BOOKING_Data$RELSTDT),
'Blank',
'Not Blank')

BOOKING_Data<-BOOKING_Data%>%
filter(JLOCAT=='MAIN')%>%
filter(OBSOLETE=='N')
BOOKING_Data$Current<-ifelse(BOOKING_Data$Blank_Reldate=='Blank'|
BOOKING_Data$Blank_Relst=='Not Blank',
'Current',
'Released')

table(BOOKING_Data$Current)

BOOKING_Data<-BOOKING_Data%>%
filter(Current=='Current')

####Clean HA_Data###

HA_Data<-Query_HA[,c("SYSID",
"SECTION",
"BLOCK",
"CELL")]

###merge Booking and HA Data####

BOOKING_Data$SYSID<-gsub(' ','',BOOKING_Data$SYSID)
HA_Data$SYSID<-gsub(' ','',HA_Data$SYSID)

BOOKING_Data<-merge(by='SYSID',
BOOKING_Data,
HA_Data,
all.x = TRUE)

#Clean null housing data
BOOKING_Data$SECTION[which(is.na(BOOKING_Data$SECTION))]<-'No Housing Assaigned'

####Create Custom Columns####

BOOKING_Data$Full Name<-paste(BOOKING_Data$LASTNAME,
', ',
BOOKING_Data$FIRSTNAM,
' ',
BOOKING_Data$MIDDLE,
sep = '')
BOOKING_Data$Full Name<-gsub(' ',' ',BOOKING_Data$Full Name)

BOOKING_Data$Facility<-'MHOC'

BOOKING_Data$Housing Location<-paste(BOOKING_Data$SECTION,
'-',
BOOKING_Data$BLOCK,
'-',
BOOKING_Data$CELL)
BOOKING_Data$Housing Location<-gsub(' ','',BOOKING_Data$Housing Location)

BOOKING_Data$Active<-'A'

####Limit Data to Relevant Columns####

BOOKING_Data<-BOOKING_Data[,c("PIN",
"LASTNAME",
"FIRSTNAM",
"MIDDLE",
"Full Name",
"Facility",
"SECTION",
"BLOCK",
"CELL",
"Housing Location",
"Active")]

###pipe Deliminated Format####

BOOKING_Data$PIN<-gsub(' ','',BOOKING_Data$PIN)
BOOKING_Data$LASTNAME<-gsub(' ',' ',BOOKING_Data$LASTNAME)
BOOKING_Data$FIRSTNAM<-gsub(' ',' ',BOOKING_Data$FIRSTNAM)
BOOKING_Data$MIDDLE<-gsub(' ',' ',BOOKING_Data$MIDDLE)
BOOKING_Data$Full Name<-trimws(BOOKING_Data$Full Name)
BOOKING_Data$Facility<-gsub(' ','',BOOKING_Data$Facility)
BOOKING_Data$SECTION<-gsub(' ','',BOOKING_Data$SECTION)
BOOKING_Data$BLOCK<-gsub(' ','',BOOKING_Data$BLOCK)
BOOKING_Data$CELL<-gsub(' ','',BOOKING_Data$CELL)
BOOKING_Data$Active<-gsub(' ','',BOOKING_Data$Active)

BOOKING_Data$Extraction<-paste(BOOKING_Data$PIN,
BOOKING_Data$LASTNAME,
BOOKING_Data$FIRSTNAM,
BOOKING_Data$MIDDLE,
BOOKING_Data$Full Name,
BOOKING_Data$Facility,
BOOKING_Data$SECTION,
BOOKING_Data$BLOCK,
BOOKING_Data$CELL,
BOOKING_Data$Housing Location,
BOOKING_Data$Active,
sep = "|")

BOOKING_Data<-as.data.frame(BOOKING_Data$Extraction)

####Write File####

write.table(BOOKING_Data,
'\\omsutl\transfer\Export PB Com.csv',
row.names = FALSE,
col.names = FALSE)
'''

The problem here is that the export file is exported as below with a " in the beginning and end of each line. I would like to export this file without the beginning and end quote. How do I accomplish this?

"J180XXX|XXXYEAR|XXXFREY||XXXYEAR, XXXFREY|XXXX|NoHousingAssaigned|NA|NA|NoHousingAssaigned-NA-NA|A"

2

The default separator of write.table() is a space. If your data are separated with |, you need to set the sep parameter or write.table() to "|".

write.table(BOOKING_Data,
'\\omsutl\transfer\Export PB Com.csv',
row.names = FALSE,
col.names = FALSE, sep = "|")

You mat also want to set quote = FALSE.

3

That was it! Thank you so much!

4

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.