Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

Introduction

This article is used for the recommendations given in Excel files - CSV format.

The test consists in:

  • First, generate the test cases: an ABAP program (provided in last chapter) generates several test files with the same text, but with different encodings (iso-8859-1, utf-8, utf-16le, utf-16be), with BOM (only for utf encodings) or without BOM, with different field separators (either comma, semicolon, or tabulation).
  • Open each of these files (by double-clicking from Windows Explorer) using 3 computers:
    • Windows XP, Excel 2003, French, decimal separator ",", date format DD/MM/YYYY
    • Windows XP, Excel 2007, English, decimal separator ".", date format MM/DD/YYYY
    • Windows XP, Excel 2007, Russian, decimal separator ",", date format DD.MM.YYYY

All these files contain the same text:

=15522/100→=-15522/100→=15500/100→15522E-2→-50→0↵
="0000100"→"AA→AA↵
BB""BB"→démo français→2004-12-31→03:00:00→20%↵
000100→"0000100"→20041231→12312004→'0000100↵
"155.22"→"-155.22"→"155.22-"→"155,22"→"-155,22"→"155,22-"↵
".5"→",5"→100¥→£10→$10→10€

(→ represents either horizontal tabulation, comma or semicolon, ↵ represents new line)

The 2 first lines correspond to formats valid whatever Excel release and regional and language options are used.
The last lines correspond to formats that may be valid or invalid, so they should be avoided.

Results

In the results below:

  • The test file name indicates the encoding, the presence of the BOM, the field separator.
  • We can see the valid numbers: they are aligned on the right side of cells. The user will have the possibility to use Excel formulas on these cells (sum, etc.)
  • Using a currency character is not a good idea, it may depend on regional settings. $ may not work. So, amount and currency code should be stored in separate fields.

As you can see, there is only one valid format: UTF-16LE with BOM and horizontal tabulation as field separator, though the tab character is not displayed correctly in the middle of "AAAA", and you should avoid the new line character inside a cell as it doesn't work in Excel 2003.

Test file

Windows XP - French - Excel 2003
decimal separator "," date format DD/MM/YYYY

Windows XP - English - Excel 2007
decimal separator "." date format MM/DD/YYYY

Windows XP - Russian - Excel 2007
decimal separator "," date format DD.MM.YYYY

iso88591_comma

iso88591_semi

iso88591_tab

utf8_bom_comma

utf8_bom_semi

utf8_bom_tab

utf8_comma

utf8_semi

utf8_tab

utf16be_bom_comma

Unrecognized format (star)

utf16be_bom_semi

Unrecognized format (star)

utf16be_bom_tab

Unrecognized format (star)

utf16be_comma

Unrecognized format (star)

utf16be_semi

Unrecognized format (star)

utf16be_tab

Unrecognized format (star)

utf16le_bom_comma

utf16le_bom_semi

utf16le_bom_tab

utf16le_comma

Unrecognized format (star)

utf16le_semi

Unrecognized format (star)

utf16le_tab

Unrecognized format (star)

(star) In Excel 2003, before displaying CSV having encodings utf-16le without BOM, or utf-16be with and without BOM, Excel displays a popup which informs that format is not recognized, in French here:

Test files generator

Be careful, some of the characters in the source below (€, £) may not be accepted if you use a non-Unicode SAP system.

REPORT ztestcsv.
DATA g_text TYPE string.
 DATA:
   iso_8859_1 TYPE REF TO cl_abap_conv_out_ce,
   utf_8      TYPE REF TO cl_abap_conv_out_ce,
   utf_16be   TYPE REF TO cl_abap_conv_out_ce,
   utf_16le   TYPE REF TO cl_abap_conv_out_ce.

 iso_8859_1 = cl_abap_conv_out_ce=>create(
          encoding = '1100' ).
 utf_8 = cl_abap_conv_out_ce=>create(
          encoding = '4110' ).
 utf_16be = cl_abap_conv_out_ce=>create(
          encoding = '4102' ).
 utf_16le = cl_abap_conv_out_ce=>create(
          encoding = '4103' ).

 concatenate
`=15522/100µ=-15522/100µ=15500/100µ15522E-2µ-50µ0`
`="0000100"µ"AAµAA`
`BB""BB"µdémo françaisµ2004-12-31µ03:00:00µ20%`
`000100µ"0000100"µ20041231µ12312004µ'0000100`
`"155.22"µ"-155.22"µ"155.22-"µ"155,22"µ"-155,22"µ"155,22-"`
`".5"µ",5"µ100¥µ£10µ$10µ10€`
INTO g_text SEPARATED BY cl_abap_char_utilities=>newline.

DATA l_xstring_empty TYPE xstring.
PERFORM create_files USING g_text utf_8 'utf8_' l_xstring_empty.
PERFORM create_files USING g_text utf_8 'utf8_bom_' cl_abap_char_utilities=>BYTE_ORDER_MARK_UTF8.
PERFORM create_files USING g_text utf_16be 'utf16be_' l_xstring_empty.
PERFORM create_files USING g_text utf_16be 'utf16be_bom_' cl_abap_char_utilities=>BYTE_ORDER_MARK_BIG.
PERFORM create_files USING g_text utf_16le 'utf16le_' l_xstring_empty.
PERFORM create_files USING g_text utf_16le 'utf16le_bom_' cl_abap_char_utilities=>BYTE_ORDER_MARK_LITTLE.
PERFORM create_files USING g_text iso_8859_1 'iso88591_' l_xstring_empty.

FORM create_files USING i_text i_enc TYPE REF TO cl_abap_conv_out_ce i_filename i_xstring_bom TYPE xsequence.
PERFORM create_files_2 USING i_text i_enc i_filename i_xstring_bom 'tab' cl_abap_char_utilities=>HORIZONTAL_TAB.
PERFORM create_files_2 USING i_text i_enc i_filename i_xstring_bom 'comma' ','.
PERFORM create_files_2 USING i_text i_enc i_filename i_xstring_bom 'semi' ';'.
ENDFORM.

FORM create_files_2 USING i_text i_enc TYPE REF TO cl_abap_conv_out_ce i_filename i_xstring_bom TYPE xsequence i_filename2 i_sep.
DATA l_string TYPE string.
DATA l_filename TYPE string.
DATA l_xstring TYPE xstring.
l_string = i_text.
REPLACE ALL OCCURRENCES OF `µ` IN l_string WITH i_sep.
IF i_sep = '.'. REPLACE ALL OCCURRENCES OF `!` IN l_string WITH ','.
ELSE. REPLACE ALL OCCURRENCES OF `!` IN l_string WITH '.'.
ENDIF.
 i_enc->convert( EXPORTING data = l_string
                IMPORTING buffer = l_xstring ).
CONCATENATE 'c:\' i_filename i_filename2 '.csv' INTO l_filename.
CONCATENATE i_xstring_bom l_xstring INTO l_xstring IN BYTE MODE.
PERFORM write_bin_file USING l_filename l_xstring.
ENDFORM.

FORM write_bin_file
      USING
        i_filename      TYPE string
        i_file_xstring  TYPE xstring.
  TYPES xx(50) TYPE x.
  DATA lt_xstring TYPE TABLE OF xx.
  DATA l_length TYPE i.
  CALL METHOD cl_swf_utl_convert_xstring=>xstring_to_table
    EXPORTING
      i_stream = i_file_xstring
    IMPORTING
      e_table  = lt_xstring
    EXCEPTIONS
      OTHERS   = 3.
  l_length = XSTRLEN( i_file_xstring ).
  CALL METHOD cl_gui_frontend_services=>gui_download
    EXPORTING
      bin_filesize = l_length
      filename     = i_filename
      filetype     = 'BIN'
    CHANGING
      data_tab     = lt_xstring
    EXCEPTIONS
      OTHERS       = 3.
ENDFORM.                    "write_bin_file