Home » SQL & PL/SQL » SQL & PL/SQL » how to load csv file that is in a CLOB column into a table ?
 how to load csv file that is in a CLOB column into a table ? [message #172995] |
Thu, 18 May 2006 20:17  |
smeira
Messages: 8
Registered: May 2006
Location: Australia
|
Junior Member |
|
|
Hi,
Does someone have an ideia on loading CSV data into a table?
The difficulty is that this CSV data is stored in another table , in a Clob column, and not in a directory.
What would be better, use the dbms_lob to retrieve the records and do the insert or put this clob in a directory (dbms_lob + utl_file) and use external tables ? or any other idea ?
Thanks,
Silvana
|
|
|
|
|
|
|
|
|
|
| Re: how to load csv file that is in a CLOB column into a table ? [message #173012 is a reply to message #173001] |
Thu, 18 May 2006 23:41  |
smeira
Messages: 8
Registered: May 2006
Location: Australia
|
Junior Member |
|
|
Sorry, I will try to be more specific:
The data I want to load is inside a Clob column in a CSV format:
example:
1)Source table with the Clob column
desc tab_source
csv_col clob
2)Target table I want to load data into
desc tab_target
col1 number,
col2 varchar2(50),
col3 date
The data to be loaded in tab_target is in tab_source , in the csv_col column and the content of this column is in a csv format...
Was it clear or it seems to be more complicated ?
|
|
|
|
| Re: how to load csv file that is in a CLOB column into a table ? [message #173243 is a reply to message #173012] |
Sat, 20 May 2006 22:54 |
 |
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You could do it using sql alone or sql with a function. I demonstrated both below.
SCOTT@10gXE> -- tables and test data:
SCOTT@10gXE> CREATE TABLE tab_source
2 (csv_col CLOB)
3 /
Table created.
SCOTT@10gXE> INSERT ALL
2 INTO tab_source VALUES ('1,test1,20-MAY-2006')
3 INTO tab_source VALUES ('2,test2,19-MAY-2006')
4 INTO tab_source VALUES ('3,test3,21-MAY-2006')
5 SELECT * FROM DUAL
6 /
3 rows created.
SCOTT@10gXE> SELECT * FROM tab_source
2 /
CSV_COL
--------------------------------------------------------------------------------
1,test1,20-MAY-2006
2,test2,19-MAY-2006
3,test3,21-MAY-2006
SCOTT@10gXE> CREATE TABLE tab_target
2 (col1 NUMBER,
3 col2 VARCHAR2(50),
4 col3 DATE)
5 /
Table created.
SCOTT@10gXE> -- sql only:
SCOTT@10gXE> INSERT INTO tab_target (col1, col2, col3)
2 SELECT TO_NUMBER (SUBSTR (csv_col, 1, INSTR (csv_col, ',') - 1)),
3 SUBSTR (csv_col, INSTR (csv_col, ',') + 1,
4 (INSTR (csv_col, ',', 1, 2)
5 - INSTR (csv_col, ',')) - 1),
6 TO_DATE (SUBSTR (csv_col, INSTR (csv_col, ',', 1, 2) + 1), 'DD-MON-YYYY')
7 FROM tab_source
8 /
3 rows created.
SCOTT@10gXE> SELECT * FROM tab_target
2 /
COL1 COL2 COL3
---------- -------------------------------------------------- ---------
1 test1 20-MAY-06
2 test2 19-MAY-06
3 test3 21-MAY-06
SCOTT@10gXE> -- reset:
SCOTT@10gXE> ROLLBACK
2 /
Rollback complete.
SCOTT@10gXE> SELECT * FROM tab_target
2 /
no rows selected
SCOTT@10gXE> -- sql with function:
SCOTT@10gXE> CREATE OR REPLACE FUNCTION list_element
2 (p_string VARCHAR2,
3 p_element INTEGER,
4 p_separator VARCHAR2 DEFAULT ',')
5 RETURN VARCHAR2
6 AS
7 v_string VARCHAR2(32767);
8 BEGIN
9 v_string := p_string || p_separator;
10 FOR i IN 1 .. p_element - 1 LOOP
11 v_string := SUBSTR (v_string, INSTR (v_string, p_separator) + LENGTH (p_separator));
12 END LOOP;
13 RETURN SUBSTR (v_string, 1, INSTR (v_string, p_separator) - 1);
14 END list_element;
15 /
Function created.
SCOTT@10gXE> INSERT INTO tab_target (col1, col2, col3)
2 SELECT TO_NUMBER (list_element (csv_col, 1)),
3 list_element (csv_col, 2),
4 TO_DATE (list_element (csv_col, 3), 'DD-MON-YYYY')
5 FROM tab_source
6 /
3 rows created.
SCOTT@10gXE> SELECT * FROM tab_target
2 /
COL1 COL2 COL3
---------- -------------------------------------------------- ---------
1 test1 20-MAY-06
2 test2 19-MAY-06
3 test3 21-MAY-06
SCOTT@10gXE>
|
|
|
|
| Re: how to load csv file that is in a CLOB column into a table ? [message #173324 is a reply to message #173243] |
Sun, 21 May 2006 20:14 |
smeira
Messages: 8
Registered: May 2006
Location: Australia
|
Junior Member |
|
|
Beautiful solution, Barbara,but the only problem is that you put each csv line in a separate line into the csv_col in tab_source.
The csv_col should have all the csv lines , not just 1 line.
Imagine that you uploaded a file from the Web and the entire file was put into one Clob column.
Is it clear ?
|
|
|
|
| Re: how to load csv file that is in a CLOB column into a table ? [message #173520 is a reply to message #173324] |
Mon, 22 May 2006 19:01 |
|
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@10gXE> CREATE TABLE tab_source (csv_col CLOB)
2 /
Table created.
SCOTT@10gXE> INSERT INTO tab_source VALUES (
2 '1,test1,20-MAY-2006,2,test2,19-MAY-2006,3,test3,21-MAY-2006,')
3 /
1 row created.
SCOTT@10gXE> SELECT * FROM tab_source
2 /
CSV_COL
--------------------------------------------------------------------------------
1,test1,20-MAY-2006,2,test2,19-MAY-2006,3,test3,21-MAY-2006,
SCOTT@10gXE> CREATE TABLE tab_target
2 (col1 NUMBER,
3 col2 VARCHAR2(50),
4 col3 DATE)
5 /
Table created.
SCOTT@10gXE> INSERT INTO tab_target (col1, col2, col3)
2 SELECT TO_NUMBER (list_element (csv_col, rn - 2)),
3 list_element (csv_col, rn - 1),
4 TO_DATE (list_element (csv_col, rn), 'DD-MON-YYYY')
5 FROM tab_source,
6 (SELECT ROWNUM rn
7 FROM DUAL
8 CONNECT BY LEVEL <=
9 (SELECT LENGTH (csv_col)
10 - LENGTH (REPLACE (csv_col, ',', ''))
11 FROM tab_source))
12 WHERE MOD (rn, 3) = 0
13 /
3 rows created.
SCOTT@10gXE> SELECT * FROM tab_target
2 /
COL1 COL2 COL3
---------- -------------------------------------------------- ---------
1 test1 20-MAY-06
2 test2 19-MAY-06
3 test3 21-MAY-06
SCOTT@10gXE>
|
|
|
|
| Re: how to load csv file that is in a CLOB column into a table ? [message #173664 is a reply to message #173520] |
Tue, 23 May 2006 18:33 |
smeira
Messages: 8
Registered: May 2006
Location: Australia
|
Junior Member |
|
|
Hi Barbara,
Good on you !
I have been trying to understand the "CONNECT BY LEVEL" ...
What if each line is bellow each other (like a flat file) and not besides ( as you did) ?
Ex:
Instead of having the clob like this:
'1,test1,20-MAY-2006,2,test2,19-MAY-2006,3,test3,21-MAY-2006,'
You will have:
1,test1,20-MAY-2006
2,test2,19-MAY-2006
3,test3,21-MAY-2006
All of the three lines in the same clob
Is it a good idea to read a piece of the clob, using dbms_lob.read with the amount parameter equal to the record size and then using your function "list_element" to do the insert ?
|
|
|
|
| Re: how to load csv file that is in a CLOB column into a table ? [message #173667 is a reply to message #173664] |
Tue, 23 May 2006 19:21 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
|
Thanks Barbara for introducing us to new features, but for this problem, I'd personnaly do it the good old fassioned way --> dump the clob to flat file then just use sqlloader (or external tables) to load it in. I'd want a soution which allows for the occasional rejected row and have the solution simple enough for an inexperienced programmer to fix....
|
|
|
|
| Re: how to load csv file that is in a CLOB column into a table ? [message #173825 is a reply to message #173664] |
Wed, 24 May 2006 12:09 |
|
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Here are three options to choose from.
The first option uses the list_element function and a single sql insert statement. The list_element is a generic function, not specific to the situation. It is not something that you would ever modify. You just use it like you would any built-in function. The select statement used in the insert, uses a common row generation technique. I have additionally provided an alternate method below that, which simply selects rownum from any table big enough to have enough rows and checks the length of the result. You may find it easier to understand and maintain. This method does not allow for bad data.
The second option uses a single pl/sql block and handles rejected rows.
The third method uses spooling and external tables and handles rejected rows. You could use utl_file instead of spooling if you like and you could use SQL*Loader instead of external tables if you like.
-- option 1 (function and insert):
SCOTT@10gXE> CREATE TABLE tab_source (csv_col CLOB)
2 /
Table created.
SCOTT@10gXE> INSERT INTO tab_source VALUES (
2 '1,test1,20-MAY-2006
3 2,test2,19-MAY-2006
4 3,test3,21-MAY-2006')
5 /
1 row created.
SCOTT@10gXE> SELECT * FROM tab_source
2 /
CSV_COL
--------------------------------------------------------------------------------
1,test1,20-MAY-2006
2,test2,19-MAY-2006
3,test3,21-MAY-2006
SCOTT@10gXE> CREATE TABLE tab_target
2 (col1 NUMBER,
3 col2 VARCHAR2(50),
4 col3 DATE)
5 /
Table created.
SCOTT@10gXE> CREATE OR REPLACE FUNCTION list_element
2 (p_string VARCHAR2,
3 p_element INTEGER,
4 p_separator VARCHAR2 DEFAULT ',')
5 RETURN VARCHAR2
6 AS
7 v_string VARCHAR2(32767);
8 BEGIN
9 v_string := p_string || p_separator;
10 FOR i IN 1 .. p_element - 1 LOOP
11 v_string := SUBSTR (v_string, INSTR (v_string, p_separator)
12 + LENGTH (p_separator));
13 END LOOP;
14 RETURN SUBSTR (v_string, 1, INSTR (v_string, p_separator) - 1);
15 END list_element;
16 /
Function created.
SCOTT@10gXE> INSERT INTO tab_target (col1, col2, col3)
2 SELECT list_element (the_rows, rn - 2, ','),
3 list_element (the_rows, rn - 1, ','),
4 list_element (the_rows, rn, ',')
5 FROM (SELECT list_element (csv_col || CHR(10), n, CHR(10)) || ',' AS the_rows
6 FROM tab_source,
7 (SELECT ROWNUM n
8 FROM DUAL
9 CONNECT BY LEVEL <=
10 (SELECT LENGTH (csv_col || CHR(10))
11 - LENGTH (REPLACE (csv_col || CHR(10), CHR(10), ''))
12 FROM tab_source))),
13 (SELECT ROWNUM rn
14 FROM DUAL
15 CONNECT BY LEVEL <= 3)
16 WHERE MOD (rn, 3) = 0
17 /
3 rows created.
SCOTT@10gXE> SELECT * FROM tab_target
2 /
COL1 COL2 COL3
---------- -------------------------------------------------- ---------
1 test1 20-MAY-06
2 test2 19-MAY-06
3 test3 21-MAY-06
SCOTT@10gXE> ROLLBACK
2 /
Rollback complete.
SCOTT@10gXE> SELECT * FROM tab_target
2 /
no rows selected
-- alternate inssert:
SCOTT@10gXE> INSERT INTO tab_target (col1, col2, col3)
2 SELECT list_element (the_rows, rn - 2, ','),
3 list_element (the_rows, rn - 1, ','),
4 list_element (the_rows, rn, ',')
5 FROM (SELECT list_element (csv_col || CHR(10), n, CHR(10)) || ',' AS the_rows
6 FROM tab_source,
7 (SELECT ROWNUM n FROM user_objects)),
8 (SELECT ROWNUM rn FROM user_objects WHERE ROWNUM <= 3)
9 WHERE MOD (rn, 3) = 0
10 AND LENGTH (the_rows) > 3
11 /
3 rows created.
SCOTT@10gXE> SELECT * FROM tab_target
2 /
COL1 COL2 COL3
---------- -------------------------------------------------- ---------
1 test1 20-MAY-06
2 test2 19-MAY-06
3 test3 21-MAY-06
SCOTT@10gXE>
-- option 2 (pl/sql block):
SCOTT@10gXE> CREATE TABLE tab_source (csv_col CLOB)
2 /
Table created.
SCOTT@10gXE> INSERT INTO tab_source VALUES (
2 '1,test1,20-MAY-2006
3 2,test2,19-MAY-2006
4 some bad data
5 3,test3,21-MAY-2006')
6 /
1 row created.
SCOTT@10gXE> SELECT * FROM tab_source
2 /
CSV_COL
--------------------------------------------------------------------------------
1,test1,20-MAY-2006
2,test2,19-MAY-2006
some bad data
3,test3,21-MAY-2006
SCOTT@10gXE> CREATE TABLE tab_target
2 (col1 NUMBER,
3 col2 VARCHAR2(50),
4 col3 DATE)
5 /
Table created.
SCOTT@10gXE> CREATE TABLE rejects (rejected_row VARCHAR2(4000))
2 /
Table created.
SCOTT@10gXE> DECLARE
2 v_clob CLOB;
3 v_string VARCHAR2(32767);
4 v_string2 VARCHAR2(32767);
5 v_col1 tab_target.col1%TYPE;
6 v_col2 tab_target.col2%TYPE;
7 v_col3 tab_target.col3%TYPE;
8 BEGIN
9 SELECT csv_col || CHR(10) || ' ' INTO v_clob FROM tab_source;
10 WHILE DBMS_LOB.GETLENGTH (v_clob) > 3 LOOP
11 v_string := SUBSTR (v_clob, 1, INSTR (v_clob, CHR(10)) - 1);
12 BEGIN
13 v_col1 := TO_NUMBER (SUBSTR (v_string, 1, INSTR (v_string, ',') - 1));
14 v_string2 := SUBSTR (v_string, INSTR (v_string, ',') + 1);
15 v_col2 := SUBSTR (v_string2, 1, INSTR (v_string2, ',') - 1);
16 v_col3 := TO_DATE (SUBSTR (v_string2, INSTR (v_string2, ',') + 1), 'DD-MON-YYYY');
17 INSERT INTO tab_target (col1, col2, col3)
18 VALUES (v_col1, v_col2, v_col3);
19 EXCEPTION
20 WHEN OTHERS THEN INSERT INTO rejects (rejected_row) VALUES (v_string);
21 END;
22 v_clob := SUBSTR (v_clob, INSTR (v_clob, CHR(10)) + 1);
23 END LOOP;
24 END;
25 /
PL/SQL procedure successfully completed.
SCOTT@10gXE> SELECT * FROM tab_target
2 /
COL1 COL2 COL3
---------- -------------------------------------------------- ---------
1 test1 20-MAY-06
2 test2 19-MAY-06
3 test3 21-MAY-06
SCOTT@10gXE> SELECT * FROM rejects
2 /
REJECTED_ROW
----------------------------------------------------------------------------------------------------
some bad data
SCOTT@10gXE>
-- option 3 (spool and external tables):
SCOTT@10gXE> CREATE TABLE tab_source (csv_col CLOB)
2 /
Table created.
SCOTT@10gXE> INSERT INTO tab_source VALUES (
2 '1,test1,20-MAY-2006
3 2,test2,19-MAY-2006
4 some bad data
5 3,test3,21-MAY-2006')
6 /
1 row created.
SCOTT@10gXE> SELECT * FROM tab_source
2 /
CSV_COL
--------------------------------------------------------------------------------
1,test1,20-MAY-2006
2,test2,19-MAY-2006
some bad data
3,test3,21-MAY-2006
SCOTT@10gXE> CREATE TABLE tab_target
2 (col1 NUMBER,
3 col2 VARCHAR2(50),
4 col3 DATE)
5 /
Table created.
-- spool data to a text file, saving and restoring your settings:
STORE SET SAVED_SETTINGS REPLACE
SET ECHO OFF FEEDBACK OFF HEADING OFF PAGESIZE 0 VERIFY OFF
SPOOL tab_source.dat
SELECT csv_col FROM tab_source;
SPOOL OFF
START SAVED_SETTINGS
--
SCOTT@10gXE> CONNECT SYSTEM/SYSTEM_PASSWORD AS SYSDBA
Connected.
SYS@10gXE> CREATE OR REPLACE DIRECTORY my_dir2 AS 'c:\oracle2'
2 /
Directory created.
SYS@10gXE> GRANT READ, WRITE ON DIRECTORY my_dir2 TO scott
2 /
Grant succeeded.
SYS@10gXE> CONNECT scott/tiger
Connected.
SCOTT@10gXE> CREATE TABLE ext_tab
2 (col1 NUMBER,
3 col2 VARCHAR2(50),
4 col3 DATE)
5 ORGANIZATION external
6 (TYPE oracle_loader
7 DEFAULT DIRECTORY my_dir2
8 ACCESS PARAMETERS
9 (RECORDS DELIMITED BY NEWLINE
10 BADFILE 'MY_DIR2':'test.bad'
11 LOGFILE 'MY_DIR2':'test.log'
12 FIELDS TERMINATED BY "," LDRTRIM
13 REJECT ROWS WITH ALL NULL FIELDS
14 (col1,
15 col2,
16 col3 DATE MASK "DD-MON-YYYY"))
17 location ('tab_source.dat'))
18 REJECT LIMIT UNLIMITED
19 /
Table created.
SCOTT@10gXE> INSERT INTO tab_target SELECT * FROM ext_tab
2 /
3 rows created.
SCOTT@10gXE> CREATE TABLE rejects
2 (rejected_row VARCHAR2(4000))
3 ORGANIZATION external
4 (TYPE oracle_loader
5 DEFAULT DIRECTORY my_dir2
6 ACCESS PARAMETERS
7 (RECORDS DELIMITED BY NEWLINE
8 BADFILE 'MY_DIR2':'test2.bad'
9 LOGFILE 'test2.log'
10 FIELDS LDRTRIM
11 REJECT ROWS WITH ALL NULL FIELDS
12 (rejected_row (1:4000) CHAR(4000)))
13 location ('test.bad'))
14 REJECT LIMIT UNLIMITED
15 /
Table created.
SCOTT@10gXE> SELECT * FROM tab_target
2 /
COL1 COL2 COL3
---------- -------------------------------------------------- ---------
1 test1 20-MAY-06
2 test2 19-MAY-06
3 test3 21-MAY-06
SCOTT@10gXE> SELECT * FROM rejects
2 /
REJECTED_ROW
----------------------------------------------------------------------------------------------------
some bad data
SCOTT@10gXE>
|
|
|
|
|
|
|
|
|
|
| Re: how to load csv file that is in a CLOB column into a table ? [message #189927 is a reply to message #174090] |
Mon, 28 August 2006 11:05 |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
Hi Barbara,
I was just reading your very very helpful tips in this format and I have file (MS office excel workbook) and I need to load data from that file into Database table.
This file is modified by user(mostly CLOB fields) which I have supplied as extract.
I need to put back this modified data into table form this file. I tried to load data with sql*loader but it's not
loading in properly. Is it I need to convert the file in another format?
In file, lots records are more than 4000 characters for CLOB fields.
I have oracle 9i DB
Could you please guide me?
sample File format is like:
============================
1320900417 1320900417 job7 job7 Account Services Rep Traffic Clerk This position plans, coordinates and executes outbound freight movements from our shipping sites to our customer. Assist internal and external customers with traffic issues. Schedule orders for shipment based on customer guidelines. Keep and maintain specific routing information for customer. Verification of completed shipments and invoice status. Ability and knowledge of the best way to move shipments from a small package to a full truckload. Understanding of various scheduling screens in Newell system. Other duties which may be assigned. "<UL>
<LI>Basic knowledge of various modes of transportation and
how the industry works</LI>
<LI>Basic clerical and computer skills</LI>
<LI>Good communication skills both verbally and
written</LI>
<LI>Ability to accept responsibility and make necessary
decisions</LI></UL>"
Table stucture
==============
post_id VARCHAR2(40)
cat_id VARCHAR2(40)
category VARCHAR2(100)
job_desc CLOB
job_req CLOB
Thanks,
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun May 25 17:45:17 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
