Loading huge excel file

Question

Hello All! Loading huge excel file 35k lines (api_datasource.importFromFile) - what to do? are there any parallel execution features or some oracle hacks to speed up the process? Or probably there is some max limit number of rows to be proceeded by importFromFile? Regards, Dmitry.

Answer 1

0	Hi Dmitry, There is not a feature to speed up the current process. Also, there is not a predefined limit for the number of rows to be proceeded. Are you experiencing any problem during the import? Regards, Ibrahim link answered 05 Dec '13, 14:34 Ibrahim Sand... ♦♦ 1.5k●5 accept rate: 25%

Answer 2

Hello, sorry for long reply. Finally I ve managed to load a file using importFromFile tool. 6M Excel file with 35k rows inside loaded for about 10 minutes which is acceptable. But another issue is that is seems that the most of browsers (except Firefox) have some kind of a timeout. I have some further steps after the end of the load (for example a message that everything loaded) but they never fire. If I load a smaller file size - everything goes ok. So the question is if we can commit after some number of rows loaded or/and if there is any workaround for the "browser not responding" issue? Best regards, Dmitry

Answer 3

0	Hi Dmitry would you be able to post your Excel file structure and api_datasource.importFromFile example? I am interested to compare this as well with your results. Thanks link answered 13 Dec '13, 01:45 downunder 61●2 accept rate: 0%

Answer 4

Excel file have simple structure - 11 columns: 1st number, 2nd - date, and 9 strings. 35к rows. Table for import have 11 fields of varchar2(256).

Code:

l_ColumnNames api_datasource.tt_datasourceColumnNames;

l_blob blob;

begin

................

l_ColumnNames(1) := 'FIELD1';

l_ColumnNames(2) := 'FIELD2';

l_ColumnNames(3) := 'FIELD3';

l_ColumnNames(4) := 'FIELD4';

l_ColumnNames(5) := 'FIELD5';

l_ColumnNames(6) := 'FIELD6';

l_ColumnNames(7) := 'FIELD7';

l_ColumnNames(8) := 'FIELD8';

l_ColumnNames(9) := 'FIELD9';

l_ColumnNames(10) := 'FIELD10';

l_ColumnNames(11) := 'FIELD11';

api_datasource.importFromFile(in_datasourceName_tx => 'ds_excel_data', in_file_bl => l_blob, in_fileFormat_cd => api_datasource.FILE_FORMAT_XLS, in_datasourceColumnNames_t => l_ColumnNames);

..................

end;

Answer 5

0

Hello Team, are there any updates regarding "browser not responding" issue? (please see the original question from Dmitry)

Best regards, Anatoly

link

answered 15 Dec '13, 16:52

anatoly4u
69●16
accept rate: 0%

Hi Anatoly,

That seems to be a Google Chrome bug. We are looking for a workaround. We'll update that thread when we have one.

Kind Regards,
Yalim

(16 Dec '13, 09:48) Yalim Gerger ♦♦

Answer 6

0

sorry for resurrecting this thread but... I have an xls file which is 3mb in size and has 22k rows in it with 10 fields. appears to work, but takes a really long time, 24 minutes is this about normal???

Simon

link

answered 13 Jun '16, 05:00

apacheuk
139●2●20
accept rate: 0%

edited 13 Jun '16, 05:23

Hi Simon,

24 minutes time for this seems odd to me. Does it include an upload time if you're using an fileUpload component maybe?

If so, is it possible to check what the upload time is by commenting out the api_datasource.importFromFile API call from your procedure?

Kind regards,
Serdar

(13 Jun '16, 08:49) Serdar ♦♦

see info below, couldn't format the code in this comment :)

(13 Jun '16, 09:50) apacheuk

Answer 7

Added some debug to the code to get some timings results below as well as the relevent code...

api_debug.log('uploading file');
v_bl := api_component.getvaluebl('imp_load_tasks2.FileUploader'); 
api_debug.log('file uploaded');
api_datasource.importfromfile('_UPLOADED_TASK_APPLIC1', v_bl, api_datasource.FILE_FORMAT_XLS,     v_datasourceColumnNames_t);
api_debug.log('file imported');
api_debug.log(api_datasource.getrowcount('_UPLOADED_TASK_APPLIC1'));

debug output

USER : 13-06-2016 13:45:57 uploading file
USER : 13-06-2016 13:45:57 file uploaded
USER : 13-06-2016 14:16:09 file imported
USER : 13-06-2016 14:16:09 22011

As you can see its the actual importfromfile that takes the time the actual upload takes no time at all.

Answer 8

0

any update on this performance issue?

link

answered 23 Jun '16, 03:49

apacheuk
139●2●20
accept rate: 0%

Hi Simon,

We're on this issue. I'll let you know as soon as we found something.

By the way, can you share your excel file via e-mail if it is possible?

Kind Regrads Serdar

(23 Jun '16, 06:46) Serdar ♦♦

There is no way for me to do that due to Uk security restrictions, I can probably tell you what the column data types are and how many we have and the total number of rows if that would help?

(23 Jun '16, 07:49) apacheuk

Answer 9

Still have an issue with this and was looking into it again and noticed we are using api_datasource.FILE_FORMAT_XLS as the file format, I noticed that there is also a api_datasource.FILE_FORMAT_XLSX in the api, could just be a red hearing but figured I try it

Whenever I set to api_datasource.FILE_FORMAT_XLSX (save the file in the right format) and try and import I get the following error :-

api_datasource.importFromFile => Invalid import file format parameter "xlsx". Expected "xls".

does the api_datasource.FILE_FORMAT_XLSX option not work?

Answer 10

been trying to get to the bottom of this performance issue and if it helps here is the out from a trace for the query that I think is causing the the performance issue, I believe this is where is parsing the XML

SQL ID: bh8v3gs8v616h Plan Hash: 2711747855

SELECT CLASS ,PLATFORM ,TASK_ID ,TASK_VERS ,ACCREF ,LASTCOMPLETED ,
  DATECREATED ,DELETED ,DATEDELETED ,OLDLASTCOMPLETED ,
  BDF_DATASOURCEINS_SEQ.NEXTVAL DATASOURCEINSTANCE_OID ,:B3 
  BDF_DBOBJECTACCESSINS_OID ,BDF_DMLORDER_SEQ.NEXTVAL BDF_DMLORDER_NR ,:B2 
  BDF_STATUS_CD ,-1 BDF_SNAPSHOTINS_OID ,ROWNUM BDF_ORDER_NR ,'Y' 
  BDF_FILTERED_YN ,ROWNUM BDF_ORIGINALORDER_NR 
FROM
( SELECT EXTRACTVALUE (VALUE (DATA), '*/CLASS') CLASS ,EXTRACTVALUE (VALUE 
  (DATA), '*/PLATFORM') PLATFORM ,EXTRACTVALUE (VALUE (DATA), '*/TASK_ID') 
  TASK_ID ,EXTRACTVALUE (VALUE (DATA), '*/TASK_VERS') TASK_VERS ,EXTRACTVALUE 
  (VALUE (DATA), '*/ACCREF') ACCREF ,EXTRACTVALUE (VALUE (DATA), 
  '*/LASTCOMPLETED') LASTCOMPLETED ,EXTRACTVALUE (VALUE (DATA), 
  '*/DATECREATED') DATECREATED ,EXTRACTVALUE (VALUE (DATA), '*/DELETED') 
  DELETED ,EXTRACTVALUE (VALUE (DATA), '*/DATEDELETED') DATEDELETED ,
  EXTRACTVALUE (VALUE (DATA), '*/OLDLASTCOMPLETED') OLDLASTCOMPLETED FROM 
  TABLE (XMLSEQUENCE (EXTRACT(:B1 , 'ROWS/ROW'))) DATA)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      2.09       5.42          0      14687      86308           0
Fetch        1   3132.64    4832.89          0          0       2934       29341
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3   3134.73    4838.32          0      14687      89242       29341

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 3216     (recursive depth: 4)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     29341      29341      29341  SEQUENCE  BDF_DATASOURCEINS_SEQ (cr=17390 pr=0 pw=0     time=167987491 us)
     29342      29342      29342   COUNT  (cr=14456 pr=0 pw=0 time=158818446 us)
     29342      29342      29342    COLLECTION ITERATOR PICKLER FETCH XMLSEQUENCEFROMXMLTYPE (cr=14456 pr=0 pw=0 time=158258917 us cost=2 size=20 card=10)

Loading huge excel file

Follow this question

Related questions