7 Replies Latest reply: Feb 6, 2013 3:25 AM by BluShadow RSS

    How to read a CSV file and Insert data into an Oracle Table

    645675
      Hi all ,

      I have a simple requirement .I have a CSV file and need to read values from the file and insert it into an Oracle Table .Can someone explain me the Code to do so .I know I have to use UTL _FILE but if someone could give me the code it would be great .

      Any other method - Please explain in detail .

      Thanks in advance
        • 1. Re: How to read a CSV file and Insert data into an Oracle Table
          APC
          .I know I have to use UTL _FILE but if someone could give me the code it would be great .
          Horsefeathers. You can use SQL Loader or External Tables, both of which are covered in the documentation.

          Cheers, APC

          blog: http://radiofreetooting.blogspot.com
          • 2. Re: How to read a CSV file and Insert data into an Oracle Table
            645675
            Hey APC,

            Thanks da.BTW what's horsefeathers all about ?

            Neways I have the solution with External Tables .I specifically wanted the code for opening ,Reading ,Inserting and then Closing the CSV file (Delimited by , and each line ending in newline) using UTL_FILE.

            I have a deadlien to meet and don't have time to experiment with codes .I will propose the externale table solution but I needed a backup solution too .

            BTW ...Will visit your blog for sure .Am a writer myself caught up in the web of coding !

            Thanks for your reply !

            Cheers
            Richa
            • 3. Re: How to read a CSV file and Insert data into an Oracle Table
              BluShadow
              Obviously searching the forum for one of the most commonly asked questions is too difficult for you so I'll provide a search URL for you to click on...

              http://forums.oracle.com/forums/search.jspa?threadID=&q=Write+CSV+UTL_FILE&objID=f75&dateRange=all&userID=&numResults=30
              • 4. Re: How to read a CSV file and Insert data into an Oracle Table
                APC
                I have a deadlien to meet and don't have time to experiment with codes
                An external table will take less time to code than the equivalent UTL_FILE solution. It is unlikely anybody here is going to give you a PL/SQL program you can use because your requirements are bespoke.
                .BTW what's horsefeathers all about ?
                I use it to mean "nonsense" but it's probably a euphemism for something stronger.

                Cheers, APC

                blog: http://radiofreetooting.blogspot.com
                • 5. Re: How to read a CSV file and Insert data into an Oracle Table
                  639619
                  I was just going through the posts here. Did you solve your problem?
                  • 6. Re: How to read a CSV file and Insert data into an Oracle Table
                    Sven W.
                    To begin with, Apc didn't have any problem.
                    Furthermore when reading carefully it is sometimes helpful to click on the links that are provided.
                    • 7. Re: How to read a CSV file and Insert data into an Oracle Table
                      BluShadow
                      jeneesh wrote:
                      And, please don't "hijack" 5 year old thread..Better start a new one..
                      I've just split it off to a thread of it's own. ;)

                      @OP,
                      I have a Clob file as a in parameter in my PROC. . File is comma separated.need procedure that would parse this CLOB variable and populate in oracle table .
                      You don't have a "clob file" as there's no such thing. CLOB is a datatype for storing large character based objects. A file is something on the operating system's filesystem.

                      So, why have you stored comma seperated data in a CLOB?
                      Where did this data come from? If it came from a file, why didn't you use SQL*Loader or, even better, External Tables to read and parse the data into structured format when populating the database with it?

                      If you really do have to parse a CLOB of data to pull out the comma seperated values, then you're going to have to write something yourself to do that, reading "lines" by looking for the newline character(s), and then breaking up the "lines" into the component data by looking for commas within it, using normal string functions such as INSTR and SUBSTR or, if necessary, REGEXP_INSTR and REGEXP_SUBSTR. If you have string data that contains commas but uses double quotes around the string, then you'll also have the added complexity of ignoring commas within such string data.
                      Like I say... it's much easier with SQL*Loader of External Tables as these are designed to parse such CSV type data.