Home » RDBMS Server » Server Utilities » how to load fix length formatted data from a file
how to load fix length formatted data from a file [message #242573] Mon, 04 June 2007 02:53 Go to next message
sreelatha-p
Messages: 2
Registered: June 2007
Junior Member
Hi,

Please clarify my below doubt.

I have fixed length formatted data(no delimiters) in a file. I do not want to include the entire data in the control file(under BEGINDATA) as the data is very huge. Can I load this data into database directly from the file, without listing it in the BEGINDATA section? If it is possible, please provide me the syntax for doing so.

Thanks in advance,
Sreelatha.
Re: how to load fix length formatted data from a file [message #242575 is a reply to message #242573] Mon, 04 June 2007 03:04 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Use SQL*Loader and specify each field's position in the control file.
Re: how to load fix length formatted data from a file [message #242585 is a reply to message #242575] Mon, 04 June 2007 03:57 Go to previous messageGo to next message
sreelatha-p
Messages: 2
Registered: June 2007
Junior Member
thanks for the quick response...

But my doubt still exists. I am under the impression that, if we specify positions then we have to give the data to be loaded into database just after BEGINDATA keyword in the control file. Can the sqlldr map the positions specified in the control file to the data in input file?
If so, do you think the below control file syntax will load the data in file(mydata.csv) into the given table?

LOAD DATA
INFILE c:\mydata.csv
APPEND INTO TABLE load_positional_data
( data1 POSITION(1:4),
data2 POSITION(5:10),
data3 POSITION(11:20),
)

The content of file looks like:
1234asdfasAFD12$$hhd
2345safgshfggfhsadgf
46bh777787979jhfgjkk
.
.
3645234bbsjfhsj83478
Re: how to load fix length formatted data from a file [message #242604 is a reply to message #242585] Mon, 04 June 2007 05:26 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why didn't you try it?
SQL> $type test.csv
1234asdfasAFD12$$hhd
2345safgshfggfhsadgf
46bh777787979jhfgjkk

SQL>
SQL> $type test.ctl

LOAD DATA
INFILE      'test.csv'
REPLACE

INTO TABLE  test
(id    position    (01:04),
 name  position    (05:10),
 other position    (11:20)
)

SQL>
SQL> $sqlldr userid=scott/tiger@ora10 control=test.ctl log=test.log

SQL*Loader: Release 10.2.0.1.0 - Production on Pon Lip 4 12:25:07 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 2
Commit point reached - logical record count 3

SQL> select * From test;

ID   NAME       OTHER
---- ---------- ----------
1234 asdfas     AFD12$$hhd
2345 safgsh     fggfhsadgf
46bh 777787     979jhfgjkk

SQL>
Re: how to load fix length formatted data from a file [message #242618 is a reply to message #242573] Mon, 04 June 2007 06:18 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"Database Utilities", Chapter 6 "SQL*Loader Concepts", section "SQL*Loader Control File".
Quote:
INFILE clauses to specify where the input data is located

Regards
Michel
Previous Topic: TKRPOF
Next Topic: External Table with Sequence number
Goto Forum:
  


Current Time: Sat Dec 03 03:33:00 CST 2016

Total time taken to generate the page: 0.05910 seconds