Upload data into ZTABLE using GUI_UPLOAD

gowrinath_gundu · ‎10-04-2013

Hi Friends,

I am using GUI_UPLOAD FM to upload data into database table(ZTABLE). ZTABLE is having three fields material, long description and owner. Since I am using GUI_UPLOAD I can upload only either tab delimited or .CSV file. So I am using .CSV file and i am using below code

LOOP AT IT_OUT1 INTO IS_OUT1.

READ TABLE IT_OUT1 INTO IS_OUT1 INDEX SY-TABIX.

LTEXT = IS_OUT1.

SPLIT LTEXT AT ',' INTO IS_OUT-MATNR IS_OUT-LONG_DESC IS_OUT-OWNER.

APPEND IS_OUT TO IT_OUT.

CLEAR IS_OUT1.

ENDLOOP.

IS_OUT1 is the output table from FM GUI_UPLOAD.

IS_OUT is the table to update ZTABLE.

Issue I am facing is : Long Descprition field is containing comma' s(,) in my file. So When I am splitting the text I can get long descprition only till first comma.

Hope you got my problem. Please suggest if you get anything.

Thanks,

Gowrinath.

Former Member · ‎10-05-2013

Hi Gowrinath,

Maybe you can change the comma separator with the semi colon separator, in your pc's settings. So when you create .CSV file, it will use the semicolon separator. And you can use this in split instruction.

Regards,

Renzo.

gowrinath_gundu · ‎10-05-2013

Hi Renzo,

I appreciate your answer.

But I can't suggest my client to change the settings. Is there any other way to figure out the problem. I believe FM GUI_UPLOAD supports only .CSV and .txt files and output internal table is having only field(text). Please correct if I am wrong.

Gowrinath.

karun_prabhu · ‎10-05-2013

Hello Gowrinath.

I would suggest you to use the FM TEXT_CONVERT_XLS_TO_SAP to upload .xls / .xlsx / .txt etc. data to internal table.

Then if you have an internal table of the same structure as the Z table, you can upload the data at one shot.

For instance,

TYPES: begin of ttab.

INCLUDE STRUCTURE Ztable.

TYPES: end of ttab.

DATA : itab TYPE TABLE OF ttab WITH HEADER LINE.

modify Ztable from table itab. "(If record with same primary key exists, this will UPDATE

Else INSERT into Ztable)

COMMIT WORK.

Regards.

gowrinath_gundu · ‎10-05-2013

Hi Arun,

Field for Long Description have to store more than 255 characters. ie the reason why i went for GUI_UPLOAD. Will FM suggested by you stores more than 500 characters.

karun_prabhu · ‎10-05-2013

Haven't come across this issue before.

The FM I suggested cannot read more than 255 characters.

For your problem, try making use of individual field length to fetch the data into separate fields.

Like Material = ltext+0(18) (18-Material field length)

Long text = ltext+18(500) (500-Long description field length) and

Owner = ltext+518(N). (N-Owner field length, which I don't know )

Regards.

gowrinath_gundu · ‎10-05-2013

Hi Arun,

Thank You.

What if my file contains data as following

Material Description Owner

d32231 Bag *****

d5656555..(upto 18 characters) shoes..... *****

Material number may not be 18 characters all the time.So while splitting if we use code suggested by you then there is a chance of description to enter into material field.

Thanks,

Gowri.

karun_prabhu · ‎10-05-2013

On seeing your data, if you upload the file, automatically via GUI_UPLOAD it will go into the individual fields of internal table provided the fields are tab delimited in the file.

Between each field, if tab delimiter is maintained,right?

If it so, then why worrying about splitting?

Former Member · ‎10-05-2013

Share the code you have written for FM GUI_UPLOAD.. I think some issue with that only.

former_member215344 · ‎10-05-2013

Hi Gowrinath,

You can try copying the standard FM TEXT_CONVERT_XLS_TO_SAP.

Copy the standard structure and make it to whatever field length you want.

Just try it out. There is nothing to lose in trying. 🙂

Ajay Bose

gowrinath_gundu · ‎10-05-2013

Hi Chandra,

Below is the code I am using

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = v_file

FILETYPE = 'ASC'

* HAS_FIELD_SEPARATOR = 'X'

* HEADER_LENGTH = 0

* READ_BY_LINE = '#'

* DAT_MODE = 'X'

* CODEPAGE = ' '

* IGNORE_CERR = ABAP_TRUE

* REPLACEMENT = '#'

* CHECK_BOM = ' '

* VIRUS_SCAN_PROFILE =

* NO_AUTH_CHECK = ' '

* IMPORTING

* FILELENGTH =

* HEADER =

TABLES

DATA_TAB = IT_OUT1

* 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 ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

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

ENDIF.

LOOP AT IT_OUT1 INTO IS_OUT1.

READ TABLE IT_OUT1 INTO IS_OUT1 INDEX SY-TABIX.

LTEXT = IS_OUT1.

SPLIT LTEXT AT ',' INTO IS_OUT-MATNR IS_OUT-LANGU IS_OUT-NAME IS_OUT-COLOR IS_OUT-SHORT_DESC IS_OUT-LONG_DESC IS_OUT-STATUS_CAT IS_OUT-EMP_DISC IS_OUT-PRICE_BOOK IS_OUT-DROPSHIP.

APPEND IS_OUT TO IT_OUT.

CLEAR IS_OUT1.

ENDLOOP.

gowrinath_gundu · ‎10-05-2013

Sorry Arun I am supposed to use only .CSV file.

Former Member · ‎10-05-2013

Code looks fine, no issue with this. I don't think so we can handle your issue. Because now issue is with the CSV file.

Former Member · ‎10-05-2013

If i am getting this right, the only problem is comma in long description field.

Why dont u you simply the Statement:

 LOOP AT IT_OUT1 INTO IS_OUT1.
  READ TABLE IT_OUT1 INTO IS_OUT1 INDEX SY-TABIX.
Replace all occurance of ',' in is_out1-long_description with ''.
    APPEND IS_OUT TO IT_OUT.
    CLEAR IS_OUT1.
 ENDLOOP.

( this will remove all comma's from the field)

Let me know if i am still not getting your Problem.

Former Member · ‎10-05-2013

Dear, If he will remove all the comma's, how he will split his data based on condition..

in LTEXT i guess data is coming like this. 09304,raw,data,sap

His problem is now while he is splitting his data (raw,data -- is long_description but due to , only Raw is coming in long desc. )

Former Member · ‎10-05-2013

First of all...

what is the use for these 2 line..

READ TABLE IT_OUT1 INTO IS_OUT1 INDEX SY-TABIX.

LTEXT = IS_OUT1.

I guess there is no need of read table. and assigning data in new variable. You can use code...

LOOP AT IT_OUT1 INTO IS_OUT1.

clear IS_OUT.

SPLIT IS_OUT1 AT ',' INTO IS_OUT-MATNR IS_OUT-LONG_DESC IS_OUT-OWNER.

APPEND IS_OUT TO IT_OUT.

CLEAR IS_OUT1.

ENDLOOP.

I think we can't handle this error from here,You need to make some changes , while calling GUI_UPLOAD FM.

Former Member · ‎10-05-2013

It would be great if you share some more code with us. Esp that FM GUI_UPLOAD logic, you have written in your program

former_member219966 · ‎10-05-2013

Hi Gowrinath

Yes FM GUI_UPLOAD can only upload Tab Delimited and CSV format.

Since you are uploading long text data i would suggest your client to user tab delimited format when they are saving the file in excel.

The other way around this problem is that u need to write a code to filter out all the commas and only use first and the last one.

matno,de,scri,pti,on,owner

like above u need to use only first and the last comma rest are part of your long text.

Regards
Arpit Gupta

gowrinath_gundu · ‎10-05-2013

Thank You Arpit.

I got an idea to move description to move it to last of my excel sheet. Need to be discussed with client. If they are not satisfied then I have to figure out a code to implement idea suggested by you.

Actually there are total 10 columns in my .CSV file and Long description is the sixth one in them.

Material

Language

Description

Color

Digital Name

eDescription

Status of Catalog

Employee Discount ?

Price Book

DropShp

eDescription is the one which contains long description of product.

former_member219966 · ‎10-07-2013

Hi Gowrinath,

Ask your client to save data in .txt format in excel... the problem with excel upload in SAP is that u cannot upload more then 255 characters in one columns... which is usual problem when uploading long text ... the logic i described will be somewhat cumbersome to write as u cannot search for (') character directly since it is used in ABAP language constructs so u have to use URL_ASCII_CODE_GET' for making comparison.

Regards
Arpit Gupta

gowrinath_gundu · ‎10-05-2013

Is there any possibility of preparing final internal table without splitting?

gowrinath_gundu · ‎10-05-2013

Hi Friends,

I came up with idea of moving long descprition field of my file to last. Need to discuss with client.

One more issue: Since my long description is more tha 255 characters I am using a field of type string to store this data in database table .But in my programs when I am using that table, For example,

TABLES : ZTEST.

DATA : IT TYPE STANDARD TABLE OF ZTEST.

I am getting below error

"ZTEST" must be a flat structure. You cannot use internal tables, strings,

references, or structures as components

(ZTEST is having field LONG_DESC of type string)

Please suggest.

Thanks,

Gowrinath.

former_member392866 · ‎10-05-2013

Hi Gowrinath,

the solution given by Gupta should solve your problem(By considering first & last commas) ...

You should give rewards to the persons who solves the problem (or) helps to solve the problem .

For any other problems/suggestions please open a new discussion and close the current discussion .

If you do like this in future the searching criteria will be helpful to others who gets the same type of issues.

Please don't integrate multiple questions in one discussion. Thank you .

Regards,

Balaji.

gowrinath_gundu · ‎10-05-2013

Thanks Balaji Yadav for your valuable suggestion!

I will reward you too if you come up with good answer.

former_member193724 · ‎10-05-2013

Hi Gowrinath,

You cannot define a Table with fields having type STRING. If you want you can create a Structure containing all fields ( including STRING types) from your ZTABLE and define your internal table as type table of the structure or else you can use table type.

Rgrds.

Former Member · ‎10-07-2013

Hi Gowrinath,

Instead of GUI_UPLOAD fm, try this below sample logic(If you want).

TYPE-POOLS:kcde,slis.

TYPES:BEGIN OF ty_excel,
       emp_no   TYPE char20,
       emp_name TYPE char40,
       emp_dept TYPE char25,
       emp_mail TYPE char50,
       emp_ph   TYPE char20,
       END OF ty_excel.

DATA:lt_excel TYPE STANDARD TABLE OF ty_excel WITH HEADER LINE,
      ls_excel TYPE ty_excel.

DATA:lt_intern TYPE kcde_intern,
      ls_intern LIKE LINE OF lt_intern.

DATA:lv_index TYPE kcde_cells-col.

DATA:lt_fcat TYPE slis_t_fieldcat_alv,
      ls_fcat TYPE slis_fieldcat_alv.

FIELD-SYMBOLS:<fs_excel> TYPE ANY,
               <fs_intern> TYPE kcde_cells,
               <fs_out>    TYPE ty_excel.

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-009.
PARAMETER:p_file TYPE rlgrap-filename.
SELECTION-SCREEN END OF BLOCK b1.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

   PERFORM f4_file.

START-OF-SELECTION.
   PERFORM excel_upload.
   PERFORM build_fcat.
   PERFORM display_data.

*&---------------------------------------------------------------------*
*&      Form F4_FILE
*&---------------------------------------------------------------------*
*       Value request for input file
*----------------------------------------------------------------------*
FORM f4_file .
   CALL FUNCTION 'F4_FILENAME'
     EXPORTING
       program_name = syst-cprog
       dynpro_number = syst-dynnr
       field_name    = 'P_FILE'
     IMPORTING
       file_name     = p_file.
   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.                                                    " F4_FILE
*&---------------------------------------------------------------------*
*&      Form EXCEL_UPLOAD
*&---------------------------------------------------------------------*
*       Excel file upload to SAP internal table
*----------------------------------------------------------------------*
FORM excel_upload.

CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'
     EXPORTING
       filename                = p_file
       i_begin_col             = 1
       i_begin_row             = 2
       i_end_col               = 500
       i_end_row               = 50000
     TABLES
       intern                  = lt_intern
     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 lt_intern ASSIGNING <fs_intern>.
     MOVE <fs_intern>-col TO lv_index.
     ASSIGN COMPONENT lv_index OF STRUCTURE ls_excel TO <fs_excel>.
     MOVE <fs_intern>-value TO <fs_excel>.
     AT END OF row.
* Append data into internal table
       APPEND ls_excel TO lt_excel.
       CLEAR ls_excel.
     ENDAT.
   ENDLOOP.
ENDFORM.                    " EXCEL_UPLOAD

*&---------------------------------------------------------------------*
*&      Form BUILD_FCAT
*&---------------------------------------------------------------------*
*       Fieldcatalog building
*----------------------------------------------------------------------*
FORM build_fcat .
   REFRESH lt_fcat.
   ls_fcat-fieldname = 'EMP_NO'.
   ls_fcat-seltext_m = 'Emolyee No.'.
   APPEND ls_fcat TO lt_fcat.
   CLEAR ls_fcat.

   ls_fcat-fieldname = 'EMP_NAME'.
   ls_fcat-seltext_m = 'Name'.
   APPEND ls_fcat TO lt_fcat.
   CLEAR ls_fcat.

   ls_fcat-fieldname = 'EMP_DEPT'.
   ls_fcat-seltext_m = 'Department'.
   APPEND ls_fcat TO lt_fcat.
   CLEAR ls_fcat.

   ls_fcat-fieldname = 'EMP_MAIL'.
   ls_fcat-seltext_m = 'Email ID'.
   APPEND ls_fcat TO lt_fcat.
   CLEAR ls_fcat.

   ls_fcat-fieldname = 'EMP_PH'.
   ls_fcat-seltext_m = 'Phone No.'.
   APPEND ls_fcat TO lt_fcat.
   CLEAR ls_fcat.
ENDFORM.                    " BUILD_FCAT
*&---------------------------------------------------------------------*
*&      Form DISPLAY_DATA
*&---------------------------------------------------------------------*
*       Display Excel data
*----------------------------------------------------------------------*
FORM display_data .
   CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
     EXPORTING
       i_callback_program = sy-repid
       it_fieldcat        = lt_fcat
     TABLES
       t_outtab           = lt_excel
     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

Thanks,

Satya Srinivas

former_member339717 · ‎10-07-2013

Dear gowrinath Gundu,

use the follwing code

PARAMETERS: LV_FILE TYPE STRING DEFAULT 'D:\ZM50A\ZTEST.TXT'.

DATA: LV_FLEN TYPE I,

LT_FILETAB TYPE FILETABLE,

LV_RC TYPE I.

data : begin of lines occurs 0,

LNGTXT TYPE C LENGTH 1032.

DATA: end of lines.

DATA: WA TYPE STRING.

DATA: BEGIN OF ITAB OCCURS 0,

MATNR(18),

WERKS(4),

LTXT(1032),

END OF ITAB.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = LV_FILE

FILETYPE = 'ASC'

* HAS_FIELD_SEPARATOR = ' '

* HEADER_LENGTH = 0

* READ_BY_LINE = 'X'

* DAT_MODE = ' '

* CODEPAGE = ' '

* IGNORE_CERR = ABAP_TRUE

* REPLACEMENT = '#'

* CHECK_BOM = ' '

* VIRUS_SCAN_PROFILE =

* NO_AUTH_CHECK = ' '

IMPORTING

FILELENGTH = LV_FLEN

* HEADER =

TABLES

DATA_TAB = LINES

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.

LOOP AT LINES .

CHECK SY-TABIX NE 1.

SPLIT LINES AT ',' INTO : ITAB-MATNR ITAB-WERKS ITAB-LTXT.

******************IF YOU WANA REMOVE ',' FROM YOU LONG TEXT USE THE FOLLWOING STATEMENT

REPLACE ALL OCCURRENCES OF ',' IN: ITAB-LTXT WITH SPACE.

CONDENSE ITAB-LTXT .

APPEND ITAB.

CLEAR: ITAB.

ENDLOOP.