| Reading multiple text files [message #638134] |
Wed, 03 June 2015 11:36  |
 |
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
|
Senior Member |
|
|
i have multiple text files with the content as below.
i want to store the values of text rows in a column for example
2nd row 1455001-GTPR11 will go to column1 of table data_upload
9tb row value after b that is 360 into another column2 of table data_upload
** 9001.nc1
1455001-GTPR11
1
9001
9001
S275JR
2
PL12
B
360.00
266.00
12.00
12.00
12.00
0.00
94.200
2.153
0.000
0.000
0.000
0.000
AK
v 0.00u 0.00 0.00 0.00 0.00 0.00 0.00
360.00 0.00 0.00 0.00 0.00 0.00 0.00
360.00 266.00 0.00 0.00 0.00 0.00 0.00
0.00 266.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00 0.00 0.00
AK
h 0.00o 0.00 0.00 0.00 0.00 0.00 0.00
0.00 266.00 0.00 0.00 0.00 0.00 0.00
360.00 266.00 0.00 0.00 0.00 0.00 0.00
360.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00 0.00 0.00
SI
v 66.50u 330.00 0.00 012 1455001-GTPR11-9001
BO
v 40.00u 40.00 22.00
v 40.00u 157.00 22.00 0.00l 0.00 58.00 0.00
v 110.00u 40.00 22.00
v 110.00u 157.00 22.00 0.00l 0.00 58.00 0.00
v 180.00u 40.00 22.00
v 180.00u 157.00 22.00 0.00l 0.00 58.00 0.00
v 250.00u 40.00 22.00
v 250.00u 157.00 22.00 0.00l 0.00 58.00 0.00
v 320.00u 40.00 22.00
v 320.00u 157.00 22.00 0.00l 0.00 58.00 0.00
KA
68.00 0.00 68.00 360.00 -90.00
EN
|
|
|
|
| Re: Reading multiple text files [message #638137 is a reply to message #638134] |
Thu, 04 June 2015 01:28  |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
Can you re-write your question? There must be some typing errors, it doesn't make sense as it is. Also, if you include the CREATE TABLE statement for the table into which you want to load the data, with an example of some INSERT statements, it would help.
|
|
|
|
| Re: Reading multiple text files [message #638164 is a reply to message #638134] |
Thu, 04 June 2015 12:15 |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You can use Oracle's SQL*Loader utility to load this data. You need to be able to identify where the data that you want is located by position and/or delimiter. The following demonstration assumes that the first character of each record is an asterisk and that there is no other row beginning with an asterisk and that the fields are delimited by whitespaces and that the data that you want will always be on the 2nd and 10th lines and that your data file is named test.dat and that you have a table named data_upload with columns named column1 and column2 and may contain other columns, like this:
SCOTT@orcl> create table data_upload
2 ( column1 varchar2(14)
3 , column2 number )
4 /
Table created.
You will need to create a SQL*Loader control file (a text file) like this one, which I have named test.ctl:
load data
infile test.dat continueif next(1) != '*'
into table data_upload
fields terminated by whitespace
trailing nullcols
( fill1 filler
, fill2 filler
, column1
, fill3 filler
, fill4 filler
, fill5 filler
, fill6 filler
, fill7 filler
, fill8 filler
, fill9 filler
, column2 )
Then you can load your data from either an operating system command line or from SQL*Plus by adding the HOST command in front of it like this:
SCOTT@orcl> host sqlldr scott/tiger control=test.ctl log=test.log
SQL*Loader: Release 11.2.0.1.0 - Production on Thu Jun 4 10:04:48 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 1
The result is this:
SCOTT@orcl> select * from data_upload
2 /
COLUMN1 COLUMN2
-------------- ----------
1455001-GTPR11 360
1 row selected.
|
|
|
|
|
|
| Re: Reading multiple text files [message #638224 is a reply to message #638196] |
Sat, 06 June 2015 19:44 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
m.abdulhaq wrote on Fri, 05 June 2015 13:01thanks barbara , for the help but i have a problem of reading similar multiple files for example i have 10 text files, so do i need to create 10 .ctl files and load it one by one.Ideally there will be one folder, inside this folder all the 10 files will located.So first i have to write a program to read the folder's path into my program , open all the text files one by one and read the contents into table automatically.
You could do that and there are some examples on this forum as to how and you would only need one control file, passing the file names on the command line.
A better method, if the files have similar names, would be to use wildcards for the names, like test*.* if they all start with test, but you would have to upgrade to a current version of Oracle, not 10g, which is outdated.
[Updated on: Sat, 06 June 2015 19:45] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
