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 |
Windows XP - English - Excel 2007 |
Windows XP - Russian - Excel 2007 |
|---|---|---|---|
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 |
|
|
utf16be_bom_semi |
Unrecognized format |
|
|
utf16be_bom_tab |
Unrecognized format |
|
|
utf16be_comma |
Unrecognized format |
|
|
utf16be_semi |
Unrecognized format |
|
|
utf16be_tab |
Unrecognized format |
|
|
utf16le_bom_comma |
|
|
|
utf16le_bom_semi |
|
|
|
| utf16le_bom_tab |
|
|
|
utf16le_comma |
Unrecognized format |
|
|
utf16le_semi |
Unrecognized format |
|
|
utf16le_tab |
Unrecognized format |
|
|
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