topic Re: Large Excel file upload in Application Development and Automation Discussions

Large Excel file upload

Former Member — Wed, 15 May 2013 20:28:41 GMT

I have been trying to find a means of uploading an Excel spreadsheet into an internal table. There are a number of posts on this topic, which suggest Function Modules such as ALSM_EXCEL_TO_INTERNAL_TABLE and KCD_EXCEL_OLE_TO_INT_CONVERT. These work fine for small spreadsheets, but all FMs I have found return structures where the row and column are 4 characters each. I have a spreadsheet with more than 10,000 lines so I need my row number to be 5 characters. All FMs I have tried so far either truncate the row to 4 characters, or short dump. Does anyone know of a FM that will handle larger spreadsheets?

Re: Large Excel file upload

Former Member — Wed, 15 May 2013 21:48:46 GMT

Hi Steve,

I don't know about any other FMs but couldn't you upload the data (via FM GUI_UPLOAD) into an internal table (based on type string) and then loop through it, splitting the lines based on commas, tabs or whatever the seperator you have in Excel?

(Of course you'd first need to save the excel file as csv/tab-delimited/etc. file with a .csv/.txt/etc. extension)

Re: Large Excel file upload

ganesh_padala2 — Wed, 15 May 2013 22:04:53 GMT

Reading a Excel file with .xls extension is always difficult, you can either use the FM you mentioned or use OLE mechanism, but what we normally recommend is to ask the user to save the excel as tab-delimited text file with .txt extension and then we use GUI_UPLOAD to read the file into internal table.

Regards,

Ganesh

Re: Large Excel file upload

anup_deshmukh4 — Wed, 15 May 2013 22:23:47 GMT

Hello Steve,

You can very well save the file with extension as .CSV ( comma seperated and then process it in TXT format either from presentation server or from Application server .

Hope this helps !

Anup

Re: Large Excel file upload

arindam_m — Wed, 15 May 2013 22:30:07 GMT

Hi,

The check if you have available working memory to read such a large file and also execute the program. Often if the internal tables go big in program the memory allocated for program execution runs out and you cannot process it further.

Usually your error will be

"TSV_TNEW_PAGE_ALLOC_FAILED along with the short Text No more storage space available for extending an internal table."

You need to check ST02 for Roll area, Paging Area and Extended memory. These can be administered by BASIS Admin so get that changed to see if you can get more to execute such a large file

Cheers,

Arindam

Re: Large Excel file upload

sai_krishna24 — Wed, 15 May 2013 23:05:45 GMT

Hey Steve,

Try this function module - TEXT_CONVERT_XLS_TO_SAP this works well large data. I executed this with around 30000 records.

Check it out. Hope it will help you.

Sample code:

*   if it is uploaded from Presentation Server (XLS format)
DATA: itab_raw TYPE truxs_t_text_data.
*    Read the data
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
*       I_FIELD_SEPERATOR          =
I_LINE_HEADER              = C_X
i_tab_raw_data             = itab_raw
i_filename                 = ifile
tables
i_tab_converted_data       = itab_mara
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.

Best Regards,

SaiKrishna