Using CL_FDT_XL_SPREADSHEET class for a .CSV XSTRING

MattRook — Wed, 07 Dec 2022 21:04:38 GMT

I have a program that maintains custom Z tables by exporting the table to an excel spreadsheet and it also refreshes the table and updates from an excel spreadsheet with .XSLX.

The upload to SAP from .XLSX is done with the CL_GUI_FRONTEND_SERVICES=>GUI_UPLOAD method to get the raw data. Then converted to XSTRING and passed into the CL_FDT_XL_SPREADSHEET class and the IF_FDT_DOC_SPREADSHEET~GET_ITAB_FROM_WORKSHEET method is called to pass that data to a variable where it is used in another method to upload to SAP. This works fine.

However, I also want the program to ALSO accept .CSV files.

I use the CL_GUI_FRONTEND_SERVICES=>GUI_UPLOAD method to get the raw data, but when I try to convert the raw data to an XSTRING, an error is thrown.

My question: Is the CL_FDT_XL_SPREADSHEET class suitable for .CSV file raw data or is it only suitable for .XLSX files? If it is, can anyone see where I am going wrong or where I can fix this?

I have played around with the CL_RSDA_CSV_CONVERTER but the 'rt_table' variable that is returned is a TYPE REF TO data which is used in another method that's written which converts excel to SAP table where it refreshes and updates the table. When I try to populate that with the CSV conversion, it obvioulsy doesn't like it

Code:

<code>METHOD import_excel_data.

    DATA: lt_xtab TYPE cpt_x255,
          lv_size TYPE i.

    IF i_filetype = abap_true. "******.XLSX UPLOAD*********
      cl_gui_frontend_services=>gui_upload( EXPORTING filename   = i_file
                                                      filetype   = 'BIN'
                                            IMPORTING filelength = lv_size
                                             CHANGING data_tab   = lt_xtab
                                             EXCEPTIONS
                                                      file_open_error = 1
                                                      file_read_error = 2
                                                      error_no_gui            = 3
                                                      not_supported_by_gui    = 4
                                                      OTHERS                  = 5 ).
      IF sy-subrc <> 0.
        RAISE EXCEPTION TYPE zcx_excel_exception EXPORTING i_message = |Invalid File { i_file }| ##no_text.
      ENDIF.
    ELSE."******.CSV UPLOAD*********

      cl_gui_frontend_services=>gui_upload( EXPORTING filename   = i_file
                                                      filetype   = 'ASC'
                                                      has_field_separator = abap_true
                                            IMPORTING filelength = lv_size
                                            CHANGING data_tab   = lt_xtab
                                            EXCEPTIONS
                                                    file_open_error = 1
                                                    file_read_error = 2
                                                    error_no_gui            = 3
                                                    not_supported_by_gui    = 4
                                                    OTHERS                  = 5 ).
      IF sy-subrc <> 0.
        RAISE EXCEPTION TYPE zcx_excel_exception EXPORTING i_message = |Invalid File { i_file }| ##no_text.
      ENDIF.

    ENDIF.

    cl_scp_change_db=>xtab_to_xstr( EXPORTING im_xtab    = lt_xtab
                                              im_size    = lv_size
                                    IMPORTING ex_xstring = DATA(lv_xstring) ).

    DATA(lo_excel) = NEW cl_fdt_xl_spreadsheet( document_name = i_file
                                                xdocument     = lv_xstring ).
    lo_excel->if_fdt_doc_spreadsheet~get_worksheet_names(
      IMPORTING worksheet_names = DATA(lt_worksheets) ).

    rt_table = lo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lt_worksheets[ 1 ] ).

    IF rt_table IS INITIAL.
      RAISE EXCEPTION TYPE zcx_excel_exception EXPORTING i_message = 'No Data found in Excel File' ##no_text.
    ENDIF.

ENDMETHOD.

Re: Using CL_FDT_XL_SPREADSHEET class for a .CSV XSTRING

Jelena_Perfiljeva — Fri, 06 Jan 2023 06:03:38 GMT

Please read the comments to this blog post, this class should not be used at all: https://blogs.sap.com/2021/04/25/excel-upload-and-download-program-with-multiple-tabs-from-fiori-lauch-padweb-gui/

CSV and XLSX are very different formats and there is no standard class that would handle both. Whenever an "ABAP and Excel" blog post comes up here (and it happens monthly), people mention ABAP2XLSX

It does have an option to write into CSV and maybe you could submit a suggestion to read CSV.

Since it sounds like this program was designed specifically for Excel format and relies on specific type provided by that class, you will run into a lot of refactoring required in the "update SAP" part. At this point I would reconsider if the effort is worth it.

Re: Using CL_FDT_XL_SPREADSHEET class for a .CSV XSTRING

MattRook — Sun, 15 Jan 2023 20:45:12 GMT

Thanks for your well informed and clear answer.

I shall save and read through those blogs.

I have fixed the issue now by utilising the cl_rsda_csv_converter class and specifically the csv_to_structure method. Obviously a bit more to it but they are crux of it.

Thanks again!

Matt

topic Using CL_FDT_XL_SPREADSHEET class for a .CSV XSTRING in Application Development and Automation Discussions

Using CL_FDT_XL_SPREADSHEET class for a .CSV XSTRING

Re: Using CL_FDT_XL_SPREADSHEET class for a .CSV XSTRING

Re: Using CL_FDT_XL_SPREADSHEET class for a .CSV XSTRING