Friday, December 5, 2014

How to load multiple rows into single column in SQL Loader?


Option 1

Sample Table Script

CREATE TABLE XX_SQLLDR_DEMO_STG
(
   RECORD_ID   NUMBER,
   SENDER      VARCHAR2 (120),
   RECEIVER    VARCHAR2 (120),
   TEXT        VARCHAR2 (2000)

);

Sample Control File

LOAD DATA
CHARACTERSET UTF8
INFILE '$DATA_FILE_NAMECONTINUEIF LAST <> '|'
INTO TABLE XX_SQLLDR_DEMO_STG
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
RECORD_ID "LTRIM(RTRIM(:RECORD_ID))",
SENDER    "LTRIM(RTRIM(:SENDER))",
RECEIVER  "LTRIM(RTRIM(:RECEIVER))",
TEXT      "LTRIM(RTRIM(:TEXT))"

)

Sample Data File

Need to Add the Data / Fields delimiter at the end of each record in Data File.


1|ABCD@XYZ.COM|XYZ@ABCD.COM|Hi XYZ,

how to load Multiple lines as single record in sqlldr?

Thanks & Regards,
ABCD|
2|XYZ@ABCD.COM|ABCD@XYZ.COM|Hi ABCD,

This is test data file for multiple lines loading into a single field.

Thanks & Regards,
xyz|


It will loads the data (replacing newline character) like this...



Option 2

Sample Control File

LOAD DATA
CHARACTERSET UTF8
INFILE '$DATA_FILE_NAME"STR '~!~'"
INTO TABLE XX_SQLLDR_DEMO_STG
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
RECORD_ID "LTRIM(RTRIM(:RECORD_ID))",
SENDER    "LTRIM(RTRIM(:SENDER))",
RECEIVER  "LTRIM(RTRIM(:RECEIVER))",
TEXT      "LTRIM(RTRIM(:TEXT))"

)

Sample Data File

Need to Add the Data / Fields delimiter with unused string (like this "~!~") at the end of each record in Data File.


1|ABCD@XYZ.COM|XYZ@ABCD.COM|Hi XYZ,

how to load Multiple lines as single record in sqlldr?

Thanks & Regards,
ABCD|~!~
2|XYZ@ABCD.COM|ABCD@XYZ.COM|Hi ABCD,

This is test data file for multiple lines loading into a single field.

Thanks & Regards,
xyz|~!~


It will loads the data as expected...



1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete