uploading into internal tables

[Former Member]

Do you know of a function module I can use to uplaod multiple sheets to internal tables? Please provide sample codes if possible. Thanks.

[Former Member]

Hi Chakradhar,

ABAP code for uploading an Excel document into an internal table. See code below for structures. The

code is base on uploading a simple Excel spreadsheet.

&———————————————————————

*& Report UPLOAD_EXCEL *

*& *

&———————————————————————

*& *

*& Upload and excel file into an internal table using the following *

*& function module: ALSM_EXCEL_TO_INTERNAL_TABLE *

&———————————————————————

REPORT UPLOAD_EXCEL no standard page heading.

*Data Declaration

*—————-

data: itab like alsmex_tabline occurs 0 with header line.

• Has the following format:

• Row number | Colum Number | Value

• —————————————

• i.e. 1 1 Name1

• 2 1 Joe

TYPES: Begin of t_record,

name1 like itab-value,

name2 like itab-value,

age like itab-value,

End of t_record.

DATA: it_record type standard table of t_record initial size 0,

wa_record type t_record.

DATA: gd_currentrow type i.

*Selection Screen Declaration

*—————————-

PARAMETER p_infile like rlgrap-filename.

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

*START OF SELECTION

call function ‘ALSM_EXCEL_TO_INTERNAL_TABLE’

exporting

filename = p_infile

i_begin_col = ‘1′

i_begin_row = ‘2′ “Do not require headings

i_end_col = ‘14′

i_end_row = ‘31′

tables

intern = itab

exceptions

inconsistent_parameters = 1

upload_ole = 2

others = 3.

if sy-subrc <> 0.

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

endif.

• Sort table by rows and colums

sort itab by row col.

• Get first row retrieved

read table itab index 1.

• Set first row retrieved to current row

gd_currentrow = itab-row.

loop at itab.

• Reset values for next row

if itab-row ne gd_currentrow.

append wa_record to it_record.

clear wa_record.

gd_currentrow = itab-row.

endif.

case itab-col.

when ‘0001&#8242;. “First name

wa_record-name1 = itab-value.

when ‘0002&#8242;. “Surname

wa_record-name2 = itab-value.

when ‘0003&#8242;. “Age

wa_record-age = itab-value.

endcase.

endloop.

append wa_record to it_record.

*!! Excel data is now contained within the internal table IT_RECORD

• Display report data for illustration purposes

loop at it_record into wa_record.

write:/ sy-vline,

(10) wa_record-name1, sy-vline,

(10) wa_record-name2, sy-vline,

(10) wa_record-age, sy-vline.

endloop.

Regards,

Chandru

[former_member156446]

Hi Chak

Check these Fm's

FM ALSM_EXCEL_TO_INTERNAL_TABLE

Use this code

&---------------------------------------------------------------------
*& Report ZE0232_EXCEL_SHEET *
*& *
&---------------------------------------------------------------------
*& *
*& *
&---------------------------------------------------------------------

REPORT ZE0232_EXCEL_SHEET LINE-SIZE 700.

DATA : ITAB1 LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.

DATA : B1 TYPE I VALUE 1,

C1 TYPE I VALUE 1,

B2 TYPE I VALUE 100,

C2 TYPE I VALUE 9999.


CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = 'C:\Documents and Settings\uday\Desktop\Excel files\EXCEL_TRAIN.XLS'

I_BEGIN_COL = B1

I_BEGIN_ROW = C1

I_END_COL = B2

I_END_ROW = C2

TABLES

INTERN = itab1

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 itab1.
ON CHANGE OF ITAB1-ROW.
WRITE: /.
ENDON.
write:itab1-VALUE.

Endloop.

IF HELPFULL REWARD

[Former Member]

Hi Chakradhar, this code is to upload multiple files to internal table.

Data:LV_FILES TYPE FILETABLE,

LV_RC TYPE I,

I_DIR TYPE STRING,

IT_TEXTO TYPE STANDARD TABLE OF S_TEXTO ,

TMP_TEXTO TYPE STANDARD TABLE OF S_TEXTO ,

WA_TEXTO LIKE LINE OF IT_TEXTO.

"Open a dialog

CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_OPEN_DIALOG

EXPORTING

INITIAL_DIRECTORY = 'C:

'

MULTISELECTION = 'X'

CHANGING

FILE_TABLE = LV_FILES

RC = LV_RC.

CLEAR :IT_TEXTO[].

LOOP AT LV_FILES INTO I_DIR.

CLEAR TMP_TEXTO[].

CALL METHOD CL_GUI_FRONTEND_SERVICES=>GUI_UPLOAD

EXPORTING

FILENAME = I_DIR

READ_BY_LINE = 'X' " FILETYPE = 'ASC'

CHANGING

DATA_TAB =TMP_TEXTO

APPEND LINES OF TMP_TEXTO TO IT_TEXTO.

ENDLOOP.

or,

try this:

DATA: LV_CNT TYPE I,

FILE TYPE STRING value 'C:\*.TXT',

LT_FILENAME TYPE FILETABLE,

USER_ACT TYPE I.

*

CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_OPEN_DIALOG

EXPORTING

WINDOW_TITLE = 'PC-File Selektion'

DEFAULT_FILENAME = FILE

MULTISELECTION = 'X'

CHANGING

FILE_TABLE = LT_FILENAME

RC = LV_CNT

USER_ACTION = USER_ACT

EXCEPTIONS

FILE_OPEN_DIALOG_FAILED = 1

CNTL_ERROR = 2

ERROR_NO_GUI = 3.

*

look into table LT_FILENAME.

kindly reward if found helpful.

cheers,

Hema.

[Former Member]

U can use the function module ALSM_EXCEL_TO_INTERNAL_TABLE to upload the excel files into the internal tables.

This FM will return u the itab in the following format :

Row Column Value.

1 1 A

1 2 B.

Then u have to write the code to convert it into an itab of proper format. This u can do using the field symbols.

Similarly, u can upload the data from another excel sheet into itab2 and then using the MODIFY dbtab/INSERT dbtab statement, u can insert the data into the database tables.

u can write a program like this also

Please use FM 'GUI_UPLOAD'.

types: begin of ttab,

rec(1000) type c,

end of ttab.

types: begin of tdat,

fld1(10) type c,

fld2(10) type c,

fld3(10) type c,

end of tdat.

data: itab type table of ttab with header line.

data: idat type table of tdat with header line.

data: file_str type string.

parameters: p_file type localfile.

at selection-screen on value-request for p_file.

call function 'KD_GET_FILENAME_ON_F4'

exporting

static = 'X'

changing

file_name = p_file.

start-of-selection.

file_str = p_file.

call function 'GUI_UPLOAD'

exporting

filename = file_str

filetype = 'ASC'

tables

data_tab = itab

exceptions

file_open_error = 1

file_read_error = 2

no_batch = 3

gui_refuse_filetransfer = 4

invalid_type = 5

no_authority = 6

unknown_error = 7

bad_data_format = 8

header_not_allowed = 9

separator_not_allowed = 10

header_too_long = 11

unknown_dp_error = 12

access_denied = 13

dp_out_of_memory = 14

disk_full = 15

dp_timeout = 16

others = 17.