OraFAQ Forum: Server Utilities » Importing data from a TXT file into a table

Home » RDBMS Server » Server Utilities » Importing data from a TXT file into a table

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

Importing data from a TXT file into a table [message #387866]

Sat, 21 February 2009 00:52

shahzaib_4vip@hotmail.com
Messages: 410
Registered: December 2008
Location: karachi

Senior Member

[Topic split and edited by LF]

The original poster was asked to search the board for topics that talk about "Forms + SQL*Loader and/or external tables".

As the discussion no longer addresses Forms problems but SQL*Loader ones, topic has been split (from the original one); loading messages have been moved into the Server Utilities forum.

Thanks you Little foot

i search SQL Loader in orafaq and also in google and got answer

i Done with that Query

load data
infile 'C:\list.dat'
insert
into table imp
(
no position(01:02) integer external,
dated position(03:10) date "YYYYMMDD",
hour position(11:14) time "HH.MM",
outgoing position(17:18) integer external,
emp_no position(19:28) integer external)

The command work successfully but there is 1 problem

The time format is not set

hours column is coming without any .

its look like

HOURS
2102
2103
2104
2104
2106
2108

I need into the format in hour column 21:02

Thanks

Shahzaib Ismail

[Updated on: Sun, 22 February 2009 14:18] by Moderator

Report message to a moderator

Re: Import *.TXT data into Oracle Form [message #387915 is a reply to message #387866]

Sat, 21 February 2009 11:51

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

Senior Member
Account Moderator

The "hour" column isn't loaded correctly; here's an example of how to do it. Note that there's no TIME datatype in Oracle so you'll have to use DATE instead. By default, date portion will be "first of the current month".

Sample control file:

load data
  infile *
  replace
into table test
  (
   hours date "hh24mi"
  )

begindata
2102
2103
2104
2104
2106
2108

Snippet of SQL*Loader and SQL*Plus session:

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

Commit point reached - logical record count 5
Commit point reached - logical record count 6

SQL> select * from test;

HOURS
----------------
01.02.2009 21:02
01.02.2009 21:03
01.02.2009 21:04
01.02.2009 21:04
01.02.2009 21:06
01.02.2009 21:08

6 rows selected.

SQL>

Report message to a moderator

Re: Import *.TXT data into Oracle Form [message #387952 is a reply to message #387866]

Sun, 22 February 2009 08:40

shahzaib_4vip@hotmail.com
Messages: 410
Registered: December 2008
Location: karachi

Senior Member

@ Little Foot

Thanks for your help

I have another problem and thats

i have a data like this

31200804052103 020000750053 04
31200804050903 010000750053 04

now i have 2 number one is 020000750053 and 2nd is 010000750053

i need to load all 020000 data time in outgoing column and all 010000 data time in incoming column

just like i have 4 column
date
emp_no
incoming
outgoing

i need to post the data on the column like this

Date Emp_no Incoming Outgoing

05-4-08 750053 09:03 21:03

Hope you understand my requirement

Thanks & Regards

Shahzaib Ismail

Report message to a moderator

Re: Import *.TXT data into Oracle Form [message #387974 is a reply to message #387952]

Sun, 22 February 2009 14:03

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

Senior Member
Account Moderator

Perhaps NULLIF might help?

A table:

SQL> create table test (id varchar2(10), incoming date, outgoing date);

Table created.

A control file and simplified sample data:

load data
  infile *
  replace
into table test
  (
   id position (6:12),
   incoming position (1:4) date "hh24mi" nullif (6:11) = '020000',
   outgoing position (1:4) date "hh24mi" nullif (6:11) = '010000'
  )

begindata
2103 020000
0903 010000

Loading and results:

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

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

SQL> select * from test;

ID         INCOMING         OUTGOING
---------- ---------------- ----------------
020000                      01.02.2009 21:03
010000     01.02.2009 09:03

SQL>

[Updated on: Sun, 22 February 2009 14:13]

Report message to a moderator

Re: Importing data from a TXT file into a table [message #388038 is a reply to message #387866]

Mon, 23 February 2009 01:04

shahzaib_4vip@hotmail.com
Messages: 410
Registered: December 2008
Location: karachi

Senior Member

@ Little Foot

Why the date is coming with time in hours column

I am only need time.

Hope there is some solution

Thanks

Shahzaib ismail

Report message to a moderator

Re: Importing data from a TXT file into a table [message #388061 is a reply to message #388038]

Mon, 23 February 2009 01:37

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

Senior Member
Account Moderator

As I've already told you:

Note that there's no TIME datatype in Oracle so you'll have to use DATE instead. By default, date portion will be "first of the current month".

You can not store TIME only. If you want to do that, you'll have to use a CHARACTER datatype column, but that will ruin all chances to do any kind of date/time arithmetic in an acceptable way.

Live with it! For displaying purposes, you can always choose to show only information you are interested in. TO_CHAR function will successfully return any valid format (including HH24:MI).

Report message to a moderator

Re: Importing data from a TXT file into a table [message #388172 is a reply to message #387866]

Mon, 23 February 2009 10:56

shahzaib_4vip@hotmail.com
Messages: 410
Registered: December 2008
Location: karachi

Senior Member

Thanks Little Foot for your satisfied answer.

Shahzaib Ismail

Report message to a moderator

Previous Topic:	trailing option in ctl file
Next Topic:	How to Overwrite tables in existing user with import command imp in oracle 8.1.5 or 8.1.7 (merged 3)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 06:20:29 CDT 2024