How to write out an excel file that will retain leading 0's in id columns

[hasibul212003]

mydf <- data.frame(x=c('01','02','03'),age=sample(1:100,3))

mydf$x <- formatC(mydf$x,format="s")

write.table(mydf,file='mydf.xls',quote = T,row.names=F,sep="\t")

I have a dataset with id's that have leading 0's in the id column. I need to write the data to an excel file that will retain the leading 0's. The above code is a simulation illustrating what I have tried so far. I t does not retain leading 0's in the id column.

Any suggestions would be greatly appreciated.

[prubin]

Try xlsx::write.xlsx(mydf, file='mydf.xls', row.names = FALSE).

[hasibul212003]

Thank you so much. That worked flawlessly.

[Scoco]

If you view the output file from your original code in any editor other than Excel you should see the leading zeros (try right-clicking your file and selecting 'Edit' to view it in Notepad).

Excel always tries to convert strings which look like they could be numbers to numbers, so if your last "Text to Columns" operation had tab as a delimiter it will parse it automatically with "General" format and convert your "01", "02", and "03" to 1, 2, and 3. Sadly, IMHO, this happens in spite of the quotes you have written, and even if you preface with ' (apostrophe) which will prevent conversion to numeric when typed into Excel but not when reading a file.

To see your leading zeroes in Excel, first save it with an extension other than ".xls", e.g. ".csv" (even though you are using tab as the delimiter). To be safe, perform a Text to Columns (from the Data menu) in Excel on something else without Tab as a delimiter before opening this file, and do the Text to Columns manually, setting the data type for the columns with leading zeros to "Text" in Step 3 of 3.

By the way, the mydf$x <- formatC(mydf$x,format="s") is unnecessary: the quotes in c('01', '02', '03') ensure it is a character vector.