UPLOADING FROM EXCEL TO Z TABLE

[Former Member]

Hi ,

CAN YOU TAKE A LOOK AT THIS CODE AND LET ME WHATS WRONG IN IT AS ITS LEADING TO SHORT DUMP ERROR.

I need to update a ztable from an excel sheet.

Help me out

data : begin of t_upload1 occurs 0,

ZZPHYACT like zfix2-ZZPHYACT,

ZZLNAME like zfix2-ZZLNAME,

ZZFNAME like zfix2-ZZFNAME,

end of t_upload1.

• selection-screen

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

selection-screen : skip 1.

parameters : p_file like rlgrap-filename.

selection-screen : skip 1.

selection-screen : end of block blk.

at selection-screen on value-request for p_file.

• F4 Value for File

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

• PROGRAM_NAME = sy-repid

• DYNPRO_NUMBER = SYST-DYNNR

• FIELD_NAME = ' '

STATIC = 'X'

• MASK = ' '

CHANGING

FILE_NAME = p_file

EXCEPTIONS

MASK_TOO_LONG = 1

OTHERS = 2

.

IF SY-SUBRC <> 0.

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

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

ENDIF.

START-OF-SELECTION.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = p_file

I_BEGIN_COL = 1

I_BEGIN_ROW = 2

I_END_COL = 3

I_END_ROW = 12507

TABLES

INTERN = t_upload1

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3

.

IF SY-SUBRC <> 0.

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

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

ENDIF.

LOOP AT t_upload1.

WRITE: / t_upload1-ZZPHYACT, 20 t_upload1-ZZLNAME , 45 t_upload1-ZZFNAME.

HERE IAM JUST CHECKING I NEED TO UPDATE A ZTABLE

ENDLOOP.

[Former Member · Accepted solution]

Hi Suchitra,

I have modified your code . You just run this code . It will directly upload the data from the excel file into the internal table.

(Here I have created a custom table Zkunal1 . It comsists of 4 fields , MANDT, FNAME, LNAME & PLACE. In the test file, you can enter the data as

100 Ram Kumar Delhi

100 Shyam Kumar B'lore.)

(after running it you just have to use your database table thats it.)

&----

---

*& Report ZKUN_FILE14 *

*& *

&----

---

*& *

*& *

&----

---

REPORT ZKUN_FILE14 .

tables : zkunal1.

FIELD-SYMBOLS : <fs> .

DATA : fldname(50) TYPE c.

DATA itab TYPE TABLE OF alsmex_tabline WITH HEADER LINE.

data : begin of t_upload1 occurs 0.

include structure zkunal1.

data :end of t_upload1.

data : col type i.

DATA : cmp LIKE TABLE OF rstrucinfo WITH HEADER LINE.

data: progname like sy-repid,

dynnum like sy-dynnr.

• selection-screen

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

selection-screen : skip 1.

parameters : p_file like rlgrap-filename.

selection-screen : skip 1.

selection-screen : end of block blk.

at selection-screen on value-request for p_file.

• F4 Value for File

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

• PROGRAM_NAME = sy-repid

• DYNPRO_NUMBER = SYST-DYNNR

• FIELD_NAME = ' '

STATIC = 'X'

• MASK = ' '

CHANGING

FILE_NAME = p_file

EXCEPTIONS

MASK_TOO_LONG = 1

OTHERS = 2

.

IF SY-SUBRC <> 0.

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

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

ENDIF.

START-OF-SELECTION.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = p_file

I_BEGIN_COL = 1

I_BEGIN_ROW = 1

I_END_COL = 4

I_END_ROW = 5

TABLES

INTERN = itab

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3

.

IF SY-SUBRC <> 0.

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

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

ENDIF.

CALL FUNCTION 'GET_COMPONENT_LIST'

EXPORTING

program = SY-REPID

fieldname = 'ZKUNAL1'

tables

components = cmp

.

LOOP AT ITAB.

AT NEW row.

IF sy-tabix <> 1.

APPEND T_UPLOAD1.

ENDIF.

ENDAT.

col = itab-col.

READ TABLE cmp INDEX col.

CONCATENATE 'T_UPLOAD1-' cmp-compname INTO fldname.

ASSIGN (fldname) TO <fs>.

<fs> = ITAB-value.

ENDLOOP.

Append t_upload1.

LOOP AT t_upload1.

WRITE: / t_upload1-fname , 20 t_upload1-lname , 45 t_upload1-place.

*HERE IAM JUST CHECKING I NEED TO UPDATE A ZTABLE

ENDLOOP.

Regards,

Kunal.

[Former Member]

Hi suchitra,

1. U want to upload data from EXCEL

into internal table.

2. and u are using ALSM_EXCEL_TO_INTERNAL_TABLE.

3. But We cannot do this direclty !

4. In this FM,

there is one TABLES

INTERN = t_upload1

The definition of this t_upload1

should be as per

INTERN LIKE ALSMEX_TABLINE

and not as per your data.

5. Hence, it is giving error here .

*----

---

For uploading purpose :

6. There are TWO options.

a) either save the excel to TAB Delimited file,

and use GUI_UPLOAD to upload the data in internal table.

b) use FM for excel purpose.

7. a) is easy and recommended

8. b) there is a FM for it,

but we have to apply some more logic

bcos the FM uploads data of excel

in the intenal table,

CELL BY CELL

9. afTER THAT , we have to convert this cell by cell data,

into our format of internal table.

10. use this code (just copy paste in new program)

(it is tried wit T001 structure data)

(it will AUTOMATICALLY based upon the

fields of internal table,

convert data from cell by cell,

to that of internal table fields)

REPORT abc.

*----

---

DATA : ex LIKE TABLE OF alsmex_tabline WITH HEADER LINE.

DATA : t001 LIKE TABLE OF t001 WITH HEADER LINE.

DATA : cmp LIKE TABLE OF rstrucinfo WITH HEADER LINE.

DATA : col TYPE i.

DATA : col1 TYPE i.

FIELD-SYMBOLS : <fs> .

DATA : fldname(50) TYPE c.

*----

---

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = 'd:\def.xls'

i_begin_col = 1

i_begin_row = 1

i_end_col = 100

i_end_row = 100

TABLES

intern = ex

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

BREAK-POINT.

*----

---

CALL FUNCTION 'GET_COMPONENT_LIST'

EXPORTING

program = sy-repid

fieldname = 'T001'

TABLES

components = cmp.

*----

---

LOOP AT ex.

AT NEW row.

IF sy-tabix <> 1.

APPEND t001.

CLEAR t001.

ENDIF.

ENDAT.

col = ex-col.

col1 = col + 1.

READ TABLE cmp INDEX col.

CONCATENATE 'T001-' cmp-compname INTO fldname.

ASSIGN (fldname) TO <fs>.

<fs> = ex-value.

ENDLOOP.

BREAK-POINT.

regards,

amit m.

[Former Member]

HI

GOOD

FIRST YOU UNCOMMENT ALL THE

IF SY-SUBRC <> 0.

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

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

ENDIF.

LINES

THAN YOU CHECK WITH THE PARAMETERS THAT YOU ARE PASSING THROUGH

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

THANKS

MRUTYUN

[Former Member]

HI <b>Tripathy</b>,

<b>VERY BAD.</b>

KINDLY GIVE REPLIES WHICH ARE ATLEAST CLOSER TO THE QUESTION BEING DISCUSSED HERE.

Thanks,

ramana

[Former Member]

hi,

In 2 places, you have to change your code.

1) your internal table definition

this should of the format given in the Function module

i.e., having 3 fields of row,column,value

what this function module will do is fetching the values from the excel file and put them in the MATRIX format.

for example : if your file have

100,101,102

103,104,105

106,107,108

after calling the function module your internal table data will be as below

row column value

-

---

-

---

-

---

1 1 100

1 2 101

1 3 102

2 1 103

2 2 104

2 3 105

etc..

so your value only in VALUE field & its identified by ROW & column value.

we have to write our logic to fetch this values into other internal table.

2) max no of rows

this i think we can give upto 9999.

[Former Member]

Hi,

Define your Internal table of Type : <b>alsmex_tabline</b>

And from this table copy the contents to the internal table : t_upload1

  DATA itab TYPE TABLE OF alsmex_tabline WITH HEADER LINE.
  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename    = filename
      i_begin_col = 1
      i_begin_row = 1
      i_end_col   = 3
      i_end_row   = 65535
    TABLES
      intern      = itab.

  LOOP AT itab.
    CASE itab-col.
    WHEN '0001'. 
        t_upload1-ZZPHYACT = itab-value.
    WHEN '0002'.
        t_upload1-ZZLNAME = itab-value.
    WHEN '0003'.
        t_upload1-ZZFNAME = itab-value.
    AT END OF row.
      APPEND t_upload1.
      CLEAR t_upload1.
    ENDAT.
  ENDLOOP.

[ferry_lianto]

Hi Suchitra,

When you use FM ALSM_EXCEL_TO_INTERNAL_TABLE, there are

two prerequisites were taken.

- Max. Columns = 200

- Max. Rows = 5000

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
  EXPORTING
    filename    = filename
    i_begin_col = '1'
    i_begin_row = '1'
    i_end_col   = '200'      <--- Max. Columns 
    i_end_row   = '5000'     <--- Max. Rows    

Perhaps you may need to break your file into small files instead of with 12507 rows.

Hope this will help.

Regards,

Ferry Lianto

Please reward points if helpful.

[Former Member]

Hi,

declare ur internal table t_upload as the following data : t_upload1 like ALSMEX_TABLINE occurs 0 with header line .

then u can just can format the data into ur desired format

Regards

Vick

[Former Member]

Hi

for some reason its going to short dump error.

suchitra

[Former Member]

That's what..decalre t_upload1 type the structure as defined in the FM ALSM_EXCEL_TO_INTERNAL_TABLE

[Former Member]

Suchitra,

'ALSM_EXCEL_TO_INTERNAL_TABLE' expects the internal table with a specific format. It can't be of any table structure

[nablan_umar]

What kind of error did you get?