APEX 3.2: Export data from .CSV to Oracle DB

Question

APEX 3.2 Oracle 11

My requirement is to create a GUI process that allows a user to load a .CSV file, which only has 4 columns and may have multiple rows. Then update the data table in the database with the corresponding data from the .CVS file.

CSV file:
ID Number:  Field Name:  Channel:   Analyst:
123456      Title         Retail    John Smith
123456      City          Retail    John Smith


Current DB:
ID Number:  Field Name:  Channel:   Analyst:
123456      Title         Retail    (null)
123456      City          (null)    (null)


After Update DB
ID Number:  Field Name:  Channel:   Analyst:
123456      Title         Retail    John Smith
123456      City          Retail    John Smith

Any ideas or links is appreciated.

Tom Tom · Accepted Answer · 2012-07-26T07:10:37

Provide a file browse item, upload your files to wwv_flow_files.

Then parse the BLOB content, see this link:

http://christopherbeck.wordpress.com/2012/04/03/parsing-a-csv-file-in-plsql/

The comments are worth a read too. For example, the Alexendria PLSQL utility library is mentioned. This library contains lots and lots of tools which are plsql based, and is worth a damn good look!

http://code.google.com/p/plsql-utils/

(Quoted from Morten's comment in the blog)

Chris,

As I pointed out, the latest version of the csv_util_pkg package can be found in the Alexandria library, and this does indeed support optionally enclosed values.

I just tested it with your example data:
 select * from table(csv_util_pkg.clob_to_csv(‘normal,”commas,,,in the
 field”,”"”enclosed”"”,”random “” double “” quotes”,”commas,,, “” and
 double “”"” quotes”‘))
And this splits the data into 5 columns:
 c001 = normal c002 = commas,,,in the field c003 = “enclosed” c004 =
 random ” double ” quotes c005 = commas,,, ” and double “” quotes
(I suppose I should remove the older code from the blog post and just direct people to download the latest library code.)

Morten

Also, further in the comments is shown how to go from blob to clob (so the posted method could be used. Credits to Christopher Beck):

  function blob_to_clob( p_lob in blob ) return clob is
     l_clob_result   clob := 'X';
     l_dest_offsset integer := 1;
     l_src_offsset  integer := 1;
     l_lang_context integer := dbms_lob.default_lang_ctx;
     l_warning      integer;
  begin
     if p_lob is not null and length(p_lob) > 0 then
        dbms_lob.converttoclob(dest_lob     => l_clob_Result,
                               src_blob     => p_lob,
                               amount       => dbms_lob.lobmaxsize,
                               dest_offset  => l_dest_offsset,
                               src_offset   => l_src_offsset,
                               blob_csid    => dbms_lob.default_csid,
                               lang_context => l_lang_context,
                               warning      => l_warning);
        if l_warning != 0 then
           dbms_output.put_line('Function blob_to_clob warning:' || l_warning);
           return null;
        end if;
        return l_clob_result;
     else
        return null;
     end if;
  exception
     when others then
        dbms_output.put_line('Function blob_to_clob error:' || SQLCODE);
        return null;
  end blob_to_clob;

You could output the columns to a global temp table, or to collections, and then do your update-logic on this. (careful with GTT and apex though. No problem as long as you're in the same session, but if you would for example make this a second process, there is no guarantee that the same session will get used!)

APEX 3.2: Export data from .CSV to Oracle DB

1 Answers