Text file to Excel file

[Former Member]

Hi Friends,

here am uploading text file from desktop into int tab using GUI_upload. then i want to down load that data from int tab into other file formate like Excel sheet.

how to do? what is the FM for that?

Thanks in advance,

murali.

[Former Member]

Hi

To download data from internal table to Excel use function module 'GUI_DOWNLOAD'.

Thanks

Vasudha

[Former Member]

You can do it with GUI_DOWNLOAD as well. Just mention Filetyp as "WK1".

[Former Member]

thank u Gupta, it is getting and onemore dought is

how to provide headdings for that colums in excel file

Regards,

murali

[Former Member]

Hi Murli,

Use FM <b>'GUI_Download'</b> to download data from an internal table.

Regards,

Hemant

[Former Member]

HI

File transfer from frontend PC to an internal backend table.

A format conversion is possible. The functions are similar to the module WS_UPLOAD, however the data provider is used for the upload and not GMUX.

Example

CALL FUNCTION 'GUI_UPLOAD'

exporting

filetype = 'BIN'

filename = 'C:\DOWNLOAD.BIN'

tables

data_tab = itab.

loads the contents of the file 'C:\DOWNLOAD.BIN' on the frontend PC into the internal backend table itab. The transferred data is not converted.

CALL FUNCTION 'GUI_UPLOAD'

exporting

filetype = 'ASC'

filename = 'C:\DOWNLOAD.TXT'

tables

data_tab = itab.

loads the contents of the file 'C:\DOWNLOAD.TXT' on the frontend PC line by line into the internal backend table itab. The transferred data is converted.

Or else

You can try with this fm also TEXT_CONVERT_XLS_TO_SAP

TYPE-POOLS truxs.
 
DATA: Begin of i_articles occurs 0,
        matnr like mara-matnr,
        flag(6) type c,
      End of i_articles.
 
DATA: v_file type string,
      i_temp type truxs_t_text_data.
 
selection-screen begin of block b1.
parameters: fl_file like rlgrap-filename lower case.
selection-screen end of block b1.
 
FORM UploadData.
 
  v_file = fl_file.
 
  CALL FUNCTION 'GUI_UPLOAD'
    EXPORTING
      FILENAME                      = v_file
     FILETYPE                      = 'ASC'
     HAS_FIELD_SEPARATOR           = 'X'
*     HEADER_LENGTH                 = 0
*     READ_BY_LINE                  = 'X'
     DAT_MODE                      = ' '
*   IMPORTING
*     FILELENGTH                    =
*     HEADER                        =
    TABLES
      DATA_TAB                      = i_temp
   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.
 
  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
     I_FIELD_SEPERATOR          = 'X'
*     I_LINE_HEADER              =
      I_TAB_RAW_DATA             = i_temp
      I_FILENAME                 = fl_file
    TABLES
      I_TAB_CONVERTED_DATA       = i_articles
   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.
 
 
ENDFORM.                    " UploadData

Regards

Pavan

Message was edited by:

Pavan praveen

[Former Member]

Hi pavan Thanks for ur quick replay,

here my problem is am using gui_upload for upload and gui_download for download, here am downloading file as text file with headings as .txt , but i want download as excel file with headings, but i can able to getting data into excel file but am not getting headings.

so how to provide headings for excel file data.

Regards,

murali.

[Former Member]

Hi

Use fm ALSM_EXCEL_TO_INTERNAL_TABLE to upload data frm excel.

Use function module GUI_UPLOAD

The FILETYPE refer to the type of file format you need: For e.g 'WK1' - Excel format , 'ASC' - Text Format etc.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = 'C:\test.csv'

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.

Regards

Pavan

[Former Member]

hi,

check my above code in bold letters.

it works for u

regrads

siva

[Former Member]

Hi friends thank u very much, i got resolved my problem.

i ve given gd rewards .

Regards,

murali.

[Former Member]

hi,

check this code

REPORT zmm_basic_data .

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

• Report to View Detail Of Material Master BASIC DATA.

• Start Date 07.05.2007

• Request No.-GR3K931783

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

TABLES: mara,marc,makt.

DATA: gi_mara LIKE mara OCCURS 1 WITH HEADER LINE,

gi_marc LIKE marc OCCURS 1 WITH HEADER LINE,

gi_makt LIKE makt OCCURS 1 WITH HEADER LINE.

DATA: BEGIN OF gi_download OCCURS 1,

matnr LIKE mara-matnr,"material no

maktx LIKE makt-maktx,"material description

matkl LIKE mara-matkl,"material group

werks LIKE marc-werks,"plant

ekgrp LIKE marc-ekgrp,"purchasing group

spart LIKE mara-spart,"division

meins LIKE mara-meins,"base uit of measure

bismt LIKE mara-bismt,"old material no.

prdha LIKE mara-prdha,"product hierarchy

brgew LIKE mara-brgew,"gross weight

ntgew LIKE mara-ntgew,"net weight

gewei LIKE mara-gewei,"weight unit

volum LIKE mara-volum,"volume

voleh LIKE mara-voleh,"volume unit

zeinr LIKE mara-zeinr,"document no.

zeiar LIKE mara-zeiar,"document type

zeivr LIKE mara-zeivr,"document version

zeifo LIKE mara-zeifo,"page format of document

blanz LIKE mara-blanz,"number of sheets

spras LIKE makt-spras,"language key

END OF gi_download.

<b>DATA: BEGIN OF gi_fieldnames OCCURS 1,

mandt(50),

END OF gi_fieldnames.</b>

*file path and file name data declaration.

DATA: stripped_name LIKE rlgrap-filename,

file_path LIKE rlgrap-filename.

DATA: inpath LIKE ltran-path01,

file LIKE ltran-file01,

outpath LIKE ltran-path02.

• Field Symbols ************

FIELD-SYMBOLS <mara> LIKE gi_mara.

FIELD-SYMBOLS <marc> LIKE gi_marc.

FIELD-SYMBOLS <makt> LIKE gi_makt.

FIELD-SYMBOLS <download> LIKE gi_download.

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

SELECTION-SCREEN: SKIP.

SELECT-OPTIONS: s_werks FOR marc-werks.

SELECT-OPTIONS: s_ekgrp FOR marc-ekgrp.

SELECT-OPTIONS: s_matnr FOR mara-matnr.

SELECT-OPTIONS: s_matkl FOR mara-matkl.

SELECT-OPTIONS: s_spart FOR mara-spart.

SELECTION-SCREEN: SKIP.

PARAMETER fnm TYPE rlgrap-filename OBLIGATORY.

SELECTION-SCREEN: SKIP.

SELECTION-SCREEN: END OF BLOCK b1.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR fnm.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

static = 'X'

CHANGING

file_name = fnm

EXCEPTIONS

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

*For fetching the baisc data.

START-OF-SELECTION.

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

*Getting the general data based on material no,division and material

*group.

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

SELECT * FROM mara INTO TABLE gi_mara

WHERE matnr IN s_matnr

AND spart IN s_spart

AND matkl IN s_matkl.

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

*Getting the plant data based on material no,plant and purchasing

*group.

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

IF NOT gi_mara[] IS INITIAL.

SELECT * FROM marc INTO TABLE gi_marc

FOR ALL ENTRIES IN gi_mara

WHERE matnr EQ gi_mara-matnr

AND werks IN s_werks

AND ekgrp IN s_ekgrp.

ENDIF.

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

*Getting the material description based on material no

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

IF NOT gi_mara[] IS INITIAL.

SELECT * FROM makt INTO TABLE gi_makt

FOR ALL ENTRIES IN gi_mara

WHERE matnr = gi_mara-matnr.

ENDIF.

• Fetching all data into single internal table ********

SORT gi_mara BY matnr.

SORT gi_makt BY matnr.

SORT gi_marc BY matnr.

*****Transfering the data into gi_download*******

IF s_werks[] IS INITIAL and s_ekgrp[] IS INITIAL.

LOOP AT gi_mara ASSIGNING <mara>.

MOVE-CORRESPONDING <mara> TO gi_download.

READ TABLE gi_marc ASSIGNING <marc>

WITH KEY matnr = <mara>-matnr

BINARY SEARCH.

IF sy-subrc = 0.

MOVE <marc>-werks TO gi_download-werks.

MOVE <marc>-ekgrp TO gi_download-ekgrp.

ENDIF.

READ TABLE gi_makt ASSIGNING <makt>

WITH KEY matnr = <mara>-matnr

BINARY SEARCH.

IF sy-subrc = 0.

MOVE <makt>-maktx TO gi_download-maktx.

MOVE <makt>-spras TO gi_download-spras.

ENDIF.

APPEND gi_download.

CLEAR gi_download.

ENDLOOP.

ELSE.

LOOP AT gi_marc ASSIGNING <marc>.

READ TABLE gi_mara ASSIGNING <mara>

WITH KEY matnr = <marc>-matnr

BINARY SEARCH.

IF sy-subrc = 0.

MOVE-CORRESPONDING <mara> TO gi_download.

ENDIF.

READ TABLE gi_makt ASSIGNING <makt>

WITH KEY matnr = <marc>-matnr

BINARY SEARCH.

IF sy-subrc = 0.

MOVE <makt>-maktx TO gi_download-maktx.

MOVE <makt>-spras TO gi_download-spras.

ENDIF.

MOVE <marc>-werks TO gi_download-werks.

MOVE <marc>-ekgrp TO gi_download-ekgrp.

APPEND gi_download.

CLEAR gi_download.

ENDLOOP.

ENDIF.

IF gi_download[] IS INITIAL.

MESSAGE i001(sa) WITH 'Data not found'.

LEAVE LIST-PROCESSING.

ENDIF.

*******Downloading the basic data********

<b> gi_fieldnames-mandt = 'Material no'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Material description'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Material group'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Plant'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Purchasing group'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Division'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Base uit of measure'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Old material no.'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Product hierarchy'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Gross weight'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Net weight'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Weight unit'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Volume'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Volume unit'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Document no.'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Document type'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Document version'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Page format of document'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Number of sheets'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.

gi_fieldnames-mandt = 'Language key'.

APPEND gi_fieldnames.

CLEAR gi_fieldnames.</b> CALL FUNCTION 'SO_SPLIT_FILE_AND_PATH'

EXPORTING

full_name = fnm

IMPORTING

stripped_name = stripped_name

file_path = file_path

EXCEPTIONS

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

CLEAR fnm.

CONCATENATE file_path stripped_name INTO fnm.

CLEAR: inpath,file,stripped_name,file_path,outpath.

<b>CALL FUNCTION 'WS_DOWNLOAD'

EXPORTING

filename = fnm

filetype = 'DAT'

TABLES

data_tab = gi_download

fieldnames = gi_fieldnames

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

OTHERS = 10.

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 i003(sa) WITH 'File downloaded successfuly' fnm.</b>

ENDIF.

REFRESH: gi_download,gi_mara,gi_marc,gi_makt.

CLEAR: gi_download,gi_mara,gi_marc,gi_makt.

regards

siva

Message was edited by:

siva prasad