Community
Application Development and Automation Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

Regarding excel to internal table

Former Member

‎2008 Mar 12 2:57 PM

0 Likes
605

Hi frnds,

This is my test data.

<code>

Version 0

From period 1

To period 12

Fiscal year 2008

Cost Center Cost Element Period 1 Period 2 Peiod 3 Period 4 Peiod 5 Period 6 Period 7 Period 8 Period 9 Period 10 Period 11 Period 12

Amt/Curr

104910 600100 186000 160000 150000 186000 175000 165000 186000 195000 200000 160000 170000 150000

610220 350000 250000 280000 195000 280000 250000 195000 280000 250000 210000 250000 250000

105310 600110 250600 300200 186000 250600 208000 300200 250600 300200 315000 412000 300200 175000

<code>

Frnds above is my test data which is presnt in excel sheet.

Version , from period , to period ,fisical year ,cost center and cost element are all header data.

104910( cost center ) , 600100(cost element).

From 186000(Period 1 ) to 150000(period 12) all are in one row only.

same for below records also.

so i want to upload it to Internal table .

can anyone tell me how to approach.

Below i m giving my way of approach but its not succesful

<code>

DATA: x TYPE i,

y TYPE i.

x = 1.

y = 1.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_fname

i_begin_col = 1

i_begin_row = 2

i_end_col = 14

i_end_row = 65

TABLES

intern = it_excel

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

READ TABLE it_excel INDEX 1.

itab_wa-var1 = it_excel-value.

READ TABLE it_excel INDEX 2.

itab_wa-var2 = it_excel-value.

READ TABLE it_excel INDEX 3.

itab_wa-var3 = it_excel-value.

READ TABLE it_excel INDEX 4.

itab_wa-var4 = it_excel-value.

READ TABLE it_excel INDEX 5.

itab_wa-var5 = it_excel-value.

READ TABLE it_excel INDEX 6.

itab_wa-var6 = it_excel-value.

APPEND itab_wa TO itab_hd.

REFRESH it_excel.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_fname

i_begin_col = 1

i_begin_row = 7

i_end_col = 14

i_end_row = 65

tables

intern = it_excel

  • EXCEPTIONS

  • INCONSISTENT_PARAMETERS = 1

  • UPLOAD_OLE = 2

  • OTHERS = 3

.

*if sy-subrc <> 0.

  • message e010(zz) with text-001. "Problem uploading Excel Spreadsheet

*endif.

clear it_final.

ENDFORM. "gui_upload

</code>

regards,

satish

Hi frnds,

This is my test data.

<code>

Version 0

From period 1

To period 12

Fiscal year 2008

Cost Center Cost Element Period 1 Period 2 Peiod 3 Period 4 Peiod 5 Period 6 Period 7 Period 8 Period 9 Period 10 Period 11 Period 12

Amt/Curr

104910 600100 186000 160000 150000 186000 175000 165000 186000 195000 200000 160000 170000 150000

610220 350000 250000 280000 195000 280000 250000 195000 280000 250000 210000 250000 250000

105310 600110 250600 300200 186000 250600 208000 300200 250600 300200 315000 412000 300200 175000

<code>

Frnds above is my test data which is presnt in excel sheet.

Version , from period , to period ,fisical year ,cost center and cost element are all header data.

104910( cost center ) , 600100(cost element).

From 186000(Period 1 ) to 150000(period 12) all are in one row only.

same for below records also.

so i want to upload it to Internal table .

can anyone tell me how to approach.

Below i m giving my way of approach but its not succesful

<code>

DATA: x TYPE i,

y TYPE i.

x = 1.

y = 1.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_fname

i_begin_col = 1

i_begin_row = 2

i_end_col = 14

i_end_row = 65

TABLES

intern = it_excel

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

READ TABLE it_excel INDEX 1.

itab_wa-var1 = it_excel-value.

READ TABLE it_excel INDEX 2.

itab_wa-var2 = it_excel-value.

READ TABLE it_excel INDEX 3.

itab_wa-var3 = it_excel-value.

READ TABLE it_excel INDEX 4.

itab_wa-var4 = it_excel-value.

READ TABLE it_excel INDEX 5.

itab_wa-var5 = it_excel-value.

READ TABLE it_excel INDEX 6.

itab_wa-var6 = it_excel-value.

APPEND itab_wa TO itab_hd.

REFRESH it_excel.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_fname

i_begin_col = 1

i_begin_row = 7

i_end_col = 14

i_end_row = 65

tables

intern = it_excel

  • EXCEPTIONS

  • INCONSISTENT_PARAMETERS = 1

  • UPLOAD_OLE = 2

  • OTHERS = 3

.

*if sy-subrc <> 0.

  • message e010(zz) with text-001. "Problem uploading Excel Spreadsheet

*endif.

clear it_final.

ENDFORM. "gui_upload

</code>

regards,

satish

Reply
3 REPLIES 3
Read only

Former Member

‎2008 Mar 12 3:09 PM

0 Likes
577

Any help frnds.

regards,

satish

Read only

Former Member

‎2008 Mar 12 3:10 PM

0 Likes
577

Hi,

this function module uploads data from excel to internal table,

check this sample code and make the changes needed in your program

TYPE-POOLS truxs.

TABLES:zmatnr.

DATA : itab LIKE alsmex_tabline OCCURS 0 WITH HEADER LINE.

DATA row LIKE alsmex_tabline-row.

data : g_matnr like mara-matnr.

data : count type i.

data : itab_count type i.

data : gi_final like zmatnr occurs 0 with header line.

*data : begin of gi_final occurs 0,

mat_old like mara-matnr,

mat_new like mara-matnr,

end of gi_final.

***********************Selection Screen*************************

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

PARAMETER : pfname LIKE rlgrap-filename OBLIGATORY.

select-options : records for count.

SELECTION-SCREEN END OF BLOCK b1.

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

*********************At Selection Screen*************************

AT SELECTION-SCREEN ON VALUE-REQUEST FOR pfname.

PERFORM search.

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

START-OF-SELECTION.

perform process.

form process.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = pfname

i_begin_col = 1

i_begin_row = 2

i_end_col = 12

i_end_row = 65000

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.

describe table itab lines itab_count.

row = 1.

loop at itab.

if itab-row row.

append gi_final.

clear gi_final.

endif.

case itab-col.

when '1'.

CLEAR G_MATNR.

gi_final-OLD_MATNR = itab-value.

CONCATENATE 'NEW' gi_final-old_matnr INTO itab-value.

gi_final-new_MATNR = itab-value.

endcase.

row = itab-row.

endloop.

append gi_final.

clear gi_final.

Regards

Sudheer

Read only

Former Member

‎2008 Mar 12 3:21 PM

0 Likes
577

try using the below

FORM GET_MAT_FROM_SPREAD .

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_FILE2

TABLES

i_tab_converted_data = i_spreadsheet[] . "ACTUAL DATA

  • EXCEPTIONS

  • conversion_failed = 1

  • OTHERS = 2.

IF SY-SUBRC = 0.

ENDIF.

where i_spread sheet[] is the internal table

I_SPREADSHEET TYPE STANDARD TABLE OF T_MATERIAL,

and t_material structure is as follows

TYPES:

BEGIN OF T_MATERIAL,

INDEX(3) TYPE C,

MATNR TYPE MARA-MATNR, "Material Number

MENGE TYPE MSEG-MENGE,

MEINS TYPE MARA-MEINS, "Base Unit of Measure

MATKL TYPE MARA-MATKL, "Material group

WGBEZ60 TYPE T023T-WGBEZ60,"Material grp Desc.

END OF T_MATERIAL,

Note:u should give the structure same as the input ur giving in excel file

for eg:in excel if ur giving:first material then description

try to give the structure t_material also in the same order