2012-01-06 Created by Baoxinjin
I. Summary
How to read and write CSV
1. Write a CSV
Step1. Create a Test Catalog
Step2. Write a CSV file
Step3. View CSV file
2. Read CSV
Step1. Create a test table
Step2. Read CSV file
Step3. View Table
Ii. writing a CSV file
Step1. Create a Test Catalog
-- Create a Test catalog CREATE OR REPLACE as ' /home/oracle/bxjcsv ' - - Directory permission assignment GRANTREADon to public;
Step2. Write a CSV file
DECLAREf_emp Utl_file. File_type; CURSORcur_emp is SELECTfirst_name,last_name,email,phone_number,salary fromHR. EMPLOYEES
WHERE rownum<=10; Rec_emp cur_emp%ROWTYPE;BEGINf_emp:=Utl_file. FOPEN ('Bxjcsv','emp_dept. CSV','W',32767); forRec_empinchcur_emp LOOP utl_file. PUT (F_emp, rec_emp.first_name); Utl_file. PUT (F_emp,',' ||rec_emp.last_name); Utl_file. PUT (F_emp,',' ||rec_emp.email); Utl_file. PUT (F_emp,',' ||rec_emp.phone_number); Utl_file. PUT (F_emp,',' ||rec_emp.salary); Utl_file. New_line (f_emp); ENDLOOP; Utl_file. FCLOSE (f_emp);END;
Step3. View CSV file
Third, read the CSV file
Step1. Create a test table
CREATE TABLE VARCHAR2 (VARCHAR2 (VARCHAR2 ( VARCHAR2 (number)
Step2. Read CSV file
DECLAREf_emp Utl_file. File_type; V_lineVARCHAR2( +); V_first_name Hr.bxj_employees.first_name%TYPE; V_last_name Hr.bxj_employees.last_name%TYPE; V_email Hr.bxj_employees.email%TYPE; V_phone_number Hr.bxj_employees.phone_number%TYPE; V_salary hr.bxj_employees.salary%TYPE;BEGINf_emp:=Utl_file. FOPEN ('Bxjcsv','emp_dept. CSV','R'); IFUtl_file.is_open (f_emp) ThenLOOPBEGINUtl_file. Get_line (F_emp, V_line, +); IFV_line is NULL Then EXIT; END IF; V_first_name:=Regexp_substr (V_line,'[^,]+',1,1); V_last_name:=Regexp_substr (V_line,'[^,]+',1,2); V_email:=Regexp_substr (V_line,'[^,]+',1,3); V_phone_number:=Regexp_substr (V_line,'[^,]+',1,4); V_salary:=Regexp_substr (V_line,'[^,]+',1,5); INSERT intohr.bxj_employeesVALUES(v_first_name,v_last_name,v_email,v_phone_number,v_salary); COMMIT; EXCEPTION whenNo_data_found Then EXIT; END; ENDLOOP; END IF; Utl_file. FCLOSE (f_emp);END;
Step3. View Table
Thanks and regards
Plsql_plsql read/write CSV file mode (case)