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

Digg This!     StumbleUpon     Del.icio.us

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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?

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

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
);

-- 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
)

SQLLDR  USERID=scott/tiger@conect_string
  CONTROL=question.ldr  LOG=question.log

• 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...