CODEWITHSUNDEEP
rxlsxxls
gaut
gaut

Reputation: 5958

Wrong format saving XLS files: automatic conversion to numbers

I have a string that I need to save as-is in XLS format, but the WriteXLS function seems to always convert to a number. The error is also present when opening the xls manually. How can I fix this?

nb <- data.frame("92343E102", stringsAsFactors = F)
WriteXLS::WriteXLS(nb, "testdf.xls")
readxl::read_xls("testdf.xls")
> read_xls("testdf.xls")
# A tibble: 1 x 1
  X.92343E102.
         <dbl>
1     9.23e106

Expected result: 92343E102

I would need to do so without having python installed if possible, so dataframes2xls is not an option for me - appreciate the attemp anyway

Upvotes: 1

Views: 361

Answers (4)

gaut
gaut

Reputation: 5958

Ok finally I think found a way to avoid XLS conversion of strings like "12E123" to scientific double, which requires no excel interaction using XLConnect 

nb <- data.frame(as.character('92343E102'), stringsAsFactors = F)
WriteXLS::WriteXLS(nb, "testdf.xls")
readxl::read_xls("testdf.xls")
wb <- XLConnect::loadWorkbook("testdf.xls")
XLConnect::createSheet(wb, name="newsheet")
XLConnect::writeWorksheet(wb, nb, sheet = "newsheet")
XLConnect::saveWorkbook(wb)
readxl::read_xls("testdf.xls", sheet=1) #converted string to wrong number
readxl::read_xls("testdf.xls", sheet=2) # success! string stays string

result:

> readxl::read_xls("testdf.xls", sheet=1)
# A tibble: 1 x 1
  as.character..92343E102..
                      <dbl>
1                  9.23e106
> readxl::read_xls("testdf.xls", sheet=2)
# A tibble: 1 x 1
  as.character..92343E102..
  <chr>                    
1 92343E102

Of course since I only have 32bit JAVA at work I might have to switch to R32 for this, or write 32bit-R routines to correctly save my XLS files..

One thing at a time... Hope this helps someone

Upvotes: 0

IRTFM
IRTFM

Reputation: 263481

Install the writexl package:

 install.packages("devtools", dependencies=TRUE)
# devtools has a _lot_ of dependencies
# it also has a bunch of system tool requirements
 devtools::install_github("ropensci/writexl")
#make a copy of iris
 tmp <- iris
# set [1,1] to your string:
 tmp[1,1] <- "92343E102"  # makes that column 'character'
 tmp2 <- writexl::write_xlsx(tmp)
 readxl::read_xlsx(tmp2)

# A tibble: 150 x 5
   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
   <chr>              <dbl>        <dbl>       <dbl> <chr>  
 1 92343E102            3.5          1.4         0.2 setosa 
 2 4.9                  3            1.4         0.2 setosa 
 3 4.7                  3.2          1.3         0.2 setosa 
 4 4.6                  3.1          1.5         0.2 setosa 
 5 5                    3.6          1.4         0.2 setosa 
 6 5.4                  3.9          1.7         0.4 setosa 
 7 4.6                  3.4          1.4         0.3 setosa 
 8 5                    3.4          1.5         0.2 setosa 
 9 4.4                  2.9          1.4         0.2 setosa 
10 4.9                  3.1          1.5         0.1 setosa 
# ... with 140 more rows

Upvotes: 2

Ian Wesley
Ian Wesley

Reputation: 3624

If you are fine saving this as a csv, then opening it in Excel. One option is to use the Get External Data from Text Wizard on the Data Tab. Then select your csv file, configure the appropriate options, and on the third step of the wizard select import as text on the column that has the text in it, as pictured below. Then you can save this as an .xls and open it again, with out Excel changing the data type to scientific.

enter image description here

Upvotes: 1

duckmayr
duckmayr

Reputation: 16940

I would suggest to you to use dataframes2xls::write.xls() instead: 

# Make a sample dataframe:
nb <- data.frame(A = "92343E102", B = 92343E102, stringsAsFactors = F)
WriteXLS::WriteXLS(nb, "testdf.xls") # Write out using WriteXLS() ...
readxl::read_xls("testdf.xls") # Doesn't work, per your post
#> # A tibble: 1 x 2
#>          A        B
#>      <dbl>    <dbl>
#> 1 9.23e106 9.23e106
# Maybe we can specify what kind of column it is when reading in the data?
readxl::read_xls("testdf.xls", col_types = "text")
# Still doesn't work, it must be the writer
#> # A tibble: 1 x 2
#>   A           B          
#>   <chr>       <chr>      
#> 1 -2147483648 -2147483648
dataframes2xls::write.xls(nb, "testdf2.xls") # So, try a different writer
readxl::read_xls("testdf2.xls")#, col_types = "text") # Works!
#> # A tibble: 1 x 2
#>   A                      B
#>   <chr>              <dbl>
#> 1 "\"92343E102\"" 9.23e106

Created on 2018-11-02 by the reprex package (v0.2.1)

Upvotes: 1

Related Questions