Home » SQL & PL/SQL » SQL & PL/SQL » Read CSV file in PLSQL (Windows XP)
Read CSV file in PLSQL [message #402565] Mon, 11 May 2009 09:04 Go to next message
scorpio4frenz
Messages: 48
Registered: October 2008
Member
I'm working on a system that allows users to upload a Excel (XLS) file via a PLSQL cartridge. I've a java servlet that converts the Excel file into CSV format and stores in the database as a blob.

I'm trying to create a procedure that can read the contents of the CSV file and display them to the user in a tabular format (will be done using cartridges). I'm getting confused about using collections for this purpose.

I'm thinking of using something like this example:
declare
type sr_row is table of varchar2(100);
type sr_data is table of sr_row;
sr_rec sr_data;
 
begin
  sr_rec := new sr_data(sr_row('AA','2700','614','08/08','1','0'),
                        sr_row('BA','6418','613','08/09','1','2')
                        );
  for i in 1..sr_rec.count loop
     for j in 1..sr_rec(i).count loop
      dbms_output.put_line (i||' '||j||' cell='||sr_rec(i)(j));
     end loop;
  end loop;
end;


I'm getting confused about how to use this method to read row-by-row of the CSV file. (I'll be using DBMS_LOB.converttoclob to convert the blob into clob).

Any help appreciated.
Re: Read CSV file in PLSQL [message #402571 is a reply to message #402565] Mon, 11 May 2009 09:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I'm trying to create a procedure that can read the contents of the CSV file and display them to the user in a tabular format

Use an external table.

Regards
Michel
Re: Read CSV file in PLSQL [message #402574 is a reply to message #402571] Mon, 11 May 2009 09:49 Go to previous messageGo to next message
scorpio4frenz
Messages: 48
Registered: October 2008
Member
Michael,

Thanks for your reply.

Could you please provide me an example of that?

Thanks.
Re: Read CSV file in PLSQL [message #402576 is a reply to message #402565] Mon, 11 May 2009 09:59 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.lmgtfy.com/?q=oracle+external+table
Re: Read CSV file in PLSQL [message #402577 is a reply to message #402574] Mon, 11 May 2009 10:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.oracle.com/pls/db102/search?remark=quick_search&word=external+table&tab_id=&format=ranked

Regards
Michel
Re: Read CSV file in PLSQL [message #402579 is a reply to message #402565] Mon, 11 May 2009 10:11 Go to previous messageGo to next message
scorpio4frenz
Messages: 48
Registered: October 2008
Member
Thanks, guys.

Quick question, is it not possible to be done using 2D arrays, as I've illustrated in my example in my first post?

i know how to read the data once it is in that format...just not sure how to get the data into the array structure.

Any ideas?
Re: Read CSV file in PLSQL [message #402581 is a reply to message #402565] Mon, 11 May 2009 10:13 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Any ideas?
It is possible.
Re: Read CSV file in PLSQL [message #402582 is a reply to message #402581] Mon, 11 May 2009 10:17 Go to previous messageGo to next message
scorpio4frenz
Messages: 48
Registered: October 2008
Member
BlackSwan wrote on Mon, 11 May 2009 10:13
>Any ideas?
It is possible.


Good to hear that, because that's the approach I was looking to take. Can you please provide me an example of how to get the CSV data from the blob into a 2D array?

I really appreciate your time and help.
Re: Read CSV file in PLSQL [message #402588 is a reply to message #402579] Mon, 11 May 2009 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database works with set not with array.
Your application works with array, convert result set from query into array in your application not in database.

Regards
Michel

[Updated on: Mon, 11 May 2009 10:34]

Report message to a moderator

Re: Read CSV file in PLSQL [message #402589 is a reply to message #402588] Mon, 11 May 2009 10:37 Go to previous messageGo to next message
scorpio4frenz
Messages: 48
Registered: October 2008
Member
Michel Cadot wrote on Mon, 11 May 2009 10:33
Database works with set not with array.
Your application works with array, convert result set from query into array in your application not in database.

Regards
Michel



I'm sorry, but I don't think I understand...
Re: Read CSV file in PLSQL [message #402593 is a reply to message #402589] Mon, 11 May 2009 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your application?
Why do you want to use an array?

Regards
Michel

[Updated on: Mon, 11 May 2009 10:52]

Report message to a moderator

Re: Read CSV file in PLSQL [message #402607 is a reply to message #402593] Mon, 11 May 2009 11:10 Go to previous messageGo to next message
scorpio4frenz
Messages: 48
Registered: October 2008
Member
Michel,

My application basically allows the users to upload their data into the system in the form of an Excel spreadsheet. The spreadsheet has 20 columns and more than 13 rows each time. The upload capability is coded using PLSQL cartridge. After the file is uploaded, my cartridge is calling a java servlet, which uses Oracle Clean Content to convert the excel into CSV and put it back in the same table that it was uploaded in. The file is a blob data type.

After this servlet returns "success", I'm working on the procedure that will read the CSV file from the "uploaded_files" table and display the data in that file to the user on the screen (as a html table, created using cartridges). Once the user reviews the data, he can choose to submit or reject. If submitted, I'll need to insert the data into the "project_data" table.

I thought the array was a simple implementation. Once the data is in that format, it's easier to read it. But I don't know how to get the data in the right format in the first place.

I'm not an expert in PLSQL and am having trouble understanding this.

Any code samples and tips from you are greatly appreciated.

[Updated on: Mon, 11 May 2009 11:11]

Report message to a moderator

Re: Read CSV file in PLSQL [message #402618 is a reply to message #402607] Mon, 11 May 2009 12:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where are the data you want to display in a file or in a CLOB field (note CLOB not BLOB should be used).

In both cases, your application use SELECT to get the data and displays it as it wants... or maybe a REF CURSOR in a complex case but in any case you use an array.

Regards
Michel

[Updated on: Mon, 11 May 2009 12:16]

Report message to a moderator

Re: Read CSV file in PLSQL [message #402621 is a reply to message #402618] Mon, 11 May 2009 12:30 Go to previous messageGo to next message
scorpio4frenz
Messages: 48
Registered: October 2008
Member
Michel Cadot wrote on Mon, 11 May 2009 12:15
Where are the data you want to display in a file or in a CLOB field (note CLOB not BLOB should be used).

In both cases, your application use SELECT to get the data and displays it as it wants... or maybe a REF CURSOR in a complex case but in any case you use an array.

Regards
Michel



Michel,

The data is in a blob field in the "uploaded_files" table. The blob is basically the converted CSV file, converted from the uploaded xls file.

I know I have to use clob. In one of my posts above, I mentioned that I'm using dbms_lob.converttoclob to convert the blob into clob.

The problem is how to read the clob one row at a time into the array structure (or anything similar)?

Re: Read CSV file in PLSQL [message #402624 is a reply to message #402565] Mon, 11 May 2009 12:42 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Please post PL/SQL (formatted) & DDL for table which holds cvs data
Re: Read CSV file in PLSQL [message #402630 is a reply to message #402624] Mon, 11 May 2009 12:49 Go to previous messageGo to next message
scorpio4frenz
Messages: 48
Registered: October 2008
Member
BlackSwan wrote on Mon, 11 May 2009 12:42
Please post PL/SQL (formatted) & DDL for table which holds cvs data


BlackSwan,

The table which holds the CSV file, as a blob is like this:
CREATE TABLE UPLOAD_DATA
(
  FNAME         VARCHAR2(256 BYTE)              NOT NULL,
  MIME_TYPE     VARCHAR2(128 BYTE),
  DOC_SIZE      NUMBER,
  DAD_CHARSET   VARCHAR2(128 BYTE),
  LAST_UPDATED  DATE,
  CONTENT_TYPE  VARCHAR2(128 BYTE),
  BLOB_CONTENT  BLOB
)


In this, the FNAME is the filename, mime_type is "text/csv" in case of a CSV file, dad_charset is "ascii", content_type is "raw".

Is this what you were asking for?
Re: Read CSV file in PLSQL [message #402631 is a reply to message #402621] Mon, 11 May 2009 12:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand what is in your field. Is it one row per original csv file or one row per file?

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Read CSV file in PLSQL [message #402634 is a reply to message #402631] Mon, 11 May 2009 13:05 Go to previous messageGo to next message
scorpio4frenz
Messages: 48
Registered: October 2008
Member
Michel Cadot wrote on Mon, 11 May 2009 12:51
I don't understand what is in your field. Is it one row per original csv file or one row per file?

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel



Michel,

Thanks for your patience.

Each row in the above posted table is corresponding to a single uploaded file (one row per CSV). Each CSV file can contain several rows and columns of data.

I don't know how I can post a working example here...considering that I have multiple components in my application...one is a cartridge to upload files, one is the table to store the blob, one is a java servlet to convert xls to csv.

Here's what I have so far in my procedure to read the csv:

declare

 CURSOR c_content IS
      SELECT blob_content
      FROM upload_data
      WHERE FNAME = pfile;    -- pfile is the "fname" for the csv file
      
  
 v_blob_content blob;
 v_content clob;
 v_dest_offset    INTEGER := 1;
 v_src_offset     INTEGER := 1;
 v_lang_context   INTEGER := 0;
 v_warning        INTEGER := 0;
 
 begin
 
     open c_content ;
        fetch c_content into v_blob_content;
     close c_content;
    
-- Convert to clob 
     DBMS_LOB.createtemporary (v_content, TRUE);
     DBMS_LOB.converttoclob (v_content,
                                       v_blob_content,
                                       DBMS_LOB.lobmaxsize,
                                       v_dest_offset,
                                       v_src_offset,
                                       0,
                                       v_lang_context,
                                       v_warning
                                );                                        


After this step, what I need is to loop through the contents of the clob, and display the contents in a tabular format. The output should look similar to what it looks like in the Excel spreadsheet (same row and column structure).

I've been struggling with a way to read the clob and get the above mentioned output.

I would really appreciate directions for the same.

Re: Read CSV file in PLSQL [message #402643 is a reply to message #402634] Mon, 11 May 2009 13:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once you have the file content in v_content field, you have to use instr, substr functions to get the lines and fields. You can use a pipelined function to simulate this come from a table.

Regards
Michel
Re: Read CSV file in PLSQL [message #402645 is a reply to message #402634] Mon, 11 May 2009 14:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For instance, assuming csv lines have 3 fields:
SQL> create table t (id integer, val clob);

Table created.

SQL> insert into t values (0, 
  2  'v11,v12,v13
  3  v21,v22,v23');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;
        ID
----------
VAL
--------------------------------------------------------------------------------------------------
         0
v11,v12,v13
v21,v22,v23

1 row selected.

SQL> create or replace type myt is object (
  2    f1 varchar2(10),
  3    f2 varchar2(10),
  4    f3 varchar2(10))
  5  /

Type created.

SQL> create or replace type mya as table of myt
  2  /

Type created.

SQL> create or replace function f (p_id integer)
  2  return mya
  3  pipelined
  4  is
  5    l_clob clob;
  6    l_n    pls_integer;
  7    l_line varchar2(1000);
  8  begin
  9    select val into l_clob from t where id = p_id;
 10    if substr(l_clob,-1,1) != chr(10) then l_clob := l_clob || chr(10); end if;
 11    l_n := instr(l_clob,chr(10));
 12    l_line := substr(l_clob,1,l_n)||',';
 13    while l_n > 0 loop
 14      pipe row (myt(
 15        substr(l_line, 1, instr(l_line,',')-1),
 16        substr(l_line, instr(l_line,',')+1, instr(l_line,',',1,2)-instr(l_line,',')-1),
 17        substr(l_line, instr(l_line,',',1,2)+1, instr(l_line,',',1,3)-instr(l_line,',',1,2)-1)
 18        ));
 19      l_clob := substr(l_clob,l_n+1);
 20      l_n := instr(l_clob,chr(10));
 21      l_line := substr(l_clob,1,l_n)||',';
 22    end loop;
 23  end;
 24  /

Function created.

SQL> select * from table(f(0));
F1         F2         F3
---------- ---------- ----------
v11        v12        v13
v21        v22        v23

2 rows selected.

Regards
Michel
Re: Read CSV file in PLSQL [message #402802 is a reply to message #402645] Tue, 12 May 2009 09:58 Go to previous messageGo to next message
scorpio4frenz
Messages: 48
Registered: October 2008
Member
Michel,

Thanks for the detailed explanation of the solution. That sounds like a good way to implement what I need. I've been trying to create something on the lines of the example you mentioned but I keep getting a "PLS-00222" error.

The difference in your example and my code is that I've a "record" type instead of "object". I've declared the "record" and "table of records" types in my Package specification. Then I created a pipelined function in my package body, something like this:

 function read_clob (pfile varchar2)
    return sr_data_tab   -- this is my table of records
  pipelined
    is
    l_clob clob;
    l_n    pls_integer;
    l_line varchar2(2000);
  begin
     select csv_data into l_clob from sr_excel where fname = 'F665501201/Samp_Mod.xls';  
-- I've a table which stores filename and clob content. 
I insert the clob content into this table after my blob is converted into clob.
    
   if substr(l_clob,-1,1) != chr(10) then l_clob := l_clob || chr(10); end if;
    l_n := instr(l_clob,chr(10));
    l_line := substr(l_clob,1,l_n)||',';
    while l_n > 0 loop

-- sr_record is my record type (it has 7 fields).
      pipe row (sr_record(        
        substr(l_line, 1, instr(l_line,',')-1),
        substr(l_line, instr(l_line,',')+1, instr(l_line,',',1,2)-instr(l_line,',')-1),
        substr(l_line, instr(l_line,',',1,2)+1, instr(l_line,',',1,3)-instr(l_line,',',1,2)-1),
' ',
' ',
' ',
' '));
     l_clob := substr(l_clob,l_n+1);
     l_n := instr(l_clob,chr(10));
     l_line := substr(l_clob,1,l_n)||',';
    end loop;
  end;


When I run this, I'm getting this error:
"PLS-00222: no function with name 'SR_RECORD' exists in this scope"

Any comments?

Once again, greatly appreciate your time and effort.


[Updated on: Tue, 12 May 2009 10:06] by Moderator

Report message to a moderator

Re: Read CSV file in PLSQL [message #402804 is a reply to message #402802] Tue, 12 May 2009 10:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I've declared the "record" and "table of records" types in my Package specification.

You have to do it with create type statement.

Regards
Michel
Re: Read CSV file in PLSQL [message #402808 is a reply to message #402804] Tue, 12 May 2009 10:20 Go to previous messageGo to next message
scorpio4frenz
Messages: 48
Registered: October 2008
Member
What do you mean? I was thinking that a "record" type should work as well as "object" type.

My package specification looks something like this:

CREATE OR REPLACE PACKAGE PKG_EXCEL_UPLOAD AS

type sr_record is record 
                        (f1 varchar2(100),
                         f2 varchar2(4) ,
                         f3 varchar2(6),
                         f4 varchar2(1),
                         f5 varchar2(5),
                         f6 varchar2(5),
                         f7 varchar2(2)
                        );
 
-- Table of SR records
type sr_data_tab is table of sr_record;

PROCEDURE EXCEL_UPLOAD

-- Form for uploading spreadsheet

 (P_DATE IN DATE := '25-OCT-2008' 
 ,P_CODE IN VARCHAR2 := 'SR'
 ,PSESSION_ID IN NUMBER := 2621
 );
 
function read_clob (pfile varchar2)
    return sr_data_tab
  pipelined;
  
PROCEDURE upload_process 
-- Deals with calling the servlet to convert xls to csv and will
-- also be calling the pipelined function to display data
(
 p_id VARCHAR2 default 'E177',
 p_file VARCHAR2 DEFAULT NULL
);

END PKG_EXCEL_UPLOAD;
/


I'm not seeing what I'm doing wrong. Thanks for your patience and help.

[Updated on: Tue, 12 May 2009 10:23]

Report message to a moderator

Re: Read CSV file in PLSQL [message #402811 is a reply to message #402565] Tue, 12 May 2009 10:44 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
To use types in SQL they must be created as SQL types using create type statements.
Types declared in packages are PL/SQL types and are only visible to PL/SQL.
Re: Read CSV file in PLSQL [message #402812 is a reply to message #402811] Tue, 12 May 2009 10:49 Go to previous messageGo to next message
scorpio4frenz
Messages: 48
Registered: October 2008
Member
cookiemonster wrote on Tue, 12 May 2009 10:44
To use types in SQL they must be created as SQL types using create type statements.
Types declared in packages are PL/SQL types and are only visible to PL/SQL.


Yes, I get that. However, my question is whether a PLSQL type like the one I have can be used in the pipelined function.

By the way, I'm creating everything in TOAD and not sql plus. Also, if possible, I would like everything to be a part of the package, including the type definitions.
Re: Read CSV file in PLSQL [message #402814 is a reply to message #402812] Tue, 12 May 2009 10:58 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
scorpio4frenz wrote on Tue, 12 May 2009 16:49
cookiemonster wrote on Tue, 12 May 2009 10:44
To use types in SQL they must be created as SQL types using create type statements.
Types declared in packages are PL/SQL types and are only visible to PL/SQL.


Yes, I get that. However, my question is whether a PLSQL type like the one I have can be used in the pipelined function.


Not as far as I'm aware.
Quote:

By the way, I'm creating everything in TOAD and not sql plus.


So? Doesn't chnage the syntax you use to create objects.
Also when doing stuff to post here you might find sqlplus better - easier to copy and paste eveything that happened - including errors.
Quote:

Also, if possible, I would like everything to be a part of the package, including the type definitions.

Again, not possible as far as I'm aware.
Re: Read CSV file in PLSQL [message #402817 is a reply to message #402808] Tue, 12 May 2009 11:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I was thinking that a "record" type should work as well as "object" type.

Try it and feedback.

Regards
Michel
Re: Read CSV file in PLSQL [message #402818 is a reply to message #402814] Tue, 12 May 2009 11:08 Go to previous messageGo to next message
scorpio4frenz
Messages: 48
Registered: October 2008
Member
cookiemonster wrote on Tue, 12 May 2009 10:58
scorpio4frenz wrote on Tue, 12 May 2009 16:49
cookiemonster wrote on Tue, 12 May 2009 10:44
To use types in SQL they must be created as SQL types using create type statements.
Types declared in packages are PL/SQL types and are only visible to PL/SQL.


Yes, I get that. However, my question is whether a PLSQL type like the one I have can be used in the pipelined function.


Not as far as I'm aware.
Quote:

By the way, I'm creating everything in TOAD and not sql plus.


So? Doesn't chnage the syntax you use to create objects.
Also when doing stuff to post here you might find sqlplus better - easier to copy and paste eveything that happened - including errors.
Quote:

Also, if possible, I would like everything to be a part of the package, including the type definitions.

Again, not possible as far as I'm aware.



Ok. Might be a stupid question, but as far as I know I cannot create objects within a package or plsql block. So, how am I supposed to create an object and then reference it in my package?

I've never used objects before...just wondering how they get stored in the database and how (if at all) I can view them through TOAD.

By the way, see this: http://www.oracle-developer.net/display.php?id=207

They have an example which uses PLSQL types in pipelined function. I'm just not able to tweak it right!
Re: Read CSV file in PLSQL [message #402819 is a reply to message #402817] Tue, 12 May 2009 11:13 Go to previous messageGo to next message
scorpio4frenz
Messages: 48
Registered: October 2008
Member
Michel Cadot wrote on Tue, 12 May 2009 11:03
Quote:
I was thinking that a "record" type should work as well as "object" type.

Try it and feedback.

Regards
Michel



I tried it, and used code I posted in one of my earlier posts. I'm getting PLS00222 error.

There's something wrong with the way I'm creating the records in the pipe row...still debugging.

Any comments appreciated.
Re: Read CSV file in PLSQL [message #402825 is a reply to message #402819] Tue, 12 May 2009 11:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus, copy and paste your session as I did.
Also post your Oracle version with 4 decimals.

Regards
Michel
Re: Read CSV file in PLSQL [message #402840 is a reply to message #402825] Tue, 12 May 2009 12:53 Go to previous messageGo to next message
scorpio4frenz
Messages: 48
Registered: October 2008
Member
Michel Cadot wrote on Tue, 12 May 2009 11:44
Use SQL*Plus, copy and paste your session as I did.
Also post your Oracle version with 4 decimals.

Regards
Michel



SQL*Plus on my machine is having some issues...will need to wait until those get resolved.

However, by then, I did try the approach exactly as you had mentioned in your very first code snippet (created an object instead of record). I did the following in TOAD:

create or replace type sr_record as object 
                        (f1 varchar2(100),
                         f2 varchar2(4),
                         f3 varchar2(6),
                         f4 varchar2(1),
                         f5 varchar2(5),
                         f6 varchar2(5),
                         f7 varchar2(2),
                         f8 varchar2(5),
                         f9 varchar2(10),
                         f10 varchar2(10),
                         f11 number,
                         f12 number, 
                         f13 number,
                         f14 varchar2(5),
                         f15 number(15,2),
                         f16 number(14,2),
                         f17 number(8,7),
                         f18 number
                        );


Then I created this:
create or replace type sr_data_tab as table of sr_record;


After that, I created the function separately like this:
 create or replace function read_clob (p_file varchar2)
    return sr_data_tab
  pipelined
    as
    l_clob clob;
    l_n    pls_integer;
    l_line varchar2(5000);
   
   
  begin
     select csv_data into l_clob from sr_tmp_table where fname = p_file; 
    
   if substr(l_clob,-1,1) != chr(10) then l_clob := l_clob || chr(10); end if;
    l_n := instr(l_clob,chr(10));
    l_line := substr(l_clob,1,l_n)||',';
    while l_n > 0 loop
      pipe row (sr_record(
        substr(l_line, 1, instr(l_line,',')-1),
        substr(l_line, instr(l_line,',')+1, instr(l_line,',',1,2)-instr(l_line,',')-1),
        substr(l_line, instr(l_line,',',1,2)+1, instr(l_line,',',1,3)-instr(l_line,',',1,2)-1),
        substr(l_line, instr(l_line,',',1,3)+1, instr(l_line,',',1,4)-instr(l_line,',',1,3)-1),
        substr(l_line, instr(l_line,',',1,4)+1, instr(l_line,',',1,5)-instr(l_line,',',1,4)-1),
        substr(l_line, instr(l_line,',',1,5)+1, instr(l_line,',',1,6)-instr(l_line,',',1,5)-1),
        substr(l_line, instr(l_line,',',1,6)+1, instr(l_line,',',1,7)-instr(l_line,',',1,6)-1),
        substr(l_line, instr(l_line,',',1,7)+1, instr(l_line,',',1,8)-instr(l_line,',',1,7)-1),
        substr(l_line, instr(l_line,',',1,8)+1, instr(l_line,',',1,9)-instr(l_line,',',1,8)-1),
        substr(l_line, instr(l_line,',',1,9)+1, instr(l_line,',',1,10)-instr(l_line,',',1,9)-1),
        substr(l_line, instr(l_line,',',1,10)+1, instr(l_line,',',1,11)-instr(l_line,',',1,10)-1),
        substr(l_line, instr(l_line,',',1,11)+1, instr(l_line,',',1,12)-instr(l_line,',',1,11)-1),
        substr(l_line, instr(l_line,',',1,12)+1, instr(l_line,',',1,13)-instr(l_line,',',1,12)-1),
        substr(l_line, instr(l_line,',',1,13)+1, instr(l_line,',',1,14)-instr(l_line,',',1,13)-1),
        substr(l_line, instr(l_line,',',1,14)+1, instr(l_line,',',1,15)-instr(l_line,',',1,14)-1),
        substr(l_line, instr(l_line,',',1,15)+1, instr(l_line,',',1,16)-instr(l_line,',',1,15)-1),
        substr(l_line, instr(l_line,',',1,16)+1, instr(l_line,',',1,17)-instr(l_line,',',1,16)-1),
        substr(l_line, instr(l_line,',',1,17)+1, instr(l_line,',',1,18)-instr(l_line,',',1,17)-1)
       ));
     l_clob := substr(l_clob,l_n+1);
     l_n := instr(l_clob,chr(10));
     l_line := substr(l_clob,1,l_n)||',';
    end loop;
  end;


I was trying to test this using something like:
select * from table (read_clob('F13252004/samp_file.xls'));


I've attached the sample file with this post. I'm getting a "numeric or value error".

Once again, any pointers appreciated.
  • Attachment: samp_file.xls
    (Size: 13.50KB, Downloaded 236 times)
Re: Read CSV file in PLSQL [message #402842 is a reply to message #402840] Tue, 12 May 2009 13:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your file is not a csv file, it is an excel one.
The first prerequisite is a csv file.

Regards
Michel
Re: Read CSV file in PLSQL [message #402843 is a reply to message #402842] Tue, 12 May 2009 13:07 Go to previous messageGo to next message
scorpio4frenz
Messages: 48
Registered: October 2008
Member
As I had mentioned before, I've a servlet that converts the Excel into CSV format and puts it back in the database as a blob.

I accidentally attached the original Excel before the conversion. Attaching now a CSV version.

If you open in Textpad, you will see it as a csv.
Re: Read CSV file in PLSQL [message #402844 is a reply to message #402843] Tue, 12 May 2009 13:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
As I had mentioned before, I've a servlet that converts the Excel into CSV format and puts it back in the database as a blob.

But I also said we don't care about this step.

Some fields in your file are empty and maybe some are missing.
The example I posted assumes all fields exist and are not empty, I didn't test with empty fields, you have to improve the code yourself, I gave you the way.
In addition, the example code assumes all fields are strings, you have numbers so you have to take care about conversion. It should not matter if your purpose is to display the result why handling them as number?

Regards
Michel
Re: Read CSV file in PLSQL [message #402845 is a reply to message #402844] Tue, 12 May 2009 13:47 Go to previous messageGo to next message
scorpio4frenz
Messages: 48
Registered: October 2008
Member
Michel Cadot wrote on Tue, 12 May 2009 13:40
Quote:
As I had mentioned before, I've a servlet that converts the Excel into CSV format and puts it back in the database as a blob.

But I also said we don't care about this step.

Some fields in your file are empty and maybe some are missing.
The example I posted assumes all fields exist and are not empty, I didn't test with empty fields, you have to improve the code yourself, I gave you the way.
In addition, the example code assumes all fields are strings, you have numbers so you have to take care about conversion. It should not matter if your purpose is to display the result why handling them as number?

Regards
Michel



I tested with another sample file without any empty fields. Still getting the error...didn't realize some of my fields were numbers...silly me! Will try again by converting them.

The reason they are numbers is because eventually the step after data display is going to be insertion into a table. The data types are set based on that table.

I'll keep you posted.

Re: Read CSV file in PLSQL [message #402956 is a reply to message #402840] Wed, 13 May 2009 04:55 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Quote:
create or replace type sr_record as object 
                        (f1 varchar2(100),
                         f2 varchar2(4),
                         f3 varchar2(6),
                         f4 varchar2(1),
                         f5 varchar2(5),
                         f6 varchar2(5),
                         f7 varchar2(2),
                         f8 varchar2(5),
                         f9 varchar2(10),
                         f10 varchar2(10),
                         f11 number,
                         f12 number, 
                         f13 number,
                         f14 varchar2(5),
                         f15 number(15,2),
                         f16 number(14,2),
                         f17 number(8,7),
                         f18 number
                        );



In the file you published, the fifth field contains data in the format 08-Sep, 08-Aug and so on. 6 characters, not 5.

Edit: Sorry, just saw now that that's something Excel does for you when it opens the file, even when it's a CSV. Viewing it in notepad shows it as 8-Aug. But it still raises the question: What do you do if the date is 10-Aug? What you might also think about (I don't know how your data in these files is going to be, but I see that f1 har 100 characters) is that a field in a csv can contain a comma if it's surrounded by quotes. If that was true for one of your fields, the algorithm would break.

[Updated on: Wed, 13 May 2009 05:08]

Report message to a moderator

Re: Read CSV file in PLSQL [message #403004 is a reply to message #402956] Wed, 13 May 2009 07:39 Go to previous messageGo to next message
scorpio4frenz
Messages: 48
Registered: October 2008
Member
c_stenersen wrote on Wed, 13 May 2009 04:55
Quote:
create or replace type sr_record as object 
                        (f1 varchar2(100),
                         f2 varchar2(4),
                         f3 varchar2(6),
                         f4 varchar2(1),
                         f5 varchar2(5),
                         f6 varchar2(5),
                         f7 varchar2(2),
                         f8 varchar2(5),
                         f9 varchar2(10),
                         f10 varchar2(10),
                         f11 number,
                         f12 number, 
                         f13 number,
                         f14 varchar2(5),
                         f15 number(15,2),
                         f16 number(14,2),
                         f17 number(8,7),
                         f18 number
                        );



In the file you published, the fifth field contains data in the format 08-Sep, 08-Aug and so on. 6 characters, not 5.

Edit: Sorry, just saw now that that's something Excel does for you when it opens the file, even when it's a CSV. Viewing it in notepad shows it as 8-Aug. But it still raises the question: What do you do if the date is 10-Aug? What you might also think about (I don't know how your data in these files is going to be, but I see that f1 har 100 characters) is that a field in a csv can contain a comma if it's surrounded by quotes. If that was true for one of your fields, the algorithm would break.


Thanks for your input. You are right about commas being a part of the text ... I haven't accounted for that yet. The thing is that there's another piece of this system where the users upload TXT files. In that module, they have been given a standardized format of how the data should be, and string functions are being used to extract the data. My guess is that a standard will be issued in case of what kind of data can be contained in the Excel as well...depends on the Project Manager's decision. My highest priority is to get this thing working...at least at a very basic level for now.
Re: Read CSV file in PLSQL [message #403265 is a reply to message #402565] Thu, 14 May 2009 09:08 Go to previous message
scorpio4frenz
Messages: 48
Registered: October 2008
Member
I'm having some problems integrating everything together in my package. The package does not get to the part about reading the clob at all.

Something is failing in between. I'm successfully getting a converted CSV file from the servlet, but everything after that is not happening. Even the "success" message from the servlet is not getting displayed

It must be a minor mistake with how the page redirects when the form is submitted...I just am unable to put my finger on it.

If anyone can point out the issue, I'd greatly appreciate it.

CREATE OR REPLACE PACKAGE BODY PKG_EXCEL_UPLOAD IS

PROCEDURE EXCEL_UPLOAD
 (PREPORT_DATE IN DATE
 ,PSESSION_ID IN NUMBER
 )
 IS
 
 -- This procedure is the cartridge that 
--creates the form for file upload...
--I'm not posting the entire code here
 -- Just posting the form part
 
 htp.p('<FORM name="uploadForm" enctype="multipart/form-data" action="PKG_EXCEL_UPLOAD.UPLOAD_PROCESS" method="post" onSubmit="javascript:return submitForm(this);">');
 
 end excel_upload;

PROCEDURE UPLOAD_PROCESS
(
 p_pid VARCHAR2,
 p_file VARCHAR2 DEFAULT NULL
)
IS

   -- Uploaded file cannot be found
   uploaded_file_missing EXCEPTION;

   --Uploaded file is not in Excel binary format
   wrong_file_format EXCEPTION;

   -- Uploaded file type cannot be determined
   unverifiable_file_format EXCEPTION;
 
   -- Conversion to CSV format failed
   file_conversion_failed EXCEPTION;

   -- Error occurs while parsing the converted CSV data
   parse_error EXCEPTION;

-- Variable of SR table type
v_sr_data sr_data_tab;
 
   -- Retrieves the MIME type for the uploaded file
   CURSOR c_mime_type IS
      SELECT mime_type
      FROM uploads
      WHERE NAME = p_file;

   -- Retrieves the content for the converted CSV file
   CURSOR c_content IS
      SELECT blob_content
      FROM uploads
      WHERE NAME = p_file;                                                                                                                                                                                                                                 
  
   -- Container for an error message if an error occurs
   v_error                    VARCHAR2 (4000);                
   
   v_response                 VARCHAR2 (7);
   
   -- CSV content from the uploaded file converted to clob
   v_content                  CLOB;
   
   -- MIME type of the uploaded file
   v_mime_type                VARCHAR2 (128);
   
   --Temp variable to display servlet's success
   v_tmp  varchar2(200);
   
   -- Used for reading the clob
   v_sr_tmp sr_data_tab;
     

BEGIN  -- Main

begin


htp.p('File name: '||p_file);
htp.nl();

   
    -- Check if file was uploaded
       IF p_file IS NULL THEN
            RAISE uploaded_file_missing;
       END IF;
     
    -- Check the ensure the MIME type of
-- the uploaded file is correct
         OPEN c_mime_type;
           FETCH c_mime_type INTO v_mime_type;
         CLOSE c_mime_type; 

   IF v_mime_type IS NULL THEN 
            RAISE wrong_file_format;
   ELSIF v_mime_type = 'application/octet-stream'THEN
            RAISE unverifiable_file_format;
   ELSIF v_mime_type <> 'application/vnd.ms-excel' THEN
            RAISE wrong_file_format;
   END IF;

   -- Commit the file to the database 
   COMMIT;
       
   v_tmp := 'http://myserver/SExcel/servlet/SExcel.ConvertFile?file='||UTL_URL.ESCAPE(p_file, TRUE);
  
  htp.p(v_tmp);
  
  
   v_response := UTL_HTTP.request(
                 'http://myserver/SExcel/servlet/SExcel.ConvertFile?file='||UTL_URL.ESCAPE(p_file, TRUE));
     
   htp.p('<br> Servlet responded: '||v_response); 

   -- Check to ensure the file conversion attempt succeeded
      IF v_response IS NULL OR v_response <> 'success' THEN
            RAISE file_conversion_failed;
      END IF;
 
   declare

            v_blob_content   BLOB;
            v_dest_offset    INTEGER := 1;
            v_src_offset     INTEGER := 1;
            v_lang_context   INTEGER := 0;
            v_warning        INTEGER := 0;
            
   
            begin
            
            htp.p('opening cursor');
            OPEN c_content;
               FETCH c_content INTO v_blob_content;
            CLOSE c_content;
                        
            htp.p('before clob conversion');
            DBMS_LOB.createtemporary (v_content, TRUE);
            DBMS_LOB.converttoclob (v_content,
                                       v_blob_content,
                                       DBMS_LOB.lobmaxsize,
                                       v_dest_offset,
                                       v_src_offset,
                                       0,
                                       v_lang_context,
                                       v_warning
                                      );
  
               -- Insert into temporary table     
               insert into s_excel (excel_filename,csv_data) values (p_file, v_content);
               commit;        
               
                  htp.p('after insert');
                  
              select read_clob(p_file)   
-- Read_clob is my pipeline function for 
--reading the csv data
              into v_sr_tmp
              from dual;
      
             for i in 1..v_sr_tmp.count loop
                htp.p(v_sr_tmp(i).l_name);  

-- l_name is my first column in the excel spreadsheet. For --testing purpose, i'm only displaying one column.
                htp.nl();
             end loop;
           
 
                     
     IF v_warning = DBMS_LOB.warn_inconvertible_char THEN
                RAISE file_conversion_failed;
               END IF;
            
            end;
            
                      
              
    EXCEPTION

    -- Uploaded file is not in Excel binary format
    WHEN wrong_file_format THEN
      v_error := 'Uploaded file is not an Excel file';
      
    -- Uploaded file type cannot be determined
    WHEN unverifiable_file_format THEN
      v_error := 'File format unverifiable';

    -- Conversion to CSV format failed
    WHEN file_conversion_failed THEN
      v_error := 'Conversion to CSV format failed';

    -- Error while parsing the converted CSV data
    WHEN parse_error THEN
      v_error := 'Parsing failed - ' || v_error;
     
   --Numeric or value error
    WHEN VALUE_ERROR THEN
      v_error := 'Numeric or value error';
    
   -- All other errors
    WHEN OTHERS THEN
      v_error := 'Unhandled exception - ' || SQLERRM; 
         RAISE file_conversion_failed;

END;


if v_error is not null then
 htp.p(v_error);
 htp.nl();
end if;

end;

END pkg_excel_upload;
/

[Updated on: Thu, 14 May 2009 11:12]

Report message to a moderator

Previous Topic: Comparing two fields in the same table to see if they are different
Next Topic: query long time execution
Goto Forum:
  


Current Time: Sat Dec 03 01:23:16 CST 2016

Total time taken to generate the page: 0.10467 seconds