1 Reply Latest reply: Jul 5, 2013 3:36 PM by Barbara Boehmer RSS

    Importing a Flat File to Oracle and updating another table

    6dbc2e2a-7854-4bfa-80a8-4bc14afe3c30

      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

        • 1. Re: Importing a Flat File to Oracle and updating another table
          Barbara Boehmer

          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 returnedfile.txt

           

          env2,03-07-2013

           

          env3,04-07-2013

           

          env4,05-07-2013

           

          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-7, sysdate-3)

           

            3  into manifest values ('env2', sysdate-6, null)

           

            4  into manifest values ('env3', sysdate-5, null)

           

            5  select * from dual

           

            6  /

           

          3 rows created.

           

          SCOTT@orcl_11gR2> select * from manifest

           

            2  /

           

          ENVELOPE        DATESENTO DATERETUR

           

          --------------- --------- ---------

           

          env1            28-JUN-13 02-JUL-13

           

          env2            29-JUN-13

           

          env3            30-JUN-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 Fri Jul 5 13:15:06 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            28-JUN-13 02-JUL-13

           

          env2            29-JUN-13 03-JUL-13

           

          env3            30-JUN-13 04-JUL-13

           

          3 rows selected.