Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2009
    Posts
    13

    Unanswered: importing csv file

    Hi guys

    I used the utl_file method to export some values from a table to a csv file.
    I now need to import these files back in.
    What methods are there to do this?

    thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    The other option is to use external tables, which allows oracle to treat a csv file as a database table.

    External Tables | Oracle FAQ

  4. #4
    Join Date
    Aug 2009
    Posts
    13
    Hi

    Is it not possible to use UTL File for this?

  5. #5
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by GuestUser31
    Is it not possible to use UTL File for this?
    It is, but why re-invent the wheel?

  6. #6
    Join Date
    Aug 2009
    Posts
    13
    Because I'm now half way there in creating the UTL file.
    This seems like a good example so Im following this one
    Use UTL_FILE to read from a file and insert into the lecturer table. : UTL_FILESystem PackagesOracle PL / SQL

  7. #7
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Doesn't really make sense to me, unless you need to manipulate the contents of the file before inserting it into the database in such a way that it can't be done through SQL.

    Otherwise this seems like a waste of time to me.

    With UTL_FILE the input file must reside on the server anyway. And in that case external tables are a lot easier and probably more stable than a home-grown solution.

  8. #8
    Join Date
    Aug 2009
    Posts
    13
    unless you need to manipulate the contents of the file before inserting it into the database
    Yep I do, hence why I used UTL.

  9. #9
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Which begs the question, why did you ask for advice in the first place?

  10. #10
    Join Date
    Aug 2009
    Posts
    13
    Because its interesting to look at other ways and I was also interested if you would point me towards UTL or away.

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    OraFAQ Forum: SQL & PL/SQL => Import File

    Please do not confuse me with facts since I have already made up my mind.
    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.

  12. #12
    Join Date
    Aug 2009
    Posts
    13
    I was interested in other ways to import the file other than UTL. UTL was my preferred choice and therefore any other options would be considerations depending on how useful they were.
    Please do not confuse me with facts
    So you dont want the facts rather you want fiction? hmm.....

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What did you learn today different from what you were told yesterday?
    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.

  14. #14
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If there is no structure to the file, UTL_FILE is the way to go.
    if there is structure, but decisions to make during loading, use external files
    if it is a straight load use sql loader.

    slowest - utl_file
    faster - sql_loader
    fastest - external file.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  15. #15
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down Waste of time...

    Quote Originally Posted by beilstwh
    If there is no structure to the file, UTL_FILE is the way to go.
    if there is structure, but decisions to make during loading, use external files
    if it is a straight load use sql loader.

    slowest - utl_file
    faster - sql_loader
    fastest - external file.
    Even if there is no structure (or minimum structure), it's fastest to load as external file where each "line" is one column (or maximum columns depending on structure available) and then manipulate with SQL.

    Maybe the OP believes in wasting time (or he/she is a contractor that charges by the hour)?
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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