Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2012
    Posts
    7

    Unanswered: SQL loader script

    Hi,

    I have a file with follwoing details.

    LRNO|EngineSequence|EngineBuilder|EngineDesigner|E ngineModel|NumberOfCylinders|Bore|Stroke|Stroke
    Type|MaxPower
    5103974|1|Caterpillar|Caterpillar||12||||550
    5103974|2|Caterpillar|Caterpillar||12|||4|550
    5103974|3|MWM|MWM||||||


    but while checking vi editor data as


    LRNO|EngineSequence|EngineBuilder|EngineDesigner|E ngineModel|NumberOfCylinders|Bore|Stroke|Stroke
    Type|MaxPower^M
    5103974|1|Caterpillar|Caterpillar||12||||550^M
    5103974|2|Caterpillar|Caterpillar||12|||4|550^M
    5103974|3|MWM|MWM||||||^M


    and the SQL loader script as

    load data
    infile '../in/tblAuxEngines.txt'
    truncate
    into table dfn_IHS_AuxEngines
    fields terminated by '|' --optionally enclosed by '"'
    trailing nullcols
    (
    LRNO "ltrim(rtrim(:LRNO))",
    EngineSequence "ltrim(rtrim(:EngineSequence))",
    EngineBuilder "ltrim(rtrim(:EngineBuilder))",
    EngineDesigner "ltrim(rtrim(:EngineDesigner))",
    EngineModel "ltrim(rtrim(:EngineModel))",
    NumberOfCylinders "ltrim(rtrim(:NumberOfCylinders))",
    Bore "ltrim(rtrim(:Bore))",
    Stroke "ltrim(rtrim(troke))",
    StrokeType "ltrim(rtrim(trokeType))",
    MaxPower "trim(:MaxPower)",
    SQLLoaddate SYSDATE)



    but i could not able avoid that charcter(^M) in table as well in column "MaxPower"

    say for example two rows are

    LRNO ENGINESEQUENCE ENGINEBUILDER ENGINEDESIGNER ENGINEMODEL NUMBEROFCYLINDERS BORE STROKE STROKETYPE MAXPOWER SQLLOADDATE

    5103974 1 Caterpillar Caterpillar 12 550
    11/1/2012 12:50:49 PM
    5103974 2 Caterpillar Caterpillar 12 4 550
    11/1/2012 12:50:49 PM

    I could not able to past that special char here.

    Could you please tell me is there any way to avoid that special chars ?

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hi,

    ^M = chr(13) = carriage return = part of DOS end of line not used in *nix. Check details in this article: http://en.wikipedia.org/wiki/Newline
    Quote Originally Posted by shyamu View Post
    Could you please tell me is there any way to avoid that special chars ?
    Eliminate those characters from the file e.g. by using dos2unix utility: http://linux.about.com/od/commands/l...l1_dos2uni.htm
    Load the converted file.

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I have not tried this myself but you could see if the stream loader format works with \r\n.

    LOAD DATA INFILE "str '\r\n'" ....
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Quote Originally Posted by it-iss.com View Post
    I have not tried this myself but you could see if the stream loader format works with \r\n.
    LOAD DATA INFILE "str '\r\n'" ....
    This does work, I use it in different sql loader control files:
    Code:
    load 
    infile "/path/to/file" "str '\r\n'"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •