Let me begin by answering a related question, a question very similar to yours. In fact, this question is yours with only one character added:
I have an ASCII text file delimited by "|" characters. What is the best of reading these records into tables: using PL/SQL?
Requires Free Membership to View
No, the best way of reading those files is to use SQL*Loader. For all its quirks and poor documentation, SQL*Loader is still the best tool for this job. Trying to read the file in PL/SQL is somewhat like trying to open a can of peas with a hammer: it's possible to do it, but why would you ever want to? You'd be much better off getting a package of fresh or frozen peas and opening that with a hammer.
To use SQL*Loader, you have to write a control file that will describe the input fields and link them to database tables and columns. The control file also specifies various options, like what to do with records that have errors.
To load this file, question.csv:
Question|Answer|Category|Date Submitted|ID "How can I read a text file with ""|"" characters between fields?"|Use SQLLoader|PLSQL|15-Jul-2003|1 Why do canned peas taste so bad?||MISC|15-Jul-2003|2into this table:
CREATE TABLE question ( question_id NUMBER (8) PRIMARY KEY, question_text VARCHAR2 (200) NOT NULL, answer_text VARCHAR2 (4000), status_code CHAR (1), submit_dt DATE );you can use this control file, question.ldr:
-- Question.ldr Load Question table -- 18 July 2003 -- Script written. (Frank Kulash) -- OPTIONS ( -- The first line in the file is a header: ignore it SKIP = 1 ) LOAD DATA INFILE 'question.csv' -- Rejected records will be written to BADFILE BADFILE 'question.bad' -- APPEND means add to existing table, -- REPLACE means truncate table first APPEND INTO TABLE question FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( question_text, answer_text CHAR (4000), category_abbr FILLER, submit_dt DATE 'DD-Mon-YYYY', question_id )You can run SQL*Loader from an operating-system command prompt like this:
SQLLDR USERID=scott/tiger@conect_string CONTROL=question.ldr LOG=question.log(The command above is really one long line.) Note these features:
- SQL*Loader reads the table description from the database, so you don't have to give type specifications for most numbers or short strings.
- Specify CHAR (len) for string columns over 250 characters long
- Specify FILLER to skip a column in the data file
- Database columns not specified will be set to NULL
modify_user_name CONSTANT 'SCOTT', modify_dt_tm SYSDATE, id SEQUENCE (MAX)
Now to answer your question: Next page...
This was first published in July 2003
Join the conversationComment
Share
Comments
Results
Contribute to the conversation