Home » SQL & PL/SQL » SQL & PL/SQL » loading data into table from csv file with spaces between data
| loading data into table from csv file with spaces between data [message #303913] |
Mon, 03 March 2008 05:04  |
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
|
Member |
|
|
Hi,
I have to read a csv file and load the data into a table.
The table layout fits the number of delimited fields in the csv file. One thing to notice is that some of the text fields have quotes that I require to ignore when loading the data.
For example….
"2008 P02",001126,"KMART HOLDING CORPORATION ",
It needs to be Loaded as 2008 P02 into field SOP_PERIOD, 001126 into field CUSTOMER_ID, KMART HOLDING CORPORATION into field CUSTOMER_NAME and so forth………
I have earlier tried to load data to table but with quotes.
Please suggest me a way to load data into table where the records have spaces between them as given above and without quotes.
Thanks,
Soni
|
|
|
|
|
|
|
|
| Re: loading data into table from csv file with spaces between data [message #303921 is a reply to message #303918] |
Mon, 03 March 2008 05:25  |
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
|
Member |
|
|
The code is big DECLARE
inputdir VARCHAR2(100) := '&1';
inputfile VARCHAR2(100) := '&2';
inputhandle UTL_FILE.FILE_TYPE;
inputbuff VARCHAR2(32767);
n_rec_seq NUMBER := 0;
v_str VARCHAR2(2000);
n_pos NUMBER := 1;
n_rec_cnt NUMBER := 0;
v_temp_inputbuff VARCHAR2(32767);
n_first_pos NUMBER :=1;
n_second_pos NUMBER ;
n_last_pos NUMBER ;
n_end_pos NUMBER ;
n_err_cnt NUMBER :=0;
n_col_cnt NUMBER :=0;
PROCEDURE insert_rpt_err AS
v_report VARCHAR2(50) := 'SOP SUPPLEMENTAL SHEET ERRORS';
v_item VARCHAR2(25);
v_dest VARCHAR2(100);
v_prog VARCHAR2(100);
v_type VARCHAR2(100);
v_size VARCHAR2(100);
v_color VARCHAR2(100);
v_detail VARCHAR2(50);
BEGIN
n_first_pos := 1;
n_second_pos := INSTR(v_temp_inputbuff,',',n_first_pos);
v_item := TRIM(REPLACE(SUBSTR(inputbuff, n_first_pos,
n_second_pos - 1),'"',''));
n_first_pos := n_second_pos ;
n_second_pos := INSTR(v_temp_inputbuff,',',n_first_pos + 1);
v_dest := TRIM(REPLACE(SUBSTR(inputbuff, n_first_pos + 1,
n_second_pos - n_first_pos -1 ),'"',''));
n_first_pos := n_second_pos ;
n_second_pos := INSTR(v_temp_inputbuff,',',n_first_pos + 1);
n_first_pos := n_second_pos ;
n_second_pos := INSTR(v_temp_inputbuff,',',n_first_pos + 1);
v_prog := TRIM(REPLACE(SUBSTR(inputbuff, n_first_pos + 1,
n_second_pos - n_first_pos -1 ),'"',''));
n_first_pos := n_second_pos ;
n_second_pos := INSTR(v_temp_inputbuff,',',n_first_pos + 1) ;
v_type := TRIM(REPLACE(SUBSTR(inputbuff, n_first_pos + 1,
n_second_pos - n_first_pos -1),'"',''));
n_first_pos := n_second_pos ;
n_second_pos := INSTR(v_temp_inputbuff,',',n_first_pos + 1) ;
v_size := TRIM(REPLACE(SUBSTR(inputbuff, n_first_pos + 1,
n_second_pos - n_first_pos -1),'"',''));
n_first_pos := n_second_pos ;
n_second_pos := INSTR(v_temp_inputbuff,',',n_first_pos + 1) ;
v_color := TRIM(REPLACE(SUBSTR(inputbuff, n_first_pos + 1,
n_second_pos - n_first_pos -1),'"',''));
n_first_pos := n_second_pos ;
n_second_pos := INSTR(v_temp_inputbuff,',',n_first_pos + 1) ;
v_detail := TRIM(REPLACE(SUBSTR(inputbuff, n_first_pos + 1,
n_second_pos - n_first_pos -1),'"',''));
insert_info_rpt(v_report, v_item, v_dest, v_prog, v_type, v_size,
v_color, v_detail, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL
);
n_err_cnt := n_err_cnt + SQL%ROWCOUNT;
END insert_rpt_err;
BEGIN
--Truncate the sop_supplemental_data table prior to load
DBMS_OUTPUT.PUT_LINE('..Truncating sop_supplemental_data table');
EXECUTE IMMEDIATE 'TRUNCATE TABLE zrobi10.sop_supplemental_data';
--Opening .csv file
inputhandle := UTL_FILE.FOPEN(inputdir, inputfile, 'r');
n_rec_seq := 0;
<<MAIN_LOOP>> LOOP
BEGIN
UTL_FILE.GET_LINE(inputhandle, inputbuff);
inputbuff := TRIM(inputbuff);
n_rec_seq := n_rec_seq + 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT MAIN_LOOP;
END;
BEGIN
v_temp_inputbuff := inputbuff;
n_pos := 1;
FOR i in 1..63
LOOP
n_first_pos := INSTR(v_temp_inputbuff,'"',n_pos) ;
IF n_first_pos = 0 then
EXIT;
END IF;
n_second_pos := INSTR(v_temp_inputbuff,'"',n_first_pos+1);
v_temp_inputbuff:= SUBSTR(v_temp_inputbuff,1,n_first_pos)||
REPLACE(SUBSTR(v_temp_inputbuff,
n_first_pos + 1,
n_second_pos - n_first_pos
),'"',' ') ||
SUBSTR(v_temp_inputbuff,n_second_pos + 1);
n_pos := n_second_pos + 1;
END LOOP;
FOR i in 2..15
LOOP
n_pos := INSTR(v_temp_inputbuff,',',n_pos + 1) ;
END LOOP;
v_str := TRIM(SUBSTR(inputbuff,1,n_first_pos - 1 ))||
TRIM(SUBSTR(inputbuff,n_pos + 1,
n_second_pos - n_pos - 1));
n_first_pos := n_second_pos ;
n_second_pos := INSTR(v_temp_inputbuff,'"',n_first_pos + 1) ;
n_first_pos := INSTR(v_temp_inputbuff,'"',n_second_pos + 1) ;
n_last_pos := n_pos;
FOR i in 16..63
LOOP
n_last_pos := INSTR(v_temp_inputbuff,'"',n_last_pos + 1);
END LOOP;
n_end_pos := INSTR(v_temp_inputbuff,'"',n_last_pos + 1) ;
IF n_end_pos = 0 THEN
v_str := v_str || REPLACE(REPLACE(REPLACE(REPLACE(
SUBSTR(v_temp_inputbuff,
n_first_pos +1)
,'"', ' ')
,' ', ' ')
,'(', '-')
, ')','');
ELSE
v_str := v_str || REPLACE(REPLACE(REPLACE(REPLACE(
SUBSTR(v_temp_inputbuff,
n_first_pos +1,
n_end_pos - n_first_pos - 1)
,'"',' ')
,' ',' ')
,'(','-')
, ')','');
END IF;
dbms_output.put_line('v_str:'||v_str);
EXECUTE IMMEDIATE 'INSERT INTO zrobi10.sop_supplemental_data
(sop_period, customer_id, customer_name,
channel, business, prod_group,
program, prod_type, cloth_type,
ord_pol, demand_type, make_buy,
facility, country, data_source,
jan_units_cy, jan_amount_cy, feb_units_cy,
feb_amount_cy, mar_units_cy, mar_amount_cy,
apr_units_cy, apr_amount_cy, may_units_cy,
may_amount_cy, jun_units_cy, jun_amount_cy,
jul_units_cy, jul_amount_cy, aug_units_cy,
aug_amount_cy, sep_units_cy, sep_amount_cy,
oct_units_cy, oct_amount_cy, nov_units_cy,
nov_amount_cy, dec_units_cy, dec_amount_cy,
jan_units_cy, jan_amount_cy, feb_units_cy,
feb_amount_cy, mar_units_cy, mar_amount_cy,
apr_units_cy, apr_amount_cy, may_units_cy,
may_amount_cy, jun_units_cy, jun_amount_cy,
jul_units_cy, jul_amount_cy, aug_units_cy,
aug_amount_cy, sep_units_cy, sep_amount_cy,
oct_units_cy, oct_amount_cy, nov_units_cy,
nov_amount_cy, dec_units_cy, dec_amount_cy)
VALUES ('||v_str||')';
EXCEPTION WHEN OTHERS THEN
insert_rpt_err;
END;
END LOOP MAIN_LOOP;
n_rec_cnt := n_rec_cnt + n_rec_seq;
UTL_FILE.FCLOSE(inputhandle);
COMMIT;
END;
Can I attach the csv file! I do not have to use sql*Loader.
[Updated on: Mon, 03 March 2008 05:27] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: loading data into table from csv file with spaces between data [message #303930 is a reply to message #303927] |
Mon, 03 March 2008 05:47 |
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
|
Member |
|
|
Michel,
I am going to write a mail to ask if I can do it through loader/external table. Before this please let me know if I can really do this using them as I am new to loader/external table.
My csv file has data like :
For example….
"2008 P02",001126,"KMART HOLDING CORPORATION ",
It needs to be Loaded as 2008 P02 into field SOP_PERIOD, 001126 into field CUSTOMER_ID, KMART HOLDING CORPORATION into field CUSTOMER_NAME and so forth………
Please let me know if we can easily o this using external table/loader.....so that I can write a mail for specifications.
I just want to be sure before saying that I can do this using sql*loader.
Thanks,
Soni
[Updated on: Mon, 03 March 2008 06:19] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Aug 26 19:31:35 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
