Large Excel file upload

[Former Member]

Hi

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?

[Former Member]

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)

[ganesh_padala2]

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

[anup_deshmukh4]

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

[arindam_m]

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

[sai_krishna24]

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