DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: data loading

  1. #1
    Join Date
    Nov 2000
    Posts
    51

    Angry data loading

    Hi,

    I tried to load raw data (text format) to my database. One of the column contains telephone number like 416-999-9999 on the text file, the format the user want is 4169999999. How can I get rid of hyphens. I am using sqlldr to load the data.

    Thanks for your help

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

  3. #3
    Join Date
    Nov 2000
    Posts
    51
    Thank you for your help. I took a quick look at the document you sent but could not find a proper command for this. The replace is to delete the data before loading. This is the first time I tried to use sqlldr and and here is my control file:
    ...
    LOAD DATA
    INFILE '/home/oracle/LOAD/T28548518.txt'
    insert
    INTO TABLE pager_data
    fields terminated by ","
    (
    ...
    charge decimal external,
    phone_num char,
    capnum char,
    c7 filler,
    c8 filler,
    ...
    )

    and my data file contains:
    ...,2.00,416-719-2729,A01-E0414589,...

    from the data file, you can see the phone number. What I try to is load these three columns into oracle database, the phone number should be just numbers without hyphens, and if the capnum is the same in each row, add the charge together. Do you think sqlldr have the functionality or I have to manipulate the data from database.

    Thanks

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    For your original problem, try:

    phone_num char "REPLACE(:phone_num,'-')",


    and if the capnum is the same in each row, add the charge together
    . . . and what if the phone_number etc are different?
    I've never seen an example of loading totals like that - why don't you do some research and let us know?

  5. #5
    Join Date
    Nov 2000
    Posts
    51
    Thanks a lot. The replace function works perfect. I will ask my user about the total charges and get back to you. Thanks for your help!

  6. #6
    Join Date
    Nov 2000
    Posts
    51
    I just found out I need to add up the total charge if the phonenum is the same. Is there anyway I can do this in the sqlldr?

    Thanks

  7. #7
    Join Date
    Jan 2006
    Posts
    6
    Quote Originally Posted by lwangf
    Thank you for your help. I took a quick look at the document you sent but could not find a proper command for this. The replace is to delete the data before loading. This is the first time I tried to use sqlldr and and here is my control file:
    ...
    LOAD DATA
    INFILE '/home/oracle/LOAD/T28548518.txt'
    insert
    INTO TABLE pager_data
    fields terminated by ","
    (
    ...
    charge decimal external,
    phone_num char,
    capnum char,
    c7 filler,
    c8 filler,
    ...
    )

    and my data file contains:
    ...,2.00,416-719-2729,A01-E0414589,...

    from the data file, you can see the phone number. What I try to is load these three columns into oracle database, the phone number should be just numbers without hyphens, and if the capnum is the same in each row, add the charge together. Do you think sqlldr have the functionality or I have to manipulate the data from database.

    Thanks
    The first post on this thread is for a text file with commas. What if you have no commas to separate rows or fields? The fields are separated at specific character lengths...?

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by lwangf
    I just found out I need to add up the total charge if the phonenum is the same. Is there anyway I can do this in the sqlldr?

    Thanks
    What do you need done with the total charge? It seems unlikely that you'd want it posted to every line. One option for more sophisticated maipulation of the loaded data is to define the file as an external table. You can then use SQL statements to read it.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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


Click Here to Expand Forum to Full Width