Skip to Main Content
  • Questions
  • Need to load a file that is written in matrix format

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Amit.

Asked: February 16, 2004 - 1:25 pm UTC

Last updated: February 18, 2004 - 7:45 am UTC

Version: 9.2.0.3

Viewed 1000+ times

You Asked

Hi Tom,
I require to load a semi colon delimited file that looks like this:

F100;F101;F102;F103;F103b
GB09;0000712061;0;2;NULL
GB09;11003930;0;3;NULL

Now I am trying to load it into a table that will hold values in this order:

F100 GB09
F101 0000712061
F102 0
F103 2
F103b NULL
F100 GB09
F101 11003930
F102 0
F103 3
F103b NULL

I have not been able to understand, how to load this data using SQL Loader or Utl_file package. I am unable to read the file character by character where I can make use of the delimiter.
Can you help me on this ?

Thanks in Advance
Amit

and Tom said...

you will not be able to directly -- but, we can easily write a control file that can load this sort of data. What you would need to do is write a control file based on the header record -- then it is easy.

control file:

LOAD DATA
infile data.dat
replace
INTO TABLE T FIELDS TERMINATED BY ";" ( a constant 'F100', b )
INTO TABLE T FIELDS TERMINATED BY ";" ( a constant 'F101', b )
INTO TABLE T FIELDS TERMINATED BY ";" ( a constant 'F102', b )
INTO TABLE T FIELDS TERMINATED BY ";" ( a constant 'F103', b )
INTO TABLE T FIELDS TERMINATED BY ";" ( a constant 'F103b', b )


command line:

$ sqlldr / t skip=1

SQL*Loader: Release 9.2.0.4.0 - Production on Mon Feb 16 14:53:37 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 2


and resulting data:

ops$tkyte@ORA920PC> select * from t;

A B
---------- ----------
F100 GB09
F100 GB09
F101 0000712061
F101 11003930
F102 0
F102 0
F103 2
F103 3
F103b NULL
F103b NULL

10 rows selected.



At worst -- you'd have to write a script that got the first line of the .dat file, to generate the ctl file you need to load with.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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

Tom Kyte
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.





Tom Kyte
February 18, 2004 - 7:45 am UTC

not really, looks like "code needed" to me.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library