topic Re: gui_upload map excel input to internal table in Application Development and Automation Discussions

gui_upload map excel input to internal table

Former Member — Thu, 14 Jun 2012 14:30:44 GMT

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

Re: gui_upload map excel input to internal table

Former Member — Thu, 14 Jun 2012 14:48:17 GMT

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

Re: gui_upload map excel input to internal table

Former Member — Thu, 14 Jun 2012 15:00:55 GMT

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

Re: gui_upload map excel input to internal table

Former Member — Thu, 14 Jun 2012 16:49:06 GMT

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

Re: gui_upload map excel input to internal table

former_member213851 — Thu, 14 Jun 2012 17:40:08 GMT

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

Re: gui_upload map excel input to internal table

Kartik2 — Fri, 15 Jun 2012 03:53:00 GMT

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

Re: gui_upload map excel input to internal table

venkateswaran_k — Fri, 15 Jun 2012 07:49:04 GMT

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

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

Re: gui_upload map excel input to internal table

Former Member — Fri, 15 Jun 2012 08:53:51 GMT

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?

Re: gui_upload map excel input to internal table

Former Member — Fri, 15 Jun 2012 08:55:46 GMT

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.

Re: gui_upload map excel input to internal table

Former Member — Fri, 15 Jun 2012 08:59:14 GMT

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.

Re: gui_upload map excel input to internal table

venkateswaran_k — Fri, 15 Jun 2012 09:10:11 GMT

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

Re: gui_upload map excel input to internal table

Former Member — Fri, 15 Jun 2012 09:16:55 GMT

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.

Re: gui_upload map excel input to internal table

dayakar_sama — Fri, 15 Jun 2012 11:46:19 GMT

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

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

hope this will help Gary..!!

thanks,

sama

Re: gui_upload map excel input to internal table

Former Member — Fri, 15 Jun 2012 13:29:46 GMT

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

Re: gui_upload map excel input to internal table

venkateswaran_k — Fri, 15 Jun 2012 13:50:05 GMT

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

Re: gui_upload map excel input to internal table

Former Member — Tue, 19 Jun 2012 09:43:17 GMT

Hi Venkat,

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

Re: gui_upload map excel input to internal table

Former Member — Tue, 19 Jun 2012 10:12:54 GMT

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.

Re: gui_upload map excel input to internal table

Sandeep_Panghal — Tue, 14 May 2013 16:29:55 GMT

Hi Gary,

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

Cheers,

Sandeep