Simple SQL loader

25 08 2011

Say we have records in this format and need to load into database.

Party_number,scheme_year,Status

918888379,2005,Pass

199966199,2008,Fail

999162249,2005,Pass

999909093,2005, Pass

999991399,2005, Pass

Steps to follow:

  1. Create a csv file with above details.

a. create from notepad . copy the contents and save as “test.csv” – select file type as “All”

b. create from excel – save as csv option

2. Create a table if not existing

CREATE TABLE xx_load_test

(party_number varchar2(15),

scheme_year number,

status varchar2(15)

)

3. Create control file. Open notepad and type these and save as “mycontrol.ctl “

– select file type as “All”

LOAD DATA

INFILE ‘C:\test\test.csv’

append INTO TABLE xx_load_test

FIELDS TERMINATED BY ‘,’

OPTIONALLY ENCLOSED BY ‘”‘

TRAILING NULLCOLS (

PARTY_NUMBER,SCHEME_YEAR,STATUS

)

4. Execute the below command from command prompt.

sqlldr apps<password>@<database> control=C:\test\ mycontrol.ctl

Check the table by quering

SELECT * FROM xx_load_test

Enjoy loadering 🙂 x