SQL Loader - loading data with embedded newlines

About

You may struggle to load data with embedded line separator (new lines of in-line carriage return). sqlloader offer two possibilities :

  • if you have a special character at the end of each line: the stream record format

Using the STR attribute, we can specify a new end-of-line character (or sequence of characters). This allows us to create an input data file that has some special character at the end of each line. The newline is no longer special.

  • if you don't have a special character at the end of each line: the CONTINUEIF statement

Articles Related

The data to load

The target.dat file with the data as csv format with double quote and a vertical bar at the end of each line. Suppress the vertical bar when you test with the CONTINUEIF statment.

"1","My texte with
a of
several carriage return 
in the de description. If you add to this

text that it wil be a
BIGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG

text with moooooooooooooooooooooooore than
255 character, you must then 
explicitly give the size
  of the          buffer. If you count it, you will then find 330 characters"|
"2","A second row but only to not let alone the first one"|

The target table

 CREATE TABLE target
 ( 
   ID integer,
   DESCRIPTION varchar2(500)
 );

The SQLLDR command syntax

When you have create the control file (see section below), just run this command:

C:\data_to_load>sqlldr userid=gerardnico/gerardnico control=target.ctl log=target.log
 
SQL*Loader: Release 10.2.0.3.0 - Production on Fri Jan 8 01:16:00 2010
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
Commit point reached - logical record count 2

The controle files

Depending of your platform (Windows, Unix, Mac-Os), the end of line changes: See Data Storage - (Newline|End of Line (EOL)|Line Separators) by OS and language

STR

Stream Record format Documentation “str X'220D0A'” is equivalent to “str '|\r\n'” for the windows platform.

LOAD DATA
INFILE 'target.dat' "str X'220D0A'"
INTO TABLE target
REPLACE
FIELDS TERMINATED BY ','
(
"ID",
"DESCRIPTION" CHAR(500)
)
The terminator_string is specified as either 'char_string' or X'hex_string' where:
  • 'char_string' is a string of characters enclosed in single or double quotation marks
  • X'hex_string' is a byte string in hexadecimal format that you can get with the cast_to_raw function as:
SELECT utl_raw.cast_to_raw( '|'||chr(10) ) FROM dual;
UTL_RAW.CAST_TO_RAW('|'||CHR(10))
-------------------------------------------------------------------------------
7C0A

Nonprintable characters should be specified as an X'hex_string' however some nonprintable characters can be specified as

  • Carriage returns = \r = chr(13)
  • Line Feeds = \n = chr(10)
  • Vertical tab = \v =
  • Horizontal tab = \t = chr(9)

CONTINUEIF LAST

The controle file with the clause CONTINUEIF LAST

LOAD DATA
INFILE 'target.dat'
CONTINUEIF LAST != '"'
INTO TABLE target
APPEND
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
(
"ID",
"DESCRIPTION" CHAR(500)
)

The result

SET pagesize 9999
SET linesize 105
COLUMN description format A100
COLUMN id format 99
SELECT id, description FROM target;

With the stream format

SQL> SELECT id, description FROM target;

 ID DESCRIPTION
--- -----------------------------------------------------------------------------------
  1 My texte with
    a of
    several carriage return
    in the de description. If you add to this

    text that it wil be a
    BIGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG

    text with moooooooooooooooooooooooore than
    255 character, you must then
    explicitly give the size
      of the          buffer. If you count it, you will then find 330 characters

  2 A second row but only to not let alone the first one

With CONTINUEIF

No carriage return are still visible. You lost the format.

 ID DESCRIPTION
--- ----------------------------------------------------------------------------------------------------
  1 My texte witha ofseveral carriage return in the de description. If you add to thistext that it wil b
    e aBIGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGtext with moooooooooooooooooooooooore than255 charac
    ter, you must then explicitly give the size  of the          buffer. If you count it, you will then
    find 330 characters

  2 A second row but only to not let alone the first one

Support

  • SQL*Loader-457: Comparison text of CONTINUEIF LAST must have length 1 not 2

Documentation / Reference

  • Bookmark "SQL Loader - loading data with embedded newlines" at del.icio.us
  • Bookmark "SQL Loader - loading data with embedded newlines" at Digg
  • Bookmark "SQL Loader - loading data with embedded newlines" at Ask
  • Bookmark "SQL Loader - loading data with embedded newlines" at Google
  • Bookmark "SQL Loader - loading data with embedded newlines" at StumbleUpon
  • Bookmark "SQL Loader - loading data with embedded newlines" at Technorati
  • Bookmark "SQL Loader - loading data with embedded newlines" at Live Bookmarks
  • Bookmark "SQL Loader - loading data with embedded newlines" at Yahoo! Myweb
  • Bookmark "SQL Loader - loading data with embedded newlines" at Facebook
  • Bookmark "SQL Loader - loading data with embedded newlines" at Yahoo! Bookmarks
  • Bookmark "SQL Loader - loading data with embedded newlines" at Twitter
  • Bookmark "SQL Loader - loading data with embedded newlines" at myAOL
 
database/oracle/sqlloader_carriage_return_in_text_field.txt · Last modified: 2012/11/09 10:46 by gerardnico