Home » SQL & PL/SQL » SQL & PL/SQL » Read a CSV file (10.2.0.1,apex 3.1)
Read a CSV file [message #340092] Mon, 11 August 2008 07:36 Go to next message
pmapc
Messages: 46
Registered: July 2008
Member
I have a CSV file with 460000 records. I'm using UTL_FILE.FOPEN and UTL_FILE.get_line to open the file and read it line by line but I get this error:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small 

when I split the file to smaller files(for examples files with 2000 records) everything works fine.
here is a part of my procedure that I used:
LOOP 
begin

UTL_FILE.get_line(file_handle,v_string,32767);

IF (length(v_string )>0) THEN
v_string := v_string||',';

var1 := substr(v_string,1,instr(v_string,',',1,1)-1);
var2 := substr(v_string, instr(v_string,',',1,1)+1,  
instr(v_string,',',1,2) - instr(v_string,',',1,1) -1);
var3 := substr(v_string, instr(v_string,',',1,2)+1,  
instr(v_string,',',1,3) - instr(v_string,',',1,2) -1);
var4 := substr(v_string, instr(v_string,',',1,3)+1,  
instr(v_string,',',1,4) -instr(v_string,',',1,3) -1);
var5 := substr(v_string, instr(v_string,',',1,4)+1,  
instr(v_string,',',1,5) - instr(v_string,',',1,4) -1);
var6 := substr(v_string, instr(v_string,',',1,5)+1,  
instr(v_string,',',1,6) - instr(v_string,',',1,5) -1);
var7 := substr(v_string, instr(v_string,',',1,6)+1,  
instr(v_string,',',1,7) - instr(v_string,',',1,6) -1);
var8 := substr(v_string, instr(v_string,',',1,7)+1,  
instr(v_string,',',1,8) - instr(v_string,',',1,7) -1);
var9 := substr(v_string, instr(v_string,',',1,8)+1,  
instr(v_string,',',1,9) - instr(v_string,',',1,8) -1);
var10 := substr(v_string, instr(v_string,',',1,9)+1,  
instr(v_string,',',1,10) - instr(v_string,',',1,9) -1);
var11 := substr(v_string, instr(v_string,',',1,10)+1,  
instr(v_string,',',1,11) - instr(v_string,',',1,10) -1);
var12 := substr(v_string, instr(v_string,',',1,11)+1,  
instr(v_string,',',1,12) - instr(v_string,',',1,11) -1);
var13 := substr(v_string, instr(v_string,',',1,12)+1,  
instr(v_string,',',1,13) - instr(v_string,',',1,12) -1);
var14 := substr(v_string, instr(v_string,',',1,13)+1,  
instr(v_string,',',1,14) - instr(v_string,',',1,13) -1);
var15 := substr(v_string, instr(v_string,',',1,14)+1,  
instr(v_string,',',1,15) - instr(v_string,',',1,14) -1);

insert into DOCUMENTS
(TITLE,AUTHORS,KEYWORDS,ABSTRACT,JOURNAL_NAME,YEAR,ISSUE,PAGE
,EXTRA1,EXTRA2,EXTRA3,EXTRA4,EXTRA5,ADDRESS,FILENAME,LIB_ID,INSERT_DATE)
values (var1,var2,var3,var4,var5,var6,var7,var8,var9,var10,
var11,var12,var13,var14,var15,Lib_ID,SYSDATE);

END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No data Found');
exit;
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('INVALID PATH');
exit;
END; 

END LOOP; 


I think this problem is something related to instr or substr methods because when I remove them UTL_FILE.get_line could finish its work for all the records in the large excel file. but I don't know how can I change the buffer size for them.

any suggestion or idea around this problem?

Best,
Re: Read a CSV file [message #340093 is a reply to message #340092] Mon, 11 August 2008 07:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Loader or external table to read a CSV file.
They are made for and far faster than what you can ever create.

Regards
Michel
Re: Read a CSV file [message #340095 is a reply to message #340092] Mon, 11 August 2008 07:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Look at your code. It handles the file line by line, so it would not matter for instr, substr or whatever if the file contains 1 or 1 zillion records.
It is far more likely that (at least) one of your 460000 records is not formatted the way you expect it to be.

Follow Michel's advice and use the tools Oracle created for this instead of trying to reinvent the wheel. It is more error-prone and far far slower if you do it your way.
Re: Read a CSV file [message #340099 is a reply to message #340093] Mon, 11 August 2008 08:10 Go to previous messageGo to next message
pmapc
Messages: 46
Registered: July 2008
Member
Thanks for reply, I need a user interface for this part of my applicaion in apex, because I want to give this possibility to my application's users to upload a CSV file with a specifed format and then the application read this file and put the records to oracle after some processes. I'm in lack of time and don't know does these solutions suitable for me to change or not, because I already implemented my application with mentioned way.
Do have any idea?
Is there any solution to solve the problem when use the UTL_FILE?

http://www.dba-oracle.com/art_ext_tabs_spreadsheet.htm
External spreadsheets are not good for large files because the entire file must be reread into Oracle whenever a change is saved to the spreadsheet.

problem is not with my data because I copy the records from some data that I'm sure they had not any problem.

Best,

[Updated on: Mon, 11 August 2008 08:24]

Report message to a moderator

Re: Read a CSV file [message #340108 is a reply to message #340099] Mon, 11 August 2008 08:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What you can do with utl_file can be far better done with external table.

Regards
Michel
Re: Read a CSV file [message #340299 is a reply to message #340108] Tue, 12 August 2008 04:36 Go to previous messageGo to next message
pmapc
Messages: 46
Registered: July 2008
Member
Thank you very much guys, you really save me, Thank you for your great clues. Smile
Re: Read a CSV file [message #340307 is a reply to message #340299] Tue, 12 August 2008 04:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Are you being cynical or did you actually solve your problem?
Re: Read a CSV file [message #340309 is a reply to message #340307] Tue, 12 August 2008 05:03 Go to previous messageGo to next message
pmapc
Messages: 46
Registered: July 2008
Member
Noooooo, why cynical !!!! Shocked

I changed my way, now I am using EXTERNAL TABLES to import my big files to ORACLE.
Re: Read a CSV file [message #340722 is a reply to message #340309] Thu, 14 August 2008 00:40 Go to previous messageGo to next message
pmapc
Messages: 46
Registered: July 2008
Member
I have encountered with a problem using external tables. the last column in csv file have a extra charachter that I can not see what is it, but when I select its length its return its real length+1 .when I copy the column data and paste it into notepad it has double quotation around itself. Does any know the reason?

this is my table creation command:
create table csv_article (
TITLE VARCHAR2(4000),
AUTHORS VARCHAR2(4000),
KEYWORDS VARCHAR2(4000),
ABSTRACT VARCHAR2(4000),
JOURNAL_NAME VARCHAR2(4000),
YEAR number,
ISSUE VARCHAR2(4000),
PAGE VARCHAR2(4000),
EXTRA1 VARCHAR2(4000),
EXTRA2 VARCHAR2(4000),
EXTRA3 VARCHAR2(4000),
EXTRA4 VARCHAR2(4000),
EXTRA5 VARCHAR2(4000),
ADDRESS VARCHAR2(4000),
FILENAME VARCHAR2(4000)
)
organization external (
  type              oracle_loader
  default directory ext_dir_a
  access parameters (
    records delimited  by newline
    fields  terminated by ','
    missing field values are null
  )
  location ('articles.csv')
)
reject limit unlimited;
Re: Read a CSV file [message #340725 is a reply to message #340722] Thu, 14 August 2008 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This seems a difference in "end of line" characte(s) between the system that created the file and the server where you read it.
Check this and your transfer method (activate ascii option).

Regards
Michel
Re: Read a CSV file [message #340729 is a reply to message #340725] Thu, 14 August 2008 01:08 Go to previous messageGo to next message
pmapc
Messages: 46
Registered: July 2008
Member
both of them are windows 2003 . in microsoft word it was like this:
"282500009.pdf
"
like an extra newline.

how can I activate ascii option?

[Updated on: Thu, 14 August 2008 01:12]

Report message to a moderator

Re: Read a CSV file [message #340732 is a reply to message #340729] Thu, 14 August 2008 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use an hexaeditor and verify what is the extra-character at the end of line or check the program that generates the file to see what it does.

Regards
Michel
Re: Read a CSV file [message #340741 is a reply to message #340732] Thu, 14 August 2008 02:05 Go to previous messageGo to next message
pmapc
Messages: 46
Registered: July 2008
Member
it is Hexadezimal 0D = decimal 13 = Carriage Return (CR).

I changed

records delimited by newline
to
records delimited by '\r\n'

but get same result. any idea? Sad
Re: Read a CSV file [message #340749 is a reply to message #340741] Thu, 14 August 2008 02:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Do you have newline chars as well?
If not, they are delimited by \r only
Re: Read a CSV file [message #340751 is a reply to message #340741] Thu, 14 August 2008 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What are ALL the end of line characters? "\n", "\r\n", "\n\r"...?

Regards
Michel
Re: Read a CSV file [message #340754 is a reply to message #340751] Thu, 14 August 2008 02:45 Go to previous messageGo to next message
pmapc
Messages: 46
Registered: July 2008
Member
it works with '\r\r'!!!
but at last as the last record it selects an extra null record in my select command.
Re: Read a CSV file [message #358550 is a reply to message #340754] Tue, 11 November 2008 19:23 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
pmapc wrote on Thu, 14 August 2008 02:45
it works with '\r\r'!!!
but at last as the last record it selects an extra null record in my select command.


I'm writing the results of my dynamic query into a .csv file format. But in my case '\r\r' , '\n\r' , '\n' and '\r\n' doesnt work as a delimeter for a newline. Any suggestions?


while ( dbms_sql.fetch_rows(cursor_name) > 0 ) loop
   for i in 1 .. l_colCnt loop
    dbms_sql.column_value(cursor_name, i, columnValue );

     recLine := recLine||substr(columnValue, 1, 200)||',';

     cnt4 := cnt4 + 1;
     targetFile := outFile4;
     utl_file.put(targetFile, recLine);	
     utl_file.new_line(targetFile);

   end loop;
     
    recLine := recLine||'\r\r';
 
end loop; 
Re: Read a CSV file [message #358552 is a reply to message #358550] Tue, 11 November 2008 19:47 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
i think this is the better code yet still to no avail.. already tried '\r\r' , '\r\n' , '\n' and '\n\r'

while ( dbms_sql.fetch_rows(cursor_name) > 0 ) loop
   for i in 1 .. l_colCnt loop
    dbms_sql.column_value(cursor_name, i, columnValue );


     if i = l_colCnt then
     recLine := recLine||'\r\r';

     else
     recLine := recLine||substr(columnValue, 1, 200)||',';
     end if;

     cnt4 := cnt4 + 1;
     targetFile := outFile4;
     utl_file.put(targetFile, recLine);	
     utl_file.new_line(targetFile);

   end loop; 
end loop; 

[Updated on: Tue, 11 November 2008 19:48]

Report message to a moderator

Re: Read a CSV file [message #358622 is a reply to message #358552] Tue, 11 November 2008 23:54 Go to previous message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
chr(13) works..
Previous Topic: unusable index (merged)
Next Topic: create database
Goto Forum:
  


Current Time: Sat Dec 10 03:20:13 CST 2016

Total time taken to generate the page: 0.09161 seconds