Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2003
    Posts
    81

    Unanswered: SQLLDR and comma

    I have a set of data including decimal-separeted data like

    A,B,123.45,C

    123.45 is separated by . instead of , (which is my decimal-separator in Oracle).

    How will I get SQLLDR to read the data correct? I have a large amount of data, so switching . to , in the indata is out of the question.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm not sure whether you can have two different separators in an input file ... I'm unable to help you, but would like to know why and who decided to separate A, B and 123 with a comma, 123 and 45 with a period, and 45 and C with a comma again?

    Can you get a "properly" created file, so that all values are separated with the same character (or, perhaps, having a fixed field size)?

  3. #3
    Join Date
    Jun 2003
    Posts
    81
    Maybe if I write it in another way, it is more easy to understand.

    I have a table like:

    COL1 varchar2
    COL2 varchar2
    COL3 number(7,3)
    COL4 varchar2

    and an amount of data (in a txt file) like:

    'A' ; 'B' ; 123.45 ; 'C'
    'A' ; 'D' ; 234.44 ; 'E'

    but when I use SQLLDR it expects that my data is decimal separated with a comma, like:


    'A' ; 'B' ; 123,45 ; 'C'
    'A' ; 'D' ; 234,44 ; 'E'

    And since I have very huge input-data files I don't want to change these.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, that's much more clear.

    How about applying the REPLACE function to the numeric field which would replace . with a ,?

  5. #5
    Join Date
    Jun 2003
    Posts
    81
    I don't know what you mean. My table and my .ctl file are build like:

    CREATE TABLE TMP
    (
    COL1 varchar2(10),
    COL2 varchar2(10),
    COL3 number(7,3)
    COL4 varchar2(20)
    )

    and

    load data
    infile 'C:\temp\infile.csv'
    into table OSAK.TMP
    FIELDS TERMINATED BY ';'
    (
    COL1 ,
    COL2 ,
    COL3 ,
    COl4
    )
    begindata
    'ABC,'DEF',417.122,'EFG'

  6. #6
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    load data
    infile 'C:\temp\infile.csv'
    into table OSAK.TMP
    FIELDS TERMINATED BY ';'
    (
    COL1 ,
    COL2 ,
    COL3 replace(:col3,',','.') ,
    COl4
    )
    I think this is the correct syntax.



    (Replace(char, search_string, replacement_string)

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Use the following. If you have an infile, you shouldn't have a begindata. I also assume that OSAK is the correct schema name that contains the TMP table and that you have insert rights.


    load data
    infile 'C:\temp\infile.csv'
    into table OSAK.TMP
    FIELDS TERMINATED BY ';'
    (
    COL1 ,
    COL2 ,
    COL3 ,
    COl4
    )
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Jun 2003
    Posts
    81
    I have dropped the begindata section - thank you. The OSAK.TMP is the correct name (and I do get data in, if I drop the number(X,X) fields and data.

    But still I don't get any data in, when having the number(X,X) fields and data. How should I use the REPLACE function?

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    It should know that it is a number from the table description, but you can force it to format correctly by using column definations. Also tell sqlloader that your data might be enclosed by single quotes. The enclosed clause is ended with 4 single quotes.

    load data
    infile 'C:\temp\infile.csv'
    into table OSAK.TMP
    fields terminated by ’;’ optionally enclosed by ''''
    (
    COL1 varchar2(10),
    COL2 varchar2(10),
    COL3 number(7,3)
    COL4 varchar2(20)
    )
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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