SQL-LOADER:Skip option

Skip

In order to skip the rows at the beginning of the flat file.We use skip option in the control file as shown below.

If I want to skip 5 rows i will give skip=5 in my control file then the first five rows of the flat file are skiped and remaining data will be loaded in the staging table.

 

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:

Options (skip=n)    --First n rows will be skiped

load data

infile 'bin/flat_file_var.txt' --Give your flat file location

insert into table stu_data

fields terminated by ','

optionally enclosed by '"'

(

sno,

sname,

branch,

year,

grade

)

SQL-LOADER:Loading combined Physical Records

Loading Combined Physical Records

There may be chances that we get the data in which each row starts with a special character like '@','#','$' etc.Then we will have to use continueif to load the data from such kind of flat file into staging table.

There may be scenerios where we need to load two or three rows of the flat-file as a single row in the staging table then we will use Concatenate option to acheive that requirement.

USING CONTINUEIF:

load data

infile *

replace

continueif this (1)= '@'

into table vnk_emp

fields terminated by ','

optionally enclosed by '"'

trailing nullcols

(

c1,

c2,

c3,

c4 char terminated by whitespace

)

BEGINDATA

@1,2,

    3,4

@5,6,

    7,8

USING CONCATENATE:

load data

infile *

replace

concatenate 2

into table vnk_emp

fields terminated by ','

optionally enclosed by '"'

trailing nullcols

(

c1,

c2,

c3,

c4 

)

BEGINDATA

1,2,

3,4

5,6,

7,8

Staging table:

Create table vnk_emp(c1 number,

                                   c2 number,

                                   c3 number,

                                   c4 number

                                  )

SQL-LOADER:Loading Data from Multiple Flat-files

Loading Data from Multiple Flat-files

FLAT FILE1:

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+"

FLAT FILE2:

11,"Dev","CSE","Final","A+"

12,"sasi Kanth","ECE","Final","B"

13,"johnny","CSE","Third","A"

14,"isaac","IT","Third","B"

15,"Babu","ECE","Final","B+"

16,"Kiran","EEE","Second","C"

17,"Gangadhar","E&I","First","D"

18,"Suresh","MCA","Third","A"

19,"Pavan","MCA","Second","D"

20,"Joseph","CSE","Third","D+"

CONTROL  FILE:

load data

infile '/bin/flat_file1.txt'

infile '/bin/flat_file2.txt'

insert into table vnk_mul_data

fields terminated by ','

optionally enclosed by '"'

trailing nullcols

(

sno,

sname,

branch,

year,

grade

)

Here we have two flat-files named  flat_file1,flat_file2. The Control file stores the data from these two data files into the staging table vnk_mul_data

Staging table:

create table vnk_mul_data(sno number,

                                         sname varchar2(20),

                                         branch varchar2(10),

                                         year varchar2(10),

                                         grade varchar2(3)

                                         )

SQL-Loader:Loading Data into Multiple Tables

Loading Data into Multiple Tables

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_file11.txt' --Give your Flat file location

insert into table vnk_t1

fields terminated by ','

optionally enclosed by '"'

trailing nullcols

(

sno,

sname,

branch

)

into table vnk_t2

fields terminated by ','

optionally enclosed by '"'

trailing nullcols

(

year,

grade

)

Here we have a flat file named flat_file11 which is comma seperated file.The Control file loads the data in the flat file into two staging tables vnk_t1,vnk_t2.

SQL-Loader:Loading Fixed Length Data

Loading Fixed Length Data

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  Siri Giri   E&I First  D

8  Sravan      MCA Third  A

9  Pradeep     MCA Second D

10 Joe-Rohith  CSE Third  D+

CONTROL  FILE:

load data

infile '/bin/flat_file_fixed.txt'

append into table student_data

(

Sno  position(1:1)  char(2),

sname  position(4:14)  char(11),

branch  position(16:18)  char(3),

year  position(20:25)  char(6),

grade  position(27:28)  char(2)

)

Staging Table:

create table stu_data(sno number,

                                sname varchar2(20),

                                branch varchar2(10),

                                year varchar2(10),

                                grade varchar2(3)

                                )

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)

                                )