OraFAQ Forum: Server Utilities » how to load fix length formatted data from a file

Home » RDBMS Server » Server Utilities » how to load fix length formatted data from a file

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to load fix length formatted data from a file [message #242573]

Mon, 04 June 2007 02:53

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.

Report message to a moderator

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

Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Use SQL*Loader and specify each field's position in the control file.

Report message to a moderator

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

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

Report message to a moderator

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

Littlefoot
Messages: 21811
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>

Report message to a moderator

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

Michel Cadot
Messages: 68665
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

Report message to a moderator

Previous Topic:	TKRPOF
Next Topic:	External Table with Sequence number

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jun 26 13:47:54 CDT 2024