Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2003
    Location
    Mumbai,India.
    Posts
    36

    Unanswered: SQL * LOADER enhancements???-interesting one.

    Hi,

    In SQL loader,
    1)Is it possible to split a data into three and inserting the resulting 3 data into 3 different columns? How?
    Example:
    .csv having AAABBBCCCC
    thin SQL loader sholud split this into three like 'AAA','BBB' and 'CCCC' and insert into three different columns.

    2)Is it possible to change the data based on some condition before inserting it into a table?
    Example:
    .csv having 0,1

    Conditions: if '0' then 'Y'
    if '1' then 'N'
    therefore,the values to be inserted into the table would be Y and N instead of 0 and 1.

    3)Is it possinle to insert a data into 3 different table at a time?
    Example:
    .csv having ABC
    then this value has to be inserted into 3 different tables atonce.

    4)Is it possible to implement all of these 3 possibilities in one shot?

    Thanks for your time and efforts in advance.....

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    2)
    insert into table2
    select (CASE when value = 0 THEN 'N' ELSE 'Y' END) from table1;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Oct 2003
    Location
    Mumbai,India.
    Posts
    36
    ThanX..
    What about other thigs???
    pls...

  4. #4
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    1) you can use substr in control file as in eg below:
    load data
    into table ss
    append
    fields terminated by ','
    (col1 "substr(:col1,1,1)",
    col2,
    col3)


    3)You can mention names of 2 tables as in eg below:
    load data
    into table ss
    append
    fields terminated by ','
    (col1 "substr(:col1,1,1)",
    col2,
    col3)

    into table ss2
    append
    fields terminated by ','
    (col1 "substr(:col1,1,1)",
    col2,
    col3)

    4) yes, you can do all 3 in single control file.

    Originally posted by india_sha
    ThanX..
    What about other thigs???
    pls...
    Oracle can do wonders !

  5. #5
    Join Date
    Oct 2003
    Location
    Mumbai,India.
    Posts
    36
    A lot of thanx...
    i'll try with these..
    i hope these things will solve my problem..

  6. #6
    Join Date
    Oct 2003
    Location
    Mumbai,India.
    Posts
    36
    hey..i got a problem in this..

    my requirement is,
    single data has to be picked up from .csv then i've to split that data into three b'fore loading into three different columns...
    like,
    load data
    into table ss
    append
    fields terminated by ','
    (col1 "substr(:col1,1,3)",
    col2 "substr(:col2,4,7)",
    col3 "substr(:col3,8,10)"
    )

    here for all the col1,col2,col3 columns data should be supplied from the same single data at .csv.

    How can i achive this?

  7. #7
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Use:

    load data
    into table ss
    append
    fields terminated by ','
    TRAILING NULLCOLS
    (col1 "substr(:col1,1,1)",
    col2 "substr(:col1,2,2)" ,
    col3 "substr(:col1,3,3)" )


    The value read as first column is stored in :col1 which then you substring to insert into 3 columns...

    Remember, if you have an additional column in csv file which is not in your table, use filler.
    Oracle can do wonders !

  8. #8
    Join Date
    Oct 2003
    Location
    Mumbai,India.
    Posts
    36
    Hey..
    I've tried that it's working fine but the next column didn't get the data...
    My .CTL file looks like this,

    LOAD DATA

    APPEND
    INTO TABLE "EMP_DET"


    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"' AND '"'
    TRAILING NULLCOLS

    (ENO
    ,
    ENAME
    ,
    PHPRE "to_char(substr(hpre,1,4))"
    ,
    PHNO "to_char(substr(hpre,5,8))"
    ,
    EXTN "to_char(substr(hpre,13,4))"
    ,
    JOIN_DATE DATE 'MM-DD-YY'
    "SUBSTR(:JOIN_DATE,1,15)"

    )


    In this ,i'm not getting data for JOIN_DATA but the data is there in .CSV file.What's the reason for this/ How can i solve it?

  9. #9
    Join Date
    Oct 2003
    Location
    Mumbai,India.
    Posts
    36
    hey what's this who put this icons?/??
    O.K no probs...pls answer me...

  10. #10
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    can you upload the log file generated?

    Originally posted by india_sha
    hey what's this who put this icons?/??
    O.K no probs...pls answer me...
    Oracle can do wonders !

  11. #11
    Join Date
    Oct 2003
    Location
    Mumbai,India.
    Posts
    36
    SORRY FOR LATE REPLY..
    HERE IS THE LOGFILE ATTACHMENT...

    MY CTL FILE IS,

    LOAD DATA
    INFILE 'E:\nyr\sqlload\empdet.txt'
    BADFILE 'E:\nyr\sqlload\empdet.bad'
    DISCARDFILE 'E:\nyr\sqlload\empdet.dsc'


    APPEND
    INTO TABLE "EMP_DET"


    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"' AND '"'
    TRAILING NULLCOLS

    (ENO
    ,
    ENAME
    ,
    PHPRE "to_char(substr(hpre,1,4))"
    ,
    PHNO "to_char(substr(hpre,5,8))"
    ,
    EXTN "to_char(substr(hpre,13,4))"
    ,
    JOIN_DATE DATE 'MM-DD-YY'
    "SUBSTR(:JOIN_DATE,1,15)"

    )



    IN THIS I'M NOT GETTING ANY DATA UNDER JOIN_DATE....
    Attached Files Attached Files

  12. #12
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Your date format is MM-DD-YY where as substring is of 15 chars. Possibility of mismath in date formats. But that would throw an error, which is not the case (as is visible from log file)

    Two more info pls:
    - data file
    - table desc
    Oracle can do wonders !

  13. #13
    Join Date
    Oct 2003
    Location
    Mumbai,India.
    Posts
    36
    table script is,
    CREATE TABLE EMP_DET (
    ENO NUMBER (3),
    ENAME VARCHAR2 (25),
    PHPRE VARCHAR2 (25),
    PHNO VARCHAR2 (25),
    EXTN VARCHAR2 (25),
    JOIN_DATE VARCHAR2 (15))
    Attached Files Attached Files

  14. #14
    Join Date
    Oct 2003
    Location
    Mumbai,India.
    Posts
    36
    Hey Have u got any idea???

  15. #15
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    I used your table structure, your CTL file and your data file and it inserted both the records with JOIN_DATE in the table ... result attached.
    Attached Files Attached Files
    Oracle can do wonders !

Posting Permissions

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