The previous example loaded a CSV data file. In this example we'll look at very simple fixed-field data file, and how to use SQL*Loader to transfer that data into an Oracle table.
With a fixed-field data file, there are no comma separators or enclosing double quote marks. The fields are always found in the same position on each line. The white space between values is composed of spaces -- actual ASCII text characters that look empty. For example, in the data show below, the names are always found in the position 1 to 10 (or we could also include the next 2 spaces for possible larger names and say that the first field extends from 1 to 12).
The Fixed-Field data file (file name = "Data.txt")
When using this type of date file, we'll need to carefully determine the starting and ending position for each column of data. Shown below is our data with a raster of positions from "01" to "46".
Examining the data by position.
By careful examination we could conclude that the name field spans positions 1-12, the balance values span positions 13-19, and the date spans positions 21-43.
So, as in example #1, lets create a folder somewhere on our PC and put this "Data.txt" in that folder (the data in the 1st box shown above).
Make sure you have an Oracle table with the proper fields.
And, here's a quick description of what the commands in the control file are doing:
Create the batch file in the same directory as the data file and the control file.
Double-click on the batch file and the SQL*Load should complete very quickly. Query the data and it should all be there.
The Fixed-Field Data File
The Fixed-Field data file (file name = "Data.txt")
NAME BALANCE START_DT
Jones, Joe 14 Jan-12-2012 09:25:37 AM
Loyd, Lizy 187.26 Aug-03-2004 03:13:00 PM
Smith, Sam 298.5 Mar-27-1997 11:58:04 AM
Doyle, Deb 5.95 Nov-30-2010 08:42:21 PM
When using this type of date file, we'll need to carefully determine the starting and ending position for each column of data. Shown below is our data with a raster of positions from "01" to "46".
Examining the data by position.
0000000001111111111222222222233333333334444444
1234567890123456789012345678901234567890123456
NAME BALANCE START_DT
Jones, Joe 14 Jan-12-2012 09:25:37 AM
Loyd, Lizy 187.26 Aug-03-2004 03:13:00 PM
Smith, Sam 298.5 Mar-27-1997 11:58:04 AM
Doyle, Deb 5.95 Nov-30-2010 08:42:21 PM
By careful examination we could conclude that the name field spans positions 1-12, the balance values span positions 13-19, and the date spans positions 21-43.
So, as in example #1, lets create a folder somewhere on our PC and put this "Data.txt" in that folder (the data in the 1st box shown above).
The Oracle Table
create table scott.sql_loader_demo_simple
( customer_full_name varchar2(50)
, account_balance_amt number
, account_start_date date
) ;
The Control File
Notice that this control file is a little different from the one we used to load CSV values. The order of the columns in this list isn't as critical -- we have the table column name followed by the positions of where to find that data in our data file. Also we don't need to specify a field separator like the comma, or enclosure symbols like the double quotes. In the same directory with your data file, create this control file and name it "Control.txt".
The Control file (file name = "Control.txt")------------------------------------------------------------
-- SQL-Loader Control File
------------------------------------------------------------
options ( skip=1 )
load data
infile 'Data.txt'
truncate into table scott.sql_loader_demo_simple
( customer_full_name POSITION( 1: 12 )
, account_balance_amt POSITION( 13: 19 )
, account_start_date POSITION( 21: 43 )
DATE "Mon-DD-YYYY HH:MI:SS am"
)
And, here's a quick description of what the commands in the control file are doing:
A) options ( skip=1 )
B) load data
C) infile 'Data.txt'
D) truncate into table scott.sql_loader_demo_simple
E) ( customer_full_name POSITION( 1: 12 )
F) , account_balance_amt POSITION( 13: 19 )
G) , account_start_date POSITION( 21: 43 )
H) DATE "Mon-DD-YYYY HH:MI:SS am"
I) )
Line A = Skip the header row of the data file.
Line B = This is the command to start loading data.
Line C = This is the name of your data file.
Line D = This is the schema and name of your Oracle table.
The "truncate" specifies that the existing data in the
table will be truncated or erased prior to the load.
Line E-I = This is the comma seperated list of columns to be loaded.
The position (start and end) of each column's source data
is specified as shown.
Line H = The DATE modifier is needed for the column right above it.
This can be located at the end of the line or just below it
as shown.
The Batch File
@echo off
sqlldr 'scott / tiger @ my_database' control='Control.txt' log='Results.log'
pause
0 comments