Reading a CSV file becomes more complicated when it includes quoation marks with combination of commas inside a column. To overcome that issues we can use string shunting method which realy works in certain cases.
Following excercise will help you to extract a CSV file by shunting every data item after a read from the string. You must know which columns are provided with quotation makrs.
Step 1: Create a CSV file
create a csv file using any text editor and it should be saved with extension like filename.csv
copy and paste following text in the text file and save it.
1233222, JACK, "123 UNKNOWN STREET, UNKNOWN TOWN", 12-DEC-2020, "THE CUSTOMER IS TAGGED AS VIP CUSTOMER", 200000000
1233222, TOM, "321 UNKNOWN STREET, UNKNOWN TOWN", 12-MAR-2010, "", 7000
1233222, JERRY, "551 UNKNOWN STREET, UNKNOWN TOWN", 12-OCT-2009, "THE ACCOUNT HOLDER MAY APPLY FOR LOAN, AS THE CUSTOMER IS AN INVESTOR", 150000
Step 2: Create a database directory
C:\ mkdir csvfiles
C:\SQLPLUS / AS SYSDBA
SQL> DROP DIRECTORY CSV_FILES;
SQL> CREATE DIRECTORY CSV_FILES AS 'C:\csvfiles';
SQL> GRANT read, write ON DIRECTORY CSV_FILES TO HR ;
Step 3: Create and compile following procedure in HR user
SQL> CONNECT HR/HR@ORCL
CREATE OR REPLACE PROCEDURE READ_WRITE_CSV (
P_FILE_LOCATION IN VARCHAR2,
P_FILENAME IN VARCHAR2,
P_READ_WRITE_MODE IN VARCHAR2
) as
V_EXTRACT_FILE UTL_FILE.FILE_TYPE;
V_EXTRACT_LOCATION VARCHAR2(300);
V_READ_WRITE_MODE VARCHAR2(1);
V_LENGTH number;
V_FILE_LINE varchar2(4000);
account_id varchar2(500);
customer_name varchar2(500);
customer_address varchar2(500);
account_open_date varchar2(500);
comments varchar2(500);
opening_balance varchar2(500);
BEGIN
V_EXTRACT_FILE := UTL_FILE.fopen(P_FILE_LOCATION,P_FILENAME,P_READ_WRITE_MODE);
--=================
-- string format
--=================
-- account_id, customer name, customer address, account open date, comments, opening balance
LOOP
utl_file.get_line (V_EXTRACT_FILE,V_FILE_LINE);
-- account id
V_LENGTH := (INSTR(V_FILE_LINE , ',', 1, 1)) ;
account_id := substr(V_FILE_LINE , 1, V_LENGTH -1) ;
V_FILE_LINE := SUBSTR( V_FILE_LINE, V_LENGTH+1 );
--customer name
V_LENGTH := (INSTR(V_FILE_LINE , ',', 1, 1)) ;
customer_name := substr(V_FILE_LINE , 1, V_LENGTH -1) ;
V_FILE_LINE := SUBSTR( V_FILE_LINE, V_LENGTH+1 );
--customer address
V_LENGTH := (INSTR(V_FILE_LINE , '"', 1, 2))+1 ;
customer_address := substr(V_FILE_LINE , 1, V_LENGTH -1) ;
V_FILE_LINE := SUBSTR( V_FILE_LINE, V_LENGTH+1 );
--account open date
V_LENGTH := (INSTR(V_FILE_LINE , ',', 1, 1)) ;
account_open_date := substr(V_FILE_LINE , 1, V_LENGTH -1) ;
V_FILE_LINE := SUBSTR( V_FILE_LINE, V_LENGTH+1 );
--comments
V_LENGTH := (INSTR(V_FILE_LINE , '"', 1, 2))+1 ;
comments := substr(V_FILE_LINE , 1, V_LENGTH -1) ;
V_FILE_LINE := SUBSTR( V_FILE_LINE, V_LENGTH+1 );
--openning balance
opening_balance := substr(V_FILE_LINE , 1 ) ;
dbms_output.put_line(customer_name ||'-'||
customer_address ||'-'||
account_open_date ||'-'||
comments ||'-'||
opening_balance
);
end loop;
EXCEPTION
WHEN NO_DATA_FOUND THEN
COMMIT;
UTL_FILE.fclose(V_EXTRACT_FILE);
dbms_output.put_line('OK') ;
WHEN UTL_FILE.INVALID_PATH THEN
dbms_output.put_line('NOT OK - INVALID PATH') ;
WHEN OTHERS THEN
UTL_FILE.fclose(V_EXTRACT_FILE);
DBMS_OUTPUT.PUT_LINE(SQLERRM) ;
END;
/
SQ>
Step 4: Execute procedure to read CSV file
SQL> SET SERVEROUTPUT ON
SQL> EXECUTE READ_WRITE_CSV ('CSV_FILES', 'customer.csv', 'R' ) ;
