Load fixed length flat file data into Oracle 1

[pnad]

Hello all,

I have a fixed length flat file that I need to upload into an Oracle table. What is the most effecient way to do this. I do not have access to install/use SQL Loader.

Any help will be appreciated.

Thanks.

 

[SantaMufasa]

PNad,

If you had access to SQL*Loader, not only could you load the data into a table, but you could actually turn the flat file into an instantly accessible external table.

But absent SQL*Loader, here's a trick that you can use for fixed-length flat files. This trick depends upon your fixed-length columns having either at least two leading blanks in front and behind each logical column (including column 1 and at the end of the line), or a specific non-blank delimeter. Also ensure that there is an extra <cr-lf> at the beginning and at the end of your flat-file.

If you cannot construct your fixed-length flat-file data to match the above dependency, then this "trick" can become a rather labour-intensive exercise since you will have to manually ensure that your data fits the above provisions.

Let's presume you have a three-logical-column flat file of Department data that you want to read into a table named DEPT:

Section 1 -- Sample Department data:

  10  Finance                            1  
  31  Sales                              1  
  32  Sales                              2  
  33  Sales                              3  
  34  Sales                              4  
  35  Sales                              5  
  41  Operations                         1  
  42  Operations                         2  
  43  Operations                         3  
  44  Operations                         4  
  45  Operations                         5

Section 2 -- Code to Create the DEPT table:

SQL> create table dept (id number, name varchar2(50), region_id number);

Table created.

Section 3 -- How to prepare flat-file data to INSERT into new DEPT table: Using an editor like MS Word (that is able to recognise <CR-LF> end-of-line sequences), issue the following text-preparation commands:

1) Invoke Word
2) Open <flat-file-name>
3) Edit -> Replace -> (Find what) "  " [two adjacent spaces] -> (Replace with) "~" [some obsucure, but visible character) -> [Replace All]
4) Edit -> Replace -> (Find what) "~~" [two adjacent special characters] -> (Replace with) "~" [one special character]
5) Repeat step 4 until all "~~" appear as "~".
6) Edit -> Replace -> (Find what) "~" [one special character] -> (Replace with) "','" [Oracle INSERT values-separator punctuation]
7) Edit -> Replace -> (Find what) "','^p','" [two sets of value separators split by a "^p", MS Word's method of addressing a <cr-lf> pair] -> (Replace with) "');^pINSERT INTO DEPT VALUES ('" [proper syntax to INSERT values into the DEPT table]
8) Clean up the first and last lines of the flat file to remove any extraneous INSERT code.
9) File -> Save As -> ('Save as' type): "Text Only (*.txt)" -> (File name): <flat-file-name>.[b]sql[/b] -> [Save] (Answer 'Yes' to saving without formatting)
10) Exit MS Word
11) Using Notepad, Open <flat-file-name>.[b]sql[/b]
12) Remove extraneous MS Word lines at the end of the file of INSERT commands.
13) Save the file of INSERTs

Section 4 -- Run your INSERT-commands script:

1)Connect to Oracle as target owner of table.
2) SQL> @<flat-file-name>

(Example INSERT-script proof-of-concept execution:)
SQL> @dept

1 row created.
...
1 row created. (for each INSERT)

SQL> select * from dept;

        ID NAME                                                REGION_ID
---------- -------------------------------------------------- ----------
        10 Finance                                                     1
        31 Sales                                                       1
        32 Sales                                                       2
        33 Sales                                                       3
        34 Sales                                                       4
        35 Sales                                                       5
        41 Operations                                                  1
        42 Operations                                                  2
        43 Operations                                                  3
        44 Operations                                                  4
        45 Operations                                                  5

11 rows selected.

Although the above tactic looks pretty involved/complicated, without SQL*Loader executables, it's pretty much your only alternative, unless you use PL/SQL's UTL_FILE routines to open a flat file, parse the input, and produce INSERT statements in that way.

Let us know how things turn out.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[pnad]

SantaMufasa,

We are going to get SQL*Loader - it turns out that this will not be a one-time upload and will need to be done on a regular basis and not involve too much manual intervention.

Thank you for your great response, though !

 

[SantaMufasa]

If/when you get SQL*Loader, I particularly like Oracle's EXTERNAL TABLE feature that allows you to access your fixed-length flat file as though it is a regular read-only Oracle table. Then when you get a new copy of the flat file, you needn't re-run SQL*Loader, but instead, provided that the file has the same name and format, the new data is accessible immediately via a standard SELECT statement.

If this sounds interesting to you, please initiate a new thread with an inquiry re: "Creating an EXTERNAL TABLE."

Good luck,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]