utl_file and quotes

[sweetleaf]

Hi,

I'm using utl_file to read a .csv file that has quotations around each field.

my code works when i strip out these quotes, but when the quotes are left in my code tanks out immediately:
/* this is where the code bombs when theres quotes */
BEGIN
fid_in := UTL_FILE.FOPEN(loc, file_in,'R');

does anyone know why?

 

[sem]

This statement can not raise an error depending on file content!

Regards, Dima

http://seminihin.narod.ru

 

[lewisp]

Can you tell us what the error message is when your code 'tanks out'?

 

[sweetleaf]

thanks for the replies..

actually its an invalid operation error, which struck me as weird. but, as mentioned when i remove the quotes i do not get this error..

 

[sem]

Can you explain your task? Are you tyring to process CSV file named file_in and located in loc, or its name/path are stored in CSV file? I'm pretty sure that file content can not affect its opening, although misspelled filename can.

Regards, Dima

http://seminihin.narod.ru

 

[carp]

Once again, I have to agree with Dima.
I do not believe ticks INSIDE a file can cause the file to not open. I suspect your code is failing farther on, when it's trying to read/parse your file's contents and finding the quotes in it.

Perhaps you might try embedding debugging statements in your code to narrow down where the problem really is.

Elbert, CO
1010 MDT

 

[sweetleaf]

thanks all for your replies,

i'll try debugging somemore and will get back to you.

cheers

 

[sweetleaf]

Hi,

Its actually not the quotes themselves causing the problem, its that there are close to 300 columns in this csv file. Each column is encased in quotes which adds up to a lot of bytes..

When i open up the file for viewing using EditPlus each record resides on only 1 row. But when opened using notepad,
some of the records wrap to where the next record ought to begin..

I think that oracle "sees" the file the way notepad does and that utl_file.get_line gets 'confused' by this wrapping.

I know i could write a shell script to eliminate the quotes from a file, but is there a way to eliminate quotes from within a file using utl_file (note: it would have to be before utl_file.GET_LINE, so it couldnt simply be replace(line_in,'"',null).. as all that happens after get_line..)

thanks again

 

[carp]

I really don't believe it is the quotes! Since Oracle has no idea what's in the file, it cannot react to the quotes BEFORE you do a get_line!

To demonstrate, here are the contents of my testfile.txt file:

'quack','moo'
'cluck','bark'

Note that it DOES have quotes.
Here is my test script as well as its output:

14:27:14 SQL> SET SERVEROUTPUT ON
14:27:57 SQL>
14:27:57 SQL> DECLARE
14:27:57   2     l_file  utl_file.file_type := utl_file.fopen('c:\sql','testfile.txt','R');
14:27:57   3     l_buffer VARCHAR2(32000);
14:27:57   4  BEGIN
14:27:57   5     LOOP
14:27:57   6        BEGIN
14:27:57   7           utl_file.get_line(l_file,l_buffer);
14:27:57   8           dbms_output.put_line(l_buffer);
14:27:57   9        EXCEPTION
14:27:57  10           WHEN no_data_found THEN
14:27:57  11              exit;
14:27:57  12        END;
14:27:57  13     END LOOP;
14:27:57  14     utl_file.fclose(l_file);
14:27:57  15  END;
14:27:57  16  /
'quack','moo'
'cluck','bark'

PL/SQL procedure successfully completed.

As you can see, the code ran without error and the quotes were handled with no trouble.

Without seeing all of your actual code, it's hard to say where the problem lies. But I don't think it's because utl_file knows that there are quotes within your text!

Elbert, CO
1431 MDT

 

[sweetleaf]

Thanks Carp,

Yes i know and i am in agreement. As mentioned in my previous message, i think its got something to do with the way data is wrapping inside the file (i dont think its the quotes themselves causing the problem anymore). By eliminating the quotes i get to reduce the amount of characters per line, hence lessen the chance of this weird wrapping that is going on in the file.

 

[carp]

About how long (# characters) are the rows that are wrapping?

 

[sem]

Notepad wraps lines when you ask it (select menu Format/Word Wrap). Thus it doesn't view but rather display it in such manner.

Line length limit in UTL_FILE.GET_LINE is 32767 (the same as for VARCHAR2), but the variable you pass as a buffer may be declared shorter.

But again, this error may be caused by UTL_FILE.GET_LINE, not by UTL_FILE.FOPEN

Regards, Dima

http://seminihin.narod.ru