use SQL loader to dump a fixed length file in to DB

anumkoshy · #1 (**permalink**) 10-10-2007

consider a fixed length file

12345abcd8901
12345abcd7777
12345njdu8888
12345hdku8388

i would like to dump it in to oracle DB using sql loader
12345 in to first coloumn
abcd in to second coloumn
8901 in to 3rd coloumn

dennis.jacob · #2 (**permalink**) 10-10-2007

Two steps

1.Make a csv file

Code:

awk 'BEGIN{FIELDWIDTHS = "5 4 4"}; {print $1","$2","$3;}' filename

2.

Code:

load data
          infile 'filename.csv'
		  into table tablename
          fields terminated by ","

nir_s · #3 (**permalink**) 10-10-2007

You can edit the control file of the sqlloader by the following:

Code:

LOAD DATA
INFILE <data_file_path_and_name>
INTO TABLE <table_name> (
<column_name> POSITION(<integer>:<integer>) <data_type>,
<column_name> POSITION(<integer>:<integer>) <data_type>,
<column_name> POSITION(<integer>:<integer>) <data_type>)

I.e:
LOAD DATA
   INFILE /tmp/load_data.dat
   INTO TABLE t1
     (col1 POSITION(1:5) ,
      col2 POSITION(6:9) , 
      col3 POSITION(10:13))

Regards,
Nir

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
What the command to find out the record length of a fixed length file?	tranq01	UNIX for Dummies Questions & Answers	9	12-04-2008 04:04 PM
how to convert Fixed length file to delimited file.	satyam_sat	Shell Programming and Scripting	7	04-03-2008 02:41 AM
Manipulating a fixed length file w/o PERL	dabear	Shell Programming and Scripting	4	03-31-2008 04:33 PM
convert XML file into Text file(fixed length)	ram2s2001	Shell Programming and Scripting	0	11-03-2005 01:28 AM
creating a fixed length output from a variable length input	r1500	Shell Programming and Scripting	2	12-03-2003 01:09 PM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:


	Powered by