ABAP code to upload XLSX file to SAP using ABAP. The method shown in the blog will work in SAP GUI and Webgui (program launched from Fiori Launchpad). This approach will also work with OData.
Code is based on class CL_FDT_XL_SPREADSHEET which can be instantiated using the file name and excel file in XSTRING variable. This processing of file as XSTRING is particularly useful in case of Fiori Apps, OData CREATE_STREAM method pass uploaded file already in XSTRING which can be simply passed on to class CL_FDT_XL_SPREADSHEET constructor.
After instantiating the class get list of worksheets in the file using IF_FDT_DOC_SPREADSHEET~GET_WORKSHEET_NAMES. Method GET_WORKSHEET_NAMES returns internal table. In most of the case, you would read the first worksheet name and call method IF_FDT_DOC_SPREADSHEET~GET_ITAB_FROM_WORKSHEET with worksheet name to get data in a dynamic internal table. However, if you have data over multiple worksheets then you would have to call method GET_ITAB_FROM_WORKSHEET in the loop for each worksheet.
There are some function modules (TEXT_CONVERT_XLS_TO_SAP) available in SAP which can help you read data from XLS/XLSX file but these are based on Office Integration and requires MS Office installed on your PC. Also, this Office Integration solution (along with a range of other features) is not supported in WebGUI. In other words, these FMs will only work when you are running your program in SAP WebGUI.
I have structured the logic in standalone SE38 program so you can copy-paste and have it ready for testing in your system. Obviously, you would have to adapt the code to use it in your application but you get the idea of how it works.
*---------------------------------------------------------------------*
* Report ZPW_EXCELUPLOAD
*---------------------------------------------------------------------*
REPORT zpw_excelupload.
FIELD-SYMBOLS : <gt_data> TYPE STANDARD TABLE .
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME .
PARAMETERS : p_file TYPE ibipparms-path OBLIGATORY,
p_ncol TYPE i OBLIGATORY DEFAULT 10.
SELECTION-SCREEN END OF BLOCK b1 .
*--------------------------------------------------------------------*
* at selection screen
*--------------------------------------------------------------------*
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
DATA: lv_rc TYPE i.
DATA: lt_file_table TYPE filetable,
ls_file_table TYPE file_table.
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
window_title = 'Select a file'
CHANGING
file_table = lt_file_table
rc = lv_rc.
IF sy-subrc = 0.
READ TABLE lt_file_table INTO ls_file_table INDEX 1.
p_file = ls_file_table-filename.
ENDIF.
START-OF-SELECTION .
PERFORM read_file .
PERFORM process_file.
*---------------------------------------------------------------------*
* Form READ_FILE
*---------------------------------------------------------------------*
FORM read_file .
DATA : lv_filename TYPE string,
lt_records TYPE solix_tab,
lv_headerxstring TYPE xstring,
lv_filelength TYPE i.
lv_filename = p_file.
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
filename = lv_filename
filetype = 'BIN'
IMPORTING
filelength = lv_filelength
header = lv_headerxstring
TABLES
data_tab = lt_records
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.
"convert binary data to xstring
"if you are using cl_fdt_xl_spreadsheet in odata then skips this step
"as excel file will already be in xstring
CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
EXPORTING
input_length = lv_filelength
IMPORTING
buffer = lv_headerxstring
TABLES
binary_tab = lt_records
EXCEPTIONS
failed = 1
OTHERS = 2.
IF sy-subrc <> 0.
"Implement suitable error handling here
ENDIF.
DATA : lo_excel_ref TYPE REF TO cl_fdt_xl_spreadsheet .
TRY .
lo_excel_ref = NEW cl_fdt_xl_spreadsheet(
document_name = lv_filename
xdocument = lv_headerxstring ) .
CATCH cx_fdt_excel_core.
"Implement suitable error handling here
ENDTRY .
"Get List of Worksheets
lo_excel_ref->if_fdt_doc_spreadsheet~get_worksheet_names(
IMPORTING
worksheet_names = DATA(lt_worksheets) ).
IF NOT lt_worksheets IS INITIAL.
READ TABLE lt_worksheets INTO DATA(lv_woksheetname) INDEX 1.
DATA(lo_data_ref) = lo_excel_ref->if_fdt_doc_spreadsheet~get_itab_from_worksheet(
lv_woksheetname ).
"now you have excel work sheet data in dyanmic internal table
ASSIGN lo_data_ref->* TO <gt_data>.
ENDIF.
ENDFORM.
*---------------------------------------------------------------------*
* Form PROCESS_FILE
*---------------------------------------------------------------------*
FORM process_file .
DATA : lv_numberofcolumns TYPE i,
lv_date_string TYPE string,
lv_target_date_field TYPE datum.
FIELD-SYMBOLS : <ls_data> TYPE any,
<lv_field> TYPE any.
"you could find out number of columns dynamically from table <gt_data>
lv_numberofcolumns = p_ncol .
LOOP AT <gt_data> ASSIGNING <ls_data> FROM 2 .
"processing columns
DO lv_numberofcolumns TIMES.
ASSIGN COMPONENT sy-index OF STRUCTURE <ls_data> TO <lv_field> .
IF sy-subrc = 0 .
CASE sy-index .
* when 1 .
* when 2 .
WHEN 10 .
lv_date_string = <lv_field> .
PERFORM date_convert USING lv_date_string CHANGING lv_target_date_field .
WRITE lv_target_date_field .
WHEN OTHERS.
WRITE : <lv_field> .
ENDCASE .
ENDIF.
ENDDO .
NEW-LINE .
ENDLOOP .
ENDFORM.
*---------------------------------------------------------------------*
* Form DATE_CONVERT
*---------------------------------------------------------------------*
FORM date_convert USING iv_date_string TYPE string CHANGING cv_date TYPE datum .
DATA: lv_convert_date(10) TYPE c.
lv_convert_date = iv_date_string .
"date format YYYY/MM/DD
FIND REGEX '^\d{4}[/|-]\d{1,2}[/|-]\d{1,2}$' IN lv_convert_date.
IF sy-subrc = 0.
CALL FUNCTION '/SAPDMC/LSM_DATE_CONVERT'
EXPORTING
date_in = lv_convert_date
date_format_in = 'DYMD'
to_output_format = ' '
to_internal_format = 'X'
IMPORTING
date_out = lv_convert_date
EXCEPTIONS
illegal_date = 1
illegal_date_format = 2
no_user_date_format = 3
OTHERS = 4.
ELSE.
" date format DD/MM/YYYY
FIND REGEX '^\d{1,2}[/|-]\d{1,2}[/|-]\d{4}$' IN lv_convert_date.
IF sy-subrc = 0.
CALL FUNCTION '/SAPDMC/LSM_DATE_CONVERT'
EXPORTING
date_in = lv_convert_date
date_format_in = 'DDMY'
to_output_format = ' '
to_internal_format = 'X'
IMPORTING
date_out = lv_convert_date
EXCEPTIONS
illegal_date = 1
illegal_date_format = 2
no_user_date_format = 3
OTHERS = 4.
ENDIF.
ENDIF.
IF sy-subrc = 0.
cv_date = lv_convert_date .
ENDIF.
ENDFORM .
Uploaded this excel file.
Below results from SAP GUI and WebGUI.
Pawan!!!! hope your remember me,, This is Venu from ATOS, you saved my day, exactly looking for something like this..
Hi Venu, how you doing? Glad this helped you.
I have used this recently in one of Fiori App we did.
Hi Pawan , Do you have sample code related to excel file upload from other ip. address like( //122.23.45.6/ ) which have folder with login password , our program should capable for login as well after passing login id pass words.
Easily coded and very useful. The simplest XLSX upload implementation I have found on the web. Nice job! Thanks for sharing, you have been really helpful!
Hi there, my business partner found out that there is a limit to the number of worksheets than can be uploaded. If you are just uploading one or two spreadsheets then this is a good tool.
Hi, thank you for letting us know. Did you managed to find out the limit? I think this class is used in Data Migration Cockpit and I would imagine limit would be more than one or two.
I have just uploaded a spreadsheet with 10 worksheets and I can see 10 worksheets in internal table lt_worksheets (line 111).
Hi Pawan, I think the worksheet limit is 32. We have 10 business objects in our SAP PM master data tool and task lists have 16 worksheets. I am going to try CL_XLSX_DOCUMENT next, I will keep you posted, regards Derek
If you are interested, please take a look at our product dMax
https://cdesignengineers.com/services/products/
Thank you!
what you have done to upload file via Fiori launchpad? I am getting Z drive filepath which doesnot exist. can you tell me what code you adapted to use it in your application
Did you solve the issue with Fiori launchpad Z drive path?
Thanks you! Its very useful
Hello, does this upload method allow the excel file to be open on workstation when uploading to gui?
Very helpful. thanks!
Do you have sample for Notepad?
Hi Pawan,
The above code is very helpful for .xlsx files. Do you have similar code for .xls file with multiple worksheets also
should work for Macro enabled workbook.
I have a requirement where i have to read the macro enabled excel file (.xls) with multiple worksheets.
Thanks
Murthy
Hi Pawan,
I´m currently facing a weird problem with Method CONVERT_DEC_TIME_TO_HHMMSS in CL_FDT_XL_SPREADSHEET.
I have time-formatted values (e.g. 10:45:03) in my Excel and by uploading them into SAP-tables some values get changed by the above method.
I found that the method return invalid seconds (> 59) and sometimes (really! no pattern in behavior) swapped them (e.g. from “02” to “20”).
I´m at SAP_BASIS/SAP_ABA 750 and wondering, if you have a solution?
Thanks
Sebastian
Just to add to the above:
A workaround would be to add a “T” before the timestamp, e.g. “T10:45:03”. Seems to me, that the SAP-method will handle the values then correctly.
But unfortunately I didn´t found a way to change the value during export (using your coding via CL_SALV_EXPORT_TOOL_ATS_XLS to export any table to XLSX), since the structure is bound to a SAP-datafield type with a length of only 6 chars (HHMMSS), thus I can´t add a “T” w/o cutting the last digit.
Any ideas?
Hello Pawan, I tried to use cl_gui_frontend_services=>file_open_dialog from WEBGUI but it is throwing dump.
Hi Pawan,
I have tried you method and it is wonderfull, thanks very much for sharing!
Hi Pawan,
I tried running your example, but only returned the message
‘File “_rels/.rels” not found in package’.
Can you help me with this?
regard.
Kim.
Hi Pawan, It was nice really nice blog that helped me. Can you please also upload code for Download data from SAP to Excel multiple tabs.
We did using OLE object coding but it work from SAP GUI and not work from FIORI.
Please help this is really urgent.
Hi Pawan, Nice blog and very helpful. Please also share how to download SAP internal tables data to Excel multiple sheets. We did using OLE concepts but that not working when run from Fiori.
Can you please help with any links or code as this is bit urgent.
Thank you so much! That was exactly what I needed.
Any reason why you are using BIN parameter in GUI_UPLOAD and convert the binary data into xstring on the next step instead of using ASC parameter and have xstring directly after GUI_UPLOAD?
Excellent solution for upload from excel directly.
Thanks a lot.
Best regards.
but where is the column name at output
How do i solve error type “ibipparms” is unkown?
Can any one help me How to upload bulk data in SAP to pass Credit note in FB75.
very helpful – Thanks. Saved me good few hours of work time to write a database upload program.
Thanks for this post, it was really helpful.