upload data from presentation server to ztable.

[Former Member]

hi gurus ,

I have an file that is .xls in my presentation server.

i have one table that is zuser in my database.

i need to upload the data from the .xls file to my database.

can any one help me by giving simple sample program to work me on this requirment.

Basically i need to upload data to my table from xls file format.

Reward points sure.

Thanks in Advance,

Arun

[Former Member]

Please find the below code to get records from Excel to an internal table

* Data declarations to download data from excel
data : it_data type standard table of alsmex_tabline initial size 0,
       is_data type alsmex_tabline.

* Declaration of ty_tab
types : begin of ty_tab,
          bukrs type anla-bukrs,
          anln1 type anla-anln1,
          anln2 type anla-anln2,
          buy_back type anla-buy_back,
        end of ty_tab.

* Data declarations for ty_tab
data : it_tab type standard table of ty_tab initial size 0,
       is_tab type ty_tab.

start-of-selection.

  refresh : it_data, it_tab, it_anla, it_final, it_bdcdata.

* Upload data from Excel to internal table format
  call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
       exporting
            filename                = p_ifname
            i_begin_col             = 1
            i_begin_row             = 1
            i_end_col               = 256
            i_end_row               = 65356
       tables
            intern                  = it_data
       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.

* Append EXCEL Data into a internal table
  loop at it_data into is_data.
    at new row.
      clear is_tab.
    endat.

    if is_data-col = '001'.
      move is_data-value to is_tab-bukrs.
    endif.
    if is_data-col = '002'.
      move is_data-value to is_tab-anln1.
      call function 'CONVERSION_EXIT_ALPHA_INPUT'
           exporting
                input  = is_tab-anln1
           importing
                output = is_tab-anln1.
    endif.
    if is_data-col = '003'.
      move is_data-value to is_tab-anln2.
      call function 'CONVERSION_EXIT_ALPHA_INPUT'
           exporting
                input  = is_tab-anln2
           importing
                output = is_tab-anln2.
    endif.
    if is_data-col = '004'.
      move is_data-value to is_tab-buy_back.
    endif.

    at end of row.
      append is_tab to it_tab.
      clear is_tab.
    endat.
    clear : is_data.
  endloop.

  sort it_tab by bukrs anln1 anln2.
  " Now ur data is in internal table. insert the records into Z table using the insert statement.

Regards

Gopi

[gopi_narendra]

Please find the below code to get records from Excel to an internal table

* Data declarations to download data from excel
data : it_data type standard table of alsmex_tabline initial size 0,
       is_data type alsmex_tabline.

* Declaration of ty_tab
types : begin of ty_tab,
          bukrs type anla-bukrs,
          anln1 type anla-anln1,
          anln2 type anla-anln2,
          buy_back type anla-buy_back,
        end of ty_tab.

* Data declarations for ty_tab
data : it_tab type standard table of ty_tab initial size 0,
       is_tab type ty_tab.

start-of-selection.

  refresh : it_data, it_tab, it_anla, it_final, it_bdcdata.

* Upload data from Excel to internal table format
  call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
       exporting
            filename                = p_ifname
            i_begin_col             = 1
            i_begin_row             = 1
            i_end_col               = 256
            i_end_row               = 65356
       tables
            intern                  = it_data
       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.

* Append EXCEL Data into a internal table
  loop at it_data into is_data.
    at new row.
      clear is_tab.
    endat.

    if is_data-col = '001'.
      move is_data-value to is_tab-bukrs.
    endif.
    if is_data-col = '002'.
      move is_data-value to is_tab-anln1.
      call function 'CONVERSION_EXIT_ALPHA_INPUT'
           exporting
                input  = is_tab-anln1
           importing
                output = is_tab-anln1.
    endif.
    if is_data-col = '003'.
      move is_data-value to is_tab-anln2.
      call function 'CONVERSION_EXIT_ALPHA_INPUT'
           exporting
                input  = is_tab-anln2
           importing
                output = is_tab-anln2.
    endif.
    if is_data-col = '004'.
      move is_data-value to is_tab-buy_back.
    endif.

    at end of row.
      append is_tab to it_tab.
      clear is_tab.
    endat.
    clear : is_data.
  endloop.

  sort it_tab by bukrs anln1 anln2.
  " Now ur data is in internal table. insert the records into Z table using the insert statement.

Regards

Gopi

[Former Member]

check the sample program and it will updates Ztable..

I used FM ALSM_EXCEL_TO_INTERNAL_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

Thanks

Seshu

[Former Member]

solved my self.