Wednesday, October 22, 2008

To put data from csv file to a custom table

Following is the script

PROCEDURE load_csv_2_cust_table ( p_file_name IN VARCHAR2, p_file_path IN VARCHAR2 ) IS

l_file UTL_FILE.file_type;
l_file_line VARCHAR2 (4000);
temp_line VARCHAR2(4000);
csv_col VARCHAR(4000);
comma_pos1 NUMBER(5);
colon_pos1 NUMBER(5);
comma_pos2 NUMBER(5);
colon_pos2 NUMBER(5);
temp_str VARCHAR(4000);
table_counter NUMBER :=0;

BEGIN
l_file := UTL_FILE.fopen (p_file_path, p_file_name, 'r',32767);

IF UTL_FILE.is_open (l_file) THEN
LOOP
BEGIN
l_file_line := NULL;
UTL_FILE.get_line (l_file, l_file_line);
-- parse the delimited line
IF l_file_line IS NOT NULL
THEN gv_data_tbl.DELETE;
temp_line := l_file_line;
table_counter := 0;
LOOP
-- if temp line is null , exit out of for loop
IF trim(temp_line) IS NULL then EXIT ; END IF;
comma_pos1 := NVL(INSTR(temp_line,','),0);
colon_pos1 := NVL(INSTR(temp_line,'"'),0);
comma_pos2 := NVL(INSTR(temp_line,',',1,2),0);
colon_pos2 := NVL(INSTR(temp_line,'"',1,2),0);
-- only one column . no comma , colon
IF comma_pos1 = 0 THEN
temp_str := temp_line;
temp_line := null;
END IF;

-- no colon , but comma is there
IF colon_pos1 = 0 AND comma_pos1 <> 0
THEN
temp_str := SUBSTR(temp_line,1,comma_pos1-1);
temp_line := SUBSTR(temp_line,comma_pos1+1);
END IF;
-- comma and colon , comma comes first
IF colon_pos1 <> 0 AND comma_pos1 <>0 AND colon_pos1 > comma_pos1
THEN
temp_str := SUBSTR(temp_line,1,comma_pos1-1);
temp_line := SUBSTR(temp_line,comma_pos1+1);
END IF;

-- comma and colon , colon comes first
IF colon_pos1 <> 0 AND comma_pos1 <>0 AND colon_pos1 < comma_pos1
THEN
temp_str := SUBSTR(temp_line,2,colon_pos2-2);
temp_line := SUBSTR(temp_line,colon_pos2+2);
END IF;
table_counter := table_counter +1;
gv_data_tbl(table_counter):=temp_str;
END LOOP;
INSERT INTO custom_table(col1, col2, col3, col4) VALUES
(gv_data_tbl(1),gv_data_tbl(2),gv_data_tbl(3),gv_data_tbl(4));
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN EXIT;

END;
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN dbms_output.put_line(SQLERRM);
RAISE;
END ;

1 comment:

oracle fusion said...

Nice article and explanation is good,Thank you for sharing your experience on oracle Apps and fusion articles.you have clearly explained about the process thus it is very much interesting and i got more information from your blog.

Oracle Fusion Training Institute