Excel Upload to Internal Table

[Former Member]

Hi all,

I want to upload data which is in EXCEL file to an internal table.(BSP)

I have already read the blogs/theards for this topic. Question I have is, Is it possible to Upload an EXCEL file but not CSV to an Internal Table.

I have already implemented the below code for uploading an CSV file to Internal Table:

METHOD do_handle_event .

DATA: conv TYPE REF TO cl_abap_conv_in_ce,

fileupload TYPE REF TO cl_htmlb_fileupload.

DATA: xcontent TYPE xstring,

length TYPE i,

name TYPE string,

type TYPE string,

content TYPE string,

s_table TYPE string_table.

DATA: default_view TYPE REF TO if_bsp_page.

IF NOT htmlb_event IS INITIAL.

CASE htmlb_event->server_event.

WHEN 'Upload'.

fileupload ?= cl_htmlb_manager=>get_data( request = request id = 'myUpload' name = 'fileUpload' ).

IF ( fileupload->file_name NE '' ) AND ( fileupload->file_length GT 0 ).

name = fileupload->file_name.

length = fileupload->file_length.

type = fileupload->file_content_type.

xcontent = fileupload->file_content.

conv = cl_abap_conv_in_ce=>create( input = xcontent ignore_cerr = 'X').

conv->read( IMPORTING data = content len = length ).

SPLIT content AT cl_abap_char_utilities=>cr_lf INTO TABLE s_table.

MOVE name TO filename.

IF NOT s_table IS INITIAL.

process_data( s_table ).

ENDIF.

ENDIF.

ENDCASE.

ENDIF.

ENDMETHOD.

Ariel Ferreiro has helped me with the below code:

*-- VARIABLES: Events

DATA: event TYPE REF TO cl_htmlb_event,

button_event TYPE REF TO cl_htmlb_event_button.

event ?= cl_htmlb_manager=>get_event( runtime->server->request ).

• Button?

IF event->name = 'button' AND event->event_type = 'click'.

button_event ?= event.

IF button_event->id = 'btnUpload'.

• -- get the file HTMLB

fileUpload ?= CL_HTMLB_MANAGER=>GET_DATA(

request = request

id = 'txtFile'

name = 'fileUpload' ).

• Check if the user selected something

IF ( fileUpload->file_name NE '' ) AND

( fileUpload->file_length GT 0 ).

• Check if it is an excel file.

IF fileUpload->file_content_type EQ 'application/vnd.ms-excel'.

DATA:

input_string TYPE STRING,

fields TYPE string_table,

s_table TYPE string_table,

conv TYPE REF TO CL_ABAP_CONV_IN_CE,

len TYPE I,

iserror TYPE I,

tmp TYPE C.

• :: READ THE COnteNT OF XSTRING

conv = CL_ABAP_CONV_IN_CE=>CREATE(

input = fileUpload->file_content ).

• :: READ AND CONVERT

conv->READ(

importing

data = input_string

len = len ).

split input_string at cl_abap_char_utilities=>cr_lf

into table s_table.

field-symbols: <wa_table> like line of s_table.

LOOP AT s_table assigning <wa_table>.

• :: SKIP THE HEADER OF THE EXCEL (if it has one title header)

IF SY-TABIX NE 1.

split <wa_table> at cl_abap_char_utilities=>HORIZONTAL_TAB

into table fields.

• :: NOW PARSE THE FIELDS AND WORK WITH THEM (its all yours!

[guillaume-hrc · Accepted solution]

Hi,

If I can remember, Excel has its own binary format (BIFF) depending on the version you are using (Excel 97 is not the same as Excel 2000, which is itself not the same as Excel 2003), so it can be a right tough job to handle all those formats.

Besides, you are dealing with binary data and thus, it is awfully difficult to manage !

Found a document about these, check it :

http://sc.openoffice.org/excelfileformat.pdf

You might try to mimic one of the standard FM 'TEXT_CONVERT_XLS_TO_SAP', or wait for the new Excel XML format that might be easier to manipulate...

Best regards,

Guillaume

[kamathp]

This may not be a good way..but I think you can try this...I have not tried this...but just thought of a idea where we could use SAP Gui upload functions...

Write a ABAP program to upload the file in xls format.

Keep a parameter on the screen for filename.

In your BSP, Upload the file name in your BSP layout and use SUBMIT REPORT .....statement to pass the value to the ABAP report where the data is uploaded to internal table.

In the ABAP program pass the internal table to memory. (EXPORT...)

In the BSP get the internal table from memory(IMPORT)

Let me know if that works!!

[Former Member]

Hello PK,

thank you for your reply. The problem is in fact writing the report / program to upload the .xls file. Since all SAPGui functions are not available (or better: not working) in BSP, the problem is how to get the .xls from its proprietary Microsoft format into an internal table. I know of no way to transform the xls.

The Upload generates a binary xstring containing the .xls. I do not know how to get on from there.

As a result of all the input I have gotten, I have decided to change requirements from .xls to .csv, for they are far easier to import (or... they can be imported, whereas xls can not).

Thanks,

Lutz

[kamathp]

Lutz,

You can use the below to upload excel file in a ABAP program. Remember, what I told in my earlier reply was writing the upload part in the ABAP program and calling it via BSP. This may not work if you write it in BSP events.

data: it_exl like alsmex_tabline occurs 0 with header line.

• Uploads the excel file .

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'

exporting

filename = p_dfile ( this should be xls file)

i_begin_col = '1'

i_begin_row = '2'

i_end_col = '5'

• I_END_ROW = '2000'

i_end_row = p_row

tables

intern = it_exl

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

translate it_exl to upper case.

case it_exl-col.

when '0001'.

t_mara-matnr = it_exl-value.

when '0002'.

t_mara-mtart = it_exl-value.

when '0003'.

t_mara-spart = it_exl-value.

endcase.

at end of row.

append t_mara.

clear t_mara.

endat.

endloop.

The above logic will place your excel contents to a itab.(t_mara).

Note: You should know how many columns will be there in your excel and write logic accordingly 0001, 0002...

In the above case I knew there are 3 colums matnr, mtart and spart. If there are 10 colums then you have to write 10 cases and only at end you have to append it.

Once you upload it to the internal table . EXPORT this ITAB to memory with an ID.

In your BSP, first SUBMIT to this program passing the filename.

IMPORT that itab with that ID.

Thats it, your uploaded values are in your BSP Itab!

[Former Member]

PK,

thank you for the info.

I forgot to mention that I work with SAP-CRM. For some reason we do not have the ALSM_EXCEL_TO_INTERNAL_TABLE function.

I have even thought of copying that function from ERP somewhen along the way.

Are you positive that the submit would work with that function? I think that it will not run in BSP context.

Thank you,

Lutz

[Former Member]

Greetings,

I too tried copying the FM to my SCM system. Turned out to be more of a hassle than it was worth. I am not sure PK's method will work. Or if it does it seems a little unorthodox to me. But then again I have done my share of 'outside the box thinking ;-)' in the past. Anyway, if you try the submit and get it working please let me know. I am curious. In the end, as I am sure you already have done, I would search SDN and read Thomas Jungs BLOG on uploading a spreadsheet. At least I think it was him who wrote a BLOG on it. IMHO asking the endusers to upload a CSV in not a big deal and it makes it easier for support of your app. Like I said that is just my opinion.

Cheers,

Rich

[Former Member]

Hello,

in fact Brian McKellar has just answered me on the issue (which seems to be a fairly old one).

His advice was very similar: Use csv and save your energy for something important.

I am just amazed that there is no built in way to import Excel files via BSP.

But then it does make sense, for an Excel import would expose you to a number of problems such as what version of Excel will the file be? What if the user calling the BSP runs Linux and does not have Excel?

Csv upload seems a lot more generic.

Thanks, Lutz

P.S.: I found a blog by Thomas Jung on downloading as excel file, not on uploading excel files.

Maybe I missed it.

[Former Member]

Greetings,

Well maybe if Microsoft had bought SAP a few years ago this kinda stuff might be easier. But lest I digress. Brian is the man. I'd listen to him too.

I must be wrong. Its possible that Thomas Jung's BLOG was on download not upload. I did that in my BSP over 2 years ago and old age is setting in and effecting my memory;-)

Good luck with your BSP. Maybe when your done you could re-write it as a WD4A. Hah. In your spare time

Cheers!

Rich

[Former Member]

Richard,

for some reason my BSP looks a lot like your BSP sample.

Most of the time I am glad Microsoft did not buy SAP (Actually it was more like "SAP did not want to be bought by Microsoft.", if I recall correctly ^^).

But then programming in .net is so much more intuitive that sometimes I wish they had.

Hopefully, luck will not have anything to do with my programming, haha.

As for WebDynpros I think I will not see those in a long time.

Cheers to you, Richard,

next time I will post my own question so I can assign you the points you deserve - promise.

Lutz

P.S.: I am sad to see that I am where you were two years ago

Does that mean that you can tell me where I will be in two years as well?

[Former Member]

Hey,

Yes you are correct. SAP chose not to be bought. No worries about the points. I don't care about them. Well have to learn from each other.

I have no crystal ball about where you'll be in 2 years. I just happen to be in the right place at the right time.

FYI not sure if its still on SDN but there is a fantastic interview with Brian, Dirk and Rudiger about BSP and WD4A. Its a video interview. If I remember correctly one of the things they said was that they would prefer people know BSP before getting into WD4A. So your definitly headed in the right direction.

Cheers,

Rich

[Former Member]

Greetings,

Good luck. I went down this road a few years ago and I ended up asking the users to please save their data as .CSV files. Which was all well and good till me European friends decided they wanted to use the app also. Then I had to look for semi-colon seperated values. Then I was switching my keyboard to simulate the European format for testing etc.....what a PIA. Got it working though.

Lest I digress, if I remember correctly XLS is saved in a MS proprietary format. The other poster said BIFF(didn't know thats what it was called) which is why I asked my users to use .CSV. Not exactly an elegant solution but it workd.

Thats just my 2 cents..

Cheers,

Rich

[Former Member]

Hello all,

we need exactly the same solution. With a WinGUI it seems fairly simple to import an excel file using "ALSM_Excel_To_Internal_Table" or "WS_UPLOAD" / "GUI_UPLOAD".

The problem is, that these functions do not work with BSP and serverside execution.

Did anyone find a solution to this issue?

Please advise...

TIA, Lutz Morrien

[Former Member]

Morning,

Please see VGP's response above. He has posted the code needed to upload a spreadsheet. You are using IE to access SAP not the SAPGUI. SInce when using BSP the SAPGui is not involved hence you cannot use any of the SAPGui services. GUI_UPLOAD or WS_UPLOAD etc......

Let me know if you want I can post the exact code I used but it is fairly similar to what VGP posted.

Cheers,

Rich

[Former Member]

Hello Richard,

in fact everything related to SAPGUI is not working and I understand why.

I am trying out the code from VGP and it will cause an exception within the conversion part:

conv = CL_ABAP_CONV_IN_CE=>CREATE( input = xcontent ignore_cerr = 'X' ).

* READ AND CONVERT

conv->READ( importing data = content len = length ).

split content at cl_abap_char_utilities=>cr_lf

into table s_table.

If I run it with an excel sheet saved as .csv, it will split it into rows.

However, if I run it with a .xls, xcontent will contain the propietory excel format and will not split at all.

s_table contains one row reading (much longer though):

###&#2161;##################>############# {...} ######

Is the code meant to import csv really? I am about ready to tell our customers that they have to save as csv before they import spreadsheets.

Thank you for your help,

Lutz Morrien

P.S.: Yes, your code might help a lot on the way.

[Former Member]

Hi,

Yes I had to tell the users to please save their spreadsheet as a .CSV file before uploading it. They weren't exactly happy at first then they got over it. Not sure if it matters but my code was running in a SCM 4.0 system and I couldn't find some of the same Excel FM that I found in an ERP system.

Anyway here is the code. I took it right from SDN and barely did anything and it worked. Thank god for SDN. Hope this formats ok.

DATA: xcontent TYPE xstring,

content TYPE string,

conv TYPE REF TO cl_abap_conv_in_ce,

len TYPE i,

len2 TYPE i.

DATA: data TYPE REF TO cl_htmlb_fileupload.

.

.

.

.

data ?= cl_htmlb_manager=>get_data(

request = runtime->server->request

name = c_fileupload

id = c_body_subc_myfileupload1 ).

IF data IS NOT INITIAL.

name = data->file_name.

xcontent = data->file_content.

len = data->file_length.

ENDIF.

conv = cl_abap_conv_in_ce=>create( input = xcontent ).

conv->read( IMPORTING data = content len = len ).

SPLIT content AT cl_abap_char_utilities=>cr_lf

INTO TABLE lt_tsi.

LOOP AT lt_tsi INTO wa_tsi.

.

.

.

.

ENDLOOP.

[Former Member]

Dear Richard,

thank you again. So the code really imports .csv, not .xls.

Mine looks a lot like that one and it does work.

Three more questions if I may...

Does Excel always use <;> as delimiter when it is saved to .csv?

What do I have to do to import special chars from unicode. the code does not import chars like "€äöü".

CL_ABAP_CONV_IN_CE uses UTF-8, but I obviously have UTF-16. The chars show up as '"####".

Last but not least: can I credit you with forum points, if I am not the owner of a thread? Or do I have to open a thread to credit points?

thank you and forgive my ignorance, I am just getting into BSP and ABAP.

Lutz

[Former Member]

Addition:

If I save the .xls to .csv, then open it with notepad.exe and save it using UTF8 format, all is well.

So it is a problem with encoding.

Lutz

[Former Member]

Hey,

To the best of my knowledge Excel does not alway use <;> as a delimiter. Since your asking this question I am assuming your not in the US. In the US it uses a comma. With that said you can change the delimiter on your laptop. If your BSP is going to be run globally then you need to account for this. You can test it yourself by changing your laptop setting between US and Europe. I forget how right now. Probably on the control panel or something.

Yes its on the control panel. Regional and Language settings. If your in Europe after testing your upload change your regional settings to be US. And try to upload the same .CSV file again. You should see the issues that you may need to account for in your code.

Does this make any sense?

Rich

[Former Member]

Hello Richard,

yes, all you said does make sense.

My solution is to have the user set the delimiter in addition to giving the import file name.

That should be flexible enough.

Only problem here is that the user might save with Excel and not know what delimiter is chosen.

Another problem is the decimal point in numbers. (€ 1,000.00 or 1.000,00 €)

We will figure something out I guess. After all, the Excel file needs to have a known format anyway to transfer it into an internal table.

All is well,

csv import is working perfectly with your code.

Thank you for your valuable input (since I cannot assign you points),

Lutz