Home » RDBMS Server » Server Utilities » creating control file to load data from a text file
| creating control file to load data from a text file [message #124531] |
Sun, 19 June 2005 20:40  |
sikyala
Messages: 33
Registered: May 2005
|
Member |
|
|
I have a text file that I need to load into an Oracle database.
Below is a sample of the text in the text file.
File Format: backslash delimited ("\"), followed by a number in brackets.
The number indicates the field number; a legend of all possible fields follows.
Actual data begins after the line of '='.
(1) IAC Assigned Accession Number\(2) Citation Status\(3) Report Classification\(4) Citation Classification\(5) Distribution Limitation(s)\(6) Abstract Classification\(7) Title Classification\(8) Distribution Statement\(9) Creation Date\(10) Unclassified Title\(11) Report Date\(12) Document Location\(13) IAC Document Type Code\(14) IAC Document Type\(15) Media Count\(16) Pagination Code\(17) Descriptor Classification\(18) TAB Year\(19) TAB Issue Number\(20) Personal Author(s)\(21) Abstract\(22) IAC Subject Terms\(23) Accession Number\(24) Supplementary Note\(25) Identifiers\(26) Descriptors\(27) Field(s) & Group(s)\(28) Organization Type Code\(29) Organization Type\(30) Geo-Political Code\(31) Monitor Series\(32) Monitor Acronym\(33) Contract Number\(34) Report Number(s)\(35) Corporate Author\(36) Source Code\(37) Identifier Classification\(38) Copyright Status\(39) Name of Responsible Person
===================================================================================================================================== ===================================================================================================================================== ===================================================================================================================================== ===================================================================================================================================== ===================================================================================================================================== ===================================================================================================================================== ============================================================================================================
\(1) NT-53556 NT53556\(10) Radar Signal Interpretation Using Neural Network for Defect Detection in Concrete,\(20) Shoukry, Samir N. Martinelli, David Varadarajan, Srinivas T. Halabe, Udaya B. \(11) 01 Mar 1996\(15) 5 Page(s) \(22) N--(U) ISSN 0025-5327, BRIDGES, CONCRETE, GROUND PENETRATING RADAR, INFRASTRUCTURE, NEURAL NETWORKS.; \(27) na\(14) JOURNAL ARTICLES \(12) NTIAC \(3) Unclassified \(5) 01 - APPROVED FOR PUBLIC RELEASE 21 - JOURNAL ARTICLES ANNOUNCEMENT ONLY \(8) Availability: Published in 'Materials Evaluation'; 54,3; March 1996; 393-397; 10 refs. (Copies not available from NTIAC/DTIC).\(21) A neural network based on the learning vector quantization (LVQ) technique is applied for the classification of radar echo waveforms obtained from concrete bridge deck specimens with varying internal defects. A randomly chosen set of as measured (raw) time-domain echo-signals was used in training the network to classify waveforms into those representing defective and nondefective conditions in the concrete specimens. The same set of waveforms as preprocessed by subtracting the reference noise signal and was again separately used to train the networks. Different versions of LVQ algorithms were used independently in training, and the best results in terms of the recognition accuracy were obtained by training using the OLVQ (optimized LVQ) algorithm. The OVLQ trained neural network was able to classify an independent set of test waveforms with a higher degree of accuracy than those trained with other variants of LVQ algorithms. It has been observed that the subtraction of the reference noise component (preprocessing of the time domain signal) had no effect on the performance of the correct recognition of the OVLQ network. The performance of the network indicates its high potential for use in the analysis and interpretation of raw radar echo waveforms. This technique also holds promise for successful classification of radar waveforms into classes that represent specific kind of internal defects in concrete specimens such as cracks, delaminations, voids, etc. (Author).\(24) Published by American Society for Nondestructive Testing, 1711 Arlingate Lane, P.O. Box 28518, Columbus, OH 43228-0518.\(9) na\(23) ADD339501 \(38) yes\(39) George Matzkanin
\(1) NT-53555 NT53555\(10) Optimal Pultrusion Process Conditions for Improving the Dynamic Properties of Graphite-Epoxy Composite Beams, \(20) Kowsika, Murthy V. Mantena, P. Raju \(11) 01 Mar 1996\(15) 7 Page(s) \(22) N--(U) ISSN 0025-5327, COMPOSITES, DAMPING, FLEXURE, GRAPHITE EPOXY, PULTRUSION, REGRESSION ANALYSIS, VIBRATION ANALYSIS, MATHEMATICAL MODELS.; \(27) na\(14) JOURNAL ARTICLES \(12) NTIAC \(3) Unclassified \(5) 01 - APPROVED FOR PUBLIC RELEASE 21 - JOURNAL ARTICLES ANNOUNCEMENT ONLY \(8) Availability: Published in 'Materials Evaluation'; 54,3; March 1996; 386-392; 16 refs. (Copies not available from NTIAC/DTIC).\(21) The manufacturing process variables significantly influence the mechanical properties of pultruded composites. In this study, a statistical central composite design (CCD) test pattern has been used to manufacture unidirectional graphite-epoxy pultruded composite beams under carefully controlled process conditions.
There are over 4000 records formatted like this. I can not figure out how to write a control file that would load these records in a table because of the way the fields are labeled. There are fields that have no value.
If there is someone who knows how I can get this data loaded I would appreciate your help.
Thanks!
|
|
|
|
|
|
|
|
|
|
| Re: creating control file to load data from a text file [message #124549 is a reply to message #124531] |
Mon, 20 June 2005 02:45  |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
scott@ORA92> CREATE TABLE your_table
2 (col1 varchar2(4000),
3 col2 varchar2(4000),
4 col3 varchar2(4000),
5 col4 varchar2(4000),
6 col5 varchar2(4000),
7 col6 varchar2(4000),
8 col7 varchar2(4000),
9 col8 varchar2(4000),
10 col9 varchar2(4000),
11 col10 varchar2(4000),
12 col11 varchar2(4000),
13 col12 varchar2(4000),
14 col13 varchar2(4000),
15 col14 varchar2(4000),
16 col15 varchar2(4000),
17 col16 varchar2(4000),
18 col17 varchar2(4000),
19 col18 varchar2(4000),
20 col19 varchar2(4000),
21 col20 varchar2(4000),
22 col21 varchar2(4000),
23 col22 varchar2(4000),
24 col23 varchar2(4000),
25 col24 varchar2(4000),
26 col25 varchar2(4000),
27 col26 varchar2(4000),
28 col27 varchar2(4000),
29 col28 varchar2(4000),
30 col29 varchar2(4000),
31 col30 varchar2(4000),
32 col31 varchar2(4000),
33 col32 varchar2(4000),
34 col33 varchar2(4000),
35 col34 varchar2(4000),
36 col35 varchar2(4000),
37 col36 varchar2(4000),
38 col37 varchar2(4000),
39 col38 varchar2(4000),
40 col39 varchar2(4000))
41 /
Table created.
scott@ORA92> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\oracle'
2 /
Directory created.
scott@ORA92> DECLARE
2 v_bfile BFILE := BFILENAME ('MY_DIR', 'your_text_file.txt');
3 v_clob CLOB;
4 v_dest_offset INTEGER := 1;
5 v_src_offset INTEGER := 1;
6 v_src_csid NUMBER := NLS_CHARSET_ID ('US7ASCII');
7 v_lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
8 v_warning INTEGER;
9 v_string VARCHAR2(32767);
10 v_col VARCHAR2(30);
11 v_col1 VARCHAR2(32767);
12 v_col2 VARCHAR2(32767);
13 v_col3 VARCHAR2(32767);
14 v_col4 VARCHAR2(32767);
15 v_col5 VARCHAR2(32767);
16 v_col6 VARCHAR2(32767);
17 v_col7 VARCHAR2(32767);
18 v_col8 VARCHAR2(32767);
19 v_col9 VARCHAR2(32767);
20 v_col10 VARCHAR2(32767);
21 v_col11 VARCHAR2(32767);
22 v_col12 VARCHAR2(32767);
23 v_col13 VARCHAR2(32767);
24 v_col14 VARCHAR2(32767);
25 v_col15 VARCHAR2(32767);
26 v_col16 VARCHAR2(32767);
27 v_col17 VARCHAR2(32767);
28 v_col18 VARCHAR2(32767);
29 v_col19 VARCHAR2(32767);
30 v_col20 VARCHAR2(32767);
31 v_col21 VARCHAR2(32767);
32 v_col22 VARCHAR2(32767);
33 v_col23 VARCHAR2(32767);
34 v_col24 VARCHAR2(32767);
35 v_col25 VARCHAR2(32767);
36 v_col26 VARCHAR2(32767);
37 v_col27 VARCHAR2(32767);
38 v_col28 VARCHAR2(32767);
39 v_col29 VARCHAR2(32767);
40 v_col30 VARCHAR2(32767);
41 v_col31 VARCHAR2(32767);
42 v_col32 VARCHAR2(32767);
43 v_col33 VARCHAR2(32767);
44 v_col34 VARCHAR2(32767);
45 v_col35 VARCHAR2(32767);
46 v_col36 VARCHAR2(32767);
47 v_col37 VARCHAR2(32767);
48 v_col38 VARCHAR2(32767);
49 v_col39 VARCHAR2(32767);
50 BEGIN
51 DBMS_LOB.FILEOPEN (v_bfile);
52 DBMS_LOB.CREATETEMPORARY (v_clob, TRUE);
53 DBMS_LOB.LOADCLOBFROMFILE
54 (v_clob,
55 v_bfile,
56 DBMS_LOB.GETLENGTH (v_bfile),
57 v_dest_offset,
58 v_src_offset,
59 v_src_csid,
60 v_lang_context,
61 v_warning);
62 DBMS_LOB.FILECLOSE (v_bfile);
63 v_clob := SUBSTR (v_clob, INSTR (v_clob, '=')) || '\(1)\ ';
64 v_clob := SUBSTR (v_clob, INSTR (v_clob, '\') + 1);
65 WHILE DBMS_LOB.GETLENGTH (v_clob) > 3 LOOP
66 v_string := SUBSTR (v_clob, 1, INSTR (v_clob, '\') - 1);
67 v_col := SUBSTR (v_string, 2, INSTR (v_string, ')') - 2);
68 v_string := LTRIM (SUBSTR (v_string, INSTR (v_string, ')') + 1));
69 IF v_col = '1' THEN
70 IF v_col1 IS NOT NULL THEN
71 INSERT INTO your_table
72 VALUES (v_col1, v_col2, v_col3, v_col4, v_col5, v_col6, v_col7, v_col8, v_col9, v_col10,
73 v_col11, v_col12, v_col13, v_col14, v_col15, v_col16, v_col17, v_col18, v_col19, v_col20,
74 v_col21, v_col22, v_col23, v_col24, v_col25, v_col26, v_col27, v_col28, v_col29, v_col30,
75 v_col31, v_col32, v_col33, v_col34, v_col35, v_col36, v_col37, v_col38, v_col39);
76 v_col1 := NULL;
77 v_col2 := NULL;
78 v_col3 := NULL;
79 v_col4 := NULL;
80 v_col5 := NULL;
81 v_col6 := NULL;
82 v_col7 := NULL;
83 v_col8 := NULL;
84 v_col9 := NULL;
85 v_col10 := NULL;
86 v_col11 := NULL;
87 v_col12 := NULL;
88 v_col13 := NULL;
89 v_col14 := NULL;
90 v_col15 := NULL;
91 v_col16 := NULL;
92 v_col17 := NULL;
93 v_col18 := NULL;
94 v_col19 := NULL;
95 v_col20 := NULL;
96 v_col21 := NULL;
97 v_col22 := NULL;
98 v_col23 := NULL;
99 v_col24 := NULL;
100 v_col25 := NULL;
101 v_col26 := NULL;
102 v_col27 := NULL;
103 v_col28 := NULL;
104 v_col29 := NULL;
105 v_col30 := NULL;
106 v_col31 := NULL;
107 v_col32 := NULL;
108 v_col33 := NULL;
109 v_col34 := NULL;
110 v_col35 := NULL;
111 v_col36 := NULL;
112 v_col37 := NULL;
113 v_col38 := NULL;
114 v_col39 := NULL;
115 END IF;
116 v_col1 := v_string;
117 ELSIF v_col = '2' THEN v_col2 := v_string;
118 ELSIF v_col = '3' THEN v_col3 := v_string;
119 ELSIF v_col = '4' THEN v_col4 := v_string;
120 ELSIF v_col = '5' THEN v_col5 := v_string;
121 ELSIF v_col = '6' THEN v_col6 := v_string;
122 ELSIF v_col = '7' THEN v_col7 := v_string;
123 ELSIF v_col = '8' THEN v_col8 := v_string;
124 ELSIF v_col = '9' THEN v_col9 := v_string;
125 ELSIF v_col = '10' THEN v_col10 := v_string;
126 ELSIF v_col = '11' THEN v_col11 := v_string;
127 ELSIF v_col = '12' THEN v_col12 := v_string;
128 ELSIF v_col = '13' THEN v_col13 := v_string;
129 ELSIF v_col = '14' THEN v_col14 := v_string;
130 ELSIF v_col = '15' THEN v_col15 := v_string;
131 ELSIF v_col = '16' THEN v_col16 := v_string;
132 ELSIF v_col = '17' THEN v_col17 := v_string;
133 ELSIF v_col = '18' THEN v_col18 := v_string;
134 ELSIF v_col = '19' THEN v_col19 := v_string;
135 ELSIF v_col = '20' THEN v_col20 := v_string;
136 ELSIF v_col = '21' THEN v_col21 := v_string;
137 ELSIF v_col = '22' THEN v_col22 := v_string;
138 ELSIF v_col = '23' THEN v_col23 := v_string;
139 ELSIF v_col = '24' THEN v_col24 := v_string;
140 ELSIF v_col = '25' THEN v_col25 := v_string;
141 ELSIF v_col = '26' THEN v_col26 := v_string;
142 ELSIF v_col = '27' THEN v_col27 := v_string;
143 ELSIF v_col = '28' THEN v_col28 := v_string;
144 ELSIF v_col = '29' THEN v_col29 := v_string;
145 ELSIF v_col = '30' THEN v_col30 := v_string;
146 ELSIF v_col = '31' THEN v_col31 := v_string;
147 ELSIF v_col = '32' THEN v_col32 := v_string;
148 ELSIF v_col = '33' THEN v_col33 := v_string;
149 ELSIF v_col = '34' THEN v_col34 := v_string;
150 ELSIF v_col = '35' THEN v_col35 := v_string;
151 ELSIF v_col = '36' THEN v_col36 := v_string;
152 ELSIF v_col = '37' THEN v_col37 := v_string;
153 ELSIF v_col = '38' THEN v_col38 := v_string;
154 ELSIF v_col = '39' THEN v_col39 := v_string;
155 END IF;
156 v_clob := SUBSTR (v_clob, INSTR (v_clob, '\') + 1);
157 END LOOP;
158 END;
159 /
PL/SQL procedure successfully completed.
scott@ORA92> COLUMN col1 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col2 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col3 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col4 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col5 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col6 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col7 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col8 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col9 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col10 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col11 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col12 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col13 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col14 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col15 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col16 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col17 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col18 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col19 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col20 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col21 FORMAT A50 WORD_WRAPPED
scott@ORA92> COLUMN col22 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col23 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col24 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col25 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col26 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col27 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col28 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col29 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col30 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col31 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col32 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col33 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col34 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col35 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col36 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col37 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col38 FORMAT A10 WORD_WRAPPED
scott@ORA92> COLUMN col39 FORMAT A10 WORD_WRAPPED
scott@ORA92> SET LINESIZE 500
scott@ORA92> SELECT * FROM your_table
2 /
COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10 COL11 COL12 COL13 COL14 COL15 COL16 COL17 COL18 COL19 COL20 COL21 COL22 COL23 COL24 COL25 COL26 COL27 COL28 COL29 COL30 COL31 COL32 COL33 COL34 COL35 COL36 COL37 COL38 COL39
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
NT-53556 Unclassifi 01 - Availabili na Radar 01 Mar NTIAC JOURNAL 5 Page(s) Shoukry, A neural network based on the learning vector N--(U) ADD339501 Published na yes George
NT53556 ed APPROVED ty: Signal 1996 ARTICLES Samir N. quantization (LVQ) technique is applied for the ISSN by Matzkanin
FOR PUBLIC Published Interpreta Martinelli classification of radar echo waveforms obtained 0025-5327, American
RELEASE 21 in tion Using , David from concrete bridge deck specimens with varying BRIDGES, Society
- JOURNAL 'Materials Neural Varadaraja internal defects. A randomly chosen set of as CONCRETE, for
ARTICLES Evaluation Network n, measured (raw) time-domain echo-signals was used GROUND Nondestruc
ANNOUNCEME '; 54,3; for Defect Srinivas in training the network to classify waveforms into PENETRATIN tive
NT ONLY March Detection T. Halabe, those representing defective and nondefective G RADAR, Testing,
1996; in Udaya B. conditions in the concreprompt te specimens. The INFRASTRUC 1711
393-397; Concrete, same set of waveforms as preprocessed by TURE, Arlingate
10 refs. subtracting the reference noise signal and was NEURAL Lane, P.O.
(Copies again separately used to train the networks. NETWORKS.; Box 28518,
not Different versions of LVQ algorithms were used Columbus,
available independently in training, and the best results in OH
from terms of the recognition accuracy were obtained by 43228-0518
NTIAC/DTIC training using the OLVQ (optimized LVQ) algorithm. .
). The OVLQ trained neural network was able to
classify an independent set of test waveforms with
a higher degree of accuracy than those trained
with other variants of LVQ algorithms. It has been
observed that the subtraction of the reference
noise component (preprocessing of the time domain
signal) had no effect on the performance of the
correct recognition of the OVLQ network. The
performance of the network indicates its high
potential for use in the analysis and
interpretation of raw radar echo waveforms. This
technique also holds promise for successful
classification of radar waveforms into classes
that represent specific kind of internal defects
in concretprompt e specimens such as cracks,
delaminations, voids, etc. (Author).
NT-53555 Unclassifi 01 - Availabili Optimal 01 Mar NTIAC JOURNAL 7 Page(s) Kowsika, The manufacturing process variables significantly N--(U) na
NT53555 ed APPROVED ty: Pultrusion 1996 ARTICLES Murthy V. influence the mechanical properties of pultruded ISSN
FOR PUBLIC Published Process Mantena, composites. In this study, a statistical central 0025-5327,
RELEASE 21 in Conditions P. Raju composite design (CCD) test pattern has been used COMPOSITES
- JOURNAL 'Materials for to manufacture unidirectional graphite-epoxy , DAMPING,
ARTICLES Evaluation Improving pultruded composite beams under carefully FLEXURE,
ANNOUNCEME '; 54,3; the controlled process conditions.
GRAPHITE
NT ONLY March Dynamic EPOXY,
1996; Properties PULTRUSION
386-392; of ,
16 refs. Graphite-E REGRESSION
(Copies poxy ANALYSIS,
not Composite VIBRATION
available Beams, ANALYSIS,
from MATHEMATIC
NTIAC/DTIC AL
). MODELS.;
scott@ORA92>
|
|
|
|
Goto Forum:
Current Time: Wed Jul 03 08:28:38 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
