topic Re: Transfering the data from excel sheet in Application Development and Automation Discussions

Transfering the data from excel sheet

Former Member — Fri, 15 Jun 2007 14:20:16 GMT

Hi,

When you are passing the data from the excel sheet (With the first line containing the field names) to the internal table, how to ensure that the data be passed to the corresponding fields in the internal table even though its not in the order?

Regards

shekhar

Re: Transfering the data from excel sheet

Former Member — Fri, 15 Jun 2007 14:24:28 GMT

Hi Shekhar,

We need to call the below function module to upload the data from teh Excel sheer

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE' 
       EXPORTING 
            filename                = filename 
            i_begin_col             = begcol  " Start column where we want to pick the values
            i_begin_row             = begrow  " Start row where we want to pick the values

            i_end_col               = endcol  " end column where we want to pick the values

            i_end_row               = endrow    " End row where we want to pick the values
       TABLES 
            intern                  = intern   " You need to maintain the Correct fields accoring to the Excle sheet
       EXCEPTIONS 
            inconsistent_parameters = 1 
            upload_ole              = 2 
            OTHERS                  = 3.

Regards

Sudheer

Re: Transfering the data from excel sheet

Former Member — Fri, 15 Jun 2007 14:31:50 GMT

Check this sample code:


REPORT ZV_EXCEL_UPLOAD .

TYPE-POOLS: TRUXS.

PARAMETERS: P_FILE TYPE  RLGRAP-FILENAME.

TYPES: BEGIN OF T_DATATAB,
      COL1(2)    TYPE C,
      COL2(2)    TYPE C,
      COL3(6)    TYPE C,
      COL4(255)    TYPE C,
      COL5(255)    TYPE C,
      COL6(255)    TYPE C,
      COL7(255)    TYPE C,
      COL8(255)    TYPE C,
      END OF T_DATATAB.
DATA: IT_DATATAB TYPE STANDARD TABLE OF T_DATATAB,
      WA_DATATAB TYPE T_DATATAB.

DATA: IT_RAW TYPE TRUXS_T_TEXT_DATA.

* At selection screen
AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.
  CALL FUNCTION 'F4_FILENAME'
       EXPORTING
            FIELD_NAME = 'P_FILE'
       IMPORTING
            FILE_NAME  = P_FILE.


***********************************************************************
*START-OF-SELECTION.
START-OF-SELECTION.
  CLEAR: IT_DATATAB.
  REFRESH: IT_DATATAB.
  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
*     I_FIELD_SEPERATOR        =
*      I_LINE_HEADER            =  'X'
      I_TAB_RAW_DATA           =  IT_RAW       " WORK TABLE
      I_FILENAME               =  P_FILE
    TABLES
      I_TAB_CONVERTED_DATA     = IT_DATATAB[]    "ACTUAL DATA
   EXCEPTIONS
      CONVERSION_FAILED        = 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.


***********************************************************************
* END-OF-SELECTION.
END-OF-SELECTION.
*  SORT IT_DATATAB BY COL1.
*  DELETE ADJACENT DUPLICATES FROM IT_DATATAB COMPARING COL1.
  LOOP AT IT_DATATAB INTO WA_DATATAB.
    NEW-LINE.
    WRITE:  WA_DATATAB-COL1,
            WA_DATATAB-COL2,
            WA_DATATAB-COL3,
            WA_DATATAB-COL4,
            WA_DATATAB-COL5,
            WA_DATATAB-COL6,
            WA_DATATAB-COL7,
            WA_DATATAB-COL8.
  ENDLOOP.

Vasanth

Re: Transfering the data from excel sheet

Former Member — Fri, 15 Jun 2007 14:41:18 GMT

Hi,

Thanks for the replies..

but the problem i'm facing is fields can be in any order in the excel sheet. Whichever is the order, i need to assign the data to the proper fields...

Can we do that

Regards

Shekhar

Re: Transfering the data from excel sheet

Former Member — Fri, 15 Jun 2007 14:44:16 GMT

see the example program and which uploads data from xls file 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

Re: Transfering the data from excel sheet

Former Member — Fri, 15 Jun 2007 17:17:12 GMT

Hi Sekhar,

Dynamically i think its not possible findout the columns in the excel. Just we need to create the internal table as per the source file and need to import the data through any standard FM.

If you have different order in internal table, we wont get thecorrect data in to internal tables.

regards,

reward if its useful

Re: Transfering the data from excel sheet

Former Member — Mon, 18 Jun 2007 04:26:13 GMT

hi,

take the internal table same as excel file order then u will get correct population .

if u eant to check the populated data then download it into another file using gui_download there u can check the data

Re: Transfering the data from excel sheet

Former Member — Mon, 18 Jun 2007 08:29:45 GMT

Hi,

For uploading data we need to call the function module

ALSM_EXCEL_TO_INTERNAL_TABLE

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_file

i_begin_col = c_begcol

i_begin_row = c_begrow

i_end_col = c_endcol

i_end_row = c_endrow

TABLES

intern = int_count

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

i think it is not possible to upload the fields in proper order