Home » SQL & PL/SQL » SQL & PL/SQL » tokenize CSV data for UTL_FILE load
tokenize CSV data for UTL_FILE load [message #37241] Sat, 26 January 2002 04:40 Go to next message
Vishws
Messages: 3
Registered: January 2002
Junior Member
Hello,

I am trying to load CSV (Excel comma separated values) file into tables using UTL_FILE.
Some data in excel fields have , and " for example

Baby id Name Sex Weight Notes
1 Smith, John M 7 Head cir 13", Length 30"

Which when saved into Excel csv has the foll. format with " as the escape character

Baby id,Name,Sex,weight,Notes
1,"Smith,John",M,7,"Head cir 13"", Length 30"""

A generic procedure that would tokenize csv data taking care of the escape character " and , in data
would be great.

tokenize_csv(
strIn in varchar2, (i.e. input string)
nosTkn out number, (i.e number of tokens returned)
TknTbl out arr_ty (i.e. a varchar2 PL/SQL table of tokens) )

I am newbie at this and thanks very much for your help.

Vishwas
Re: tokenize CSV data for UTL_FILE load [message #37243 is a reply to message #37241] Sat, 26 January 2002 07:10 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
why dont u use sqlloader?
Re: tokenize CSV data for UTL_FILE load [message #37245 is a reply to message #37241] Sat, 26 January 2002 16:30 Go to previous messageGo to next message
Vishws
Messages: 3
Registered: January 2002
Junior Member
Thanks, but it is not possible to use sqlloader as lot of processing is involved before loading the data in multiple tables.

Regards,

Vishwas
Re: tokenize CSV data for UTL_FILE load [message #37247 is a reply to message #37245] Sun, 27 January 2002 09:13 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
then load into staging tables (you can drop these tables, once you done with loading) and write a stored procedure to load from these tables to acyual tables
Re: tokenize CSV data for UTL_FILE load [message #37257 is a reply to message #37241] Mon, 28 January 2002 05:03 Go to previous messageGo to next message
Vishws
Messages: 3
Registered: January 2002
Junior Member
Thanks very much for your interest and help.

Loading data into a temporary table is not practical for my application, as it is not a one time job run from the console - concurrent users with web forms 6i upload files in the utl_file_dir and a form with controls like checkboxes and radiobuttons is used to control what data is loaded by the procedure. This was working fine till we got data with " , ' and spaces.

Regards,

Vishwas
Re: tokenize CSV data for UTL_FILE load [message #37267 is a reply to message #37241] Mon, 28 January 2002 09:16 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
easy to replace two " with one " using replace(in_str, '""', '"')

See http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:464420312302
Previous Topic: Problem with a procedure
Next Topic: Re: Problem with a procedure
Goto Forum:
  


Current Time: Thu Mar 28 20:18:08 CDT 2024