How to retain leading zeroes when exporting data from R to excel

[Nikki1]

I am running a SQL query in R, where the MemberID column has 9 or 10 characters. When I manually copy and paste this in excel as text format, the MemberID column retains leading zero for those members who have 9 digits. But when I automate R to run script and export data to excel, it doesn't retain leading zeroes in the MemberID column. It is a numeric column and I tried changing it to a character but it doesn't work. Is there a way to keep the leading zeroes in excel?

Data <- sqlQuery(odbcChannel,
"Select distinct
PlanName,
MemberName,
MemberID,
PlanID
From Member
where PlanID ='AR'")

Data$MemberID <-as.character(Data$MemberID)

WB<-createWorkbook()
addWorksheet(WB,sheetname="Plan_AR")
writeData(WB,"Plan_AR",Data)
saveWorkbook(WB,file="MemberByPlan", overwrite =TRUE)

[FJCC]

Without access to your data, I can't be sure I'm addressing the same problem. The following code writes an Excel file with the leading zero preserved. Does it work for you?

library(openxlsx)
Data <- data.frame(ID=c("000123","001234","012345","123456"))
WB<-createWorkbook()
addWorksheet(WB,sheetName="Plan_AR")
writeData(WB,"Plan_AR",Data)
saveWorkbook(WB,file="MemberByPlan.xlsx", overwrite =TRUE)

If you still need help, please post the output of

dput(head(Data))

Put a line with three back ticks just before and after your posted output, like this
```
Pasted output
```

[Nikki1]

@FLCC Thank you, your code works but I am not sure how to implement this in my script. I am using 'as.character' to change the values of MemberID from numeric to character, though it changes the format to character but doesn't add quotes to the values. Padding with zero is not an option in this case.

[FJCC]

Please post the output of

dput(head(Data))

Run that code using the version of Data before you use as.character() to change MemberID.
Put a line with three back ticks just before and after your posted output, like this

Pasted output

[Nikki1]

Here is the output of just the MemberID column. The problem is some of the MemberID's are 10 digits numeric values, some are 9 digits but with leading zeroes and some 9 digits without leading zero in the SQL output. The excel export should be the same.

dput(head(Data))
structure(list(MEMBERID  = c(2553511594, 
2563725982, 2503234915, 2503343822, 2503354803, 2212280805)), row.names = c(NA, 
6L), class = "data.frame")

SQL output

[FJCC]

Thanks for posting the dput() output. You can see that the MemberID values are already numbers there. You can tell because there are not quotes around the values. Here is a simple example where I make one numeric column and one character column. In the dput() output, the character variable keeps its leading zero but the numeric one does not.

DF <- data.frame(A = c(123456, 0987654),
                  B = c("123456", "0987654"))
dput(DF)
structure(list(A = c(123456, 987654), B = c("123456", "0987654"
)), class = "data.frame", row.names = c(NA, -2L))

Somewhere earlier in your code, the MemberID column has been converted to numbers and the leading zeros have been dropped. I expect that the process of writing to Excel is not dropping the zeros; they are not there anymore.

I think the way to solve this problem is to find where the conversion to numbers is happening. Do you know what the column type is in the database? I would expect it to be VARCHAR but it would be good to confirm that. If you run

Data <- sqlQuery(odbcChannel,
"Select distinct
PlanName,
MemberName,
MemberID,
PlanID
From Member
where PlanID ='AR'")

str(Data)

what is the column type of MemberID?

[Nikki1]

it's numeric

str(Data)
'data.frame':	100 obs. of 1 variables:
 $ MEMBERID  : num  7.51e+09 5.80e+09 4.03e+08 8.60e+09 2.81e+09 ...

[Nikki1]

In the database it is varchar. MemberID varchar(100),null).

[FJCC]

OK, I am now dealing with something I have not done before, so my help might be limited. Try adding as.is = TRUE to your sqlQuery call, like this:

Data <- sqlQuery(odbcChannel,
"Select distinct
PlanName,
MemberName,
MemberID,
PlanID
From Member
where PlanID ='AR'", as.is = TRUE)

Is MemberID now characters or is it still numeric?

[Nikki1]

This worked. The MemberID is character, now I have the same output as SQL. @FJCC Thank you so much!!