Home » RDBMS Server » Server Utilities » Import a Flat File into Oracle and update another table
| Import a Flat File into Oracle and update another table [message #589440] |
Fri, 05 July 2013 15:34  |
 |
firebirdta84
Messages: 1
Registered: July 2013
|
Junior Member |
|
|
Hey everyone,
I am a newbie with Oracle, and I've tried for the last 2 days to solve this problem below. But all my searches and attempts have failed.
I have a text file called ReturnedFile.txt. This is a comma separated text file that contains records for two fields.... Envelope and Date Returned.
At the same time, I have a table in Oracle called Manifest. This table contains the following fields:
Envelope
DateSentOut
DateReturned
I need to write something that imports the ReturnedFile.txt into a temporary Oracle table named UploadTemp, and then compares the data in the Envelope field from UploadTemp with the Envelope field in Manifest. If it's a match, then the DateReturned field in Manifest needs updated with the DateReturned field in UploadTemp.
I've done this with SQL Server no problem, but I've been trying for two days to make this work with Oracle and I can't figure it out. I've been trying to use SQL*Loader, but I can't even get it to run properly on my machine.
I did create a Control file, saved as RetFile.ctl. Below is the contents of the CTL file:
LOAD DATA
INFILE 'C:\OracleTest\ReturnedFile.txt'
APPEND
INTO TABLE UploadTemp
FIELDS TERMINATED BY "'"
(
ENVELOPE,
DATERETURNED
)
If I could get SQL*Loader running, below is the code I came up with to import the text file and then to do the compare to the Manifest table and update as appropriate:
sqlldr UserJoe/Password123 CONTROL=C:\OracleTest\RetFile.ctl LOG=RetFile.log BAD=RetFile.bad
update Manifest m set m.DateReturned =
(select t.DateReturned
from UploadTemp t
where m.Envelope = t.Envelope
*)
That's all I got. As I said, I can't find a way to test it and I have no idea if it's even close.
PLEASE...can anyone assist me? Am I even close on this thing?
Joe
[EDITED by LF: removed superfluous empty lines]
[Updated on: Fri, 05 July 2013 15:40] by Moderator Report message to a moderator |
|
|
|
| Re: Import a Flat File into Oracle and update another table [message #589441 is a reply to message #589440] |
Fri, 05 July 2013 15:56  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
SQL*Loader should be ran from the operating system's command prompt (i.e. not SQL*Plus prompt) - did you do that? You second code suggests the opposite.
What does the log file say? It contains useful information which might help to fix issues you have.
Here's a short example which does the loading part. I created a table:SQL> desc uploadtemp
Name Null? Type
----------------------------------------- -------- -------------
ENVELOPE NUMBER
DATERETURNED DATE
SQL>
Control file (test.ctl):load data
infile 'c:\temp\test.csv'
replace
into table uploadtemp
fields terminated by "'"
(envelope,
datereturned "to_date(:datereturned, 'dd.mm.yyyy')"
)
Data to be loaded (test.csv):123'05.07.2013
45'06.07.2013
Loading session:C:\temp>sqlldr scott/tiger@xe control=test.ctl log=test.log
SQL*Loader: Release 11.2.0.2.0 - Production on Pet Srp 5 22:50:21 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 1
Commit point reached - logical record count 2
C:\temp>
Checking what we've done:C:\temp>sqlplus scott/tiger@xe
SQL*Plus: Release 11.2.0.2.0 Production on Pet Srp 5 22:51:04 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
SQL> alter session set nls_date_format = 'dd.mm.yyyy';
Session altered.
SQL> select * from uploadtemp;
ENVELOPE DATERETURN
---------- ----------
123 05.07.2013
45 06.07.2013
SQL>
Everything seems to be OK.
Now, the comparison part: I created the MANIFEST table whose one record matches one record in the UPLOADTEMP table:
SQL> select * from uploadtemp;
ENVELOPE DATERETURN
---------- ----------
123 05.07.2013
45 06.07.2013
SQL> select * from manifest;
ENVELOPE DATERETURN
---------- ----------
123 --> match
99 --> no match
Update statement:
SQL> update manifest m set
2 m.datereturned = (select u.datereturned
3 from uploadtemp u
4 where u.envelope = m.envelope
5 )
6 where m.envelope in (select u.envelope
7 from uploadtemp u
8 );
1 row updated.
SQL> select * from manifest;
ENVELOPE DATERETURN
---------- ----------
123 05.07.2013
99
SQL> |
|
|
|
| Re: Import a Flat File into Oracle and update another table [message #589449 is a reply to message #589441] |
Sat, 06 July 2013 02:23 |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I posted the following on the OTN forums:
https://forums.oracle.com/thread/2557654
If your ReturnedFile.txtfile is comma separated then you need TERMINATED BY "," not TERMINATED BY "'" in your control file. If there happens to not be an ending comma in any row, then you also need to add TRAILING NULLCOLS to your control file. You should also use a date format for your date in your control file that corresponds to the date format in your ReturnedFile.txt file, in case it does not match the date format on your system. You need to add a WHERE EXISTS clause to your update statement to prevent any rows that do not match from having the DateReturned updated to a null value. Please see the example below. If this does not help then please do a copy and paste as I did, that includes a few rows of sample data and table structure. It would also help to see your SQL*Loader log file or a SQL*Loader error message. If you can't get SQL*Loader to run properly, then you may have other issues, such as file permissions at the operating system level. There are also other options besides the methods below. For example, you could use an external table, instead of SQL*Loader, if your ReturnedFile.txtfile is on your serer, not your client. You could also use merge instead of update.
SCOTT@orcl_11gR2> host type retfile.ctl
LOAD DATA
INFILE 'ReturnedFile.txt'
APPEND
INTO TABLE UploadTemp
FIELDS TERMINATED BY ","
trailing nullcols
(ENVELOPE
, DATERETURNED date "dd-mm-yyyy")
SCOTT@orcl_11gR2> create table uploadtemp
2 (envelope varchar2(15),
3 datereturned date)
4 /
Table created.
SCOTT@orcl_11gR2> create table Manifest
2 (Envelope varchar2(15),
3 DateSentOut date,
4 DateReturned date)
5 /
Table created.
SCOTT@orcl_11gR2> insert all
2 into manifest values ('env1', sysdate-6, sysdate-4)
3 into manifest values ('env2', sysdate-5, null)
4 into manifest values ('env3', sysdate-4, null)
5 select * from dual
6 /
3 rows created.
SCOTT@orcl_11gR2> select * from manifest
2 /
ENVELOPE DATESENTO DATERETUR
--------------- --------- ---------
env1 30-JUN-13 02-JUL-13
env2 01-JUL-13
env3 02-JUL-13
3 rows selected.
SCOTT@orcl_11gR2> host sqlldr scott/tiger CONTROL=RetFile.ctl LOG=RetFile.log BAD=RetFile.bad
SQL*Loader: Release 11.2.0.1.0 - Production on Sat Jul 6 00:19:38 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 3
SCOTT@orcl_11gR2> select * from uploadtemp
2 /
ENVELOPE DATERETUR
--------------- ---------
env2 03-JUL-13
env3 04-JUL-13
env4 05-JUL-13
3 rows selected.
SCOTT@orcl_11gR2> update Manifest m
2 set m.DateReturned =
3 (select t.DateReturned
4 from UploadTemp t
5 where m.Envelope = t.Envelope)
6 where exists
7 (select t.DateReturned
8 from UploadTemp t
9 where m.Envelope = t.Envelope)
10 /
2 rows updated.
SCOTT@orcl_11gR2> select * from manifest
2 /
ENVELOPE DATESENTO DATERETUR
--------------- --------- ---------
env1 30-JUN-13 02-JUL-13
env2 01-JUL-13 03-JUL-13
env3 02-JUL-13 04-JUL-13
3 rows selected.
|
|
|
|
Goto Forum:
Current Time: Mon Mar 03 16:37:22 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
