Doubt on Upload from Excel

[Former Member]

Hi Friends,

I am uploading few customer numbers from excel using GUI_UPLOAD into an internal table.When i upload from an excel saved as ".XLS" i am not getting records

instead i am getting one row full of #. In GUI_UPLOAD i have given field seperator as "X" and file type as "ASC",but when i save the excel as tab limited file and when i give the input file with ".TXT" records are coming into the internal table is there any ways to correct the error and to upload from an excel...

please reply thanks for all to reply for my queries

[Former Member]

Try this and reward if usefull.

DATA: IT_EXCEL LIKE TABLE OF ALSMEX_TABLINE WITH HEADER LINE.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = P_FILE

I_BEGIN_COL = 1 "From 1st Column

I_BEGIN_ROW = 2 "From 2nd row

I_END_COL = 6 "Till 6th Column

I_END_ROW = 65536 "Till Row

TABLES

INTERN = IT_EXCEL

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

CLEAR IT_EXCEL.

DESCRIBE TABLE IT_EXCEL.

IF SY-TFILL = 0.

MESSAGE I937 with 'No data try again'.

STOP.

ENDIF.

LOOP AT IT_EXCEL.

CASE IT_EXCEL-COL.

WHEN '0001'.

MOVE: IT_EXCEL-VALUE TO RECORD1-MATNR_005.

WHEN '0002'.

MOVE: IT_EXCEL-VALUE TO WK_WERKS.

CONDENSE WK_WERKS NO-GAPS.

TRANSLATE WK_WERKS TO UPPER CASE.

CASE WK_WERKS.

WHEN 'HOS1'.

CLEAR WK_WERKS.

WK_WERKS = 'HOS'.

WK_RJ_STGLOC = '1RJA'.

WHEN 'HOS2'.

CLEAR WK_WERKS.

WK_WERKS = 'HOS'.

WK_RJ_STGLOC = '2RJA'.

WHEN 'HOS3'.

CLEAR WK_WERKS.

WK_WERKS = 'HOS'.

WK_RJ_STGLOC = '3RJA'.

WHEN 'MYSR'.

WK_RJ_STGLOC = '4RJA'.

WHEN '1001'.

WK_RJ_STGLOC = 'ARJA'.

WHEN 'HP'.

WK_RJ_STGLOC = '7RJA'.

WHEN 'H001'.

WK_RJ_STGLOC = 'HRJA'.

WHEN OTHERS.

MESSAGE I942 WITH WK_WERKS.

STOP.

ENDCASE.

MOVE: WK_WERKS TO RECORD1-WERKS_006.

MOVE: WK_RJ_STGLOC TO RECORD1-ALORT_007.

CLEAR: WK_WERKS,

WK_RJ_STGLOC.

WHEN '0003'.

MOVE: IT_EXCEL-VALUE TO RECORD1-BUDAT_002.

WHEN '0004'.

MOVE: IT_EXCEL-VALUE TO RECORD1-BKTXT_004.

WHEN '0005'.

MOVE: IT_EXCEL-VALUE TO RECORD1-ERFMG_007.

WHEN '0006'.

MOVE: IT_EXCEL-VALUE TO RECORD1-ERFMG_008.

ENDCASE.

AT END OF ROW.

APPEND RECORD1.

CLEAR RECORD1.

ENDAT.

ENDLOOP.

here the record1 holds your data.

provide the row and column length in the functional module as u desire.

[Former Member]

Hi gokul,

try using field seperator ",".

Reward points if this helps,

Kiran

[Former Member]

Find below one report that uploads the data into internal table.

U can get the logic out of that.

Regards,

Nitin

REPORT zphysical_stock_upload.

----

---

Starting of data declaration code required for ALV

*----

---

TYPE-POOLS : slis.

• Declaration for ALV Display

*----

---

CONSTANTS: c_pfstatus TYPE slis_formname VALUE 'SET_PF_STATUS'.

DATA:

w_current_inv_no(5) TYPE n,

w_answer TYPE c,

ws_fieldcat TYPE slis_fieldcat_alv,

it_fieldcat TYPE slis_t_fieldcat_alv,

ws_layout TYPE slis_layout_alv,

wa_event TYPE slis_alv_event,

it_eventtab TYPE slis_t_event,

wa_line TYPE slis_listheader,

gt_top_of_page_no_sel TYPE slis_t_listheader.

DATA :

BEGIN OF wfcsr_ui_popup_text,

titlebar(60) TYPE c,

question(400) TYPE c,

END OF wfcsr_ui_popup_text.

DATA :

w_pop_up_text LIKE wfcsr_ui_popup_text.

w_pop_up_text-titlebar = 'Upload Physical Stock'.

w_pop_up_text-question = 'Are u sure ! You Want To Upload Physical Stock ? '.

*----

---

TABLES : zphy_stock.

TYPES: BEGIN OF t_datatab ,

kostl(30) TYPE c, " Department Code or Cost Center

werks(30) TYPE c, " Plant

tag_number(30) TYPE c, " Tag Number

matnr(30) TYPE c, " Material Number

phy_qty(30) TYPE c, " Physical Qty.

END OF t_datatab.

DATA: it_tab TYPE filetable,

gd_subrc TYPE i.

DATA: it_datatab TYPE STANDARD TABLE OF t_datatab WITH HEADER LINE INITIAL SIZE 0,

it_phy_data TYPE STANDARD TABLE OF zphy_stock WITH HEADER LINE INITIAL SIZE 0.

DATA : gd_scol TYPE i VALUE '1',

gd_srow TYPE i VALUE '1',

gd_ecol TYPE i VALUE '256',

gd_erow TYPE i VALUE '65536'.

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

PARAMETERS: p_file LIKE rlgrap-filename OBLIGATORY

DEFAULT 'C:\Documents and Settings\nrp00161\My Documents\Inventory\Perpetual\Physical Stock.xls' .

" File Name

SELECTION-SCREEN END OF BLOCK b1.

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

• AT SELECTION-SCREEN

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL METHOD cl_gui_frontend_services=>file_open_dialog

EXPORTING

window_title = 'Select File Name'

default_extension = '*.xls'

default_filename = '*.xls'

file_filter = '*.xls'

initial_directory = 'c:\'

• MULTISELECTION =

• WITH_ENCODING =

CHANGING

file_table = it_tab

rc = gd_subrc.

• USER_ACTION =

• FILE_ENCODING =

• EXCEPTIONS

• FILE_OPEN_DIALOG_FAILED = 1

• CNTL_ERROR = 2

• ERROR_NO_GUI = 3

• NOT_SUPPORTED_BY_GUI = 4

• others = 5

.

IF sy-subrc <> 0.

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

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

ELSE.

READ TABLE it_tab INTO p_file INDEX 1.

ENDIF.

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

INITIALIZATION.

• Get Current Inventory Number

SELECT MAX( inv_number ) INTO w_current_inv_no

FROM zcurr_inv.

• START-OF-SELECTION.

START-OF-SELECTION.

PERFORM upload_excel_file TABLES it_datatab

USING p_file

gd_scol

gd_srow

gd_ecol

gd_erow.

• Build event table

PERFORM eventtab_build CHANGING it_eventtab.

PERFORM prepare_field_catelog.

PERFORM display_data.

*&----

---

*& Form upload_excel_file

&----

---

-->P_IT_DATATAB text

• -->P_P_FILE text

• -->P_GD_SCOL text

• -->P_GD_SROW text

• -->P_GD_ECOL text

• -->P_GD_EROW text

*----

---

FORM upload_excel_file TABLES it_datatab

USING p_file

p_scol

p_srow

p_ecol

p_erow.

DATA : lt_intern TYPE kcde_cells OCCURS 0 WITH HEADER LINE.

• Has the following format:

• Row number | Colum Number | Value

• ---------------------------------------

• i.e. 1 1 Name1

• 2 1 Joe

DATA : ld_index TYPE i.

FIELD-SYMBOLS : <fs>.

• Note: Alternative function module - 'ALSM_EXCEL_TO_INTERNAL_TABLE'

CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'

EXPORTING

filename = p_file

i_begin_col = p_scol

i_begin_row = p_srow

i_end_col = p_ecol

i_end_row = p_erow

TABLES

intern = lt_intern

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

IF sy-subrc <> 0.

FORMAT COLOR COL_BACKGROUND INTENSIFIED.

WRITE:/ 'Error Uploading file'.

EXIT.

ENDIF.

IF lt_intern[] IS INITIAL.

FORMAT COLOR COL_BACKGROUND INTENSIFIED.

WRITE:/ 'No Data Uploaded'.

EXIT.

ELSE.

SORT lt_intern BY row col.

LOOP AT lt_intern.

MOVE lt_intern-col TO ld_index.

ASSIGN COMPONENT ld_index OF STRUCTURE it_datatab TO <fs>.

MOVE lt_intern-value TO <fs>.

AT END OF row.

APPEND it_datatab.

CLEAR it_datatab.

ENDAT.

ENDLOOP.

ENDIF.

LOOP AT it_datatab.

MOVE-CORRESPONDING it_datatab TO it_phy_data.

APPEND it_phy_data.

ENDLOOP.

READ TABLE it_phy_data INDEX 1.

SELECT SINGLE * FROM zphy_stock WHERE inv_number = w_current_inv_no

AND kostl = it_phy_data-kostl.

IF sy-subrc = 0.

MESSAGE i000(zqm) WITH 'ERROR ! Data for Cost Center' it_phy_data-kostl ' already exists.'.

ENDIF.

ENDFORM. " upload_excel_file

&----

---

& Form display_data

&----

---

--> p1 text

• <-- p2 text

*----

---

FORM display_data .

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

• I_INTERFACE_CHECK = ' '

• I_BYPASSING_BUFFER = ' '

• I_BUFFER_ACTIVE = ' '

i_callback_program = sy-repid

i_callback_pf_status_set = c_pfstatus

i_callback_user_command = 'USER_COMMAND'

• I_CALLBACK_TOP_OF_PAGE = ' '

• I_CALLBACK_HTML_TOP_OF_PAGE = ' '

• I_CALLBACK_HTML_END_OF_LIST = ' '

i_structure_name = 'ZPHY_STOCK'

• I_BACKGROUND_ID = ' '

• I_GRID_TITLE =

• I_GRID_SETTINGS =

• IS_LAYOUT =

• IT_FIELDCAT =

• IT_EXCLUDING =

• IT_SPECIAL_GROUPS =

• IT_SORT =

• IT_FILTER =

• IS_SEL_HIDE =

• I_DEFAULT = 'X'

• I_SAVE = ' '

• IS_VARIANT =

• IT_EVENTS =

• IT_EVENT_EXIT =

• IS_PRINT =

• IS_REPREP_ID =

• I_SCREEN_START_COLUMN = 0

• I_SCREEN_START_LINE = 0

• I_SCREEN_END_COLUMN = 0

• I_SCREEN_END_LINE = 0

• IT_ALV_GRAPHICS =

• IT_HYPERLINK =

• IT_ADD_FIELDCAT =

• IT_EXCEPT_QINFO =

• I_HTML_HEIGHT_TOP =

• I_HTML_HEIGHT_END =

• IMPORTING

• E_EXIT_CAUSED_BY_CALLER =

• ES_EXIT_CAUSED_BY_USER =

TABLES

t_outtab = it_phy_data

• EXCEPTIONS

• PROGRAM_ERROR = 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.

ENDFORM. " display_data

[Former Member]

Hi!!!

You can use funcion module 'ALSM_EXCEL_TO_INTERNAL_TABLE'.

Notes.

IF a Cell is null in row, Record is corrupted...

regards..

[Former Member]

Hi Gokul,

Do not use GUI_UPLOAD if you are using excel file, better use ALSM_EXCEL_TO_INTERNAL_TABLE

Regards,

Atish

[Former Member]

mate try this fm

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

• I_FIELD_SEPERATOR =

i_line_header = 'X'

i_tab_raw_data = it_type

i_filename = p_file

TABLES

i_tab_converted_data = t_upload_file[]

EXCEPTIONS

conversion_failed = 1

OTHERS = 2.

[Former Member]

Hi Gokul,

Remove this HAS_FIELD_SEPARATOR TO 'X'. when your are using .xls

Use the function module ALSM_EXCEL_TO_INTERNAL_TABLE instead of that

if it is excel file .

Chk this out. its really helpful..

FORM upload_file.

DATA: i_text_data TYPE truxs_t_text_data.

CALL METHOD cl_gui_frontend_services=>gui_upload

EXPORTING

filename = v_filename

filetype = 'ASC'

has_field_separator = 'X'

dat_mode = ' '

CHANGING

data_tab = i_text_data

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

not_supported_by_gui = 17

error_no_gui = 18

OTHERS = 19.

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 = 'X'

i_tab_raw_data = i_text_data

i_filename = p_fname

TABLES

i_tab_converted_data = it_detail

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,

Sapna

[Former Member]

Hi Gokul

its simple use my code

data: begin of itab_string occurs 0,

record type char255,

end of itab_string.

data: L_FILETABLE TYPE FILETABLE,

L_FILETAB_H TYPE FILETABLE WITH HEADER LINE.

data: p_file1 type string.

• selection screen .

PARAMETERS: P_FILE TYPE LOCALFILE.

initialization.

at selection-screen on value-request for P_FILE.

• IF THE USER SELECT EXTENTION BUTTON IT WILL OPEN THE LOCAL DIRECTORY FOR SELECTING THE FILE LOCATION.

CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_OPEN_DIALOG

• EXPORTING

• WINDOW_TITLE =

• DEFAULT_EXTENSION = 'CSV'

• DEFAULT_FILENAME = 'C:\Documents and Settings\196093\Desktop\STATUS.csv'

• FILE_FILTER =

• INITIAL_DIRECTORY = 'C:\Documents and Settings\196093\Desktop\'

• MULTISELECTION =

• WITH_ENCODING =

CHANGING

FILE_TABLE = L_FILETABLE

RC = RC

• USER_ACTION =

• FILE_ENCODING =

EXCEPTIONS

FILE_OPEN_DIALOG_FAILED = 1

CNTL_ERROR = 2

ERROR_NO_GUI = 3

NOT_SUPPORTED_BY_GUI = 4

others = 5

.

IF SY-SUBRC <> 0.

ELSE.

LOOP AT l_filetable INTO L_FILETAB_H.

P_FILE = L_FILETAB_H-FILENAME.

move p_file to p_file1.

EXIT.

ENDLOOP.

ENDIF.

• passing the selected file name to gui_upload for loading the data

• into internal table

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = p_file1

• FILETYPE = 'ASC'

• HAS_FIELD_SEPARATOR = ' '

• HEADER_LENGTH = 0

• READ_BY_LINE = 'X'

• DAT_MODE = ' '

• CODEPAGE = ' '

• IGNORE_CERR = ABAP_TRUE

• REPLACEMENT = '#'

• CHECK_BOM = ' '

• NO_AUTH_CHECK = ' '

• IMPORTING

• FILELENGTH =

• HEADER =

TABLES

DATA_TAB = itab_string

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

.

IF SY-SUBRC <> 0.

MESSAGE I000(Z00) WITH 'PLEASE PROVIDE CORRECT FILE NAME'.

ENDIF.

reward points to all helpful answers

kiran.M

[kesavadas_thekkillath]

Try this and reward if usefull.

DATA: IT_EXCEL LIKE TABLE OF ALSMEX_TABLINE WITH HEADER LINE.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = P_FILE

I_BEGIN_COL = 1 "From 1st Column

I_BEGIN_ROW = 2 "From 2nd row

I_END_COL = 6 "Till 6th Column

I_END_ROW = 65536 "Till Row

TABLES

INTERN = IT_EXCEL

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

CLEAR IT_EXCEL.

DESCRIBE TABLE IT_EXCEL.

IF SY-TFILL = 0.

MESSAGE I937 with 'No data try again'.

STOP.

ENDIF.

LOOP AT IT_EXCEL.

CASE IT_EXCEL-COL.

WHEN '0001'.

MOVE: IT_EXCEL-VALUE TO RECORD1-MATNR_005.

WHEN '0002'.

MOVE: IT_EXCEL-VALUE TO WK_WERKS.

CONDENSE WK_WERKS NO-GAPS.

TRANSLATE WK_WERKS TO UPPER CASE.

CASE WK_WERKS.

WHEN 'HOS1'.

CLEAR WK_WERKS.

WK_WERKS = 'HOS'.

WK_RJ_STGLOC = '1RJA'.

WHEN 'HOS2'.

CLEAR WK_WERKS.

WK_WERKS = 'HOS'.

WK_RJ_STGLOC = '2RJA'.

WHEN 'HOS3'.

CLEAR WK_WERKS.

WK_WERKS = 'HOS'.

WK_RJ_STGLOC = '3RJA'.

WHEN 'MYSR'.

WK_RJ_STGLOC = '4RJA'.

WHEN '1001'.

WK_RJ_STGLOC = 'ARJA'.

WHEN 'HP'.

WK_RJ_STGLOC = '7RJA'.

WHEN 'H001'.

WK_RJ_STGLOC = 'HRJA'.

WHEN OTHERS.

MESSAGE I942 WITH WK_WERKS.

STOP.

ENDCASE.

MOVE: WK_WERKS TO RECORD1-WERKS_006.

MOVE: WK_RJ_STGLOC TO RECORD1-ALORT_007.

CLEAR: WK_WERKS,

WK_RJ_STGLOC.

WHEN '0003'.

MOVE: IT_EXCEL-VALUE TO RECORD1-BUDAT_002.

WHEN '0004'.

MOVE: IT_EXCEL-VALUE TO RECORD1-BKTXT_004.

WHEN '0005'.

MOVE: IT_EXCEL-VALUE TO RECORD1-ERFMG_007.

WHEN '0006'.

MOVE: IT_EXCEL-VALUE TO RECORD1-ERFMG_008.

ENDCASE.

AT END OF ROW.

APPEND RECORD1.

CLEAR RECORD1.

ENDAT.

ENDLOOP.

here the record1 holds your data.

provide the row and column length in the functional module as u desire.

[kesavadas_thekkillath]

also include this.

SELECTION-SCREEN SKIP 1.

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-002.

PARAMETERS: P_FILE TYPE LOCALFILE OBLIGATORY .

SELECTION-SCREEN END OF BLOCK B1.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

STATIC = 'X'

CHANGING

FILE_NAME = P_FILE.

AT SELECTION-SCREEN ON P_FILE.

WK_LEN = P_FILE.

CONDENSE WK_LEN NO-GAPS.

LENT = STRLEN( WK_LEN ).

WK_ALP = LENT - 4.

B = WK_LEN+WK_ALP(LENT).

IF ( B NE '.xls' AND B NE '.XLS' ).

MESSAGE E939.

CLEAR P_FILE.

STOP.

ENDIF.