Question Re: Excel file upload to CRM in CRM and CX Q&A

Excel file upload to CRM

Former Member — Thu, 22 Jan 2015 15:32:37 GMT

Hello everybody,

after doing some web research before, I like to ask the experts here for a good way to import data from Excel into SAP CRM. I found a few infos about function modules in ECC to convert Excel into internal table but all those FMs do not exist in CRM . As I don't think it makes sense to port those ECC FMs to CRM, I hope somebody can tell me a smarter way to import Excel to CRM.

I need to import Excel files, no CSV. I know how to import csv but the requirement is explicitly .XLS od .XLSX. I also found infos about how to import XLS data to CRM by using ActiveX functionality in BSP pages but I don't like to use ActiveX as the function shall also be available on Firefox. I need a class or function module which converts given uploaded Excel raw data into an internal table. The requirement is easy but I couldn't find a tool in CRM which offers this.

Hope, somebody can give me a hint.

Thanks and best regards,

Markus

Re: Excel file upload to CRM

nisha_vinod — Fri, 23 Jan 2015 07:57:58 GMT

Hi Markus,

See if the following code snippet helps.

The contents are present in internal table lt_xls at the end of execution.



DATA :
 gr_obj_gui_fes       TYPE REF TO cl_gui_frontend_services,
 gt_xls               TYPE filetable,
 lv_filename          TYPE STRING,
 lt_xls               TYPE TABLE OF string.
 
 DATA gv_subrc             TYPE i.
 
 
 CONSTANTS gc_window_text  TYPE string VALUE 'Please choose the xls file to be uploaded' ##no_text.
 CONSTANTS gc_default_ext  TYPE string VALUE '*.xls' ##no_text.
 
 
 CREATE OBJECT gr_obj_gui_fes.
 
   CALL METHOD gr_obj_gui_fes->file_open_dialog
     EXPORTING
       window_title            = gc_window_text    " Title Of File Open Dialog
       default_extension       = gc_default_ext    " Default Extension
     CHANGING
       file_table              = gt_xls    " Table Holding Selected Files
       rc                      = gv_subrc    " Return Code, Number of Files or -1 If Error Occurred
     EXCEPTIONS
       file_open_dialog_failed = 1
       cntl_error              = 2
       error_no_gui            = 3
       not_supported_by_gui    = 4
       OTHERS                  = 5.
 
   WRITE: 'Test'.
 
     READ TABLE gt_xls INDEX 1 INTO lv_filename.
 
 * transfer xls into internal string table
 * (each row of xls is one row in string table)
   cl_gui_frontend_services=>gui_upload(
     EXPORTING
       filename                = lv_filename
     CHANGING
       data_tab                = lt_xls    " Transfer table for file contents
     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
       not_supported_by_gui    = 17
       error_no_gui            = 18
       OTHERS                  = 19
   ).
 
   IF sy-subrc <> 0.
     MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
                WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
 
    ENDIF.

Re: Excel file upload to CRM

Former Member — Fri, 23 Jan 2015 08:11:54 GMT

Hello Nisha,

thanks for your reply to my question. Unfortuatelly the snippet you postet is not very helpful for me. I already realized the part with the GUI upload before but in the internal table is only some unprocessable ascii code source text of the XLS. I'm looking for a method to convert this code mash into a processable internal table which represents the line and column structure of the excel worksheet.

Yesterday I made some experiments with the class CL_XLSX_DOCUMENT. It seems to have a few helpful methods but I wasn't able to really extract some processable information. Here the coding which i wrote:


DATA source_line  TYPE c LENGTH 80.
 DATA source_table LIKE TABLE OF source_line.
 DATA buffer TYPE xstring.
 DATA filelength TYPE i.
 DATA lr_excel_doc TYPE REF TO cl_xlsx_document.
 DATA lr_wb_part TYPE REF TO cl_xlsx_workbookpart.
 DATA lr_ws_parts TYPE REF TO cl_openxml_partcollection.
 DATA lr_part TYPE REF TO cl_xlsx_worksheetpart.
 DATA lr_table_parts TYPE REF TO cl_openxml_partcollection .
 
 TRY.
 
     CALL FUNCTION 'GUI_UPLOAD'
       EXPORTING
         filename                = 'C:\Example.xlsx'
         filetype                = 'BIN'
       IMPORTING
         filelength              = filelength
       TABLES
         data_tab                = source_table
       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.
 
     CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
       EXPORTING
         input_length = filelength
       IMPORTING
         buffer       = buffer
       TABLES
         binary_tab   = source_table
       EXCEPTIONS
         failed       = 1
         OTHERS       = 2.
 
     lr_excel_doc =  cl_xlsx_document=>load_document( buffer ).
 
   CATCH cx_root.
 ENDTRY.
 
 TRY .
     lr_wb_part = lr_excel_doc->get_workbookpart( ).
   CATCH cx_openxml_format cx_openxml_not_found.
 ENDTRY.
 
 TRY .
     lr_ws_parts = lr_wb_part->get_worksheetparts( ).
   CATCH cx_openxml_format.
 ENDTRY.
 
 lr_part ?= lr_ws_parts->get_part( 0 ).
 
 TRY.
     lr_table_parts = lr_part->get_tableparts( ).
   CATCH cx_openxml_format.    
   CATCH cx_openxml_not_found.    
 ENDTRY.
 
 lv_data = lr_part->get_data( ).

Do you have some experience with the class CL_XLSX_DOCUMENT?

Best regards,

Markus

Re: Excel file upload to CRM

Former Member — Fri, 23 Jan 2015 08:25:58 GMT

Hi,

You can use TEXT_CONVERT_XLS_TO_SAP FM to upload an excel file. I am also attaching sample code for ur reference.

data: begin of itab occurs 0,

matnr type mara-matnr,

end of itab.

data: dummy type TRUXS_T_TEXT_DATA.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

i_tab_raw_data = dummy

i_filename = 'D:\matnr.xls'

tables

i_tab_converted_data = itab

Alternatively you can use GUI_UPLOAD.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = 'D:\matnr'

filetype = 'ASC'

has_field_separator = 'X'

TABLES

data_tab = itab.

Thanks & Regards

Richa

Re: Excel file upload to CRM

Former Member — Fri, 23 Jan 2015 08:54:28 GMT

Hello Richa,

thanks for you post. The function module TEXT_CONVERT_XLS_TO_SAP does not exist in CRM. It is only available in ECC. I tried to port the FM to CRM but that is quite tricky as a lot of includes are missing too and also type definitions.

Best regards,

Markus

Re: Excel file upload to CRM

Former Member — Fri, 23 Jan 2015 08:59:02 GMT

Hi,

You can use following code -

* DATA: lv_file TYPE string,

* it_data TYPE TABLE OF zcca_prod_master, //Table type you want to give

* ls_data TYPE zcca_prod_master.

* PARAMETERS: p_file TYPE localfile.

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

* lv_file = p_file.

* CALL FUNCTION 'GUI_UPLOAD'

* EXPORTING

* filename = lv_file

* filetype = 'ASC'

* has_field_separator = 'X'

* TABLES

* data_tab = it_data

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

* IF sy-subrc = 0.

* ENDIF.

Here, it_data will contain the data from excel file you choosen.

Let me know incase of any help.

Thanks & Regards

Richa

Re: Excel file upload to CRM

Former Member — Fri, 23 Jan 2015 09:12:08 GMT

Hello Richa,

the data in it_data is ASCII raw data. I cannot process this in SAP. I need to find a way to get the worksheet data from XLS...

Best regards,

Markus

Re: Excel file upload to CRM

Former Member — Fri, 23 Jan 2015 09:19:01 GMT

Hi,

Save you file as .txt & then try to upload data using the above code.

It will work.

Thanks & Regards

Richa

Re: Excel file upload to CRM

Former Member — Fri, 23 Jan 2015 09:23:18 GMT

Hello Richa,

yes, it seems to be the only way to convert the XLS to TXT or CSV... I was looking for a way to import a native XLS directly to SAP but this seems not to be supported in CRM.

Thanks for your help!

Best regards,

Markus

Re: Excel file upload to CRM

former_member158363 — Fri, 23 Jan 2015 18:46:16 GMT

Hi Markus,

just quick hint regarding XLSX format - I think with CL_XLSX_DOCUMENT you are on the right path.

However, if the XLSX file you need to analyze is not very complicated, you can do it yourself with simple string or XML methods.

The XLSX format is just a zipped collection of XML files. Just take any XLSX file, change extension to .ZIP and look into the file.

In /xl/worksheets/* you will find xml files with worksheets - they contain cells and either value itself (formulas and number) or reference to list of shared values (strings).

The shared values are usually in /xl/* (sharedStrings.xml, for example).

Regards,

Dawood.

Re: Excel file upload to CRM

Former Member — Mon, 26 Jan 2015 08:23:21 GMT

Hello Dawood,

thanks a lot for your hint with XLSX. This approach works! As my worksheet is not very complex, I do not even need an XSLT to transform.

Best regards,

Markus

Re: Excel file upload to CRM

Former Member — Mon, 26 Jan 2015 08:34:40 GMT

After looking for some information about class CL_XSLX_DOCUMENT, I luckily found this very good how to guide about XLSX processing with ABAP:

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/6026d7c0-c716-3010-c8be-ea771ff9d783?QuickLink=index&…