6 Replies Latest reply: Feb 6, 2013 3:28 AM by jeneesh RSS

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

    989423
      Hi All,

      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 .

      Please let me some suggestions on this.

      Thanks,
      Chandra R
        • 1. Re: How to read a CSV file and Insert data into an Oracle Table
          jeneesh
          CLOB File as a parmeter?

          Not clear..

          To read csv files you can use External Tables, if the file can be moved to the DB server.

          If the file is in a client system, you could try SQL*Loader
          • 2. Re: How to read a CSV file and Insert data into an Oracle Table
            989423
            I’m going to do HTTP request to Ogone and get text file in response I have this file in variable as CLOB. File is comma separated. I need procedure that would parse this CLOB variable and populate in oracle table.

            Edited by: 986420 on Feb 6, 2013 1:19 AM
            • 3. Re: How to read a CSV file and Insert data into an Oracle Table
              AlbertoFaenza
              Hi,

              if you want to know how to import csv file in Oracle, this is quite a common question and reported in the FAQ: SQL and PL/SQL FAQ

              Read that one and if something is still not clear try to post details about what you are trying to do.

              Regards.
              Al
              • 4. 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.
                • 5. Re: How to read a CSV file and Insert data into an Oracle Table
                  BluShadow
                  986420 wrote:
                  I’m going to do HTTP request to Ogone and get text file in response I have this file in variable as CLOB. File is comma separated. I need procedure that would parse this CLOB variable and populate in oracle table.
                  If it's not a multi-user functionality that's required, simply save the CLOB to a known file on the database server, and then use an External Table definition to read it.
                  If it's required for multi-user functionality, then it becomes a little more complicated.
                  • 6. Re: How to read a CSV file and Insert data into an Oracle Table
                    jeneesh
                    BluShadow wrote:
                    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. ;)
                    Yea got it..

                    Got confused initially.. :)