Application server Excel file to Internal table

[Former Member]

Hi,

I have excel file in Application Server.

And i want to upload that file into sap internal table.

Pleae give me response asap.....

Advance Thanks,

Kumar A

[Former Member]

hi,

chk this, put the data into an excel file.then download.

suppose data base name is

db_name_age

fields inside it are name and age.

**************************************

sample excel sheet.

coloumn 1 is name and column 2 is age

name age

A 8

C 13

D 55

************************************

DATA : int_excel LIKE alsmex_tabline OCCURS 0 WITH HEADER LINE.

data : record like db_name_age occurs 0 with header line.

DATA : v_start_col TYPE i VALUE '1', "starting col

       v_start_row TYPE i VALUE '1', " starting row

       v_end_col   TYPE i VALUE '2', " total columns

       v_end_row   TYPE i VALUE '10'. "total no of record


FORM f_upload .

  CLEAR : int_excel, int_excel[].

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = wf_filename
      i_begin_col             = v_start_col
      i_begin_row             = v_start_row
      i_end_col               = v_end_col
      i_end_row               = v_end_row
    TABLES
      intern                  = int_excel
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.

  IF sy-subrc <> 0.
*Message is 'Unable to upload data from  '  wf_filename.
    MESSAGE e169(zm050) WITH wf_filename.
  ELSE.

    SORT int_excel BY row col.
    REFRESH : record.
    CLEAR   : record.


    LOOP AT int_excel.

      CASE int_excel-col. "go thru each column.

        WHEN 1.
          record-name  = int_excel-value. 
        WHEN 2.
          record-age = int_excel-value.      

      ENDCASE.

      AT END OF row.

        APPEND record.

        CLEAR record.

      ENDAT.

    ENDLOOP.

  ENDIF.

*reocrd has all the datas

Rgds

Reshma

[Former Member]

Hi, I want to give you some point for help, but I can't find where can I do it

Rgds

Martin

[Former Member]

Hi Anil.

You can use the Function Module 'ALSM_EXCEL_TO_INTERNAL_TABLE'.

Reward Points if it is helpful.

Regards

Felipe Gelme

See the Example code -

************************************************************************

• Program : ZLWMI151_UPLOAD(Data load to ZBATCH_CROSS_REF Table)

• Type : Upload program

• Author : Seshu Maramreddy

• Date : 05/16/2005

• Transport : DV3K919574

• Transaction: None

• Description: This program will get the data from XLS File

• and it upload to ZBATCH_CROSS_REF Table

************************************************************************

REPORT ZLWMI151_UPLOAD no standard page heading

line-size 100 line-count 60.

*tables : zbatch_cross_ref.

data : begin of t_text occurs 0,

werks(4) type c,

cmatnr(15) type c,

srlno(12) type n,

matnr(7) type n,

charg(10) type n,

end of t_text.

data: begin of t_zbatch occurs 0,

werks like zbatch_cross_ref-werks,

cmatnr like zbatch_cross_ref-cmatnr,

srlno like zbatch_cross_ref-srlno,

matnr like zbatch_cross_ref-matnr,

charg like zbatch_cross_ref-charg,

end of t_zbatch.

data : g_repid like sy-repid,

g_line like sy-index,

g_line1 like sy-index,

$v_start_col type i value '1',

$v_start_row type i value '2',

$v_end_col type i value '256',

$v_end_row type i value '65536',

gd_currentrow type i.

data: itab like alsmex_tabline occurs 0 with header line.

data : t_final like zbatch_cross_ref occurs 0 with header line.

selection-screen : begin of block blk with frame title text.

parameters : p_file like rlgrap-filename obligatory.

selection-screen : end of block blk.

initialization.

g_repid = sy-repid.

at selection-screen on value-request for p_file.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

PROGRAM_NAME = g_repid

IMPORTING

FILE_NAME = p_file.

start-of-selection.

• Uploading the data into Internal Table

perform upload_data.

perform modify_table.

top-of-page.

CALL FUNCTION 'Z_HEADER'

• EXPORTING

• FLEX_TEXT1 =

• FLEX_TEXT2 =

• FLEX_TEXT3 =

.

&----

---

*& Form upload_data

&----

---

• text

----

---

FORM upload_data.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = p_file

I_BEGIN_COL = $v_start_col

I_BEGIN_ROW = $v_start_row

I_END_COL = $v_end_col

I_END_ROW = $v_end_row

TABLES

INTERN = itab

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

IF SY-SUBRC <> 0.

write:/10 'File '.

ENDIF.

if sy-subrc eq 0.

read table itab index 1.

gd_currentrow = itab-row.

loop at itab.

if itab-row ne gd_currentrow.

append t_text.

clear t_text.

gd_currentrow = itab-row.

endif.

case itab-col.

when '0001'.

t_text-werks = itab-value.

when '0002'.

t_text-cmatnr = itab-value.

when '0003'.

t_text-srlno = itab-value.

when '0004'.

t_text-matnr = itab-value.

when '0005'.

t_text-charg = itab-value.

endcase.

endloop.

endif.

append t_text.

ENDFORM. " upload_data

&----

---

*& Form modify_table

&----

---

• Modify the table ZBATCH_CROSS_REF

----

---

FORM modify_table.

loop at t_text.

t_final-werks = t_text-werks.

t_final-cmatnr = t_text-cmatnr.

t_final-srlno = t_text-srlno.

t_final-matnr = t_text-matnr.

t_final-charg = t_text-charg.

t_final-erdat = sy-datum.

t_final-erzet = sy-uzeit.

t_final-ernam = sy-uname.

t_final-rstat = 'U'.

append t_final.

clear t_final.

endloop.

delete t_final where werks = ''.

describe table t_final lines g_line.

sort t_final by werks cmatnr srlno.

• Deleting the Duplicate Records

perform select_data.

describe table t_final lines g_line1.

modify zbatch_cross_ref from table t_final.

if sy-subrc ne 0.

write:/ 'Updation failed'.

else.

Skip 1.

Write:/12 'Updation has been Completed Sucessfully'.

skip 1.

Write:/12 'Records in file ',42 g_line .

write:/12 'Updated records in Table',42 g_line1.

endif.

delete from zbatch_cross_ref where werks = ''.

ENDFORM. " modify_table

&----

---

*& Form select_data

&----

---

• Deleting the duplicate records

----

---

FORM select_data.

select werks

cmatnr

srlno from zbatch_cross_ref

into table t_zbatch for all entries in t_final

where werks = t_final-werks

and cmatnr = t_final-cmatnr

and srlno = t_final-srlno.

sort t_zbatch by werks cmatnr srlno.

loop at t_zbatch.

read table t_final with key werks = t_zbatch-werks

cmatnr = t_zbatch-cmatnr

srlno = t_zbatch-srlno.

if sy-subrc eq 0.

delete table t_final .

endif.

clear: t_zbatch,

t_final.

endloop.

ENDFORM. " select_data

[Former Member]

Hi,

Use the Fm "ALSM_EXCEL_TO_INTERNAL_TABL" . this will upload the data from Excel file to Inernal table.

Take little bit care , as this will upload the data to inernal table in different structure..

Revert back if any issues.

Reward with points if helpful.

Regards,

Naveen.

[Former Member]

Hi,

ex-

DATA: BEGIN OF ITAB2 OCCURS 0,

VALUE(250) TYPE C,

END OF ITAB2.

**************************************DATA DECLERATION************************************************

DATA: PATH LIKE DYNPREAD-FIELDNAME.

DATA : P TYPE STRING,

D TYPE STRING.

DATA : V_DATASET LIKE FILENAMECI-FILEEXTERN.

**************************************SELECTION SCREEN***********************************************

PARAMETERS: V_PATH TYPE RLGRAP-FILENAME DEFAULT 'C:test.txt'.

PARAMETERS: D_PATH TYPE RLGRAP-FILENAME.

SKIP 5.

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME.

PARAMETERS: PC_ONE RADIOBUTTON GROUP RAD1 DEFAULT 'X',

PC_OTHER RADIOBUTTON GROUP RAD1.

SELECTION-SCREEN END OF BLOCK B1.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR V_PATH.

• HELP FOR SEARCHING FILES PATH************************************************

IF PC_ONE = 'X'.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

• PROGRAM_NAME = 'ZTRIAL'

• DYNPRO_NUMBER = SYST-DYNNR

FIELD_NAME = PATH

IMPORTING

FILE_NAME = V_PATH.

*******CONVERT IN TO STRING************

P = V_PATH.

ENDIF.

**************************************SELECTION SCREEN VALIDATION***********************************

AT SELECTION-SCREEN.

IF V_PATH = ''.

MESSAGE E014. "FILE PATH NOT FOUND!!!!!!!

ENDIF.

IF D_PATH = ''.

MESSAGE E016. "TARGET FILE NOT FOUND!!!!!!!

ENDIF.

*******************FOR DOWNLOAD**********

AT SELECTION-SCREEN ON VALUE-REQUEST FOR D_PATH.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

• PROGRAM_NAME = 'ZTRIAL'

• DYNPRO_NUMBER = SYST-DYNNR

FIELD_NAME = PATH

IMPORTING

FILE_NAME = D_PATH.

***************CONVERT INTO STRING***********

D = D_PATH.

*******************************************MAIN LOGIC************************************************

START-OF-SELECTION.

IF P = ''.

P = 'test.txt'.

ENDIF.

******************UPLOAD DATA FROM OTHERS TO INTERNAL TABLE****************

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = P

• FILETYPE = 'ASC'

• HAS_FIELD_SEPARATOR = ' '

• HEADER_LENGTH = 0

• READ_BY_LINE = 'X'

• DAT_MODE = ' '

• CODEPAGE = ' '

• IGNORE_CERR = ABAP_TRUE

• REPLACEMENT = '#'

• CHECK_BOM = ' '

• IMPORTING

• FILELENGTH =

• HEADER =

TABLES

DATA_TAB = ITAB2

EXCEPTIONS

FILE_OPEN_ERROR = 1

FILE_READ_ERROR = 2

NO_BATCH = 3

GUI_REFUSE_FILETRANSFER = 4

INVALID_TYPE = 5

NO_AUTHORITY = 6

UNKNOWN_ERROR = 7

BAD_DATA_FORMAT = 8

HEADER_NOT_ALLOWED = 9

SEPARATOR_NOT_ALLOWED = 10

HEADER_TOO_LONG = 11

UNKNOWN_DP_ERROR = 12

ACCESS_DENIED = 13

DP_OUT_OF_MEMORY = 14

DISK_FULL = 15

DP_TIMEOUT = 16

OTHERS = 17

.

• IF sy-subrc = 1.

*

• MESSAGE E014. "FILE PATH NOT FOUND!!!!!!!

*

• MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

• WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

• ENDIF.

loop at itab2.

write:/

endloop.

****do rewards if usefull

regards,

vijay

[Former Member]

Hi Vijay,

Thanks for ur Reply but ur answer is for Presentation server Excel file only.

I dont think it is used for Application Serser.

Please give me the Exact Answer

Thanks and regards,

Kumar A

[Clemenss]

Hi anil kumar thunam,

it depends on the excel file format:

As no excel will run on the application server, how did it come there?

The easiest way is to download it to presentation server (i.e. FM ARCHIVFILE_SERVER_TO_CLIENT) and then as described 1000 times.

Regards

Clemens

[Former Member]

hi anil

Did you resolve your quesion for this issue? I met the same issue now. if you get the solution, can you please share it?

[Former Member]

Hi,

Please try like this.

Data: p_file type rlgrap-filename

• Getting the flat file from application server

open dataset p_file for input in text mode encoding default.

*---Data is downloaded to the application server file path

if sy-subrc = 0.

do.

read dataset p_file into ls_input-wa_string.

if sy-subrc eq 0.

append ls_input to lt_input.

else.

exit.

endif.

enddo.

*--Close the Application server file (Mandatory).

close dataset p_file.

after getting the value from application server

loop at lt_input into ls_input.

split ls_input-wa-string at ',' into table lt_commasepfile.

endloop.

[Former Member]

Hii!

I have a excel file named ztest on Application server.

I wrote the following code to upload that file in my internal table

Check this sample code.

REPORT  Z_file5.

DATA:
  fname(40).


DATA:
  BEGIN OF fs_flight,
    carrid   LIKE sflight-carrid,
    connid   LIKE sflight-connid,
    fldate   LIKE sflight-fldate,
    price    LIKE sflight-price,
    currency LIKE sflight-currency,
  END OF fs_flight.
DATA:
  t_flight LIKE
     TABLE OF
           fs_flight.
fname = '.\ztest.xls'.
OPEN DATASET fname FOR INPUT IN BINARY MODE.

DO 100 TIMES.
  READ DATASET fname INTO fs_flight.
  APPEND fs_flight TO t_flight.
  IF fs_flight IS INITIAL.
    EXIT.
  ENDIF.
  CLEAR fs_flight.
ENDDO.


LOOP AT t_flight INTO fs_flight.
  WRITE: / fs_flight-carrid,
           fs_flight-connid,
           fs_flight-fldate,
           fs_flight-price,
           fs_flight-currency.
ENDLOOP.

Regards

Abhijeet

[Former Member]

hey there,

you just have to deploy the 'ALSM_EXCEL_TO_INTERNAL_TABLE'

fm...and fill teh parameters as required,

this shall do the needful for you.

take care

[Former Member]

hey there,

you just have to deploy the 'ALSM_EXCEL_TO_INTERNAL_TABLE'

fm...and fill teh parameters as required,

this shall do the needful for you.

take care

[Former Member]

please let me know the solution

[former_member334777]

This message was moderated.