gui_upload map excel input to internal table

[Former Member]

Hello Experts,

I have the following requirement.

The final goal is to upload excel data to an internal table and eventually insert it into database table.

Now I understand that I need to use GUI_UPLOAD to upload the data from excel.

The problem is I am not sure about which columns are there in the excel file. e.g

If the internal table is of a structure type having  a,b,c,d as its elements.

Now the excel file can only have a,d as the  two columns. So I need to be able to upload them and then successfully map it to coressponding entries in the internal table.

The problem is I don't know which columns the input excel file might have. It can have only b or a,b,c,d or so on.. And the requirement is to be able to successfully map it to right columns of the internal table.

I hope I am able to explain it clearly.

Your help is much appreciated.

Cheers

[Former Member]

Well, you must have some way of identifying which columns are present. The simplest would be to require that the first row of the file is just the column name.

I don't think GUI_UPLOAD handles Excel files well. Search the forum and you'll see better methods.

Rob

[Former Member]

Hi Gary,

My suggestion would be to get the whole file into one internal table with only 1 field to hold the whole row. Then based on the header you may identify the number of columns. Since it is an XLS file you can use tab character to separate them. 

Regards,

Shravan

[former_member203305]

Hi,

do you know the max. of column that the excel could bring?. if you know that info, create a table with Max field columns. I'm using   CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP' to pass a file xlsx to an interntal table.

After you have the table with all the fields, check the header of the column and redefine where the field should go...example, field3 not empty move to fieldX...

Regards

[former_member213851]

Hi Gary,

Please use FM ALSM_EXCEL_TO_INTERNAL_TABLE as this FM reads data in row-wise.

Now once you get data inside internal table, you need to add check based on column.

EG:

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'

       exporting

            filename                = p_infile

            i_begin_col             = '1'

            i_begin_row             = '1' 

            i_end_col               = '100'

            i_end_row               = '3000'

       tables

            intern                  = itab

       exceptions

            inconsistent_parameters = 1

            upload_ole              = 2

            others                  = 3.

  if sy-subrc <> 0.

    message e010(zz) with text-001. "Problem uploading Excel Spreadsheet

  endif

* --------------Set first row retrieved to current row

  gd_currentrow = itab-row.

loop at itab.

*---------------- Reset values for next row

  if itab-row ne gd_currentrow.

  append wa_record to it_record.

  clear wa_record.

  gd_currentrow = itab-row.

  endif.

  case itab-col.

  when '0001'.      "Materiel Number

  wa_record-matnr = itab-value.

  when '0002'.      "Materiel Description

  wa_record-maktx = itab-value.

  when '0003'.      "Net price

  wa_record-netpr = itab-value.

  endcase.

  endloop.

  append wa_record to it_record.

Best Ragards,

Sachin

[Kartik2]

Dear Gary Gary,

Please go through the document . It will help you in uploading data directly from Microsoft Excel sheet to internal table.

Here after the upload you have to split the content based on tab '#'.

Number of occurances of '#' in the first line will give you an idea as of how many fields are present, then based on that you can create a dynamic internal table and populate the internal table with data.

Hope it helps. Thank you.

Regards,

kartik

[venkateswaran_k]

Hi

do you mean to say, the excel sheet can contain columns 

   a, b  -  in the 1st and 2nd colum   some times    OR

   a, b, d some times....  1st , 2nd , 3rd colum somtimes.....

Or

The xecel sheet contains fixed 5 columns  but the values  are varying put in their approrprite colums  - say

a, b  in 1st and 2nd col

a, b, d  in 1st  2nd  and 4th col

Post back with the clarificaiton

Regards,

Venkat

[Former Member]

Hello Venkat,

Exactly.

For example, the structure contains firstname, lastname, address and so on upto 30 elements.

Now in the excel file, there can be .. Smith, Gary, St peters parade..  or it can have

St peters parade, Smith, Gary, 80335

Now without column names how can I map it to the respective fields of the database table?

[Former Member]

And this is the SAP CRM system I am talking about so we can only use GUI UPLOAD and no other function modules as they are not present in SAP CRM system.

[Former Member]

Also another requirement is once the report program is written it should not be modified. Meaning if the excel file column names order is different e.g 80335, St peters parade, Gary, Smith, Teacher  then without making changes it should be written to the corresponding entries in the database table.

Hope this is clear.

[venkateswaran_k]

Okay.

At least in the Excel sheet do they maintain the headings?

moreover, the heading names should be fixed...

Example... First Name , Address etc...

In the next file theyshould not say  instead of Frist name say name  and Instead of address say Addr etc

What i Mean is at least they should have consistent Labels.(Fixed name). in all their excel sheet.. 

That means,   atleast,  all their excel sheet, the Label names are fixed.(Predefined). Their sequence may be changed...

Regards,

Venkat

[Former Member]

Say for e.g if they maintain column names then how can it be done? Column names order is not fixed. So first name, last name , address can be interchanged.

If there are no column  names maintained then how can it be done?

Is there any way where we can define some kind of mapping so that we simply change the mapping and the report program remains the same.

[Former Member]

Are you saying that not only the number and order of the fields may change from file to file, but that they may be different from record to record within a particular file??

Rob

[venkateswaran_k]

Hi

In order to access data from the external medium such as Excel sheet, we need / expect a fixed pattern. Other wise it is near to impossible. Because we write code based on logic not building intelligence in the program.  Anyways.. we do as follows

1. Ask the privider of excel sheet atleast to provide the Colum heading on the Excel sheet.

2. The column heading they should follow  same for all excel sheet.  Meaning they should use same name for the column in all their excel sheet.

3. You make an mapping table that  maps their column name and your database field.

4. Then Everyime you read excel sheet record - first read the first row and get the colum names and get

    the appropriate field from the mapping field

5. Set theloop and Allocate the values accordingly

Hope this brief would help you..  ( Probably you may also have this idea...)

Post back your idea if it is okay to you

Regards,

Venkat

[Former Member]

Hi Venkat,

What do you mean when you say Make a mapping table?

[dayakar_sama]

http://scn.sap.com/thread/3185506

http://scn.sap.com/thread/1675118

hope this will help Gary..!!

thanks,

sama

[Former Member]

Hi Gary Gary,

try for the puprose you can use the FM  --  TEXT_CONVERT_XLS_TO_SAP

this will take the data from excel and provide you in the form of an internal table.

You can define your internal table with the expected columns but make sure columns should not be less than that in excel, if more no problems .

Then you can process as per the requirement.

here is the code snippet for the same

DATA: itab_contracts TYPE TABLE OF ty_contract,

* Fields of the uploaded file

TYPES: BEGIN OF ty_contract,

         sales_org LIKE vbak-vkorg,
         sales_doc TYPE vbap-vbeln,
         sd_item LIKE vbap-posnr,
         material_entered LIKE mara-matnr,
         sd_item_desc  LIKE vbap-arktx,

END OF ty_contract.

CALL FUNCTION 'GUI_UPLOAD'

     EXPORTING

       filename                = filepath_string

       filetype                = 'ASC'

       has_field_separator     = 'X'

       dat_mode                = ''

     TABLES

       data_tab                = file_data

     EXCEPTIONS

       file_open_error         = 1

       file_read_error         = 2

       no_batch                = 3

   IF sy-subrc <> 0.

   ELSE.

     CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

       EXPORTING

         i_field_seperator    = 'X'

         i_line_header        = 'X'

         i_tab_raw_data       = file_data

         i_filename           = p_fpath

       TABLES

         i_tab_converted_data = itab_contracts

       EXCEPTIONS

         conversion_failed    = 1

         OTHERS               = 2.

     IF sy-subrc <> 0.

       MESSAGE ID sy-msgid TYPE 'S' NUMBER sy-msgno

                 WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4

DISPLAY LIKE 'E'.
         LEAVE LIST-PROCESSING.
     ENDIF.
   ENDIF.

[Sandeep_Kumar]

Hi Gary,

Did you find a solution somehow, if yes, could you please share how you achieved it?

Cheers,

Sandeep