Thanks Tom. But How can I load the data?
Nazmul Hasan, February 17, 2004 - 3:22 am UTC
In a .dat file, generated from a punch machine, the data are like the fillowing format-
"005:919043:0402123:134403:ALLEN:11"
"005:9085:0402123:134408:M.SCOTT:12"
"003:999:0402124:045258:Tom Kyte:12"
"003:77:0402124:045351:Blank !!:14"
"003:9:0402124:045455:Blank !!:12"
...
Each field is separeted by ':' and each record is enclosed with double quote( " " ).
I have to load the data is following table -
Time_office(mach_no,card_no,w_date,w_time,w_name,w_shift)
Q1: How I can I load data enclosed by double quote (" ")?
Every day the machine generate a .dat file in a default location. The file name is like 'PDNL0212'. The first 4 char 'PDNL' is fixed, the nest 4 char '0213' for date(MMDD). Every day I have to find the latest .dat file to load the data.
Q2: Is it possible, find the letest file (max date )programmatically and load the data by SQLLoader or PL/SQL?
I use 8.1.7.
Regards..
- Naz
February 17, 2004 - 8:15 am UTC
q1)
LOAD DATA
INTO TABLE t
REPLACE
FIELDS TERMINATED BY ':'
(
mach_no "substr(:mach_no,2)",
card_no,
w_date,
w_time,
w_name,
w_shift "rtrim(:w_shift,chr(34))"
)
will load the data up.
q2) your host operating system, which is what you'll be using to run SQLLDR, should be able to find the latest filename easily. eg: on unix, I would run simply:
$ sqlldr / t data=`ls -tr PDNL*.dat | tail -1`
SQL*Loader: Release 9.2.0.4.0 - Production on Tue Feb 17 08:15:59 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 5
but that'll be something you have to figure out on whatever OS you have.
Solution to Loading data file containing fields in matrix format
Amit Saha, February 17, 2004 - 6:00 am UTC
Thanks Tom.
It provided me with a new perspective to using the SQL Loader.
A reader, February 17, 2004 - 10:29 am UTC
How to convert matrix into recoreds
Ketan Popat, February 17, 2004 - 10:40 pm UTC
Hi Tom,
Can you help me in loading matrix like
1/4 0.23 - 0.29 ct. $100/CT. 1/9/04
P/CT VVS VS SI1 SI2 SI3 I1 I2 I3
D-F 23.0 17.0 14.0 11.5 10.0 8.1 6.7 4.1
G-H 18.0 14.0 12.5 10.7 9.5 7.8 6.5 3.7
I-J 13.0 11.5 10.5 9.7 8.7 7.0 5.5 3.3
K-L 10.1 9.1 8.6 8.1 7.6 6.6 4.9 2.9
M-N 8.1 7.6 7.1 6.1 5.6 4.9 4.0 2.6
the resulting record should be like this:
--
('0.23','0.29','D','VVS',23.0)
('0.23','0.29','E','VVS',23.0)
('0.23','0.29','F','VVS',23.0)
--
('0.23','0.29','D','VS',17.0)
('0.23','0.29','E','VS',17.0)
('0.23','0.29','F','VS',17.0)
Thanks in adv.
February 18, 2004 - 7:45 am UTC
not really, looks like "code needed" to me.