How to upload excel(not CSV) in BW/ECC system.

[vimal]

I tried looking for some inputs on a FM to upload excel(not CSV) in BW system or for that matter any other system but BW system doesn't have normal usual FM's to upload files which you will find in normal ECC system and our requirement was to upload excel only and not any other formats .

So i could find an FM which was helpful but there was no thread mentioned for it. So trying to cover the FM here.

There will certain limitations of this FM , which we will cover in below steps. This option might not be the  best way but if you are in BW system and need to read the excel only then you can follow below FM. You will find this way as slow way of uploading and it will take it's time.

 

Steps:

 

1. Use FM RSDS_ACCESS_OPEN_EXCEL as shown below:

    CALL FUNCTION 'RSDS_ACCESS_OPEN_EXCEL'

      EXPORTING

        i_filename     = p_file

      IMPORTING

        e_r_worksheets = pr_book

      EXCEPTIONS

        failed         = 1

        OTHERS         = 2.

 

2. So we will get the references of pr_book and using it we will have to set the worksheet name. So here comes the first limitation where you need to provide the sheet name which you want to be uploaded and without which it will not read data from excel sheet and name is case sensitive as well.

pr_sheet = pr_book->get_worksheet_by_name( gv_name ).

gv_name is the name of the sheet which you want to read from excel sheet.

 

3) Then we need to get the range , row count and column count as shown below:

"Range

pr_range = pr_sheet->get_used_range( ).

*Get max rows

p_maxrows = pr_range->get_row_count( ).

"Get col. count

p_maxcols = pr_range->get_column_count( ).

This range is of data in the sheet. Once you have range , you will then get the Max row count and Max col count as shown above.

 

4) In next step, you need to loop on max rows and max cols and get the value

        DO  p_maxrows TIMES.

          "pick next row

          lv_row = lv_row + 1.

          CLEAR lwa_cdmprctr.

*          loop on max cols

          DO p_maxcols TIMES.

            CLEAR r_value.

            lv_col = lv_col + 1.

            TRY.

                pr_range->get_value(

                  EXPORTING

                    i_x                   = lv_col

                    i_y                   = lv_row

                    i_external            = ''

                  RECEIVING

                    r_value               = r_value ).

 

5) Once you have value of column of a particular row then you need to create internal table with respective value.

 

So this is the way to read excel if you do not have other options available. It will be slow as we are reading each column of each row . So this might not be the best way but if you don't have any other options , you can go ahead with it.

 

Please share your views if you happen to use it.