Skip to content

getting WriteXLS to work or else choosing file encoding of csv files output from R

5 messages · Denis Chabot, Marc Schwartz, Brian Ripley

Original
Denis Chabot
# 
Hi,

Until now, I have always been happy to export dataframes using write.csv2. The only drawback was than they often include accented vowels and Excel on Mac seems to ONLY be able to properly understand MacRoman encoding. So I used OpenOffice instead to open them. 

But for the next little while, colleagues using Windows and who do not have a clue about encoding (and who do not want to know about encoding) need to use my files. My files will have to be in ISOLATIN1 encoding. It is unfortunate that the write.table family of functions does not have a FileEncoding option like the read.table function family.

I thought I'd get away by saving into Excel files instead of csv. I successfully created Excel files using write.xlsx from xlsx package, but this created "monster" files that were in Excel format, opened in Excel, but all accented vowels came out wrong.

I found another package, WriteXLS  that has an encoding option for its WriteXLS function. It does not work on my Mac (running 10.6.5). The provided testPerl() function confirmed what the authors states in the INSTALL file I got with the package:

"Text::CSV_XS seems to be the one most likely to require separate installation, again, with the
exception of ActiveState Perl, as noted above. Thus, in most cases, this may be the only Perl
module that you will need to install beyond what is already provided in your Perl installation
and the WriteXLS package. "

testPerl()
Perl found.

The following Perl modules were not found on this system:

Text::CSV_XS

If you have more than one Perl installation, be sure the correct one was used here.

Otherwise, please install the missing modules. See the package INSTALL file for more information.


In the INSTALL file, instructions for Mac OS X are:
"

Once the tools are installed, you can then install any missing Perl modules by using the command line in a Terminal
such as:

  [sudo -H] cpan -i Text::CSV_XS

The 'sudo -H' is optional. It is required if you want to install the additional modules for yourself and other users 
of the Mac. Otherwise, if you just want to perform a 'local' install for yourself, it is not required."

The tools he refers to are the Developer's tools, which I have (though I did not do anything to keep them "up to date"). But running the above command resulted in this:

sudo -H cpan -i Text::CSV_XS
Password:
CPAN: Storable loaded ok (v2.18)
Going to read /var/root/.cpan/Metadata
  Database was generated on Wed, 01 Dec 2010 22:42:47 GMT
Running install for module 'Text::CSV_XS'
CPAN: Data::Dumper loaded ok (v2.121_14)
'YAML' not installed, falling back to Data::Dumper and Storable to read prefs '/var/root/.cpan/prefs'
Running make for H/HM/HMBRAND/Text-CSV_XS-0.79.tgz
CPAN: Digest::SHA loaded ok (v5.45)
CPAN: Compress::Zlib loaded ok (v2.008)
Checksum for /var/root/.cpan/sources/authors/id/H/HM/HMBRAND/Text-CSV_XS-0.79.tgz ok
x Text-CSV_XS-0.79/
x Text-CSV_XS-0.79/README
x Text-CSV_XS-0.79/t/
x Text-CSV_XS-0.79/t/60_samples.t
x Text-CSV_XS-0.79/t/75_hashref.t
x Text-CSV_XS-0.79/t/70_rt.t
x Text-CSV_XS-0.79/t/15_flags.t
x Text-CSV_XS-0.79/t/21_lexicalio.t
x Text-CSV_XS-0.79/t/00_pod.t
x Text-CSV_XS-0.79/t/65_allow.t
x Text-CSV_XS-0.79/t/12_acc.t
x Text-CSV_XS-0.79/t/20_file.t
x Text-CSV_XS-0.79/t/22_scalario.t
x Text-CSV_XS-0.79/t/51_utf8.t
x Text-CSV_XS-0.79/t/81_subclass.t
x Text-CSV_XS-0.79/t/80_diag.t
x Text-CSV_XS-0.79/t/45_eol.t
x Text-CSV_XS-0.79/t/50_utf8.t
x Text-CSV_XS-0.79/t/util.pl
x Text-CSV_XS-0.79/t/10_base.t
x Text-CSV_XS-0.79/t/46_eol_si.t
x Text-CSV_XS-0.79/t/41_null.t
x Text-CSV_XS-0.79/t/40_misc.t
x Text-CSV_XS-0.79/t/30_types.t
x Text-CSV_XS-0.79/t/55_combi.t
x Text-CSV_XS-0.79/t/76_magic.t
x Text-CSV_XS-0.79/t/01_pod.t
x Text-CSV_XS-0.79/CSV_XS.PL
x Text-CSV_XS-0.79/examples/
x Text-CSV_XS-0.79/examples/speed.pl
x Text-CSV_XS-0.79/examples/csv-check
x Text-CSV_XS-0.79/examples/parser-xs.pl
x Text-CSV_XS-0.79/examples/csv2xls
x Text-CSV_XS-0.79/examples/csvdiff
x Text-CSV_XS-0.79/META.yml
x Text-CSV_XS-0.79/CSV_XS.xs
x Text-CSV_XS-0.79/MANIFEST
x Text-CSV_XS-0.79/files/
x Text-CSV_XS-0.79/files/utf8.csv
x Text-CSV_XS-0.79/files/macosx.csv
x Text-CSV_XS-0.79/ChangeLog
x Text-CSV_XS-0.79/CSV_XS.pm
x Text-CSV_XS-0.79/ppport.h
x Text-CSV_XS-0.79/Makefile.PL
CPAN: File::Temp loaded ok (v0.18)
Warning (usually harmless): 'YAML' not installed, will not store persistent state

  CPAN.pm: Going to build H/HM/HMBRAND/Text-CSV_XS-0.79.tgz

Checking if your kit is complete...
Looks good
Writing Makefile for Text::CSV_XS
Could not read '/var/root/.cpan/build/Text-CSV_XS-0.79-jEKZ8Y/META.yml'. Falling back to other methods to determine prerequisites
  HMBRAND/Text-CSV_XS-0.79.tgz
  make -- NOT OK
Warning (usually harmless): 'YAML' not installed, will not store persistent state
Running make test
  Can't test without successful make
Running make install
  Make had returned bad status, install seems impossible

If you see what I am doing wrong, your help will be most appreciated.

Is there another solution, such as easily (inside my R scripts) temporarily getting R to output csv files  in isolatin1?

Thanks in advance,

Denis Chabot
Marc Schwartz
#
On Dec 1, 2010, at 5:42 PM, Denis Chabot wrote:

            

      
      
      
    

        
Hi Denis,

Sorry that you are having issues with WriteXLS.  If my recollection is at all correct, I believe that the solution is to re-install XCode Tools on your system. The YAML related warnings are ignorable, but the other errors suggest problems with the XCode Tools install on your system. I would be sure to download the latest version of XCode Tools from:

  http://developer.apple.com/technologies/xcode.html

Note that the latest version of XCode is 3.2.5 and includes both OSX and iOS tools packaged together and weighs in at 3.5 Gb...it took around 35 minutes to download over my network connection here.

Let me know if that does not does not help you.

Regards,

Marc Schwartz

  
  


  


      

    

    

      
      

        


  

        

      Brian Ripley
    

    

      
      #
    

  


  

      
On Wed, 1 Dec 2010, Denis Chabot wrote:

        

            

      
      
      
    

        
Most likely CP1252, which is the encoding used on Windows in W. 
European languages, not-quite-a-superset of latin1. (This is all in 
the R manuals, BTW.)

            

      
      
      
    

        
But that's just syntactic sugar.

     file: either a character string naming a file or a connection open
           for writing.  ?""? indicates output to the console.

See ?file for how to specify the encoding of a connection.  Very 
likely you want

con <- file('filename', open="w", encoding = "latin1")

(or "macroman": see ?iconvlist to find encoding names on your OS).

[...]

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Denis Chabot
    

    

      
      #
    

  


  

      
Thank you Prof. Ripley, I'll read ?file and ?iconvlist.

Denis
Le 2010-12-02 ? 01:37, Prof Brian Ripley a ?crit :

            

      
      
      
    

  
  


  


      

    

    

      
      

        


  

        

      Denis Chabot
    

    

      
      #
    

  


  

      
Following Marc's suggestion, I installed the latest Developer's tools and was then able to install the missing Perl script from Terminal, as verified from within R by:

testPerl()
Perl found.

All required Perl modules were found.

I can now use WriteXLS when needed. Thanks Marc.

Denis

Le 2010-12-01 ? 20:45, Marc Schwartz a ?crit :