Read Data from excel file using Rest API ABAP

[vikash_pathak]

Hi ,

I have a requirement that i have data in excel file in some other location and this data i have to read in abap program using REST API.

Below code has used to read data but unable to process it.

<br>

lv_url = 'https://xxxxxxxxxxx'')/$value'.<br><br>CLEAR lo_http_client.<br>CALL METHOD cl_http_client=>create_by_url<br>  EXPORTING<br>    url                        = lv_url<br>  IMPORTING<br>    client                     = lo_http_client<br>  EXCEPTIONS<br>    argument_not_found         = 1<br>    plugin_not_active          = 2<br>    internal_error             = 3<br>    pse_not_found              = 4<br>    pse_not_distrib            = 5<br>    pse_errors                 = 6<br>    oa2c_set_token_error       = 7<br>    oa2c_missing_authorization = 8<br>    oa2c_invalid_config        = 9<br>    oa2c_invalid_parameters    = 10<br>    oa2c_invalid_scope         = 11<br>    oa2c_invalid_grant         = 12<br>    OTHERS                     = 13.<br>IF sy-subrc <> 0.<br>ENDIF.<br><br>lo_http_client->propertytype_logon_popup = lo_http_client->co_disabled.<br><br>CALL METHOD lo_http_client->request->set_method<br>  EXPORTING<br>    method = 'GET'.<br><br>CALL METHOD lo_http_client->request->set_header_field<br>  EXPORTING<br>    name  = 'Content-Type'<br>    value = 'application/json; odata=verbose'.<br><br>CALL METHOD lo_http_client->request->set_header_field<br>  EXPORTING<br>    name  = 'Accept'<br>    value = 'application/json; odata=verbose'.<br><br>CALL METHOD lo_http_client->request->set_header_field<br>  EXPORTING<br>    name  = 'Authorization'<br>    value = lv_auth.<br>

lo_http_client->send( ).<br><br><br>lo_http_client->receive(<br>  EXCEPTIONS<br>    http_communication_failure = 1<br>    http_invalid_state         = 2<br>    http_processing_failed     = 3 ).<br><br>CALL METHOD lo_http_client->response->get_status<br>  IMPORTING<br>    code   = lv_code<br>    reason = lv_reason.<br>

Thanks

Vikash

api-excel.png

[Sandra_Rossi]

I don't know what you experienced behind "unable to process".

Do you mean you don't get the file from the HTTP response? (as you request JSON, probably it's text so you need to call get_cdata)

Do you mean you have the file but you don't know what to do with it?

[vikash_pathak]

Hi @Sandra Rossi,

I am getting data as response in get_cdata but it is as string and with header field name of excel file ,

unable to push it to internal table. how could i push it further

[benedict-torres]

There are two kinds of "excel" in this context. A real XLSX file and a CSV file marked as XLS.

In your case, it seems you are using nothing but a comma delimited file. A quick and dirty way is to split the lv_result using CL_ABAP_CHAR_UTILITIES=>CR_LF first then split the comma separator.

In a very rough sense,

SPLIT lv_result AT cl_abap_char_utilities=>cr_lf INTO TABLE lt_data.

DATA(lr_rsda) = NEW cl_rsda_csv_converter( ).

LOOP AT lt_data
  ASSIGNING FIELD-SYMBOL(<lfs_data>).

  lr_rsda->csv_to_structure(
    EXPORTING
      i_data   = <lfs_data>                 " I_DATA
    IMPORTING
      e_s_data = ls_structure                 " E_S_DATA
  ).

ENDLOOP.

[Ulrich_Schmidt1]

torresbj1 You should turn your comment into an answer, because that's what it really is: an answer...!

(Comments are for asking quick questions/clarifications from the original author, but not for answers.)

[Ulrich_Schmidt1]

@vikash.pathak I'm removing the NW ABAP Remote Function Call tag, as I don't see, what this has to do with RFC? You are using HTTP protocol, not RFC protocol!!

(Adding "ABAP Connectivity" instead, which includes HTTP.)

[vikash_pathak]

HI @torresbj1,

Here what type of structure lt_data and ls_structure has?

SPLIT lv_result AT cl_abap_char_utilities=>cr_lf INTO TABLE lt_data.

Thanks

Vikash

[Sandra_Rossi]

@benedict-torres For information, your code has a syntax error, because CL_RSDA_CSV_CONVERTER is of type "create private" so you can't use NEW (nor CREATE OBJECT). You may instantiate the class only by calling the method CL_RSDA_CSV_CONVERTER=>CREATE.

[Sandra_Rossi]

See Benedict comment who answers your question (disclaimer: the code with "NEW CL_RSDA_CSV_CONVERTER(...)" leads to a syntax error because CL_RSDA_CSV_CONVERTER is of type "create private", instead instantiate the class by calling the method CL_RSDA_CSV_CONVERTER=>CREATE).

So, your question is about the format that you receive in the HTTP response, which is this text:

So, the format is CSV Comma-Separated Values (the name "Excel format" is meaningless).

The lines are separated by |\r\n| (CRLF). On Unix system, that could be |\n| (LF).

SPLIT lv_result AT |\r\n| INTO TABLE DATA(lines).

NB: you have edited your question and now the code is badly formatted on one line with lots of <br> 😞