how to Insert/import data in to Oracle database using csv file

if we are using oracle database and are in delima how to insert data in to it. The simplest way is to insert the data using  one to one entry using simple database command, However this may not be practical in a case where we need to insert large amount of data. so inorder to insert bulk amount of data instantly , there are basically two efficient way.

•  use of  oracle sql developer

sql developer is an easy and efficient tool in oracle database management. it faciliates the importing of data in a bulk and many others . It makes the data import in csv or any other format data to import in to tables easy .
here are the steps in importing the data

1: create the table with columns name in oracle database.
    example

2:create the csv format of data to be inserted. note: be sure that the constrants and the data type matches to the csv files and to that of table, otherwise error will be notified.
    example:
     CUST_INCOME_KEY,CUST_INCOME_IDNT,CUST_INCOME_MNTH

        1,4,5000

        2,6,6000

        3,5,7000

        4,7,8000

        ------------

       -------------

3: right click on table in which data is to be inserted. Then click on import data from the option available.
         

4:select the source file from the place where csv file is saved.Data preview will be launched ,verify the data and click next

     



5:choose column will be launched,the columns to be used are selected from moving columns from available to selected column then click next

6:column defination: source files are mapped to the destination table  i.e if we want the column CUST_INCOME_KEY of csv file to be mapped to CUST_INCOME_KEY in the table  then choice can be made here itself
       

7:finally finish the data insertion. if errors occurs do correct the errors and repeat it.

THE NEXT WAY IS

• use of SQL loader

next way of insertion is the use of sql loader. in this method we have the data file in mostly in csv format . Then we create a control file and execute the command to insert the  data in bulk.  

note: this is an example of inserting data  through csv file with  linux using sql loader in a remote host.

steps of importing data

1: create the table in oracle database.

     let us suppose we have a table named CUST_INCOME_DB with columns

     CUST_INCOME_KEY;

      CUST_INCOME_IDNT;

      CUST_INCOME_MNTH;

2: create the csv file  and store in appropriate place

     suppose we have income.csv as

       example

        CUST_INCOME_KEY,CUST_INCOME_IDNT,CUST_INCOME_MNTH

        1,4,5000

        2,6,6000

        3,5,7000

        4,7,8000

        ------------

       -------------

3:create the control file:

 e.g let us suppose that  we create the income.ctl file as 

                Example:

OPTIONS(skip=1) //skip is used to skip the row , skip=1 skips the first 1 row of csv            

                               file

LOAD DATA

INFILE 'income.csv'

 INTO TABLE CUST_INCOME_DM

FIELDS  TERMINATED BY ','

(

CUST_INCOME_KEY,

CUST_INCOME_IDNT,

CUST_INCOME_MNTH

)

4  execute the command as

                 sqlldr  username/psswd@SID control=income.ctl log=income.log          

                  bad=income.bad discard=income.discard

                   example:

                   sqlldr BANKING_TBL01/yomari@BANKINDEV control=income.ctl log=income.log 

                    bad=income.bad discard=income.discard

               

                   note: in local host SID is not needed.