Solved

How do I upload pipe delimited text file to oracle database

Posted on 2013-01-10
7
3,756 Views
Last Modified: 2013-01-14
Hi,

I've created a new database and need to import a bunch of data to the tables.  I'm looking for a method to do it through a script so that I can setup a process to upload all data going forward.  

I'm really new to oracle, so I really don't know too much about how to go about doing this...  But I've been recommended to use the SQL Loader.  Would this do the job that I'm looking for?  If so, could you give some examples or someplace I can get more details on how to go about loading these files?  thanks!
0
Comment
Question by:iamnamja
7 Comments
 
LVL 17

Expert Comment

by:Dushan911
ID: 38765706
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38765729
0
 

Author Comment

by:iamnamja
ID: 38765739
Just out of curiosity... why won't the SQL Loader do what I'm looking for?  

Also, I've found that page that you mentioned on loader faq...

I can't seem to find the loader file?  When I do the following, I get a blank response:
sql> Show Parameters control_files
OR I get an error "table or view does not exist" when i do this
sql>select value from V$PARAMETER where name = 'control_files'

Any idea?
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 38765758
- i would recommend you to download the free oracle SQL Developer and use the tool to upload the data:

http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
- manual: http://docs.oracle.com/cd/E11882_01/doc.112/e12152/intro.htm

- what is you text file format? i would suggest prepare it in .csv (comma delimetered) format. you need to create the destination table first in Oracle, then use SQL Developer to select the text file. then mapped the source column (from the text file) to the destination column (Oracle table). here are some links to how to use SQL Developer to load data from a file into Oracle.

- http://database.blogs.webucator.com/2011/02/27/importing-data-using-oracle-sql-developer/
- http://www.thatjeffsmith.com/archive/2012/04/how-to-import-from-excel-to-oracle-with-sql-developer/
0
 
LVL 16

Accepted Solution

by:
Swadhin Ray earned 500 total points
ID: 38765816
Few days back I have posted similar kind of task on my blog :


Hope this might help you but this I have done from Unix . But as other experts have suggested I have also used the method of SQL Loader.

But in another method you can write your own function and can load it.

Here are the steps:

1) First create a directory and grant read , write , execute to the user from where you want to access the flat files and load it.

2) Write a generic function to load PIPE delimited flat files:

 
CREATE OR REPLACE FUNCTION TABLE_LOAD ( p_table     in varchar2,
                     p_dir       in varchar2 DEFAULT 'YOUR_DIRECTORY_NAME',
                     P_FILENAME  in varchar2,
                     p_ignore_headerlines   IN INTEGER DEFAULT 1,
                     p_delimiter in varchar2 default '|',
                     p_optional_enclosed in varchar2 default '"' )
return number
is
/*************************************************************************** 
--    FUNCTION TABLE_LOAD
--    PURPOSE: Load the flat files i.e. only text files to Oracle
--             tables. 
--             This is a generic function which can be used for 
--             importing any text flat files to oracle database.
--    PARAMETERS:
--         P_TABLE
--            Pass name of the table for which import has to be done.
--
--         P_DIR
--            Name of the directory where the file is been placed. 
--            Note: The grant has to be given for the user to the directory
--                  before executing the function 
--
--         P_FILENAME
--            The name of the flat file(a text file)
--
--         P_IGNORE_HEADERLINES
--            By default we are passing 1 to skip the first line of the file 
--            which are headers on the Flat files. 
--
--         P_DELIMITER
--            Dafault "|" pipe is been passed.
--
--         P_OPTIONAL_ENCLOSED
--            Optionally enclosed by ' " ' are been ignored.
--

--         AUTHOR:
--                 Slobaray
**************************************************************************/
    l_input         utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    l_lastLine      varchar2(4000);
    l_cnames        varchar2(4000);
    l_bindvars      varchar2(4000);
    l_status        integer;
    l_cnt           number default 0;
    l_rowCount      number default 0;
    l_sep           char(1) default NULL;
    L_ERRMSG        varchar2(4000);
    V_EOF              BOOLEAN := false;

begin


	l_cnt := 1;
	for TAB_COLUMNS in (
	select column_name, data_type from user_tab_columns where table_name=p_table order by column_id
	) loop
		l_cnames := l_cnames || tab_columns.column_name || ',';
        l_bindvars := l_bindvars || case when tab_columns.data_type in ('DATE', 'TIMESTAMP(6)') then 'to_date(:b' || l_cnt || ',''YYYY-MM-DD HH24:MI:SS''),' else ':b'|| l_cnt || ',' end;
        
	l_cnt := l_cnt + 1;
	end loop;
	l_cnames := rtrim(l_cnames,',');
	L_BINDVARS := RTRIM(L_BINDVARS,',');
 
    L_INPUT := UTL_FILE.FOPEN( P_DIR, P_FILENAME, 'r' );
    IF p_ignore_headerlines > 0
    THEN
        BEGIN
        FOR i IN 1 .. p_ignore_headerlines
            LOOP
                UTL_FILE.get_line(l_input, l_lastLine);
            END LOOP;
        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                v_eof  := TRUE;
        end;
    END IF;
    
    if not v_eof then
    	dbms_sql.parse(  l_theCursor, 'insert into ' || p_table || '(' || l_cnames || ') values (' || l_bindvars || ')', dbms_sql.native );

    	loop
        	begin
            	utl_file.get_line( l_input, l_lastLine );
        	exception
            	when NO_DATA_FOUND then
                	exit;
        	end;

        	if length(l_lastLine) > 0 then
        		for i in 1 .. l_cnt-1
        		LOOP
            		dbms_sql.bind_variable( l_theCursor, ':b'||i,
            			ltrim(rtrim(rtrim(
            				regexp_substr(l_lastLine,'([^|]*)(\||$)',1,i),p_delimiter),p_optional_enclosed),p_optional_enclosed));
        		end loop;

        		begin
            		l_status := dbms_sql.execute(l_theCursor);
            		l_rowCount := l_rowCount + 1;
        		exception
            		when OTHERS then
                		L_ERRMSG := SQLERRM;
                		insert into BADLOG ( TABLE_NAME, ERRM, data, ERROR_DATE ) 
                values ( P_TABLE,l_errmsg, l_lastLine ,systimestamp );
        		end;
        	end if;
    	end loop;

    	dbms_sql.close_cursor(l_theCursor);
    	utl_file.fclose( l_input );
    	commit;
    end if;
insert into IMPORT_HIST (FILENAME,TABLE_NAME,NUM_OF_REC,IMPORT_DATE)
values ( P_FILENAME, P_TABLE,l_rowCount,sysdate );

UTL_FILE.FRENAME(
        P_DIR,
        P_FILENAME,
        P_DIR,
        REPLACE(P_FILENAME,
                '.txt',
                '_' || TO_CHAR(SYSDATE, 'DD_MON_RRRR_HH24_MI_SS_AM') || '.txt'
               ));
    commit; 
    RETURN L_ROWCOUNT;
end TABLE_LOAD;

/

Open in new window


Note: when you run the function then it will also modify the source flat file with timestamp , so that we can have the track like which file was loaded .

3) Check if the user is having UTL_FILE privileges or not :

SQL> SELECT OWNER,
OBJECT_TYPE
FROM ALL_OBJECTS
WHERE OBJECT_NAME = 'UTL_FILE'
AND OWNER         =<<USER>>;

If the user is not having the privileges then grant “UTL_FILE” to user from SYS user:

SQL> GRANT EXECUTE ON UTL_FILE TO <<USER>>;


4) In the function I have used two tables like:
import_hist table and badlog table to track the history of the load and another to check the bad log if it occurs while doing the load .

Under the same user create an error log table to log the error out records while doing the import:

SQL> CREATE TABLE badlog
(
errm VARCHAR2(4000),
data VARCHAR2(4000) ,
error_date TIMESTAMP
);

Open in new window


Under the same user create Load history table to log the details of the file and tables that are imported with a track of records loaded:
       
   SQL> create table IMPORT_HIST 
(
FILENAME varchar2(200),
TABLE_NAME varchar2(200),
NUM_OF_REC number,
IMPORT_DATE DATE
);

Open in new window



5) Finally run the PLSQL block and check if it is loading properly or not if not then check the badlog:

Execute the PLSQL block to import the data from the USER:

SQL> declare
  P_TABLE varchar2(200):=<<table name>>;
  P_DIR varchar2(200):=<<Directory name >>;
  P_FILENAME VARCHAR2(200):=<<File name>>;
  v_Return NUMBER;
BEGIN
 v_Return := TABLE_LOAD(
    P_TABLE => P_TABLE,
    P_DIR => P_DIR,
    P_FILENAME => P_FILENAME,
    P_IGNORE_HEADERLINES => P_IGNORE_HEADERLINES,
    P_DELIMITER => P_DELIMITER,
    P_OPTIONAL_ENCLOSED => P_OPTIONAL_ENCLOSED
  );
 
DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
end; 

Open in new window



6) Once the PLSQL block is been executed then check for any error log table and also the target table if the records are been successfully imported or not.
0
 
LVL 12

Expert Comment

by:praveencpk
ID: 38766108
>>I can't seem to find the loader file?  When I do the following, I get a blank response:
sql> Show Parameters control_files

sql>select value from V$PARAMETER where name = 'control_files' <<<

I think the user you have logged in dosen't have privileges to view v$ views. you need to login as sys/system user to access the above parameters.

for loading the data you can go with oracle utiliy sql*loader or SQL Developer tool as mention in above post.
0
 
LVL 31

Expert Comment

by:awking00
ID: 38767227
iamnamja,
Sqlloader is run from a command prompt, not from sqlplus. Take a look at this link -
http://docs.oracle.com/cd/B10500_01/server.920/a96652/part2.htm
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now