topic Re: Excel Upload Problem in Application Development and Automation Discussions

Excel Upload Problem

Former Member — Mon, 26 Aug 2013 10:19:49 GMT

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,

Re: Excel Upload Problem

former_member209120 — Mon, 26 Aug 2013 10:25:18 GMT

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.

Re: Excel Upload Problem

Former Member — Mon, 26 Aug 2013 10:36:12 GMT

Hi Ramesh,

I have tried that it doesnt work.

Regards,

Re: Excel Upload Problem

former_member209120 — Mon, 26 Aug 2013 10:43:18 GMT

This message was moderated.

Re: Excel Upload Problem

Former Member — Mon, 26 Aug 2013 10:57:00 GMT

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

Regards.

BCX

Re: Excel Upload Problem

Former Member — Mon, 26 Aug 2013 10:57:33 GMT

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

Re: Excel Upload Problem

Former Member — Mon, 26 Aug 2013 11:04:02 GMT

Hi juan,

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

Re: Excel Upload Problem

Former Member — Mon, 26 Aug 2013 11:24:08 GMT

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.

Re: Excel Upload Problem

soumik_de2 — Mon, 26 Aug 2013 11:33:10 GMT

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

Re: Excel Upload Problem

Former Member — Mon, 26 Aug 2013 11:57:28 GMT

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,

Re: Excel Upload Problem

Former Member — Mon, 26 Aug 2013 13:17:39 GMT

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.

Re: Excel Upload Problem

GirieshM — Mon, 26 Aug 2013 14:35:02 GMT

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

Re: Excel Upload Problem

gurunathkumar_dadamu — Mon, 26 Aug 2013 14:58:51 GMT

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

Re: Excel Upload Problem

Former Member — Tue, 27 Aug 2013 06:24:36 GMT

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,