CODEWITHSUNDEEP
oracle-databasecsvplsqloracle-apexoracle19c
Basudev Singh
Basudev Singh

Reputation: 69

Reading a csv file with PL/SQL

I have a csv file and i want to load it in oracle apex and when i click submit it must read through the csv file and return all rows in the csv what have null inside.

So i have a table name csvtest and it has fields id, name and age. when i upload a csv file into oracle apex using a file browser with this these fields in it, i want to read through it and find all the rows that have NULL in the column AGE using plsql and return them else if they none contain null then successfully upload the file

here is the code i have so far, i do not have any way of reading the column

DECLARE
    F_FILE UTL_FILE.FILE_TYPE;
    V_LINE VARCHAR2 (1000);
    V_ID NUMBER(10);
    V_NAME VARCHAR2(70);
    V_AGE NUMBER(2);

BEGIN
    F_FILE := UTL_FILE.FOPEN ('TEMP.CSV', 'R', 32767);
        IF UTL_FILE.IS_OPEN(F_FILE) THEN
            LOOP
                BEGIN
                    UTL_FILE.GET_LINE(F_FILE, V_LINE, 32767);
                    IF V_LINE IS NULL THEN
                    EXIT;
                    END IF;
                        V_ID := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 1);
                        V_NAME := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 2);
                        V_AGE := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 3);
                        INSERT INTO EMP_DEPT VALUES(V_ID, V_NAME, V_AGE);
                    COMMIT; 
                    EXCEPTION
                        WHEN NO_DATA_FOUND THEN
                        EXIT;
                END;
            END LOOP;
        END IF;
UTL_FILE.FCLOSE(F_FILE);
END;
/

Upvotes: 2

Views: 11476

Answers (3)

Dantel35
Dantel35

Reputation: 135

Some links in the answers do not work anymore, so just to give another example without the need of a second table, including how to get from your text(VARCHAR2) to the BLOB you need:

select line_number, col001, col002, col003, col004, col005, 
                    col006, col007, col008, col009, col010
                    -- more columns (col011 to col300) can be selected here.
  from 
       table( apex_data_parser.parse(
                  p_content                     =>  utl_raw.cast_to_raw(:PX_PAGE_ITEM_WITH_CSV),
                  p_add_headers_row             => 'Y',
                  p_max_rows                    => 1000,
                  p_skip_rows                   => 2,
                  p_csv_col_delimiter           => ';',
                  p_store_profile_to_collection => 'FILE_PARSER_COLLECTION',
                  p_file_name                   => 'tst.csv' ) )

You can do this if your CSV fits into one APEX page item.

Upvotes: 1

Salim Hlayel
Salim Hlayel

Reputation: 371

One way to do this is to use APEX_DATA_PARSER API before you save the data to your table. The PARSE function enables you to parse XML, XLSX, CSV or JSON files and returns a generic table of the following structure:

LINE_NUMBER COL001 COL002 COL003 COL004 ... COL300

Sample code can be something similar to this:

select line_number, col001,col002,col003,col004,col005,col006,col007,col008 
   from table( 
              apex_data_parser.parse(
                  p_content         => {BLOB containing CSV file},
                  p_file_name       => 'test.CSV') );

Then you can easily determine the values of the columns based on their positions and decide whether to save the data to the table or not.

Upvotes: 1

Connor McDonald
Connor McDonald

Reputation: 11616

Since you're using APEX, a very easy way is to use the provided APEX_DATA_PARSER package. Just load your CSV into a blob (or into any table that has a blob column), and then it is a simple (for example)

select line_number, col001, col002, col003, col004, col005, 
                    col006, col007, col008, col009, col010
                    -- more columns (col011 to col300) can be selected here.
  from apex_application_temp_files f, 
       table( apex_data_parser.parse(
                  p_content                     => f.blob_content,
                  p_add_headers_row             => 'Y',
                  --
                  p_max_rows                    => 5,
                  p_skip_rows                   => 2,
                  p_csv_col_delimiter           => ';',
                  --
                  p_store_profile_to_collection => 'FILE_PARSER_COLLECTION',
                  p_file_name                   => f.filename ) ) p
 where f.name = :PX_FILE

It can load CSV, JSON, XML, Excel etc etc...

Full docs here

https://docs.oracle.com/en/database/oracle/application-express/19.1/aeapi/PARSE-Function.html#GUID-B815CF74-C469-4F78-9433-643D1339E930

and some more examples on the Oracle APEX blog

https://blogs.oracle.com/apex/super-easy-csv-xlsx-json-or-xml-parsing-about-the-apex_data_parser-package

Upvotes: 3

Related Questions