Ask the Expert

Using PL/SQL to read delimited ASCII text files into tables

I have an ASCII text file delimited by "|" characters. What is the best of reading these records into tables using PL/SQL?

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|2
into 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
SQL*Loader can also generate data (id numbers, constants, etc.). A few examples:
  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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: