SQL loader control file help required
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: SQL loader control file help required

  1. #1
    Join Date
    Apr 2012
    Posts
    1

    SQL loader control file help required

    Hi there,

    I have a text file like below,

    :20:abc
    :10:bbc,
    ccc,
    999
    :30:bba

    I need to load the above data in to table like below,

    10 20 30
    --- --- ---
    bbcccc,999 abc bba

    10, 20, 30 are the columns in the table.

    Please help me to create a control file for this.

    Thanks
    Vinoth

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Logic is not clear to me but assuming is something like building rows taking specific columns from different text file lines I would suggest to define the text file as an "external table" then write a query that does what is needed.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,026
    If you feel adventurous you can use some combination of the following to merge the line back into a delimited format: sed, awk, cut, grep.

    Where I was working before I wrote some SQL to reformat the outout of openLDAP used to store connect strings into a colon delimited format. It worked but it was damn ugly!!!
    this space intentionally left blank

  4. #4
    Join Date
    Jul 2012
    Posts
    2
    Quote Originally Posted by PAVB View Post
    Logic is not clear to me but assuming is something like building rows taking specific columns from different text file lines I would suggest to define the text file as an "external table" then write a query that does what is needed.
    PAVB your right, make it a external table it should come out the best

  5. #5
    Join Date
    Jul 2012
    Posts
    2
    Quote Originally Posted by shanevic7 View Post
    PAVB your right, make it a external table it should come out the best
    Just wanted to thank you again, this suggestion for the external table has made it a lot more simpler for me and saves time.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by shanevic7 View Post
    Just wanted to thank you again, this suggestion for the external table has made it a lot more simpler for me and saves time.
    Glad to help.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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