Home » RDBMS Server » Server Utilities » Reading multiple text files (oracle 10g)
Reading multiple text files [message #638134] Wed, 03 June 2015 11:36 Go to next message
m.abdulhaq
Messages: 253
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 Go to previous messageGo to next message
John Watson
Messages: 7936
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8916
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 #638196 is a reply to message #638164] Fri, 05 June 2015 15:01 Go to previous messageGo to next message
m.abdulhaq
Messages: 253
Registered: April 2013
Location: Ajman
Senior Member
thanks 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.

Re: Reading multiple text files [message #638224 is a reply to message #638196] Sat, 06 June 2015 19:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8916
Registered: November 2002
Location: California, USA
Senior Member
m.abdulhaq wrote on Fri, 05 June 2015 13:01
thanks 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

Re: Reading multiple text files [message #638227 is a reply to message #638224] Sun, 07 June 2015 07:49 Go to previous message
m.abdulhaq
Messages: 253
Registered: April 2013
Location: Ajman
Senior Member
thanks barbara.
Previous Topic: data migration
Next Topic: Metadata of Entire DB.
Goto Forum:
  


Current Time: Thu Jun 20 07:04:22 CDT 2019