help needed on FM upload gui

[Former Member]

Hi

I am trying to upload data from an XL file into an internal table.

I have defined the internal table as type string ,

but the data is not getting uploaded properly.

I have used the following input parameters

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = filename

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 =

• HEADER =

tables

data_tab = itab_dw.

the output i am getting as just one column internal table .

How should i proceed?

Regards,

Harshit Rungta

[Former Member]

Hi

I am trying to upload data from an XL file into an internal table.

I have defined the internal table as type string ,

but the data is not getting uploaded properly.

I have used the following input parameters

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

filename = filename

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 =

• HEADER =

tables

data_tab = itab_dw.

the output i am getting as just one column internal table .

How should i proceed?

Regards,

Harshit Rungta

[MarcinPciak]

This is common question. You need to set parameter

HAS_FIELD_SEPARATOR = 'X'

Next time please look for the anwser before asking the question.

Regards

Marcin

[Former Member]

Hi,

Check this code to read the data from Excel sheet..

DATA l_count TYPE sy-tabix.

   CONSTANTS: lc_begin_col TYPE i VALUE '1',
              lc_begin_row TYPE i VALUE '2',
              lc_end_col   TYPE i VALUE '2',
              lc_end_row   TYPE i VALUE '3000'.


  CLEAR p_i_excel_data. REFRESH p_i_excel_data.

* Function module to read excel file and convert it into internal table
   CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'
     EXPORTING
       filename                = p_p_file
       i_begin_col             = lc_begin_col
       i_begin_row             = lc_begin_row
       i_end_col               = lc_end_col
       i_end_row               = lc_end_row
     TABLES
       intern                  = i_data
     EXCEPTIONS
       inconsistent_parameters = 1
       upload_ole              = 2
       OTHERS                  = 3.
* Error in file upload
   IF sy-subrc NE 0 .
     MESSAGE text-006 TYPE 'E'.
     EXIT.
   ENDIF.
   IF i_data[] IS INITIAL .
     MESSAGE text-007 TYPE 'E'.
     EXIT.
   ELSE.
     SORT i_data BY row col .
* Loop to fill data in Internal Table
     LOOP AT i_data .
       MOVE i_data-col TO l_count .
       ASSIGN COMPONENT l_count OF STRUCTURE p_i_excel_data TO <fs_source> .
       MOVE i_data-value TO <fs_source> .
       AT END OF row .
* Append data into internal table
         APPEND p_i_excel_data.
         CLEAR p_i_excel_data.
       ENDAT .
     ENDLOOP .
   ENDIF .

Or if you want to read the data from Tab delimited file then ....

CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
filename = filename
FILETYPE = 'DAT'                      "  --> Pass this 
HAS_FIELD_SEPARATOR = 'X' " --> Pass this
* 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 =
* HEADER =
tables
data_tab = itab_dw.

[Former Member]

Hi ,

thanks for you answer,

but even by using field seperator = 'X'

I get just a single column in the internal table.

wheras the excel sheet has nmany columns.

regards,

Harshit Rungta

[Former Member]

Hi Avinash,

Thanks for the answer,

how is i_data declared?

Regards,

Harshit Rungta

[Former Member]

Also space is replaced by #.

How can i avoid it ?

Regards,

Harshit Rungta

[guilherme_frisoni]

Hi Harshit,

what do you mean by

i am getting as just one column internal table.

If you declare an internal table like this:

DATA: itab_dw TYPE TABLE OF string.

You will have an one column table, with just a string column.

Besides, if your problem is getting all file content in a single LINE, maybe your file doens't have the same codepage of your SAP GUI.

Try to save your file with other text editor to have the correct CR and LF characters at the end of file.

You can check this in debug time, if you see some # character where should be a new line there is a problem.

Regards,

Frisoni

[Former Member]

Hi Frisoni,

Thanks for the help .

My requirement is that i can uploaad data from an excel file , of any structure

to my internal table .

How should i define the internal table then?

Regards,

Harshit Rungta

[MarcinPciak]

When you already have data in string table, but separated with #, you need to split it this way:

data: iit_file_fields TYPE STANDARD TABLE OF string WITH HEADER LINE.

DATA: xsep TYPE xstring,
                sep TYPE string.

              xsep = '09'. "tab mark

          CALL FUNCTION 'HR_KR_XSTRING_TO_STRING'
            EXPORTING
              in_xstring = xsep
            IMPORTING
              out_string = sep.

  Loop at it_file_data.  "it_file_data is upload table (type string)
          SPLIT it_file_data AT sep INTO TABLE it_file_fields IN CHARACTER MODE.  "now data are stored in IT_FILE_FIELDS          
  endloop.

Regards

Marcin

Also if you know file structure the easiest would be using TEXT_CONVERT_XLS_TO_SAP

Check this link [Upload Excel document into internal table|http://www.sapdev.co.uk/file/file_upexcel.htm]

Edited by: Marcin Pciak on Jul 7, 2009 8:51 PM

[guilherme_frisoni]

Harshit,

you can use ALSM_EXCEL_TO_INTERNAL_TABLE FM, but this have some line limit I cannot precise right now.

Anyway, I don't reccomend to use directly excel files. You can use CSV files to have less processing.

Regards,

Frisoni