DATA UPLOAD into 5 Different Internal Tables !!!

[Former Member]

Hi ABAPers,

I have data in 5 different TABS of an Excel Sheet.

I have to UPLOAD this data in these different TABS to 5 different Internal tables.

Can any one give me a solution to this ???

[Former Member]

first upload all the data into one internal table ........................

after words based on u r relavence pass it to [with move statement] 5 internal tablles....

reward points if helpful.....

[Former Member]

As i said.. DATA is available in 5 different tabs (Pages) in an EXCEL SHEET.

[Former Member]

Hi,

Use the FM ALSM_EXCEL_TO_INTERNAL_TABLE for loading data from multiple sheets

Regards,

Aparna

[Former Member]

But in ALSM_EXCEL_TO_INTERNAL_TABLE, only we can specify begin and end of Column/Row number. We cannot specify the page (tab) names na?

[abdulazeez12]

declare 5 internal tables like jtab1 jtab2 jtab3..jab5.

Upload the excel data into one internal table first using the function module GUI_UPLOAD.

after uploading into internal table itab..

loop at itab.

jtab1-field = itab-field1.

jtab2-field = itab-field2.

;

;

;

append: jtab1, jtab2...jtab5.

endloop.

[Former Member]

take data from all internal tabels into final internal table.....and proceed in below way......

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

fill ur inetnal table and call call the below form DOWNLOAD_TO_EXCEL, This Form is displaying an internal table it_outtab

Move data from it_outtab to it_excel

it_excel will contain all the charecter fields

DATA : BEGIN OF IT_EXCEL OCCURS 0,

FLD1(32),

FLD2(32),

FLD3(32),

FLD4(32),

FLD5(32),

FLD6(32),

FLD7(32),

FLD8(32),

FLD9(32),

FLD10(32),

FLD11(32),

FLD12(32),

FLD13(32),

FLD14(32),

FLD15(32),

FLD16(32),

FLD17(32),

FLD18(32),

FLD19(32),

FLD20(32),

FLD21(32),

END OF IT_EXCEL.

FORM DOWNLOAD_TO_EXCEL.

PERFORM GET_FILE_NAME .

CLEAR IT_EXCEL.

REFRESH IT_EXCEL.

IF NOT FNAME IS INITIAL .

PERFORM POPULATE_HEADER_DETAIL.

LOOP AT IT_OUTTAB.

IT_EXCEL-FLD1 = IT_OUTTAB-BANFN.

IT_EXCEL-FLD2 = IT_OUTTAB-BNFPO.

IT_EXCEL-FLD3 = IT_OUTTAB-EBELN.

IT_EXCEL-FLD4 = IT_OUTTAB-EBELP.

IT_EXCEL-FLD5 = IT_OUTTAB-STYLE.

IT_EXCEL-FLD6 = IT_OUTTAB-SKU.

IT_EXCEL-FLD7 = IT_OUTTAB-LP_COLOR.

IT_EXCEL-FLD8 = IT_OUTTAB-MAKTX.

IT_EXCEL-FLD9 = IT_OUTTAB-CURR_PRICE.

IF NOT IT_OUTTAB-VALID_FROM IS INITIAL.

CONCATENATE IT_OUTTAB-VALID_FROM+4(2) '/'

IT_OUTTAB-VALID_FROM+6(2) '/'

IT_OUTTAB-VALID_FROM(4) INTO IT_EXCEL-FLD10.

ENDIF.

IF NOT IT_OUTTAB-VALID_TO IS INITIAL.

CONCATENATE IT_OUTTAB-VALID_TO+4(2) '/'

IT_OUTTAB-VALID_TO+6(2) '/'

IT_OUTTAB-VALID_TO(4) INTO IT_EXCEL-FLD11.

ENDIF.

IT_EXCEL-FLD12 = IT_OUTTAB-FUT_PRICE.

IT_EXCEL-FLD13 = IT_OUTTAB-FAC_PRICE.

IF NOT IT_OUTTAB-FAC_VALID_FROM IS INITIAL.

CONCATENATE IT_OUTTAB-FAC_VALID_FROM+4(2) '/'

IT_OUTTAB-FAC_VALID_FROM+6(2) '/'

IT_OUTTAB-FAC_VALID_FROM(4) INTO IT_EXCEL-FLD14.

ENDIF.

IF NOT IT_OUTTAB-FAC_VALID_TO IS INITIAL.

CONCATENATE IT_OUTTAB-FAC_VALID_TO+4(2) '/'

IT_OUTTAB-FAC_VALID_TO+6(2) '/'

IT_OUTTAB-FAC_VALID_TO(4) INTO IT_EXCEL-FLD15.

ENDIF.

IT_EXCEL-FLD16 = IT_OUTTAB-JPN_PRICE.

IT_EXCEL-FLD17 = IT_OUTTAB-EAN11_1.

IT_EXCEL-FLD18 = IT_OUTTAB-EAN11_2.

IT_EXCEL-FLD19 = IT_OUTTAB-HANG_TAG_STAT.

IT_EXCEL-FLD20 = IT_OUTTAB-LABEL_STAT.

IT_EXCEL-FLD21 = IT_OUTTAB-CJI_STAT.

APPEND IT_EXCEL.

CLEAR IT_EXCEL.

ENDLOOP.

CALL FUNCTION 'RH_START_EXCEL_WITH_DATA'

EXPORTING

CHECK_VERSION = ' '

DATA_NAME = FNAME

DATA_PATH_FLAG = ''

DATA_TYPE = 'DAT'

• DATA_BIN_FILE_SIZE =

• MACRO_NAME = ' '

MACRO_PATH_FLAG = 'E'

FORCE_START = ' '

WAIT = ''

• IMPORTING

• WINID =

TABLES

DATA_TAB = IT_EXCEL

EXCEPTIONS

NO_BATCH = 1

EXCEL_NOT_INSTALLED = 2

WRONG_VERSION = 3

INTERNAL_ERROR = 4

INVALID_TYPE = 5

CANCELLED = 6

DOWNLOAD_ERROR = 7

OTHERS = 8

.

IF SY-SUBRC <> 0.

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

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

ENDIF.

ENDIF .

ENDFORM. " DOWNLOAD_TO_EXCEL

FORM GET_FILE_NAME.

DATA: TMP_FILENAME LIKE RLGRAP-FILENAME,

GLOBAL_FILEMASK_ALL(80),

GLOBAL_FILEMASK_MASK(20), GLOBAL_FILEMASK_TEXT(20),

GLOBAL_DOWNLOAD_PATH LIKE RLGRAP-FILENAME,

DEF_PATH LIKE RLGRAP-FILENAME,

FIELDLN TYPE I,

MODE TYPE C,

TMP_MASK LIKE GLOBAL_FILEMASK_ALL.

FIELD-SYMBOLS: .

IF GLOBAL_FILEMASK_MASK IS INITIAL.

TMP_MASK = ',.,..'.

ELSE.

TMP_MASK = ','.

WRITE GLOBAL_FILEMASK_TEXT TO TMP_MASK+1.

WRITE ',' TO TMP_MASK+21.

WRITE GLOBAL_FILEMASK_MASK TO TMP_MASK+22.

WRITE '.' TO TMP_MASK+42.

CONDENSE TMP_MASK NO-GAPS.

ENDIF.

IF NOT GLOBAL_FILEMASK_ALL IS INITIAL.

TMP_MASK = GLOBAL_FILEMASK_ALL.

ENDIF.

FIELDLN = STRLEN( DEF_PATH ) - 1.

ASSIGN DEF_PATH+FIELDLN(1) TO .

ENDIF.

CALL FUNCTION 'WS_FILENAME_GET'

EXPORTING

DEF_FILENAME = FNAME "rlgrap-filename

DEF_PATH = DEF_PATH

MASK = TMP_MASK

MODE = MODE

• TITLE = ' '

IMPORTING

FILENAME = FNAME

• RC =

EXCEPTIONS

INV_WINSYS = 01

NO_BATCH = 02

SELECTION_CANCEL = 03

SELECTION_ERROR = 04.

IF SY-SUBRC = 0.

FNAME = TMP_FILENAME.

ELSE.

ENDIF.

ENDFORM. " GET_FILE_NAME

FORM POPULATE_HEADER_DETAIL.

IT_EXCEL-FLD8 = 'S U D H E E R'.

APPEND IT_EXCEL.

CLEAR IT_EXCEL.

IT_EXCEL-FLD1 = 'Price Validation Report'.

IT_EXCEL-FLD14 = 'Page'.

APPEND IT_EXCEL.

CLEAR IT_EXCEL.

IT_EXCEL-FLD1 = 'Vendor Number/Name'.

IT_EXCEL-FLD3 = 'Plan Month'.

IT_EXCEL-FLD4 = 'Effective In-store-Date'.

APPEND IT_EXCEL.

CLEAR IT_EXCEL.

IT_EXCEL-FLD1 = VENDOR_NAME.

IT_EXCEL-FLD3 = P_BEDNR.

IT_EXCEL-FLD4 = P_EFF_DT.

APPEND IT_EXCEL.

CLEAR IT_EXCEL.

• sudheer start

APPEND IT_EXCEL.

IT_EXCEL-FLD1 = 'PR'.

IT_EXCEL-FLD2 = 'Line'.

IT_EXCEL-FLD3 = 'PO'.

IT_EXCEL-FLD4 = 'PO Line'.

IT_EXCEL-FLD5 = 'Style'.

IT_EXCEL-FLD6 = 'SKU'.

IT_EXCEL-FLD7 = 'Long Color'.

IT_EXCEL-FLD8 = 'Material Description'.

IT_EXCEL-FLD9 = 'Current Price'.

IT_EXCEL-FLD10 = 'validity'.

IT_EXCEL-FLD11 = 'Period'.

IT_EXCEL-FLD12 = 'Future Price'.

IT_EXCEL-FLD13 = 'Factory Price'.

IT_EXCEL-FLD14 = 'Validity'.

IT_EXCEL-FLD15 = 'Period'.

IT_EXCEL-FLD16 = 'Japan Price'.

IT_EXCEL-FLD17 = 'First UPC'.

IT_EXCEL-FLD18 = 'Factory UPC'.

IT_EXCEL-FLD19 = 'Hang Tag Status'.

IT_EXCEL-FLD20 = 'Label Status'.

IT_EXCEL-FLD21 = 'CJI Label Status'.

*sudheer end

APPEND IT_EXCEL.

CLEAR IT_EXCEL.

ENDFORM. " POPULATE_HEADER_DETAIL

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

Check these links for sample code :

http://www.sapdevelopment.co.uk/ms/ms_excel.htm

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

please check this example:

FORM download.

DATA: lh_file1 TYPE rlgrap-filename,

lh_file2 TYPE rlgrap-filename,

lh_file3 TYPE rlgrap-filename,

lh_datda(1).

CLEAR: lh_file1, lh_file2, lh_file3.

• Download neue Daten

CONCATENATE xh_pfad 'Datafile materialmaster new ' sy-datum(4)

sy-datum4(2) sy-datum6(2) sy-uzeit '.xls' INTO lh_file1.

• working area

DATA: index TYPE i,

lh_col TYPE i.

FIELD-SYMBOLS: .

SORT xt_bmm00 BY matnr.

SORT xt_bmmh1 BY matnr.

SORT xt_bmmh4 BY matnr.

SORT xt_bmmh7 BY matnr.

IF NOT xt_bmm00[] IS INITIAL.

MOVE 'X' TO lh_datda.

DO 4 TIMES.

CASE sy-index.

WHEN 1.

CREATE OBJECT lh_excel 'EXCEL.APPLICATION' .

SET PROPERTY OF lh_excel 'Visible' = 1 .

GET PROPERTY OF lh_excel 'Workbooks' = lh_workbooks .

GET PROPERTY OF lh_workbooks 'Application' = lh_application .

SET PROPERTY OF lh_application 'SheetsInNewWorkbook' = 1 .

CALL METHOD OF lh_workbooks 'Add' = lh_workbook .

CALL METHOD OF lh_workbook 'Sheets' = lh_sheets .

CALL METHOD OF lh_sheets 'Item' = lh_sheet

EXPORTING #1 = sy-index .

SET PROPERTY OF lh_sheet 'NAME' = 'BMMH7'.

• Überschrift erstellen

CLEAR xt_ftab.

REFRESH xt_ftab.

CALL FUNCTION 'GET_FIELDTAB'

EXPORTING

langu = sy-langu

tabname = '/SIE/TS_MM01S005'

withtext = ' '

only = 'T'

TABLES

fieldtab = xt_ftab

EXCEPTIONS

OTHERS = 1.

CLEAR lh_col.

LOOP AT xt_ftab.

ADD 1 TO lh_col.

PERFORM fill_cell

USING 1 lh_col xt_ftab-fieldname lh_excel lh_cells.

ENDLOOP.

LOOP AT xt_bmmh7.

index = sy-tabix + 1.

DO 4 TIMES.

ASSIGN COMPONENT sy-index OF STRUCTURE xt_bmmh7 TO lh_excel lh_cells.

ENDIF.

ENDDO.

ENDLOOP.

WHEN 2.

GET PROPERTY OF lh_workbook 'Sheets' = lh_sheets .

CALL METHOD OF lh_sheets 'Add' = lh_newsheet .

SET PROPERTY OF lh_newsheet 'Name' = 'BMMH4'.

CALL METHOD OF lh_excel 'WorkSheets' = lh_activesheet

EXPORTING #1 = 'BMMH4'.

CALL METHOD OF lh_activesheet 'Activate' .

• Überschrift erstellen

CLEAR xt_ftab.

REFRESH xt_ftab.

CALL FUNCTION 'GET_FIELDTAB'

EXPORTING

langu = sy-langu

tabname = '/SIE/TS_MM01S004'

withtext = ' '

only = 'T'

TABLES

fieldtab = xt_ftab

EXCEPTIONS

OTHERS = 1.

CLEAR lh_col.

LOOP AT xt_ftab.

ADD 1 TO lh_col.

PERFORM fill_cell

USING 1 lh_col xt_ftab-fieldname lh_excel lh_cells.

ENDLOOP.

LOOP AT xt_bmmh4.

index = sy-tabix + 1.

DO 4 TIMES.

ASSIGN COMPONENT sy-index OF STRUCTURE xt_bmmh4 TO lh_excel lh_cells.

ENDIF.

ENDDO.

ENDLOOP.

WHEN 3.

GET PROPERTY OF lh_workbook 'Sheets' = lh_sheets .

CALL METHOD OF lh_sheets 'Add' = lh_newsheet .

SET PROPERTY OF lh_newsheet 'Name' = 'BMMH1'.

CALL METHOD OF lh_excel 'WorkSheets' = lh_activesheet

EXPORTING #1 = 'BMMH1'.

CALL METHOD OF lh_activesheet 'Activate' .

• Überschrift erstellen

CLEAR xt_ftab.

REFRESH xt_ftab.

CALL FUNCTION 'GET_FIELDTAB'

EXPORTING

langu = sy-langu

tabname = '/SIE/TS_MM01S003'

withtext = ' '

only = 'T'

TABLES

fieldtab = xt_ftab

EXCEPTIONS

OTHERS = 1.

CLEAR lh_col.

LOOP AT xt_ftab.

• Die folgende Abfrage ist notwendig, weil der Namensraum ZZ...

• in der Struktur nicht erlaubt ist, aber im Excel-Sheet als

• Überschrift gebraucht wird.

IF xt_ftab-fieldname = 'SPIRIDON'.

MOVE 'ZZSSN' TO xt_ftab-fieldname.

ENDIF.

ADD 1 TO lh_col.

PERFORM fill_cell

USING 1 lh_col xt_ftab-fieldname lh_excel lh_cells.

ENDLOOP.

LOOP AT xt_bmmh1.

index = sy-tabix + 1.

DO 72 TIMES.

ASSIGN COMPONENT sy-index OF STRUCTURE xt_bmmh1 TO lh_excel lh_cells.

ENDIF.

ENDDO.

ENDLOOP.

WHEN 4.

GET PROPERTY OF lh_workbook 'Sheets' = lh_sheets .

CALL METHOD OF lh_sheets 'Add' = lh_newsheet .

SET PROPERTY OF lh_newsheet 'Name' = 'BMM00'.

CALL METHOD OF lh_excel 'WorkSheets' = lh_activesheet

EXPORTING #1 = 'BMM00'.

CALL METHOD OF lh_activesheet 'Activate' .

• Überschrift erstellen

CLEAR xt_ftab.

REFRESH xt_ftab.

CALL FUNCTION 'GET_FIELDTAB'

EXPORTING

langu = sy-langu

tabname = '/SIE/TS_MM01S002'

withtext = ' '

only = 'T'

TABLES

fieldtab = xt_ftab

EXCEPTIONS

OTHERS = 1.

CLEAR lh_col.

LOOP AT xt_ftab.

ADD 1 TO lh_col.

PERFORM fill_cell

USING 1 lh_col xt_ftab-fieldname lh_excel lh_cells.

ENDLOOP.

LOOP AT xt_bmm00.

index = sy-tabix + 1.

DO 21 TIMES.

ASSIGN COMPONENT sy-index OF STRUCTURE xt_bmm00 TO lh_excel lh_cells.

ENDIF.

ENDDO.

ENDLOOP.

ENDCASE.

ENDDO.

CALL METHOD OF lh_workbook 'SAVEAS'

EXPORTING #1 = lh_file1.

CALL METHOD OF lh_excel 'QUIT'.

FREE OBJECT: lh_cells, lh_sheet, lh_sheets, lh_application,

lh_workbook, lh_workbooks, lh_excel,

lh_chart, lh_activesheet.

ENDIF.

• Download geänderter Materialstammdaten

CONCATENATE xh_pfad 'Datafile materialmaster change ' sy-datum(4)

sy-datum4(2) sy-datum6(2) sy-uzeit '.xls' INTO lh_file2.

IF NOT xt_emmh1[] IS INITIAL.

MOVE 'X' TO lh_datda.

DO 2 TIMES.

CALL METHOD OF lh_sheets 'Item' = lh_sheet

EXPORTING #1 = sy-index .

CASE sy-index.

WHEN 1.

CREATE OBJECT lh_excel 'EXCEL.APPLICATION' .

SET PROPERTY OF lh_excel 'Visible' = 1 .

GET PROPERTY OF lh_excel 'Workbooks' = lh_workbooks .

GET PROPERTY OF lh_workbooks 'Application' = lh_application .

SET PROPERTY OF lh_application 'SheetsInNewWorkbook' = 1 .

CALL METHOD OF lh_workbooks 'Add' = lh_workbook .

CALL METHOD OF lh_workbook 'Sheets' = lh_sheets .

CALL METHOD OF lh_sheets 'Item' = lh_sheet

EXPORTING #1 = sy-index .

SET PROPERTY OF lh_sheet 'NAME' = 'BMMH7'.

• Überschrift erstellen

CLEAR xt_ftab.

REFRESH xt_ftab.

CALL FUNCTION 'GET_FIELDTAB'

EXPORTING

langu = sy-langu

tabname = '/SIE/TS_MM01S005'

withtext = ' '

only = 'T'

TABLES

fieldtab = xt_ftab

EXCEPTIONS

OTHERS = 1.

CLEAR lh_col.

LOOP AT xt_ftab.

ADD 1 TO lh_col.

PERFORM fill_cell

USING 1 lh_col xt_ftab-fieldname lh_excel lh_cells.

ENDLOOP.

LOOP AT xt_emmh7.

index = sy-tabix + 1.

DO 4 TIMES.

ASSIGN COMPONENT sy-index OF STRUCTURE xt_emmh7 TO lh_excel lh_cells.

ENDIF.

ENDDO.

ENDLOOP.

WHEN 2.

GET PROPERTY OF lh_workbook 'Sheets' = lh_sheets .

CALL METHOD OF lh_sheets 'Add' = lh_newsheet .

SET PROPERTY OF lh_newsheet 'Name' = 'BMMH1'.

CALL METHOD OF lh_excel 'WorkSheets' = lh_activesheet

EXPORTING #1 = 'BMMH1'.

CALL METHOD OF lh_activesheet 'Activate' .

• Überschrift erstellen

CLEAR xt_ftab.

REFRESH xt_ftab.

CALL FUNCTION 'GET_FIELDTAB'

EXPORTING

langu = sy-langu

tabname = '/SIE/TS_MM01S003'

withtext = ' '

only = 'T'

TABLES

fieldtab = xt_ftab

EXCEPTIONS

OTHERS = 1.

CLEAR lh_col.

LOOP AT xt_ftab.

• Die folgende Abfrage ist notwendig, weil der Namensraum ZZ...

• in der Struktur nicht erlaubt ist, aber im Excel-Sheet als

• Überschrift gebraucht wird.

IF xt_ftab-fieldname = 'SPIRIDON'.

MOVE 'ZZSSN' TO xt_ftab-fieldname.

ENDIF.

ADD 1 TO lh_col.

PERFORM fill_cell

USING 1 lh_col xt_ftab-fieldname lh_excel lh_cells.

ENDLOOP.

LOOP AT xt_emmh1.

index = sy-tabix + 1.

DO 72 TIMES.

ASSIGN COMPONENT sy-index OF STRUCTURE xt_emmh1 TO lh_excel lh_cells.

ENDIF.

ENDDO.

ENDLOOP.

ENDCASE.

ENDDO.

CALL METHOD OF lh_workbook 'SAVEAS'

EXPORTING #1 = lh_file2.

CALL METHOD OF lh_excel 'QUIT'.

FREE OBJECT: lh_cells, lh_sheet, lh_sheets, lh_application,

lh_workbook, lh_workbooks, lh_excel,

lh_chart, lh_activesheet.

ENDIF.

• Download geänderter Texte

CONCATENATE xh_pfad 'Datafile materialmaster change PO ' sy-datum(4)

sy-datum4(2) sy-datum6(2) sy-uzeit '.xls' INTO lh_file3.

IF NOT xt_tmmh7[] IS INITIAL.

CLEAR: lh_cells, lh_sheet, lh_sheets, lh_application,

lh_workbook, lh_workbooks, lh_excel,

lh_chart, lh_activesheet.

MOVE 'X' TO lh_datda.

CREATE OBJECT lh_excel 'EXCEL.APPLICATION' .

SET PROPERTY OF lh_excel 'Visible' = 1 .

GET PROPERTY OF lh_excel 'Workbooks' = lh_workbooks .

GET PROPERTY OF lh_workbooks 'Application' = lh_application .

SET PROPERTY OF lh_application 'SheetsInNewWorkbook' = 1 .

CALL METHOD OF lh_workbooks 'Add' = lh_workbook .

CALL METHOD OF lh_workbook 'Sheets' = lh_sheets .

CALL METHOD OF lh_sheets 'Item' = lh_sheet

EXPORTING #1 = 1 .

SET PROPERTY OF lh_sheet 'NAME' = 'BMMH7'.

• Überschrift erstellen

CLEAR xt_ftab.

REFRESH xt_ftab.

CALL FUNCTION 'GET_FIELDTAB'

EXPORTING

langu = sy-langu

tabname = '/SIE/TS_MM01S005'

withtext = ' '

only = 'T'

TABLES

fieldtab = xt_ftab

EXCEPTIONS

OTHERS = 1.

CLEAR lh_col.

LOOP AT xt_ftab.

ADD 1 TO lh_col.

PERFORM fill_cell

USING 1 lh_col xt_ftab-fieldname lh_excel lh_cells.

ENDLOOP.

LOOP AT xt_tmmh7.

index = sy-tabix + 1.

DO 4 TIMES.

ASSIGN COMPONENT sy-index OF STRUCTURE xt_tmmh7 TO lh_excel lh_cells.

ENDIF.

ENDDO.

ENDLOOP.

CALL METHOD OF lh_workbook 'SAVEAS'

EXPORTING #1 = lh_file3.

CALL METHOD OF lh_excel 'QUIT'.

FREE OBJECT: lh_cells, lh_sheet, lh_sheets, lh_application,

lh_workbook, lh_workbooks, lh_excel,

lh_chart, lh_activesheet.

ENDIF.

IF lh_datda NE 'X'.

MESSAGE i016.

ENDIF.

ENDFORM. " download

FORM fill_cell USING p_i

value

p_customer_cell1

h_excel

h_cells.

CALL METHOD OF h_excel 'Cells' = h_cells EXPORTING #1 = p_i #2 = value.

SET PROPERTY OF lh_cells 'Numberformat' = '@'.

SET PROPERTY OF h_cells 'Value' = p_customer_cell1.

ENDFORM. " fill_cell

[hymavathi_oruganti]

if u have multiple sheets, the only solution is ole programming. if u want a sample code , i will post it.

[Former Member]

Could u please post that sample code?

Thanks,

Srihari.