Importing Text File

[pheffley]

I have a delimited text file that I need to bring into my database. In the past, I've used ETL tools, but I don't have access to those anymore. Can this be done through enterprise manager, SQL*Plus, Command Prompt? I just have a stand-alone version of 9i.

Thanks,

Paul

 

[alexhu]

Use SQL*Loader - the docs are in the utilities manual - just what you need

Alex

 

[SantaMufasa]

Pheffley,

There are multiple methods available to you in Oracle to access a delimited text file. These methods range from crude to sophisticated. Progressing along that continuum, I'll present some alternatives to you:

Method 1 -- Transform your delimited file into valid SQL INSERT statements:

1) Use an editor (MS-Word, for example) that recognises carriage-return characters:

a) Ensure that all columns are delimited with single quotes and commas: Example 'Hunt','David','value-3','value-4',...

b) Replace all carriage returns with leading and trailing values for a valid Oracle INSERT statement. In Word, for example, following your invocation of a "global-replace" command ("Edit"..."Replace"), you would:
Find what: ^p
Replace with: );^pINSERT INTO table_name VALUES (
[Replace All]

c) Check the first and last lines of your delimited file to ensure that they comply with correct INSERT-command behaviour.

d) Save the file.

2) Execute the script your just created. From SQL*Plus: "@ScriptName.sql". The script should then insert all rows into the pre-existing table you specified in the "INSERT INTO..." statements.

Method 2 -- Use SQL*Loader. This is Oracle's method importing flat files into Oracle tables:

1) You can read up on SQL*Loader's capabilities in either Oracle documentation or on-line. If you enter "sqlldr" at your o/s prompt, a "Help" screen appears with SQL*Loader parameters and explanations. Or,

2) You can use my SQL*Loader control-file generator that creates a SQL*Loader command file ("temp.fil") that creates the commands necessary to load your delimited file using SQL*Loader. It presumes a) that your table structure already exists under your Oracle-user ownership, and b) you use double quotes (") to surround individual values and commas between values:

Here is my "GenLoad.sql" script code:

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. Neither the 
REM author nor Dasages, LLC makes any warranty regarding this 
REM script's fitness for any industrial application or purpose nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
accept x prompt "Enter the table to load: "
accept y prompt "Enter the flat file to read: "
set trimspool on
set verify off
set echo off
set feedback off
set heading off
set pagesize 0
spool temp.fil
select 'load data' from dual;
select 'infile "&y" replace' from dual;
select 'into table &x' from dual;
select 'fields terminated by ''"''' from dual;
select '(' from dual;
select decode (rownum,1,null,',') || column_name 
  from user_tab_columns
 where table_name = upper('&x');
select ')' from dual;
prompt
spool off
prompt
prompt At the DOS prompt, enter:
prompt sqlldr <username>/<password> temp.fil
prompt

Here is a sample invocation of GenLoad.sql:

@genload
Enter the table to load: s_dept
Enter the flat file to read: tempascii.sql
load data
infile "tempascii.sql" replace
into table s_dept
fields terminated by '"'
(
ID
,NAME
,REGION_ID
)

At the DOS prompt, enter:
sqlldr <username>/<password> temp.fil

Here is the execution of SQL*Loader using the generated control file, "temp.fil":

D:\oracle\ora92\bin\sqlldr test/test temp.fil

SQL*Loader: Release 9.2.0.4.0 - Production on Tue Nov 30 09:42:47 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 12

D:\

Method 3 -- Using Oracle 9i, you can cause Oracle to treat your delimited flat file as a read-only table. Following is code that treats the same flat file that I used in Method 2, above, as an Oracle table:

(As a DBA):
create directory MyFlatFiles as 'd:\dhunt\sqldba';

Directory created.

grant read on directory MyFlatFiles to public; -- or to specific user(s);

Grant succeeded.

(As regular user):
create table dept_ext
(  id        number,
   name      varchar2(50),
   region_id number
)
organization external
(  type oracle_loader
   default directory MyFlatFiles
   access parameters
   (records delimited by newline
    fields terminated by ','
   )
location ('TempASCII.sql')
)
reject limit 1000;

Table created.

(Note: For simplicity, I removed the quotes surrounding the values in TempASCII.sql and used just commas to delimit values, thus the "fields terminated by ','" entry.)

col a heading "ID" format 99
col b heading "NAME" format a14
col c heading "REGION" format 9
select id a, name b, region_id c from dept_ext;

 ID NAME           REGION
--- -------------- ------
 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
 50 Administration      1

12 rows selected.

So, there you have it: Three Methods to access flat files in Oracle. Let us know if you have questions...let us know your findings and results.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:14 (30Nov04) UTC (aka "GMT" and "Zulu"),
@ 10:14 (30Nov04) Mountain Time

 

[pheffley]

I do have a quick question...If I use the standard SQL*Loader method (using a control file with data), how do I handle records that aren't terminated by anything? Would this (NEWLINE) work? If not, how is it handled?

LOAD DATA
INFILE *
INTO TABLE TEMP
RECORDS TERMINATED BY NEWLINE
FIELDS TERMINATED BY ','
BEGINDATA
####A,########AA,AAAAAAAA,AAAAA,A,AAAAA,AAAA,AAAAA

I really appreciate the tips!!

Paul

 

[SantaMufasa]

Pheffley,

Yes, and "RECORDS TERMINATED BY NEWLINE" is also the default. When you say, "RECORDS TERMINATED BY NEWLINE", that means that the "record separator" is the native character combination for your platform, for example: "<cr><lf>" for Windows and "<lf>" for *nix.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 19:54 (30Nov04) UTC (aka "GMT" and "Zulu"),
@ 12:54 (30Nov04) Mountain Time

 

[pheffley]

When I tried:

LOAD DATA
INFILE *
INTO TABLE TEMP
RECORDS TERMINATED BY NEWLINE
FIELDS TERMINATED BY ','
BEGINDATA
####A,########AA,AAAAAAAA,AAAAA,A,AAAAA,AAAA,AAAAA

It failed because it was expecting "(" after the 'INTO TABLE' statement.

Also, how and where do you specify which database/schema to work with?

If you haven't guessed, this is my first time trying to do this, so I am not familiar with structure...

Thanks again

Paul

 

[SantaMufasa]

Paul,

You may specify the schema explicitly by saying "...INTO TABLE myschema.TEMP...". If you do not specify the schema, then it defaults to the schema of your login when you invoked SQL*Loader: "sqlldr schema/password...".

Does the receiving table exist prior to your SQL*Loader session? If so, rather than trying to debug the misplaced "(", I recommend your logging into SQL*Plus and using my "Method 2" script, above (provided, of course, that the order of data values of your delimited flat file matches the order of data of your target table.

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 20:17 (30Nov04) UTC (aka "GMT" and "Zulu"),
@ 13:17 (30Nov04) Mountain Time