Simple csv table export in ABAP for HANA
As you all know the import of a table via csv file is easy and straightforward in SAP HANA Studio (see e.g. http://www.saphana.com/docs/DOC-2191 from SAP HANA Academy).
The export of a table from SAP Netweaver to a csv file is unfortunately not so straightforward. Therefore I wrote a little ABAP program which simplifies the table export as csv file. This is a screenshot of the selection screen:
You can specify the table and the csv filename. Furthermore you have the following options:
- Should the table header line be written
- Change the field separator
- Should the field content be enclosed (useful for fields which contain the field separator within the table content)
- Client in the csv file (useful if source client differs from target client in HANA)
- Code page (empty value means frontend codepage, other value might be applicable depending on special characters in table and expectations within HANA)
Here comes the source code. Please drop me a little comment below, if the program could help you.
*&———————————————————————*
*& Report ZKK_TABLE_DOWNLOAD
*&
*&———————————————————————*
*&
*& Karsten Kötter, 2013-03
*& cbs Corporate Business Solutions Unternehmensberatung GmbH
*& karsten.koetter@cbs-consulting.de
*&
*&———————————————————————*
program zkk_table_download.
type–pools: abap.
*———————————————————————-*
* CLASS lcl_table_download DEFINITION
*———————————————————————-*
*
*———————————————————————-*
class lcl_table_download definition.
public section.
class–methods:
browse_local_filesystem importing i_table type tabname
returning value(e_file) type string.
methods:
initialize,
set_parameter importing i_table type tabname
i_file type string
i_separator type char01 default ‘;’
i_header type abap_bool default abap_true
i_file_client type mandt default sy–mandt
i_enclosing type char01 optional
i_codepage type cpcodepage optional,
download_table.
protected section.
types: tyt_csv type standard table of string with default key.
data:
m_table type tabname,
m_file type string,
m_header_incl type abap_bool,
m_change_client type abap_bool,
m_separator type char01,
m_enclosing type char01,
m_file_client type mandt,
m_codepage type abap_encod,
mt_dd03l type standard table of dd03l.
methods:
get_header_line returning value(es_csv) type string,
download_block changing it_data type table,
convert_to_csv importing is_line type data
returning value(es_csv) type string.
endclass. “lcl_table_download DEFINITION
*———————————————————————-*
* CLASS lcl_table_download IMPLEMENTATION
*———————————————————————-*
*
*———————————————————————-*
class lcl_table_download implementation.
*———————————————————————-*
*
*———————————————————————-*
method browse_local_filesystem.
data:
l_filename type string,
l_path type string,
l_file type string.
l_file = i_table.
replace all occurrences of ‘/’ in l_file with ”.
concatenate sy–sysid ‘-‘ sy–datum ‘-‘ l_file ‘.csv’ into l_file.
cl_gui_frontend_services=>file_save_dialog(
exporting
window_title = ‘Save table as csv file’
default_extension = ‘csv’
default_file_name = l_file
* with_encoding =
file_filter = ‘*.csv’
initial_directory = ‘D:\UserData’
prompt_on_overwrite = ‘X’
changing
filename = l_filename
path = l_path
fullpath = e_file
* user_action =
* file_encoding =
exceptions
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
others = 4 ).
if sy–subrc <> 0.
message ‘File selection failed’ type ‘E’.
endif.
endmethod. “browse_local_filesystem
*———————————————————————-*
*
*———————————————————————-*
method initialize.
clear:
m_table,
m_file,
m_separator,
m_header_incl,
m_file_client,
m_enclosing,
mt_dd03l.
endmethod. “initialize
*———————————————————————-*
*
*———————————————————————-*
method set_parameter.
data:
lrs_dd03l type ref to dd03l.
m_table = i_table.
m_file = i_file.
m_separator = i_separator.
m_header_incl = i_header.
m_file_client = i_file_client.
m_enclosing = i_enclosing.
m_codepage = i_codepage.
* Get fields of table (reading DD03L seems simpler than RTTI, think of includes…)
select * from dd03l
into corresponding fields of table mt_dd03l
where tabname = m_table
and comptype = ‘E’
order by position.
* Change client in file, if table is not cross-client and target client differs
read table mt_dd03l reference into lrs_dd03l index 1.
if lrs_dd03l->rollname = ‘MANDT’ and
m_file_client <> sy–mandt.
m_change_client = abap_true.
else.
m_change_client = abap_false.
endif.
endmethod. “set_table
*———————————————————————-*
*
*———————————————————————-*
method download_table.
data:
lrt_data type ref to data,
lrs_data type ref to data,
lt_csv type tyt_csv,
ls_csv type string.
field–symbols:
<fs_table> type standard table,
<fs_line> type data.
* Create internal table dynamically
create data lrt_data type standard table of (m_table).
assign lrt_data->* to <fs_table>.
create data lrs_data type (m_table).
assign lrs_data->* to <fs_line>.
* Select data from db
select * from (m_table)
into table <fs_table>.
* Build header line
if m_header_incl = abap_true.
ls_csv = get_header_line( ).
append ls_csv to lt_csv.
endif.
* Convert structured data to csv
loop at <fs_table> into <fs_line>.
ls_csv = convert_to_csv( <fs_line> ).
append ls_csv to lt_csv.
endloop.
* save file
download_block( changing it_data = lt_csv ).
endmethod. “download_table
*———————————————————————-*
*
*———————————————————————-*
method get_header_line.
data:
lrs_dd03l type ref to dd03l.
* Build header line
loop at mt_dd03l reference into lrs_dd03l .
if sy–tabix = 1.
es_csv = lrs_dd03l->fieldname.
else.
concatenate es_csv m_separator lrs_dd03l->fieldname into es_csv.
endif.
endloop.
endmethod. “get_header_line
*———————————————————————-*
*
*———————————————————————-*
method convert_to_csv.
data:
l_field_content type string,
l_enclosing_esc type string.
field–symbols: <fs_field> type data.
do.
assign component sy–index of structure is_line to <fs_field>.
if sy–subrc <> 0.
exit.
endif.
move <fs_field> to l_field_content.
condense l_field_content.
* Change target client for download
if sy–index = 1 and ” First field of structure?
m_change_client = abap_true. ” Change target client?
l_field_content = m_file_client.
endif.
* Surround each field with enclosing and escape enclosing
concatenate ‘\’ m_enclosing into l_enclosing_esc.
if m_enclosing is not initial.
replace all occurrences of m_enclosing in l_field_content with l_enclosing_esc.
concatenate m_enclosing l_field_content m_enclosing into l_field_content.
endif.
* Concatenate the fields to one single line separated by separator
if sy–index = 1.
es_csv = l_field_content.
else.
concatenate es_csv l_field_content into es_csv separated by m_separator.
endif.
enddo.
endmethod. “convert_to_csv
*———————————————————————-*
*
*———————————————————————-*
method download_block.
call function ‘GUI_DOWNLOAD’
exporting
filename = m_file
codepage = m_codepage
tables
data_tab = it_data.
endmethod. “download_block
endclass. “lcl_table_download IMPLEMENTATION
*———————————————————————-*
*
*———————————————————————-*
*
*———————————————————————-*
selection-screen begin of block b00 with frame title text–b00.
parameters:
table type dd02l–tabname,
file type string lower case.
selection-screen end of block b00.
selection-screen begin of block b01 with frame title text–b01.
parameters:
headincl type abap_bool default abap_true as checkbox,
separatr type c length 1 default ‘;’,
enclosng type c length 1 default ”,
fileclnt type mandt default sy–mandt,
codepage type cpcodepage default space matchcode object h_tcp00.
selection-screen end of block b01.
*———————————————————————-*
*
*———————————————————————-*
at selection-screen on value-request for file.
file = lcl_table_download=>browse_local_filesystem( i_table = table ).
*———————————————————————-*
*
*———————————————————————-*
at selection-screen on table.
data: ls_dd02l type dd02l.
select single * from dd02l into ls_dd02l where tabname = table.
if sy–dbcnt = 0.
message ‘Table does not exist’ type ‘E’.
clear table.
endif.
*———————————————————————-*
*
*———————————————————————-*
*
*———————————————————————-*
start–of–selection.
data:
lro_table_download type ref to lcl_table_download.
create object lro_table_download.
lro_table_download->initialize( ).
lro_table_download->set_parameter( i_table = table
i_file = file
i_separator = separatr
i_header = headincl
i_enclosing = enclosng
i_file_client = fileclnt
i_codepage = codepage ).
lro_table_download->download_table( ).
Thanks Karsten for sharing.
There is one issue I always had when exporting data from microsoft sql server as .csv:
If the seperator is ; and any cell contains a ; the import will fail.
Thats why we enclose the content by " (for example).
But enclosing does not really solve the problem directly: If a cell contains a ", the import will fail, unless we escape all " characters...
As far as I can see, you don't escape the enclosing character, do you?
Surround each field with enclosing
if m_enclosing is not initial.
concatenate m_enclosing l_string m_enclosing into l_string.
endif.
Microsoft SQL Server escapes it as "", but HANA needs \" so my suggestion is to replace m_enclosing by \m_enclosing first...
I hope you get the point.
Kind Regards,
Fabian
Hello Fabian,
Thanks, good point, you are right!
I'll fix this (might take some time due to my soon coming vacation).
Thanks & Best regards,
Karsten
Fixed in coding.
Thanks again to Fabian
Are you worrying about character encoding?
Usually this something to worry about when exporting from an unicode database, you need to understand in which encoding have the special chars been encoded in, and export in an encoding the application that will consume the data is expecting.
I'd suggest to add an "encoding" parameter for the desired encoding of the output.
Hello Henrique,
nice to hear from you again!
Encoding is a good point, I added an parameter as you suggested and updated the document and the coding.
Thanks a lot & best regards,
Karsten
Thanks a Lot, Karsten !!
Great post, very usefull...
Great tool!
Which changes you have apply to the code in order to allow the report to save the file in the server while executing it in background?
Fernando.
Hello Fernando,
execution in background means that the download could not be done to the client, but the file needs to be downloaded to server filesystem.
Therefore you have to replace (or enhance) the coding with GUI_DOWNLOAD and browse_local_filesystem and use OPEN DATASET & Co.
Maybe I'll implement this, but don't expect this to happen soon...
Best regards,
Karsten
Thanks! I have added some code and it works great!
Fernando.
Thanks Karsten.
Hello Sir,
The doc is very useful...
Thank You.. 🙂