Community
Application Development and Automation Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

Using CL_FDT_XL_SPREADSHEET class for a .CSV XSTRING

MattRook
Explorer

‎2022 Dec 07 9:04 PM

0 Likes
17,459

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.

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.

Reply
2 REPLIES 2
Read only

Jelena_Perfiljeva
Active Contributor

‎2023 Jan 06 6:03 AM

15,116

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-lau...

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.

Read only

MattRook
Explorer
In response to Jelena_Perfiljeva

‎2023 Jan 15 8:45 PM

0 Likes
15,116

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