fetch

Oracle SQL Loader - How to load data from file(.csv, .dat, .txt) into table - Tutorial - 3



Sharing buttons:

hello everyone welcome to a channel on

oracle SQL and pl/sql are you looking

for a way to load data from your flat

files into tables then welcome this is

the video for you

my name is Ken Marcou and I'll be

demonstrating the Oracle SQL load a

utility to load data from flat files

into Oracle database tables but before

that I would recommend you to watch our

previous two videos on oracle SQL loader

if you have not already done that it

will give you a base on the topic also I

would recommend you to subscribe to our

Channel and press the bell icon so that

you never miss a notification from us so

without wasting any more time let's hop

on what we want to do we want to load

data from files into tables what kind of

tables are this these are any regular

oracle database table what kinds of

files are this this files are in a

specific format they are in such a way

that the SQL loader can't read them so

basically there are two types of files

one is positional and other is files

that are delimited by a delimiter such

as CSV that is comma separated value

files stored at file store text files

whatever may be the extension the actual

thing licensed in the format in which

the data is ok so let's start with that

demo first of all we need a table

so I'll hop onto my SQL Developer where

we'll create a table to store some kind

of data from a file so let's say we'll

take employee ID employee name and

department ID from file and we'll load

it into the CMPs table so I'll just

create this table okay so as you can see

the table is created and it's empty I

have given number data type to employee

ID and department ID and work a data

type to employee name if you are doing a

blind loads from files into tables it is

recommended that you use where care for

every field you never know what can come

in the files you know mostly files are

supported fire by multiple vendors and

you would be loading it in

database so it's good to have all the

columns as well care and now one more

thing there might be cases where you

want to load data from one data base

into the other now one way you can do is

is through database links but say you

are not allowed to make take a database

links in that case you can just export

the data in a CSV format using any IDE

such as a developer or pl/sql developer

or toad if it's an Oracle database or a

studio manager SQL studio manager if

it's a sequence or a database use the

SQL loader utility and load that data

into Oracle database this is also one of

the ways through which you can transfer

data from one database to another so now

let's look at the other part we filed

okay so I have created a dot that file

over here now the extension does not

really matter you can call it a CSV file

I just copy the same give a CSV

extension and we can see in this file I

have three columns basically ID name and

Department ID and I have about six

records okay so the first column ID for

the first record has a value hundred

then you can see a comma this comma is

basically the delimiter which will tell

the SQL loader that this is the end of

first columns data and now starts from

next character the data for the second

column that is Steve and then you can

see there's a comma again which will

tell Oracle that this is the end of the

second columns data and the next

character starts with the third column

so that is 10 over here Department ID so

the SQL loader will read it this way it

will start from the beginning look for

the delimiter as soon as it gets a

delimiter it will understand that the

next things are for the next column you

can delete that optionally my data is

enclosed in two coats in that case if

there is data something like this

where you have a comma in the in the

value itself and you say my data ups are

optionally enclosed by quotes it will

understand that everything between the

quotes is for the same column this

example is not so complex so you don't

really need a quote you don't really

need quotes here as we'll go into the

next stages of our tutorials I will keep

on increasing the complexity of the

examples and we'll see how to tackle all

these specific scenarios okay so this is

the basic data file you can also call it

a CSV file I'll just paste the data here

and say we'd say data dot CSV let me

close it here and if we see in in the in

the file file structure you can see that

I just created this file by default

CSV is open with Excel you can just do a

left click and say open with notepad

plus plus you can see the file here so

this is the file that I won't load into

the EMPs table so what do I need for

that I need a control file so I'll start

writing a control file now as we have

seen in the previous video the control

file is the heart of the SQL load a

utility it is the only thing that you

need to write so it starts with load

data it states like this is the starting

of the session now now the first thing

that I mentioned here is in file meaning

what file I want to load I want to load

this EMP door dad file okay

so I can just give the file name over

here and if it's a dot that format it is

also not required to you before dot that

extension if it is that format but let

me give it and you know what it is it is

good that you give the full path of the

file rather than just the file name and

also give the whole path inside quotes

because you might have spaces in your

path okay so give it inside a single

quote

now next thing what I want to do I can

do a replace I can do append or a

truncate in the first run append is

always successful because the table

would be empty but if say suppose the

table has data from the first run and if

I execute it for the second time it

would fail because I'm because I penned

expects the table to be empty okay then

I can use replace or truncate now

replace would delete the data and put

the new data inside the table truncate

would truncate the data from the source

table end and then put this data into it

but we know that truncate is much more

better than delete and if you don't then

watch our video on it I'll put the link

in the description so I'll use truncate

or here I want to do what I want to load

it into a table

which table EMPs table and now if it's a

delimiter format the data is really in

in your data file then the syntax would

come like feels terminated by and what

are the fields terminated why in this

case it's a comma it's not really

required to have a comma here you can

also have something like a pipe

something like this this is also very

much valid but I just kept it comma for

the time being so feels delimited by

comma and now we start with the column

names of the table so what are the

column names in the table let's have a

look

it's EMP ID EMP name and Department ID I

just put them here EMP ID EMP name and

department-id so my first column in my

file I am saying it's an ID the ID

column in the file it's nothing but

employee ID here name is Department

employee name here and department ID is

department ID here okay so this is it I

just created the control file now if you

if you are using any other positional

format kind of a data then this would

change we'll look at that example also

not in this video but definitely we'll

look at it in future so I will just save

this and I'll give a name to this as say

load employee data dot CTL CTL is the

extension for the control file so dot

CTL I just saved it okay so now I have

the control file I have the data file

and I have the table so what are we

waiting for is just you call the SQL

loader utility and you know execute it

so I'll go to the location here and you

see MD ok giving CMD this way would open

the CMD with a default path of this why

I did I will tell you in in a while so

now the execution of the execution of

SQL loaded utility is done this way SQL

LDR the states you want you are doing an

SQL loader then you give user ID user ID

is nothing but username password you

might come across scenarios where you

your organization would say that you

cannot just show the password here so

what you will do you will not use SQL

loader utility no you will create

another user give it only the required

privileges own only the required tables

and then use that user for the SQL

loaded utility okay so those are the

first two things that you give in SQL

loader command then you specify where

your control file is so you give control

equal to you can directly give the

control file here if the control file is

in the very location from which you are

calling but I do not do that I just give

the exact path it's very easy I'll just

do a left click and give full path and

select full path I'll just paste it here

so simple done enter ok it says syntax

error at line 5 say suppose you don't go

that you don't get the you can don't get

this error over here then how would you

come to know you can just go to this

location and you can see that the log

file is generated it says syntax error I

did a type of basically I'll just open

as this is embarrassing if I and the yes

feels terminated by I'll just execute it

again so you can see that it says commit

point reached logical record three to

six seven something like that okay

so let's see the data that has got

loaded so you can see that the six

records got loaded over here 100 Steve

10 101 Billy 20 so we just loaded the

data file into our database so this is

the very first example that we have used

the data file the table and the control

file and loaded the data from file into

the table wasn't it very simple of

course it was now let's see the log that

has generated over here now what I

basically wanted to show you guys is

this log file look at the sophistication

of the log file it says where is the

control file where is the data file

where is the bad file where is the

discard file it shows you I had three

columns I took first I took the length

or star means any variable length I use

comma as the delimiter it it shows

everything it did this is the most

interesting part see what it says here

it read six rows successfully it loaded

six rows successfully one row was not

able to load due to some data error okay

so it started at this time it ended at

this time it has all the details it says

it was not able to load one file and it

has written that record into not bad

file okay so that record which didn't

load is the header the header I kept

here so what I can do is for the time

being I just remove the header from here

and load the file again this time the

table actually has some data right but

as we are using a truncate all this data

will get

gated and the data in the file will get

loaded let me just make a small change

here let me add one more like 106 say

kitchen department 20 I will save this

file I just load it again so as you can

see presently we have six records in the

table I just load the file again and

this sexual being deleted and the new

seven will be loaded I am just executing

the file again you can see it has done

its job I can log on to my SQL developer

and see that the seven records are now

loaded into this the first time the bad

file was created because of the header

say suppose I have the header in the

file and I want the data to get loaded

what will I do say I have ID name and

department here but I want to load it

every time and remove the header you can

say skip is equal to 1 in the command

where you are calling just executed and

will see the data got truncated and

reloaded so basically don't see a

difference in the data over here but the

bad file would not get generated this is

the bad file of the older run ok not the

new run this is the new log

new log file

we'll just see the log file

you can see it successfully loaded seven

records zero liquors were loaded due to

data errors means this time it's kept

that row now you can write the same

thing over here also you can write

options and skip is equal to one there

are various other options available also

we look at those option options in our

next video what other kind of options we

have and say suppose you have an extra

column in your table say suppose you

have insert date okay and just drop this

and recreate it say suppose you have

insert date but your data file just has

three columns whereas your table has

four columns of course the insert date

is null over there so you can still run

and insert date would be null for all

but say suppose you want the insert date

to be filled every time the records are

inserted how to do that if we look into

that case in the next video so guys

please to like share and subscribe to

this video and please do tune in to

watch our next example on SQL loader

thank you the Secession signing off bye

bye