Re: Excel Upload Problem

Former Member · ‎2013 Aug 26

Hi Experts,

I have created a program for excel sheet upload to z table. I have used FM 'TEXT_CONVERT_XLS_TO_SAP'. But the problem is even if my excel sheet has more records, it uploads only 17 records every time. I have been trying for a while now but it isn't working.

Below is the code for 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 = it_upload

EXCEPTIONS

conversion_failed = 1

OTHERS = 2.

IF sy-subrc <> 0.

MESSAGE text-001 TYPE 'E'.

ENDIF.

Regards,

PS

former_member209120 · ‎2013 Aug 26

Hi

Try like this

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

* I_FIELD_SEPERATOR =

* I_LINE_HEADER = 'X'

I_TAB_RAW_DATA = IT_RAW

I_FILENAME = P_FILE

TABLES

I_TAB_CONVERTED_DATA = IT_UPLOAD

EXCEPTIONS

CONVERSION_FAILED = 1

OTHERS = 2.

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 = it_upload

EXCEPTIONS

conversion_failed = 1

OTHERS = 2.

IF sy-subrc <> 0.

MESSAGE text-001 TYPE 'E'.

ENDIF.

Former Member · ‎2013 Aug 26

Hi Ramesh,

I have tried that it doesnt work.

Regards,

PS

former_member209120 · ‎2013 Aug 26

This message was moderated.

Former Member · ‎2013 Aug 26

I never used this FM. Maybe you can try with ALSM_EXCEL_TO_INTERNAL_TABLE.

Regards.

BCX

Former Member · ‎2013 Aug 26

Hello, I attached this program, which makes a download of the table and its fields. Depending on the extension when you save the file , you can save in Excel format (.xls) or text (.txt) .
Replaces zpppi00004 in different parts of the code for the name of your table

CODE:

REPORT ZTABLE.

TABLES: ZPPPI00004. "(YOUR TABLE)

TYPE-POOLS: TRUXS.

DATA: DBFIELDS TYPE STANDARD TABLE OF DBFIELD WITH HEADER LINE.
DATA: BEGIN OF DBFIELDS_AUX OCCURS 0.
DATA: NAME TYPE DBFIELD-NAME.
DATA: END OF DBFIELDS_AUX.

DATA: IT_TABLA TYPE STANDARD TABLE OF ZPPPI00004.

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-002.
PARAMETERS: P_FILE(128) TYPE C LOWER CASE OBLIGATORY.
SELECTION-SCREEN END OF BLOCK B1.

DATA: DEF_PATH(50) TYPE C VALUE 'C:\'.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.
CALL FUNCTION 'WS_FILENAME_GET' "PATH
EXPORTING
DEF_FILENAME           = P_FILE
DEF_PATH = DEF_PATH
MASK = ',*.*,*.*.'
MODE = 'O'
TITLE = 'SELECT PATH & FILENAME: '
IMPORTING
FILENAME = P_FILE
EXCEPTIONS
INV_WINSYS = 1
NO_BATCH = 2
SELECTION_CANCEL       = 3
SELECTION_ERROR        = 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.
ENDIF.

"EVENTO START-OF-SELECTION
START-OF-SELECTION.

PERFORM SELECT_DATA.

IF IT_TABLA[] IS NOT INITIAL.
"REALIZAMOS LA TRANSFERENCIA DE INFORMACIÓN
CALL FUNCTION 'WS_DOWNLOAD'
EXPORTING
FILENAME = P_FILE
FILETYPE = 'DAT' " DAT OR ASCI
TABLES
DATA_TAB = IT_TABLA
FIELDNAMES = DBFIELDS_AUX
EXCEPTIONS
FILE_OPEN_ERROR = 1
FILE_WRITE_ERROR = 2
INVALID_FILESIZE = 3
INVALID_TYPE = 4
NO_BATCH = 5
UNKNOWN_ERROR = 6
INVALID_TABLE_WIDTH = 7
GUI_REFUSE_FILETRANSFER       = 8
CUSTOMER_ERROR = 9
NO_AUTHORITY = 10
OTHERS = 11
.
IF SY-SUBRC <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ELSE.
    MESSAGE 'SUCESS' TYPE 'S'.
ENDIF.
ELSE.

MESSAGE 'NO DATA' TYPE 'I'. STOP.
ENDIF.

FORM SELECT_DATA.

SELECT *
APPENDING TABLE IT_TABLA
FROM ZPPPI00004.
IF SY-SUBRC = 0.

CALL FUNCTION 'DB_GET_TABLE_FIELDS'
EXPORTING
   TABNAME = 'ZPPPI00004'
TABLES
   DBFIELDS = DBFIELDS.
IF SY-SUBRC = 0.
   LOOP AT DBFIELDS.
     MOVE DBFIELDS-NAME TO DBFIELDS_AUX.
     APPEND DBFIELDS_AUX.
   ENDLOOP.
ELSE.
   LEAVE PROGRAM.
ENDIF.
ELSE.
   LEAVE PROGRAM.
ENDIF.
ENDFORM.

Regards

Former Member · ‎2013 Aug 26

Hi juan,

thanks for ur gentle help but ihave problem uploading the data.

Former Member · ‎2013 Aug 26

Excuse me , try FM 'ALSM_EXCEL_TO_INTERNAL_TABLE' .The internal table's type is “ALSMEX_TABLINE”

Example:

DATA: ITAB TYPE TABLE OF ALSMEX_TABLINE WITH HEADER LINE.

soumik_de2 · ‎2013 Aug 26

hi

Purushottam Sonawane i have used this FM several times nd it works perfectly.....

can u pls send the code so that we can rectify the problem and one more suggestion comment the field separator it is not required.......... if possible pls copy paste the code............

Former Member · ‎2013 Aug 26

hi,

below given is my code excerpt. It might work with ALSM_EXCEL_TO_INTERNAL_TABLE and so it should work with TEXT_CONVERT_XLS_TO_SAP also.

TYPE-POOLS: truxs.

tables: zfi0200

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

* TYPE DECLARATIONS

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

TYPES: BEGIN OF ty_zfi0200,

zbill_no TYPE zfi0200-zbill_no,

zname TYPE zfi0200-zname,

zsector TYPE zfi0200-zsector,

zdate TYPE zfi0200-zdate,

zdesignation TYPE zfi0200-zdesignation,

zbasic TYPE zfi0200-zbasic,

zyayr TYPE zfi0200-zyayr,

ztotal TYPE zfi0200-ztotal,

zairport_tax TYPE zfi0200-zairport_tax,

ztax_code TYPE zfi0200-ztax_code,

ztax_value TYPE zfi0200-ztax_value,

zcredit_note TYPE zfi0200-zcredit_note,

ztotal_value TYPE zfi0200-ztotal_value,

zairlines TYPE zfi0200-zairlines,

zcost_center TYPE zfi0200-zcost_center,

zwbs_element TYPE zfi0200-zwbs_element,

zprofit_center TYPE zfi0200-zprofit_center,

zbusiness_area TYPE zfi0200-zbusiness_area,

hkont TYPE zfi0200-hkont,

shkzg TYPE zfi0200-shkzg,

pernr TYPE zfi0200-pernr,

END OF ty_zfi0200,

BEGIN OF ty_final,

zlot_no TYPE zfi0200-zlot_no,

zdou TYPE zfi0200-zdou,

END OF ty_final.

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

* DATA DECLARATIONS

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

DATA: it_upload TYPE STANDARD TABLE OF zfi0200,

wa_upload TYPE zfi0200,

it_zfi0200 TYPE STANDARD TABLE OF zfi0200, "WITH HEADER LINE,

wa_zfi0200 TYPE zfi0200,

it_final TYPE STANDARD TABLE OF ty_final,

wa_final TYPE ty_final,

it_type TYPE truxs_t_text_data.

SELECTION-SCREEN BEGIN OF BLOCK a WITH FRAME TITLE text-003.

PARAMETERS: p_lotno TYPE zfi0200-zlot_no OBLIGATORY,

p_date TYPE zfi0200-zdou DEFAULT sy-datum OBLIGATORY.

PARAMETERS: p_file TYPE rlgrap-filename OBLIGATORY.

SELECTION-SCREEN END OF BLOCK a.

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

* Get Filename

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

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

* EXPORTING

* program_name = syst-repid

* dynpro_number = syst-dynnr

* field_name = ' '

* static = ' '

* mask = ' '

CHANGING

file_name = p_file

EXCEPTIONS

mask_too_long = 1

OTHERS = 2.

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

* Uploading the data in the file into internal table.

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

START-OF-SELECTION.

*

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

* I_FIELD_SEPERATOR =

* I_LINE_HEADER =

i_tab_raw_data = it_type

i_filename = p_file

TABLES

i_tab_converted_data = it_upload

EXCEPTIONS

conversion_failed = 1

OTHERS = 2.

IF sy-subrc <> 0.

MESSAGE text-001 TYPE 'E'.

ENDIF.

END-OF-SELECTION.

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

* Inserting data into Z table.

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

SELECT *

FROM zfi0200

INTO wa_zfi0200

WHERE zlot_no = p_lotno.

IF sy-subrc = 0.

MESSAGE text-004 TYPE 'S'.

RETURN.

ENDIF.

ENDSELECT.

LOOP AT it_upload INTO wa_upload.

zfi0200-zlot_no = p_lotno.

zfi0200-zdou = p_date.

zfi0200-zbill_no = wa_upload-zbill_no.

zfi0200-zname = wa_upload-zname.

zfi0200-zsector = wa_upload-zsector.

zfi0200-zdate = wa_upload-zdate.

zfi0200-zdesignation = wa_upload-zdesignation.

zfi0200-zbasic = wa_upload-zbasic.

zfi0200-zyayr = wa_upload-zyayr.

zfi0200-ztotal = wa_upload-ztotal.

zfi0200-zairport_tax = wa_upload-zairport_tax.

zfi0200-ztax_code = wa_upload-ztax_code.

zfi0200-ztax_value = wa_upload-ztax_value.

zfi0200-zcredit_note = wa_upload-zcredit_note.

zfi0200-ztotal_value = wa_upload-ztotal_value.

zfi0200-zairlines = wa_upload-zairlines.

zfi0200-zcost_center = wa_upload-zcost_center.

zfi0200-zwbs_element = wa_upload-zwbs_element.

zfi0200-zprofit_center = wa_upload-zprofit_center.

zfi0200-zbusiness_area = wa_upload-zbusiness_area.

zfi0200-hkont = wa_upload-hkont.

zfi0200-shkzg = wa_upload-shkzg.

zfi0200-pernr = wa_upload-pernr.

MODIFY zfi0200.

IF sy-subrc = 0.

MESSAGE text-002 TYPE 'S'.

ENDIF.

ENDLOOP.

Regards,

PS

GirieshM · ‎2013 Aug 26

Hi Purush,

I think the problem might be with the data conversion.

Can you please change the it_upload into characters and move it to your respective datatypes during loop. I tried it and found no issues other than this with coding part.

Example:

   TYPES: BEGIN OF t_datatab,
      col1(30)    TYPE c,
      col2(30)    TYPE c,
      col3(30)    TYPE c,
      col4(30)    TYPE c,
      col5(30)    TYPE c,
      col6(30)    TYPE c,
      END OF t_datatab.

   DATA: it_datatab TYPE STANDARD TABLE OF t_datatab,
      it_datatab1 TYPE STANDARD TABLE OF t_datatab,
      wa_datatab TYPE t_datatab.

DATA: it_raw TYPE truxs_t_text_data.

   AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
CALL FUNCTION 'F4_FILENAME'
    EXPORTING
      field_name = 'P_FILE'
    IMPORTING
      file_name = p_file.

   START-OF-SELECTION.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
*   I_FIELD_SEPERATOR          =
*   I_LINE_HEADER              =
      i_tab_raw_data             = it_raw
      i_filename                 = p_file
    TABLES
      i_tab_converted_data       = it_datatab[]
* EXCEPTIONS
*   CONVERSION_FAILED          = 1
*   OTHERS                     = 2
            .

LOOP AT it_datatab INTO wa_datatab.

Move the char fields in to their respective workarea.

ENDLOOP.

Hope it helps.

With Regards,

Giriesh M

Former Member · ‎2013 Aug 27

Hi Girish,

I converted it to char format and it works fine with it. I had to convert dates to internal formats though.

Thanks for your suggestion.

Regards,

PS

Former Member · ‎2013 Aug 26

The code in FM TEXT_CONVERT_XLS_TO_SAP exits processing after encountering total 5 empty rows. Could this be the case for you?

g_max_empty_rows type i value 5.

.....

while l_current_row <= g_max_empty_rows.

.....

if i_tab_converted_data is initial.

l_current_row = l_current_row + 1.

gurunathkumar_dadamu · ‎2013 Aug 26

Hi Purushottam,

try the below code.

data: lv_file TYPE string.

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

PARAMETERS: p_file TYPE rlgrap-filename OBLIGATORY.

SELECTION-SCREEN END OF BLOCK b1.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

*- Input F4 help.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

program_name = syst-cprog

IMPORTING

file_name = p_file.

AT SELECTION-SCREEN.

**- Fetching data from excel to itexcel.

lv_file = p_file.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = lv_file

has_field_separator = 'X'

* filetype = 'ASC'

TABLES

data_tab = it_final

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 it_final[] IS INITIAL.

Message 'No Data' type 'E'.

ENDIF.

Note: here your it_final is excel structure.

Please let me know if any issues.

Regards,

Gurunath

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My Account

My Account

Excel Upload Problem

Purushottam Sonawane i have used this FM several times nd it works perfectly.....