Home » SQL & PL/SQL » SQL & PL/SQL » Import CSV data from CLOB (Oracle 12c, Unix)
Import CSV data from CLOB [message #679515] |
Fri, 06 March 2020 08:13 |
|
Rayam69
Messages: 43 Registered: May 2012
|
Member |
|
|
Hi
I have a situation where CSV data is getting loaded into CLOB with multiple rows. I need to import the data into a table.
Any suggestions to do this without exporting the data into a csv file and then import data into a table.
regards,
Balaji
|
|
|
|
Re: Import CSV data from CLOB [message #679519 is a reply to message #679516] |
Fri, 06 March 2020 09:21 |
|
Rayam69
Messages: 43 Registered: May 2012
|
Member |
|
|
Hi Michael,
below is the sample rows. can you explain little further. each field separated by a comma will have to be loaded into a column in a table.
22,110,68320,80037,VCRMS,0,0,0,28-JUN-2018 15:02:29
22,110,1463,81041,AXRMQ,0,0,0,28-JUN-2018 15:02:29
regards,
|
|
|
|
Re: Import CSV data from CLOB [message #679530 is a reply to message #679520] |
Fri, 06 March 2020 12:39 |
|
Rayam69
Messages: 43 Registered: May 2012
|
Member |
|
|
Hi Michael,
i want to read the data in the tran_data field and load the inventory table. each row in transactions table may have multiple rows of csv data in the CLOB.
create table transactions(
tran_id number,
tran_data clob
);
insert into transactions(tran_id, tran_data)
values(1, '22,110,68320,80037,VCRMS,0,0,0,28-JUN-2018 15:02:29
22,110,1463,81041,AXRMQ,0,0,0,28-JUN-2018 15:02:29');
insert into transactions(tran_id, tran_data)
values(2, '22,110,42940,80063,XBAQW,0,0,0,28-JUN-2018 15:02:29
22,110,75660,81128,IVLPA,0,0,0,28-JUN-2018 15:02:29');
commit;
CREATE TABLE inventory (
id varchar2(20),
locid varchar2(20),
vendor varchar2(20),
ven_loc varchar2(20),
partnum varchar2(20),
available_qty varchar2(20),
qa_qty varchar2(20),
on_hold_qty varchar2(20),
inventory_date date
);
regards
|
|
|
Re: Import CSV data from CLOB [message #679532 is a reply to message #679530] |
Fri, 06 March 2020 13:00 |
|
Michel Cadot
Messages: 68659 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> insert into inventory
2 select regexp_substr(line, '[^,]+', 1, 1) id,
3 regexp_substr(line, '[^,]+', 1, 2) locid,
4 regexp_substr(line, '[^,]+', 1, 3) vendor,
5 regexp_substr(line, '[^,]+', 1, 4) ven_loc,
6 regexp_substr(line, '[^,]+', 1, 5) partnum,
7 regexp_substr(line, '[^,]+', 1, 6) available_qty,
8 regexp_substr(line, '[^,]+', 1, 7) qa_qty,
9 regexp_substr(line, '[^,]+', 1, 8) on_hold_qty,
10 to_date(regexp_substr(line, '[^,]+', 1, 9),
11 'DD-MON-YYYY HH24:MI:SS',
12 'NLS_DATE_LANGUAGE=AMERICAN')
13 inventory_date
14 from (
15 select regexp_substr(tran_data, '[^
16 ]+', 1, column_value) line
17 from transactions,
18 table(cast(multiset(select level from dual
19 connect by level <= regexp_count(tran_data,'
20 ')+1)
21 as sys.odciNumberList))
22 )
23 /
4 rows created.
SQL> set recsep wrap recsepchar '.'
SQL> select * from inventory;
ID LOCID VENDOR VEN_LOC PARTNUM
-------------------- -------------------- -------------------- -------------------- --------------------
AVAILABLE_QTY QA_QTY ON_HOLD_QTY INVENTORY_DATE
-------------------- -------------------- -------------------- -------------------
22 110 68320 80037 VCRMS
0 0 0 28/06/2018 15:02:29
........................................................................................................
22 110 1463 81041 AXRMQ
0 0 0 28/06/2018 15:02:29
........................................................................................................
22 110 42940 80063 XBAQW
0 0 0 28/06/2018 15:02:29
........................................................................................................
22 110 75660 81128 IVLPA
0 0 0 28/06/2018 15:02:29
........................................................................................................
4 rows selected.
|
|
|
|
|
Re: Import CSV data from CLOB [message #679552 is a reply to message #679547] |
Sat, 07 March 2020 06:29 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Michel, OP is on 12C, so it can be greatly simplified using LATERAL/CROSS APPLY. Also, Oracle regexp supports match parameter m that treats string as multi-line:
insert into inventory
select regexp_substr(line,'[^,]+',1,1) id,
regexp_substr(line,'[^,]+',1,2) locid,
regexp_substr(line,'[^,]+',1,3) vendor,
regexp_substr(line,'[^,]+',1,4) ven_loc,
regexp_substr(line,'[^,]+',1,5) partnum,
regexp_substr(line,'[^,]+',1,6) available_qty,
regexp_substr(line,'[^,]+',1,7) qa_qty,
regexp_substr(line,'[^,]+',1,8) on_hold_qty,
to_date(
regexp_substr(line, '[^,]+', 1, 9),
'DD-MON-YYYY HH24:MI:SS',
'NLS_DATE_LANGUAGE=AMERICAN'
) inventory_date
from transactions,
lateral(
select regexp_substr(tran_data,'^.*$',1,level,'m') line
from dual
connect by level <= regexp_count(tran_data,'$',1,'m')
)
/
4 rows created.
SQL> select *
2 from inventory
3 /
ID LOCID VENDOR VEN_LOC PARTNUM AVAILABLE_QTY QA_QTY ON_HOLD_QTY INVENTORY_DATE
-- ----- ------ ------- ------- ------------- ------ ----------- --------------------
22 110 68320 80037 VCRMS 0 0 0 28-JUN-2018 15:02:29
22 110 1463 81041 AXRMQ 0 0 0 28-JUN-2018 15:02:29
22 110 42940 80063 XBAQW 0 0 0 28-JUN-2018 15:02:29
22 110 75660 81128 IVLPA 0 0 0 28-JUN-2018 15:02:29
SQL>
SY.
|
|
|
|
|
Re: Import CSV data from CLOB [message #679563 is a reply to message #679562] |
Mon, 09 March 2020 11:26 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Rayam69 wrote on Mon, 09 March 2020 11:18the query returns only the first row from CLOB.
Queries Michel and I posted return all rows from each CLOB. Post your query (one that produces "only the first row from CLOB").
If you are on 11G, I'd use:
insert into inventory
with lines as(
select regexp_substr(tran_data,'^.*$',1,level,'m') line
from transactions
connect by level <= regexp_count(tran_data,'$',1,'m')
and rowid = prior rowid
and prior sys_guid() is not null
)
select regexp_substr(line,'[^,]+',1,1) id,
regexp_substr(line,'[^,]+',1,2) locid,
regexp_substr(line,'[^,]+',1,3) vendor,
regexp_substr(line,'[^,]+',1,4) ven_loc,
regexp_substr(line,'[^,]+',1,5) partnum,
regexp_substr(line,'[^,]+',1,6) available_qty,
regexp_substr(line,'[^,]+',1,7) qa_qty,
regexp_substr(line,'[^,]+',1,8) on_hold_qty,
to_date(
regexp_substr(line, '[^,]+', 1, 9),
'DD-MON-YYYY HH24:MI:SS',
'NLS_DATE_LANGUAGE=AMERICAN'
) inventory_date
from lines
/
4 rows created.
SQL> select banner
2 from v$version
3 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> select *
2 from inventory
3 /
ID LOCID VENDOR VEN_LOC PARTNUM AVAILABLE_QTY QA_QTY ON_HOLD_QTY INVENTORY_DATE
-- ----- ------ ------- ------- ------------- ------ ----------- --------------------
22 110 68320 80037 VCRMS 0 0 0 28-JUN-2018 15:02:29
22 110 1463 81041 AXRMQ 0 0 0 28-JUN-2018 15:02:29
22 110 42940 80063 XBAQW 0 0 0 28-JUN-2018 15:02:29
22 110 75660 81128 IVLPA 0 0 0 28-JUN-2018 15:02:29
SQL>
SY.
|
|
|
Re: Import CSV data from CLOB [message #679564 is a reply to message #679563] |
Mon, 09 March 2020 11:48 |
|
Rayam69
Messages: 43 Registered: May 2012
|
Member |
|
|
Thanks Solomon. This query works in 11g. I will try the other query with lateral in 12c later in the day. the query that returns only the first row is given below.
select regexp_substr(line, '[^,]+', 1, 1) id,
regexp_substr(line, '[^,]+', 1, 2) locid,
regexp_substr(line, '[^,]+', 1, 3) vendor,
regexp_substr(line, '[^,]+', 1, 4) ven_loc,
regexp_substr(line, '[^,]+', 1, 5) partnum,
regexp_substr(line, '[^,]+', 1, 6) available_qty,
regexp_substr(line, '[^,]+', 1, 7) qa_qty,
regexp_substr(line, '[^,]+', 1, on_hold_qty,
to_date(regexp_substr(line, '[^,]+', 1, 9),
'DD-MON-YYYY HH24:MI:SS',
'NLS_DATE_LANGUAGE=AMERICAN')
inventory_date
from (
select regexp_substr(tran_data, '[^
]+', 1, column_value) line
from transactions,
table(cast(multiset(select level from dual
connect by level <= regexp_count(tran_data,'
')+1)
as sys.odciNumberList))
)
/
regards.
|
|
|
|
|
|
|
Re: Import CSV data from CLOB [message #679574 is a reply to message #679572] |
Tue, 10 March 2020 04:30 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Are there any triggers on the table?
Are there any primary/unique keys that it would block on?
1500+ rows should take seconds.
What is the exact code you're running at this point?
|
|
|
Re: Import CSV data from CLOB [message #679579 is a reply to message #679570] |
Tue, 10 March 2020 07:16 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Rayam69 wrote on Mon, 09 March 2020 15:26
Its just it takes for ever to read a row of CLOB data and load into a table.
the test CLOB had close to 1500+ rows. That's a lot.
OK. So each CLOB has about 1500 lines (not rows). And how many rows are in the table? So if table has 100,000 rows we end up splitting it into 100,000 * 1,500 = 150,000,000 rows. That can take some time especially when using regexp. You might need to replace regexp with substr/instr.
SY.
|
|
|
|
|
|
Re: Import CSV data from CLOB [message #679586 is a reply to message #679585] |
Tue, 10 March 2020 09:03 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Try running your query as a query - standalone in sqlplus.
See how long it takes. Should be a few seconds.
If it's taking an hour to process a single clob then the problem is unlikely to be the query. Hence the questions I asked above.
|
|
|
|
Re: Import CSV data from CLOB [message #679590 is a reply to message #679587] |
Tue, 10 March 2020 10:01 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
SQL> create table transactions(
tran_id number,
tran_data clob
); 2 3 4
Table created.
SQL> insert into transactions(tran_id, tran_data)
values(1, '22,110,68320,80037,VCRMS,0,0,0,28-JUN-2018 15:02:29
22,110,1463,81041,AXRMQ,0,0,0,28-JUN-2018 15:02:29'); 2 3
1 row created.
SQL> insert into transactions(tran_id, tran_data)
values(2, '22,110,42940,80063,XBAQW,0,0,0,28-JUN-2018 15:02:29
22,110,75660,81128,IVLPA,0,0,0,28-JUN-2018 15:02:29'); 2 3
1 row created.
SQL> commit;
Commit complete.
SQL> CREATE TABLE inventory (
id varchar2(20),
locid varchar2(20),
vendor varchar2(20),
ven_loc varchar2(20),
partnum varchar2(20),
available_qty varchar2(20),
qa_qty varchar2(20),
on_hold_qty varchar2(20),
inventory_date date
); 2 3 4 5 6 7 8 9 10 11
Table created.
SQL> set timing on;
SQL> insert into inventory
with lines as(
select regexp_substr(tran_data,'^.*$',1,level,'m') line
from transactions
connect by level <= regexp_count(tran_data,'$',1,'m')
and rowid = prior rowid
and prior sys_guid() is not null
)
select regexp_substr(line,'[^,]+',1,1) id,
regexp_substr(line,'[^,]+',1,2) locid,
regexp_substr(line,'[^,]+',1,3) vendor,
regexp_substr(line,'[^,]+',1,4) ven_loc,
2 3 4 5 6 7 8 9 10 11 12 13 regexp_substr(line,'[^,]+',1,5) partnum,
regexp_substr(line,'[^,]+',1,6) available_qty,
regexp_substr(line,'[^,]+',1,7) qa_qty,
regexp_substr(line,'[^,]+',1,8) on_hold_qty,
to_date(
regexp_substr(line, '[^,]+', 1, 9),
'DD-MON-YYYY HH24:MI:SS',
'NLS_DATE_LANGUAGE=AMERICAN'
) inventory_date
from lines 14 15 16 17 18 19 20 21 22
23 /
4 rows created.
Elapsed: 00:00:00.01
SQL>
The issue is in your environment not ours.
Have you trying just the select on its own to see how long it takes?
|
|
|
Re: Import CSV data from CLOB [message #679598 is a reply to message #679590] |
Tue, 10 March 2020 10:27 |
|
Rayam69
Messages: 43 Registered: May 2012
|
Member |
|
|
Hi
if the CLOB has only 2 lines, its instant..
try to just add the same value given below 750+ times and update one row. then try the insert.
22,110,68320,80037,VCRMS,0,0,0,28-JUN-2018 15:02:29
22,110,1463,81041,AXRMQ,0,0,0,28-JUN-2018 15:02:29
regards.
|
|
|
Re: Import CSV data from CLOB [message #679599 is a reply to message #679598] |
Tue, 10 March 2020 10:37 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
SQL> DELETE FROM transactions;
2 rows deleted.
Elapsed: 00:00:07.72
SQL> delete from inventory;
4 rows deleted.
Elapsed: 00:00:00.00
SQL> insert into transactions(tran_id, tran_data)
values(1, '22,110,68320,80037,VCRMS,0,0,0,28-JUN-2018 15:02:29
22,110,1463,81041,AXRMQ,0,0,0,28-JUN-2018 15:02:29'); 2 3
1 row created.
Elapsed: 00:00:00.00
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL> BEGIN
FOR i IN 1..750 LOOP
UPDATE transactions
SET tran_data = tran_data||'
22,110,68320,80037,VCRMS,0,0,0,28-JUN-2018 15:02:29
22,110,1463,81041,AXRMQ,0,0,0,28-JUN-2018 15:02:29';
END LOOP;
END; 2 3 4 5 6 7 8 9 10 11 12
13 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.96
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL> insert into inventory
with lines as(
select regexp_substr(tran_data,'^.*$',1,level,'m') line
from transactions
connect by level <= regexp_count(tran_data,'$',1,'m')
and rowid = prior rowid
and prior sys_guid() is not null
)
select regexp_substr(line,'[^,]+',1,1) id,
regexp_substr(line,'[^,]+',1,2) locid,
regexp_substr(line,'[^,]+',1,3) vendor,
regexp_substr(line,'[^,]+',1,4) ven_loc,
2 regexp_substr(line,'[^,]+',1,5) partnum,
regexp_substr(line,'[^,]+',1,6) available_qty,
regexp_substr(line,'[^,]+',1,7) qa_qty,
regexp_substr(line,'[^,]+',1,8) on_hold_qty,
to_date(
regexp_substr(line, '[^,]+', 1, 9),
'DD-MON-YYYY HH24:MI:SS',
'NLS_DATE_LANGUAGE=AMERICAN'
) inventory_date
from lines 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
23 /
1502 rows created.
Elapsed: 00:01:09.68
SQL>
1 minute 10 seconds to insert 1502 rows.
|
|
|
Re: Import CSV data from CLOB [message #679600 is a reply to message #679599] |
Tue, 10 March 2020 10:44 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Now, for the third time of asking: have you tried the select by itself to see how long it takes?
For a single clob with 1500 odd rows it should take few minutes (rendering time in sqlplus means it'll take longer than the insert). Mine took 1:49
If takes a lot longer than that then your DB server is seriously under-powered.
The DB I used for my tests is hosted on my laptop.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Jun 17 02:16:11 CDT 2024
|