OPEN DATASET XLS FILE.

[Former Member]

Hi Experts,

i already searched in this forum a solution, but i didn´t find yet.

So please i hope you can help-me. The issue is : I got a program wich have to read a XLS file from Aplication Server and put it into a internal table, When the file is downloaded on the program it´s comes a dirt string(vl_linha) like '##############'

my code

OPEN DATASET pm_arq FOR INPUT IN TEXT MODE ENCODING DEFAULT.

  IF sy-subrc <> 0.
    Error
  ENDIF.

  DO.
    READ DATASET pm_arq INTO vl_linha.

    IF sy-subrc <> 0.
      EXIT.
    ELSE.
      APPEND vl_linha TO tl_linha.
    ENDIF.
  ENDDO.

Do anyone knows the solution ?is possible read a XLS extension file ?

Thank a lot.

[Former Member]

Split the data at the # sign into the work area of your internal table.

Check this thread

[Uploading a tab delimited file on the application server |;

Kind regards,

Robert

[Former Member]

Convert the XLS to tab-delimited, store that on your apps server, and read that file in....follow other reply.

Edited by: BreakPoint on Feb 28, 2011 8:45 PM

[Former Member]

Hi

still not working . Do i have to do something more ?

open dataset pm_arq for input in text mode encoding default.

  if sy-subrc <> 0.
  error
  endif.

  do.
    read dataset pm_arq into vl_linha.

   split vl_linha at cl_abap_char_utilities=>horizontal_tab into table tl_linha.

    if sy-subrc <> 0.
      exit.
    else.
      append vl_linha to tl_linha.
    endif.
  enddo.

  close dataset pm_arq.

[Former Member]

Hi,

Your split syntax is not correct.

It should be as follows:

Example (below 2 step process where you first split the data into your work area and then in step 2 append it to your internal table.)

data: ls_file_data type string.

....
read dataset pm_arq into ls_file_data.

split ls_file_data at cl_abap_char_utilities=>horizontal_tab into vl_linha-field1
                                                                  vl_linha-field2
                                                                  vl_linha-field3
                                                                  etc.

Kind regards,

Robert

[Former Member]

Hi Soft:

You can use call to function module as below example

PARAMETERS: p_xls type i default 9999 no-display.

data: c_beg_col type i value 1,

c_beg_row type i value 2,

c_end_col type i value 43,

TYPES: begin of i_load.

include STRUCTURE alsmex_tabline.

TYPES: END OF I_LOAD.

DATA: IT_LOAD TYPE STANDARD TABLE OF I_LOAD,

WA_LOAD TYPE I_LOAD.

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'

exporting

filename = IN_FILE

i_begin_col = c_beg_col

i_begin_row = c_beg_row

i_end_col = c_end_col

i_end_row = p_xls

tables

intern = IT_LOAD

exceptions

inconsistent_parameters = 1

• UPLOAD_OLE = 2

others = 3

and once you data in internal table it_load based on the column value and at end of each column append it to your desired internal table.

This should serve your purpose.

Thanks,

Ratna.

[Former Member]

hi ratnakarg07

I need get the XLS file from application sever and not Local.

Thank you

[Former Member]

Hi Softtek,

It is possible to read xls file from App. Server.

Try opening the file as Binary mode instead of Text Mode.

Regards,

Ernesto

[Former Member]

I already tried , didn´t work

tnahks

[Former Member]

Could it be that the file is corrupted?

Try uploading it with transaction CGRZ and open it in BINARY MODE. It worked for me.

Regards,

Ernesto.

[Former Member]

Ernesto please, could you post the related code ?

i wil aprecieted that.

thank.

[Former Member]

Hi Sofftek,

The Code:

TYPES: BEGIN OF ty_table,
        field1 TYPE solix,
      END OF ty_table.

DATA: lv_line TYPE solix,
      gt_table TYPE TABLE OF ty_table,
      gs_table TYPE ty_table.

PARAMETER: p_file TYPE rlgrap-filename DEFAULT '.\'.


OPEN DATASET p_file FOR INPUT IN BINARY MODE.

IF sy-subrc = 0.
*  error
ENDIF.

DO.
  READ DATASET p_file INTO lv_line.

  IF sy-subrc = 0.
    EXIT.
  ELSE.
    APPEND lv_line TO gt_table.
  ENDIF.
ENDDO.

CLOSE DATASET p_file.

I uploaded a simple excel file using CGRZ transaction and then put it in an internal table. No ###### values.

Regards,

Ernesto.

[Former Member]

Hi Ernesto thank you,

Yes now is not showing '#########################'

But is showing 'D0CF11E0A1B11AE1000000000000000000000000000000003E000300FEFF090006000000000000000000000001000000240000000000000000100000260000000100'

Do you know if is there other step for convert it ??

Thank you very much..!!

[Former Member]

Hi Sofftek,

Thinking about this, as it was mentioned before, can't it be converted to a TAB delimited file and uploaded?

Thanks & Regards,

Ernesto.

[Former Member]

Hi Ernesto,

Thanks for your helpfull answer , but i think SAP doesn´t work wiht .XLS , it´s just read files in text mode (BIT BYTE) .

I give-up , i´ve resarched a lot and didn´t find a solution.

I will try convince user save in csv extension.

Thank a lot for everybody.

[Former Member]

Hi Softtek,

The answer from RJ. Schamhart is perfect for your solution. I have worked similar to your requirement and I have achieved the expected result.

As he said use split statement and move to your internal table.

It will work, if you require more help on this please let me know, I will post you some example code here.

Regards

San.