Home » RDBMS Server » Server Utilities » Importing data from a TXT file into a table
Importing data from a TXT file into a table [message #387866] Sat, 21 February 2009 00:52 Go to next message
shahzaib_4vip@hotmail.com
Messages: 376
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 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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>
Re: Import *.TXT data into Oracle Form [message #387952 is a reply to message #387866] Sun, 22 February 2009 08:40 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
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 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
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
Re: Importing data from a TXT file into a table [message #388061 is a reply to message #388038] Mon, 23 February 2009 01:37 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As I've already told you:
LF
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).
Re: Importing data from a TXT file into a table [message #388172 is a reply to message #387866] Mon, 23 February 2009 10:56 Go to previous message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
Thanks Little Foot for your satisfied answer.


Shahzaib Ismail


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:
  


Current Time: Sat Dec 03 09:41:39 CST 2016

Total time taken to generate the page: 0.05752 seconds