SQL*Loader - Flat File

[aph]

Hi,
I have a flat file and according to the requirements we do not want to load 1st and last record into our table.

For example:

0144567AXZ
025688TYUK
0256887GFH
025688OUIH
0345569ZXC

In the above example, We want to load all records other than the 1st & last line/record.

Thanks in advance.

[psychofun]

You can use 'SKIP=1' to skip the first record. I'm not sure about the last record.

[asrarahmed]

u can use this script for loading flat file into oracle.

Before loading data first u create a table with the same column
name and column width

Load date
infile 'c:\data\data1.txt'
into table testdata
(code position (1:4) integer external,
name position (5:10) char)

Data1 is a flate file
testdata is a oracle table

[aph]

Thanks Asrar,

Are we skipping 1st & 2nd line? NO. Your solution is simply taking the value form plain text file and loading into table which I have very good idea, But I need some thing else if you look at my original post.

Thanks
aph

[raghud]

The easy way is to create external table ...

create table temp(f_name varchar(20),l_name varchar(20),sex varchar(1))
organization external
(type oracle_loader default directory admin
access parameters(
records delimited by newline badfile 'load.bad' discardfile 'load.dis' logfile 'load.log'
fields terminated by whitespace(f_name char,l_name char,sex char)
)
location ('load.txt')
)
reject limit unlimited ;

And then create your real table by selecting from temp ..

create table real as select * from temp where rownum <> 1 and rownum < (select count(*) from temp) ;

Before a try to create external table you have create directory ..

Raghu

[Shestakov]

Originally posted by raghud
The easy way is to create external table ...
create table real as select * from temp where rownum <> 1 and rownum < (select count(*) from temp) ;
Raghu

First base error:
select ... from tbl where rownum <> 1;
this query return NO ROW SELECTED in any way

SQL> select * from all_objects where rownum <> 1;

no rows selected

Second base error:
Not any garantee that FIRST LOADED ROW WILL BE FIRST SELECTED ROW and
LAST LOADED ROW WILL BE LAST SELECTED ROW..
--------------------------------------------------------------
Some notes:
ur problem is LAST ROW because first row will be reject very simple, using 'SKIP=1'

My questions are:
1. What OS u are using ?
2. Is this data have to load evry day (week...)?

if answer on first question UNUX/LINUX then u have to
write small script with :
tail -n .., wl, and sqlload (perl, sqlload) that remove last row.

if answer on second question is NO had better remove last
row from file manualy.


[Edited by Shestakov on 06-26-2002 at 11:22 PM]

[aph]

Actually simple is that we do not want to load Header and Trailer record.

example:

H999ABC
DJACK090902
DGOLD090902
DSAMS090902
TEOF

From the above example 1st line is Header, Last line is Trailer and the 3 middle are Details lines.

I need to load only Detail lines (3 in the middle).

After spending some time and carefully review of the complete syntax of SQL*loader I got the solution. It might be helpful to everyone.

LOAD DATA
INFILE 'test.dat' BADFILE 'test.bad' DISCARDFILE 'test.dis'
APPEND
INTO TABLE my_test_table
WHEN (01) <> 'H' and (01) <> 'T'
(
name POSITION(02:05) CHAR,
date POSITION(06:11) date
)

* In WHEN clause (01) is the 1st char. So in this case we can load only details lines.

Thanks to every body who participates in this post and view the post.

Thanks
aph

[sree_sri]

Hi Aph,

I got a dought on your solution. For example if you have data
like this then what you will do


000112
000434
0003434
000112
989000
989000


In this case i want to load middle lines, like
000434
0003434
000112
989000

Then what is the solution, 'SKIP=1' can remove first line, What ever it is, Like that any thing avilable for last line also.

First you asked solution based on first line and last line. But
you ended up with first char solution.

For example every time i want to remove first line then
'SKIP=1' is correct solution not WHEN (01) <> 'H' , Because
the text file data every time changes. Next text file first line can start with "M".

So in my opinion as shestakov said

if answer on first question UNUX/LINUX then u have to
write small script with :
tail -n .., wl, and sqlload (perl, sqlload) that remove last row.

is correct solution.

Sree.

[aph]

Thanks,
The file I have to process it comes with the tag of H, D & T.

So there for I think I have no problem so for, But I will consider your solution in case if I get the file without any tags.

Thanks again,
aph