topic Upload excel to internal table in Application Development and Automation Discussions

Upload excel to internal table

Former Member — Sat, 01 May 2010 21:43:55 GMT

Hi,

I'm trying to upload excel data into internal table, well the excel file layout will be different on each run of the report.

Excel file will have 60 columns and 500 record limit. I can upload the excel data using 'ALSM_EXCEL_TO_INTERNAL_TABLE' and 'KCD_EXCEL_OLE_TO_INT_CONVERT' but the output table is generates 60 lines for each record i.e.., 60 * 500 = 30,000 which could cause performance.

I try with the FM 'TEXT_CONVERT_XLS_TO_SAP', but this will only work if the file structure is static. It didn't work for dynamic file layout. Even GUI_UPLOAD doesn't work to upload excel file, it will work if I convert the file to Tab delimited file.

Please advise if you know any alternate procedure to upload excel data into internal table.

Thanks,

Kumar.

Re: Upload excel to internal table

Sandeep_Panghal — Sun, 02 May 2010 06:16:16 GMT

You can achieve the same with FM : ALSM_EXCEL_TO_INTERNAL_TABLE .

Pass table/structure name in the first column and the use this to build the structure in runtime.

By using this you can control the no. of columns.

Re: Upload excel to internal table

Former Member — Sun, 02 May 2010 06:43:13 GMT

HI,

Check this.


CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
   EXPORTING
     FILENAME  = 'C:\Documents and Settings\book1.xls'
     I_BEGIN_COL                   = 1 " Just check by keeping variable here for dynamic purpose
     I_BEGIN_ROW                   = 1
     I_END_COL                     = 3
     I_END_ROW                     = 2
   TABLES
     INTERN                        = t_kna1
  EXCEPTIONS
    INCONSISTENT_PARAMETERS       = 1
    UPLOAD_OLE                    = 2
    OTHERS                        = 3
           .
 IF SY-SUBRC <> 0.
 message 'not open' type 'S'.
 ENDIF.


 loop at t_kna1 into fs_kna1. " Here you have to loop and case it for loading into internal table
 write:/ fs_kna1-row,
         fS_kna1-col,
        fs_kna1-value.
 case w_count.
 when 1.
 fs_cust-kunnr = fs_kna1-value.
 when 2.
 fs_cust-land1 = fs_kna1-value.
 when 3.
 fs_cust-name1 = fs_kna1-value.
 append fs_cust to t_cust.
 w_count = 0.
 endcase.
 w_count = w_count + 1.
endloop.

Regards and Best wishes.

Re: Upload excel to internal table

Former Member — Sun, 02 May 2010 07:24:33 GMT

Hi Kiran,

I don't know no. of records in the excel file, but as per requirement if the excel file > 500 then I will raise error in Dynamic ALV report.

When I call the FM 'ALSM_EXCEL_TO_INTERNAL_TABLE', i make it to Begin col = Begin row = 1, and End col = 60 End row = 500.

The FM loads into internal table in different way, for each column of every row it will append in the internal table. For example

I will have to validate 60 columns or less in my requirement.

EBELN EBELP MENGE MEINS

1000 0010 90 PC

2000 0020 20 PC

Output table: row col value

1 1 EBELN

1 2 EBELP

1 3 MENGE

1 4 MEINS

2 1 1000

2 2 10

2 3 90

2 4 pc

3 1 2000

3 2 20

3 3 20

3 4 pc

Here we need to Loop for 12 times for validating 2 records, i'm looking if any FM can upload as below into internalt table.

EBELN EBELP MENGE MEINS

1000 0010 90 PC

2000 0020 20 PC

Thanks,

Venu.

Re: Upload excel to internal table

Former Member — Sun, 02 May 2010 08:11:40 GMT

HI venu,

I am afraid to say there is no function module, i searched it for the same requirement.

To get result like

EBELN EBELP MENGE MEINS

1000 0010 90 PC

2000 0020 20 PC

As mentioned above loop and case should do it.

Append the internal table at 'meins' as it is the last field.

I think we have no option but to case to get each and every row.

There is some discussion here check.

Link[Check this|http://sap.ittoolbox.com/groups/technical-functional/sap-abap/how-to-upload-excel-in-to-internal-table-by-using-function-module-alsm_excel_to_internal_table-if-we-dont-know-the-no-of-rows-2322713]

Regards and Best wishes.