Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2006
    Location
    Mumbai, India
    Posts
    26

    Exclamation Unanswered: excel data load by sql loader

    I am using following control file, data file under unix environment in oracle 9.2.0.6.

    test.ctl
    -------

    load data
    infile 'test.csv'
    append into table temp_table
    fields terminated by ","
    trailing nullcols
    (column1 integer external,
    column2 char)

    test.csv
    --------
    1,Catch
    2,Catch
    3,Catch

    Table definition:
    ---------------
    create table temp_table(column1 number(10), column2 varchar2(10));

    Following is the sql loader command I fired,
    sqlldr userid=scott/tiger control=test.ctl log=test.log

    I have three queries:
    1. Why log file file is showing all datatypes as CHARACTER?
    2. I have manually removed junk characters from the end of lines from .csv file. This file I made by saving it as .csv file in MS EXCEL.
    Howcan I avoid generating junk characters in .csv file why saving it?
    Or how I can I remove it for big .csv file?
    3. Above mentioned sql loading procedure is correct? or if some modifications are required then please give the suggestions.
    Because I am going to use it in my company for very huge data.
    *********************************************
    God Bless You.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As you've specified the 'column1' datatype as INTEGER EXTERNAL, you've told SQL*Loader to interpret it as a character that represents a number; consequentially, log file shows it as a character. To change it, specify its datatype as INTEGER (omit EXTERNAL); it specifies binary data.

    What do you call "junk characters"?

    Control file, written as it is, will do the job. You might, however, specify BAD and DISCARD files. Also, consider changing mode from APPEND to REPLACE if necessary.

  3. #3
    Join Date
    Aug 2006
    Location
    Mumbai, India
    Posts
    26
    Quote Originally Posted by Littlefoot
    As you've specified the 'column1' datatype as INTEGER EXTERNAL, you've told SQL*Loader to interpret it as a character that represents a number; consequentially, log file shows it as a character. To change it, specify its datatype as INTEGER (omit EXTERNAL); it specifies binary data.

    What do you call "junk characters"?

    Control file, written as it is, will do the job. You might, however, specify BAD and DISCARD files. Also, consider changing mode from APPEND to REPLACE if necessary.
    Every line in .csv file ends with ^M character. Which I need to remove, otherwise sql loader gives following error.

    Record 1: Rejected - Error on table TEMP1, column COLUMN3.
    ORA-01722: invalid number

    Here in above example COLUMN3 is a number datatype in oracle database table.
    *********************************************
    God Bless You.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Would it help if you trim it off? Such as
    Code:
    ...
    column3 integer external "rtrim(:column3, '^M')"

  5. #5
    Join Date
    Aug 2006
    Location
    Mumbai, India
    Posts
    26
    Quote Originally Posted by Littlefoot
    Would it help if you trim it off? Such as
    Code:
    ...
    column3 integer external "rtrim(:column3, '^M')"
    I didn't get you?
    But why it is coming at the end of each line of .CSV file.
    *********************************************
    God Bless You.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Why? I don't know; I'm not the one who is creating that CSV file.

    Do you have any influence on this process? How is it done? Could you change the way it is created? For example, MS Excel offers several CSV files to be created (based on the original file) - Macintosh CSV file, MS-DOS CSV file, ... So, could you change it from one option to another and see what happens?

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >But why it is coming at the end of each line of .CSV file.
    Let me guess.
    You've stated that you are trying to load this on a *nix system.
    I suspect that the actual CSV file is coming from Windoze system.
    If so, problem involves how the file is being transfered to the *nix system.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Like anacdent said. Non Binary Files on windows machine have a Linefeed and Carriage return at the end of each line to indicate end of record. Unix only uses LineFeed to indicate end of record. Depending on how the file was transfered to the unix system, there are a number of ways to strip off the carriage return, you just have to let us know how you transferred it to the unix machine.
    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
  •