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 #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
|
|
|
Re: Import *.TXT data into Oracle Form [message #387974 is a reply to message #387952] |
Sun, 22 February 2009 14:03 |
|
Littlefoot
Messages: 21823 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
|
|
|
|
|
|