Understanding CSV files and their handling in ABAP

112749,017

In many ABAP developments, we use CSV files and sometimes there is confusion regarding CSV itself. Is it just a text file with values separated by commas ?

Let’s look at semantics of the various components involved so we have a vocabulary to work with.

Separator: Demarcates between two fields – so it will be a comma ‘,’ for CSVs.

Delimiter: It signifies limits of things, where it begins and ends. e.g. “Test String” has two delimiters, both double quote characters. Many CSVs can have double quotes as delimiters when comma values are to be placed as text.

Terminator : Indicates end of sequence. For CSV, we can think of newline as the terminator.

So if we have confusion about whether commas or double quotes are allowed inside data, looking at the CSV specification:

The de facto standard for CSV is here in case you want to read the full standard.

http://tools.ietf.org/html/rfc4180

Definition of the CSV Format ( points 1 – 7 are from CSV standard )

1. Each record is located on a separate line, delimited by a line break.

2. The last record in the file may or may not have an ending line break.

3. There maybe an optional header line appearing as the first line of the file with the same format as normal record lines.

4. Within the header and each record, there may be one or more fields, separated by commas. Each line should contain the same

number of fields throughout the file. Spaces are considered part of a field and should not be ignored. The last field in the

record must not be followed by a comma.

5. Each field may or may not be enclosed in double quotes (however some programs, such as Microsoft Excel, do not use double quotes

at all). If fields are not enclosed with double quotes, then double quotes may not appear inside the fields.

6. Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes.

7. If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with

another double quote.

In my experience, point 7 is where we get tripped the most. CSV stands as comma separated values leading to the impression that commas are the separator and given that excel doesn’t put commas, it can start to get confusing.

So looking at some examples

Basic Example:

10, Vikas , Sydney

Data with separator / delimiter inside them.

“11”, “Vikas”, “Sydney, AU” <– Data containing comma

“12”, “Vikas”, “Sydney, “NSW” AU” <– Data containing comma and quotes in data

Handling in ABAP:

I’m focusing on reading the files as that’s where we face issues. The file can be uploaded from user’s desktop or read from the application server.

1) Write your own code:

This can be easiest to start with but can start to get complicated with time.

Get data as a string, split at comma.

split lv_data at ‘,’ into lw_struct-test1 lw_struct-test2 lw_struct-test3.

Drawbacks:

a) This won’t work if we have data with separator, terminator or delimiter ( so no commas, double-quotes or newline within data ).

b) The code will need to be updated if the file format changes – say we need to add another field test4. The code then changes to :

split lv_data at ‘,’ into lw_struct-test1 lw_struct-test2 lw_struct-test3 lw_struct-test4.

2) Read the file using KCD_CSV_FILE_TO_INTERN_CONVERT

CALL FUNCTION ‘KCD_CSV_FILE_TO_INTERN_CONVERT’

EXPORTING

i_filename = ‘C:\Temp\Upload.csv’

i_separator = ‘,’

TABLES

e_intern = gt_intern

EXCEPTIONS

upload_csv = 1

upload_filetype = 2.

Drawbacks

a) The file can be read only from presentation server/ desktop.

b) If a CSV file exists with double quotes, the last field is left with double quotes.

c) In case the file is to be read from application server, we need to read the code inside this FM and write some custom logic.

3) Use RTTI and dynamic programming along with FM RSDS_CONVERT_CSV .

It works but has lots of code . You can have a look at the code in this GIST.

CSV_Upload_long_process

In summary the steps are :

– Get structre of destination table using RTTI

– Create field catalog

– Create a dynamic table for field catalog values

– Create dynamic table lines

– Process Raw CSV data

– Store CSV files into dynamic table

Drawback:

a) Relatively long code leading to results especially if you have to program it from scratch.

Advantage:

a) Code is free from the target table format. If a new field is to be added, just update the structure for table type z_data_tty

4) Use class CL_RSDA_CSV_CONVERTER .

So the call becomes very straight forward – Instantiate the class with the separator and delimiter values. For a normal CSV, leave them as default .

* Instantiate the CSV object

call method cl_rsda_csv_converter=>create

* EXPORTING

* i_delimiter = C_DEFAULT_DELIMITER

* i_separator = C_DEFAULT_SEPARATOR

receiving

r_r_conv = lo_csv

* Process records

loop at lt_upload_data into lv_data.

CALL METHOD lo_csv->csv_to_structure

EXPORTING

i_data = lv_data

IMPORTING

e_s_data = lw_struct.

That’s It !

Advantages:

a) The code is very small – less chances of us making an error compared to the one in 3) above.

b) The code is decoupled with file structure – we get to keep the benefit from the above point .

c) It can be used for both application server / presentation server files – of course file reading will need to be done before the call.

d) The developer has documented the examples exhaustively in method CSV_TO_STRUCTURE . Big thank to him/her !

e) It’s part of package RSDA which is present in ABAP trial environments as well such as NSP .

If you feel lazy to type the full program, here is the whole source code:

CSV_Reading_option_4

Former Member

September 10, 2014 at 3:45 pm

Thanks for sharing.

For converting CSV to internal table I often use TEXT_CONVERT_CSV_TO_SAP which is also pretty straight forward (raw data in, structured data out).

Regards

Jan

Like 0

Vikas Singh

Blog Post Author

September 10, 2014 at 11:57 pm

Jan - I tried using TEXT_CONVERT_CSV_TO_SAP but it doesn't seem to work .

Look at this GIST:

https://gist.github.com/viksingh/2d0a685e0a1306240aee

Like 0

Former Member

September 12, 2014 at 10:51 am

For me it works. Maybe Your CSV file has a header line? In that case you need to set i_line_header = 'X'.

Like 0

Eng Swee Yeoh

October 20, 2014 at 3:00 am

Hi Vikas

Nice blog detailing the comparison between different approaches to reading CSV files in ABAP.

I just completed a requirement using option 3, but will definitely try out option 4 next time I have a similar requirement.

However, my approach to option 3 was not as lengthy. There was no need to create field catalogs and dynamic table. I just created a data reference LIKE LINE of the output table. Below are snippets of my code.

Rgds

Eng Swee

Like 0

Bruno Esperança

December 28, 2014 at 12:35 pm

Hi Eng,

What happens if your output table has a date field or an amount field? Are the values converted automatically?

Thanks,

Bruno

Like 1

Eng Swee Yeoh

January 2, 2015 at 10:10 pm

Hi Bruno

If I remember correctly, it did handle date and amount fields correctly. Unfortunately, I've moved to a different project and no longer have access to that system to verify.

I do think it is handled automatically by the ABAP engine in line 54 of my screenshot above.

ABAP automatically performs type conversion when the assigning values from one field to another. So, line_content (which is CLIKE) will go through conversion to whatever the current type for <ddic_column> is, i.e. date type, amount type.

Do give it a try and let me know how it goes.

Rgds

Eng Swee

Like 0

Matthew Billingham

January 3, 2015 at 6:40 am

1) Parts of this blog as copied from the linked standard for CSV. You must make it clear which parts are being quoted. (Or I'll have to reject the blog, and I actually don't want to do that).

2) While CSV has a standard definition, this is often not followed. For example, from Excel, as CSV file may well be semi-colon (;) separated. You should edit your blog to reflect this. Also, whether it affects any of the solutions you've given.

3) End of line markers may be Line Feed, but may also be Carriage Return, Line Feed. It depends on the source system and the program used to generate the file.

Like 3

Alexey Sadovoy

May 30, 2016 at 8:10 am

Disadvange (error) of method 4 - if needs structure contain field 'string' typed and CSV containts double quoted text - exception ocurs.

Like 0

Juwin Pallipat Thomas

July 11, 2016 at 7:40 pm

Parsing CSV - this is not a problem just for ABAP language. Hence, rather than just thinking of an ABAP solution, think out of the box. Have a look at this post: http://www.kimgentes.com/worshiptech-web-tools-page/2008/10/14/regex-pattern-for-parsing-csv-files-with-embedded-commas-dou.html

This is the easiest method I have found till now. Just use SPLIT command, with the REGEX from this blog. I have used it several successfully.

Thanks,

Juwin

Like 1

Derek Ren

August 18, 2016 at 4:07 am

it really help with a complete information

Like 0

Loyd Enochs

August 18, 2016 at 12:02 pm

Excellent briefing, Vikas, and thank you for sharing!

Like 0

Pages

…

Wordwrap in reports

Skip to end of banner

Go to start of banner

upload csv files into internal table

Skip to end of metadata

Created by Venkateswara Rao Appikonda on Sep 17, 2013

Go to start of metadata

Error rendering macro 'code': Invalid value specified for parameter 'com.atlassian.confluence.ext.code.render.InvalidValueException'

*--------------------------------------------------*

* report name

*-------------------------------------------------*

REPORT zupload_csv_file.

*--------------------------------------------------*

* data declarations

*-------------------------------------------------*

TYPES: BEGIN OF ttab,

rec(1000) TYPE c,

END OF ttab.

TYPES: BEGIN OF tdat,

fld1(10) TYPE c,

fld2(10) TYPE c,

fld3(10) TYPE c,

END OF tdat.

DATA: itab TYPE TABLE OF ttab WITH HEADER LINE.

DATA: idat TYPE TABLE OF tdat WITH HEADER LINE.

DATA: file_str TYPE string.

*--------------------------------------------------*

* selection screen design

*-------------------------------------------------*

PARAMETERS: p_file TYPE localfile.

*--------------------------------------------------*

* at selection screen for field

*-------------------------------------------------*

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

static = 'X'

CHANGING

file_name = p_file.

*--------------------------------------------------*

* start of selection

*-------------------------------------------------*

START-OF-SELECTION.

file_str = p_file.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = file_str

TABLES

data_tab = itab

EXCEPTIONS

file_open_error = 1

file_read_error = 2

no_batch = 3

gui_refuse_filetransfer = 4

invalid_type = 5

no_authority = 6

unknown_error = 7

bad_data_format = 8

header_not_allowed = 9

separator_not_allowed = 10

header_too_long = 11

unknown_dp_error = 12

access_denied = 13

dp_out_of_memory = 14

disk_full = 15

dp_timeout = 16

OTHERS = 17.

*--------------------------------------------------*

* process and display output

*-------------------------------------------------*

LOOP AT itab.

CLEAR idat.

SPLIT itab-rec AT ',' INTO idat-fld1

idat-fld2

idat-fld3.

APPEND idat.

ENDLOOP.

LOOP AT idat.

WRITE:/ idat-fld1, idat-fld2, idat-fld3.

ENDLOOP.

No labels

1 Comment

Former Member

Dear , if we add one or more field in internal table we are unable to append in itab please suggest

Roshan K

July 4, 2019 3 minute read

Excel Data to Internal table using ALSM_EXCEL_TO_INTERNAL_TABLE(obsolete) without additional code.

5113,626

***************************************************************************************************************

WARNING: This post is intended to Excel upload FM which is now obsolete. Follow this, only if you are working on older systems to maintain the code where you’ve already used ‘ALSM_EXCEL_TO_INTERNAL_TABLE’.

Also this post will help you grasp concept of Field symbols with below application.

*************************************************************************************************************** This blog post will help you avoid extraneous code every time you create Excel upload program by creating a simple Interface and a structure.

For ALSM_EXCEL_TO_INTERNAL_TABLE, explicitly we need to write code to transfer output of this FM into internal table of desired format. For this we have to map column number of FM output with columns of required internal table using

Refer below example.

CALL FUNCTION ‘ALSM_EXCEL_TO_INTERNAL_TABLE’ EXPORTING filename = p_file i_begin_col = 1 i_begin_row = 1 i_end_col = 9 i_end_row = 65536 TABLES intern = t_file EXCEPTIONS inconsistent_parameters = 1 upload_ole = 2 OTHERS = 3.

Output is in below format.

Further based on column number, work area is filled using CASE-ENDCASE and then Appending work area into required internal table in AT END OF ROW block.

LOOP

CASE (column)

when 1. wa_itab-col1 = value

when 2. wa_itab-col2 = value.

….and so on depending upon number of columns in Excel

ENDCASE

AT _END OF_ROW

APPEND wa_itab to itab.

END AT

ENDLOOP.

In order to avoid extraneous code when it comes to uploading Excel File with huge number of columns, create a FM with below code. Once you create Z FM, for every Excel File you’ll just need to create a structure in SE11 and you’re good to go.

Pass Desktop File path, Structure name in p_File and STR as input Parameters respectively.

First Create Structure in SE11 similar to the Excel File format.

FUNCTION zxls_into_it_2. *”———————————————————————- *”*”Local interface: *” IMPORTING *” REFERENCE(P_FILE) TYPE RLGRAP-FILENAME *” REFERENCE(HEADER) TYPE CHAR1 DEFAULT ‘X’ *” REFERENCE(STR) TYPE CHAR30 *” TABLES *” IT_XLS_DATA TYPE STANDARD TABLE *”———————————————————————- TYPE-POOLS: abap,slis. FIELD-SYMBOLS:<dy_table> TYPE STANDARD TABLE, <fs_current_field>, <wa_str>,<wa_temp> . DATA:new_line TYPE REF TO data, new_line2 TYPE REF TO data, li_fieldcat TYPE lvc_t_fcat, dy_table TYPE REF TO data, t_file TYPE STANDARD TABLE OF alsmex_tabline, wa_file TYPE alsmex_tabline, v_col TYPE i, begin_row TYPE i.

********This will provide Fields in Row format of Structure created(which should be similar to Excel Format). CALL FUNCTION ‘LVC_FIELDCATALOG_MERGE’ EXPORTING i_structure_name = str CHANGING ct_fieldcat = li_fieldcat EXCEPTIONS inconsistent_interface = 1 program_error = 2 OTHERS = 3. IF sy–subrc EQ 0.

********This will create internal table which will have fields as per the Excel Format. CALL METHOD cl_alv_table_create=>create_dynamic_table EXPORTING it_fieldcatalog = li_fieldcat i_length_in_byte = ‘X’ IMPORTING ep_table = dy_table EXCEPTIONS generate_subpool_dir_full = 1 OTHERS = 2. IF sy–subrc EQ 0. ASSIGN dy_table->* TO <dy_table>. ENDIF.

******V_col will hold the number of Columns inside Excel File which is later passed to ALSM_EXCEL_TO_INTERNAL_TABLE’ .

DESCRIBE TABLE li_fieldcat LINES v_col.

******Logic to skip Header IF header = ‘X’. begin_row = 2. ELSE. begin_row = 1. ENDIF.

******Core FM which will provide excel data in t_file with row/coumn/value format

CALL FUNCTION ‘ALSM_EXCEL_TO_INTERNAL_TABLE’ EXPORTING filename = p_file i_begin_col = 1 i_begin_row = begin_row i_end_col = v_col i_end_row = 65536 TABLES intern = t_file EXCEPTIONS inconsistent_parameters = 1 upload_ole = 2 OTHERS = 3.

IF sy–subrc EQ 0. LOOP AT t_file INTO wa_file.

******This Block will create new area to store column wise data.<wa_temp> acts as a work area which is completely blank at start of new row AT NEW row. CREATE DATA new_line2 LIKE LINE OF <dy_table>. ASSIGN new_line2->* TO <wa_temp>. ENDAT.

*******for every iteration, wa_file-col will have increment till number of columns in Excel file and then reset to 1 when there’s new row i.e row = 1. (Refer example at end of blog)

ASSIGN COMPONENT wa_file–col OF STRUCTURE <wa_temp> TO <fs_current_field>. IF sy–subrc EQ 0.

<fs_current_field> = wa_file–value. UNASSIGN <fs_current_field>. ENDIF.

*****Finally once all Columns are processed and new row is about to start, append the workarea into Internal table

AT END OF row. APPEND <wa_temp> TO it_xls_data. UNASSIGN <fs_current_field>. UNASSIGN <wa_temp>. ENDAT.

ENDLOOP. ENDIF. ENDIF. ENDFUNCTION.

Explanation with example:

Now simply create a structure in SE11, define Internal table of that type in report and call above FM and pass the structure name and file path and its done.

Sandra Rossi

July 4, 2019 at 12:00 pm

There are many drawbacks with ALSM_EXCEL_TO_INTERNAL_TABLE:

First, the limits explained in the SAP note 933420 - ALSM_EXCEL_TO_INTERNAL_TABLE:

It's not released for customers

It's only available in a R/3 or S/4 system (i.e. doesn't work on all other ABAP-based softwares)

The output structure is limited to 9999 rows and columns

The output structure is limited to 50 characters per cell.

Second, the limits because it's based on OLE:

It's very slow (especially because it doesn't use Desktop Office Integration)

It cannot run in background (or any process not connected to a SAP GUI)

Workaround ---> use abap2xlsx (it's been discussed a lot in the forum)

Like 5

Paul Hardy

July 4, 2019 at 12:50 pm

It would not be a normal week on the SAP Community if at least one person did not post a blog with some code they have come up with to do the same thing as ABAP2XLSX.

Like 5

Peter Inotai

July 4, 2019 at 1:12 pm

#YAA2XB

(yet another ABAP to XLS blog)

Like 1

y xg

July 4, 2019 at 3:52 pm

very good , very helpfull for us.

Like 0

春余

May 19, 2021 at 7:07 am

This is very useful, but in a call in "do while", the task manager will have multiple Excel.exe processes with the number of calls.I tried using "CALL METHOD OF workbook 'CLOSE' " before the "FREE object", but it didn't work. How can I kill the Excel.exe process?

Like 0