topic Re: Upload data from Excel to Internal table in Application Development and Automation Discussions

Upload data from Excel to Internal table

Former Member — Mon, 26 Nov 2007 13:56:31 GMT

Hi,

I am facing a peculiar problem in uploading the data in excel sheet to internal table.

The excel file contains material number and product hierarchy number. The product hierarchy number for some material is downloaded as 5.73101E17 for the value 573100910115782000. Though I expand the sheet the display is the same 5.73101E17. When i upload the sheet using the f'n module "ALSM_EXCEL_TO_INTERNAL_TABLE" the value is taken as it is (5.73101E+17) into the internal table which is wrong. I tried to change the data type to type n. Then the value is uploaded as 5731011700000000.

Please propose a solution to this without having to change the format of the column from general to "number without decimals"

Regards,

Sam

Re: Upload data from Excel to Internal table

Former Member — Mon, 26 Nov 2007 14:19:57 GMT

Try by changing the excel sheet column format to TEXT.

it will work.

Re: Upload data from Excel to Internal table

Former Member — Mon, 26 Nov 2007 14:24:50 GMT

hi,

try this

select the required field in the excel.right click.then click on format cells.select special and on the right side you will find type.click zip code.

Re: Upload data from Excel to Internal table

Former Member — Mon, 26 Nov 2007 14:39:49 GMT

Hello Jyothi & Raghu,

I do not want to make any changes in the format of the excel sheet columns. Is there any way that i make the changes in the program itself.

Regards,

Sam

Re: Upload data from Excel to Internal table

Former Member — Tue, 27 Nov 2007 05:02:41 GMT

Hi Samson,

Can u try this one FM's

As per u r requirement delcare the Internal table (ITAB).

DATA : FILE TYPE TRUXS_T_TEXT_DATA,

FNAME TYPE RLGRAP-FILENAME.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING
PROGRAM_NAME = SYST-REPID
DYNPRO_NUMBER = SYST-DYNNR
FIELD_NAME = ' '
STATIC = ' '
MASK = ' '

CHANGING

file_name = FNAME

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.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR =
I_LINE_HEADER =

i_tab_raw_data = DATA

i_filename = FNAME

tables

i_tab_converted_data = ITAB[]

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.

Regards,

Vijay

Re: Upload data from Excel to Internal table

Former Member — Tue, 27 Nov 2007 05:05:17 GMT

see this example code which EXCEL TO INTERNAL TABLE AND THEN TO APPLICATION

&----

*& Report ZSD_EXCEL_INT_APP

&----

REPORT ZSD_EXCEL_INT_APP.

parameter: file_nm type localfile.

types : begin of it_tab1,

f1(20),

f2(40),

f3(20),

end of it_tab1.

data : it_tab type table of ALSMEX_TABLINE with header line,

file type rlgrap-filename.

data : it_tab2 type it_tab1 occurs 1,

wa_tab2 type it_tab1,

w_message(100) TYPE c.

at selection-screen on value-request for file_nm.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

PROGRAM_NAME = SYST-REPID
DYNPRO_NUMBER = SYST-DYNNR
FIELD_NAME = ' '

STATIC = 'X'

MASK = ' '

CHANGING

file_name = file_nm

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.

refresh it_tab2[].clear wa_tab2.

file = file_nm.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = file

i_begin_col = '1'

i_begin_row = '1'

i_end_col = '10'

i_end_row = '35'

tables

intern = it_tab

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 it_tab.

case it_tab-col.

when '002'.

wa_tab2-f1 = it_tab-value.

when '004'.

wa_tab2-f2 = it_tab-value.

when '008'.

wa_tab2-f3 = it_tab-value.

endcase.

at end of row.

append wa_tab2 to it_tab2.

clear wa_tab2.

endat.

endloop.

data : p_file TYPE rlgrap-filename value 'TEST3.txt'.

OPEN DATASET p_file FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.

*--- Display error messages if any.

IF sy-subrc NE 0.

MESSAGE e001(zsd_mes).

EXIT.

ELSE.

*---Data is downloaded to the application server file path

LOOP AT it_tab2 INTO wa_tab2.

TRANSFER wa_tab2 TO p_file.

ENDLOOP.

ENDIF.

*--Close the Application server file (Mandatory).

CLOSE DATASET p_file.

loop at it_tab2 into wa_tab2.

write : / wa_tab2-f1,wa_tab2-f2,wa_tab2-f3.

endloop.

Re: Upload data from Excel to Internal table

Former Member — Tue, 27 Nov 2007 09:13:48 GMT

Hello,

I have tried to use the two function modules earlier, but the problem still persists.

Regards

Sam