How to upload excel(not CSV) in BW/ECC system.
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.
- 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.
Another option is using abap2xlsx, which also comes with a reader class capable of importing files.
You cannot call it a month if (at least one) one post regarding reading XLSX files in Excel wasn't published.
FM RSDS_ACCESS_OPEN_EXCEL is supported only in foreground because it uses OLE.
Thanks Vimal for the blog post. There are 2 more drawbacks of the FM "RSDS_ACCESS_OPEN_EXCEL".