How to read excel data into internal table in sap crm

[former_member184390]

Hi Friends,

We are working on a program where in we need to read the data from an excel file into an internal table. I have written the below code and is not working. Not sure what is missing in this piece of code.

Any pointers on this would be of great help. I tried checking with Filetype values 'asc','DAT'..

The function module ALSM_EXCEL_TO_INTERNAL_TABLE is not available in CRM.

Thanks,

Udaya

EPORT  ZTESTUDAY6.
TYPES : BEGIN OF gs_upload,                                                 " Structure to store the record from a text file
          title          TYPE string,
          first_name     TYPE string,

        END OF gs_upload.
data  lv_tab  ."#EC *
data lt_gs_upload                 TYPE   STANDARD TABLE OF gs_upload.
lv_tab = cl_abap_char_utilities=>horizontal_tab.

  CALL METHOD cl_gui_frontend_services=>gui_upload
  EXPORTING
    FILENAME                      = 'C:\Users\udayabhaskar_p\Desktop\t1.xls'
    FILETYPE                      = 'DAT'
*   HAS_FIELD_SEPARATOR           = LV_TAB
**   HEADER_LENGTH                 = 0
*   READ_BY_LINE                  = 'X'
*   DAT_MODE                      = ' '
*   CODEPAGE                      = ' '
*   IGNORE_CERR                   = ABAP_TRUE
*   REPLACEMENT                   = '#'
*   CHECK_BOM                     = ' '
*   VIRUS_SCAN_PROFILE            =
*   NO_AUTH_CHECK                 = ' '
* IMPORTING
*   FILELENGTH                    =
*   HEADER                        =
 CHANGING
      data_tab                = lt_gs_upload.

[former_member536879]

Hi,

Use this Function Module TEXT_CONVERT_XLS_TO_SAP

to update the Excel sheet value into an Internal Table.

If you give the I_LINE_HEADER = 'X' it will not read the first line in the excel sheet.

Hope this might solve your Problem.

With Regards,

Sumodh.P

[former_member184390]

Thanks Sumodh for your quick response. We dont have the function module TEXT_CONVERT_XLS_TO_SAP in CRM Box.

Any other pointers pls..

[former_member536879]

Hi,

Please try with the below code.

CALL METHOD cl_gui_frontend_services=>gui_upload

EXPORTING

FILENAME = 'C:\Users\udayabhaskar_p\Desktop\t1.xls'

FILETYPE = 'ASC'

HAS_FIELD_SEPARATOR = 'X'

After that in the Tables parameter give your internal table name.The internal table structure must be the same structure of excel too.

Try with this

With Regards,

Sumodh.P

[former_member184390]

Thanks Sumodh. I am just trying to read two columns and 2 rows..for this i have created the structure in program as below and still the issue persists. Are you able to succesfully test this..

TYPES : BEGIN OF gs_upload,

title TYPE string,

first_name TYPE string,

END OF gs_upload.

[bbalci]

Hi Udaya

You should save your excel file as a tab-delimited text file first,

use Save As > Text (Tab Delimited) option of microsoft excel.

Than declare your internal table with the same columns as excel in your abap program.

Now u can upload from this tab-delimited file with method

cl_gui_frontend_services=>gui_upload or the function 'WS_UPLOAD'.

Beware of filetype parameter sould be set to 'DAT'

Let me know encounter a problem.

Hi hope it helps.

Bulent

Edited by: Bulent Balci on Jul 7, 2010 1:42 PM

[former_member184390]

Thanks so much Bulent for the solution. It worked. Basically we are converting .xls file to .txt.

I am just thinking is there any method available that can directly read the .xls file info into an internal table.

Once again thanks for the suggestion.

[Former Member]

L_START_COL TYPE I VALUE '1',

L_START_ROW TYPE I VALUE '1',

L_END_COL TYPE I VALUE '256',

L_END_ROW TYPE I VALUE '65536',

F_ROW TYPE I VALUE '0001'.

L_INTERN : is the internal table with same sequence as that of the FILE .

PARAMETERS:P_FILE TYPE RLGRAP-FILENAME OBLIGATORY.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = P_FILE

I_BEGIN_COL = L_START_COL

I_BEGIN_ROW = L_START_ROW

I_END_COL = L_END_COL

I_END_ROW = L_END_ROW

TABLES

INTERN = L_INTERN

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

IF SY-SUBRC <> 0.

MESSAGE I006 .

CLEAR : P_FILE .

EXIT .

ENDIF.

" FORMATTING OF INTERNAL TABLE .

IF L_INTERN[] IS INITIAL.

MESSAGE I007 .

ELSE.

SORT L_INTERN BY ROW COL. " "#EC ..

LOOP AT L_INTERN INTO WA_LINTERN .

IF WA_LINTERN-ROW <> F_ROW AND WA_LINTERN-ROW <> '0002'AND WA_LINTERN-ROW <>'0003' and WA_LINTERN-ROW <>'0004'.

"'0001'.

MOVE WA_LINTERN-COL TO L_INDEX.

ASSIGN COMPONENT L_INDEX OF STRUCTURE WA_INREC TO <FS> .

IF SY-SUBRC = 0.

MOVE WA_LINTERN-VALUE TO <FS>.

ENDIF.

AT END OF ROW . "#EC *

APPEND WA_INREC TO IT_DATA. "

CLEAR WA_INREC.

ENDAT.

ENDIF .

ENDLOOP.

[former_member184390]

Thanks Swapnil for the help. Unfortunately, We do not have the Standard FM 'ALSM_EXCEL_TO_INTERNAL_TABLE' available in SAP CRM Box.

[Former Member]

Use the above FM : ALSM_EXCEL_TO_INTERNAL_TABLE will directly pass the xlsx data to the internal table defined .

make sure the sequence of the fields given in the excel and the internal table declared are same .

Please make sure the excel fiel is having correct format character and number should be properly maintained....!!!

Regards

Swapnil

[gopalkrishna_baliga]

HI Udaya,

Did u get any solution to your problem? I also have a similar requirement in CRM.

Kindly give the solution if you have.

Thanks & Regards

Gopal

[Former Member]

Hi,

Try the code snippet from the below link. It will help you to upload excel file(*.xsl) into SAP CRM.

Its working fine, customize it as you need.

http://wiki.sdn.sap.com/wiki/display/Snippets/ABAP+-+Upload+data+from+Excel+to+Sap+using+OO

Thank you.

Regards,

Swadini Sujanaranjan.

[former_member184390]

Thanks Swadini for the inputs.

Regards,

Uday

[Former Member]

Hi Bhaskar,

I also have a similar requirement in CRM, I have to upload excel file to cancel near 3000 sales orders in crm for crmd_order.

Did u get any solution to your problem?

Kindly share your valuable suggestions.

Thanks & Regards

Rajasekhar S