SQL-Loader:Loading variable length data

Loading Variable Length Data

The flat file is of comma seperated file(i.e csv). In real time we may have flat files with the extensions .csv, .dat, .txt etc

 

FLAT FILE:

1,"Nani","CSE","Final","A+"

2,"Indra Kanth","ECE","Final","B"

3,"Kalyan","CSE","Third","A"

4,"Praveen","IT","Third","B"

5,"Badra","ECE","Final","B+"

6,"Kali","EEE","Second","C"

7,"Giri","E&I","First","D"

8,"Sravan","MCA","Third","A"

9,"Pradeer","MCA","Second","D"

10,"Joe","CSE","Third","D+"

CONTROL  FILE:

load data

infile '/bin/flat_file_var.txt'   --Give your flatfile location

insert into table stu_data    --stu_data is the staging table

fields terminated by ','

optionally enclosed by '"'

trailing nullcols

(

sno,

sname,

branch,

year,

grade

)

Staging Table:

create table stu_data(sno number,

                                sname varchar2(20),

                                branch varchar2(10),

                                year varchar2(10),

                                grade varchar2(3)

                                )