How to append a word to a line in a file without moving to next line -?

Question

I am using UTL_FILE to extract output from the .csv file and trying to append the gender to the already existing file data. But in everyway I tried I am unable to get the expected output. Need quick help on this.

Actual File (Details.csv):

Name,Country,State,City
Lina,Brazil,Bahia,Salvador
John,USA,Texas,Austin
Ashton,Australia,Tasmania,Hobart

My output:

Name,Country,State,City
,Gender
Lina,Brazil,Bahia,Salvador
,Female
John,USA,Texas,Austin
,Male
Ashton,Australia,Tasmania,Hobart
,Male

Expected Output (Details_upd.csv):

Name,Country,State,City,Gender
Lina,Brazil,Bahia,Salvador,Female
John,USA,Texas,Austin,Male
Ashton,Australia,Tasmania,Hobart,Male

------Code--------

DECLARE

   f_line      VARCHAR2 (32767);

   file_1      UTL_FILE.file_type;
   file_2      UTL_FILE.file_type;

   f_dir       VARCHAR2 (25)  := 'DATA';

   L_check_UPDATED BOOLEAN;
   l_line VARCHAR2(32767);

BEGIN

   file_1 := UTL_FILE.fopen (f_dir,'Details.csv','R');

   file_2 := UTL_FILE.fopen (f_dir,'Details_upd.csv', 'W');

      UTL_FILE.get_line (file_1, f_line);

      l_line := trim(f_line);            

      UTL_FILE.PUT_LINE(file_2,l_line||','||'Gender');

   LOOP

      L_check_UPDATED := TRUE;

      /*--omitting the LOGIC part of code--*/   

        IF (L_check_UPDATED) THEN

        l_line := trim(f_line);

        UTL_FILE.PUT(file_2,l_line||',Male');

        ELSE

        l_line := trim(f_line);

        UTL_FILE.PUT_LINE(file_2,l_line||',Female');

        END IF;

   END LOOP;

   UTL_FILE.fclose (file_1);
   UTL_FILE.fclose (file_2);

END;

You're making your task too complicated/overthinking things. Presumably you're going to have to read the file in to match it up with the gender in the database(?) so you might as well just make your life easy: replace the entire file with a new file that is the old contents plus the new data. Are you averse to doing this? — Caius Jard, Apr 24 '19 at 16:41
Hi Caius , I tried copying to a new file also but still I was unable to get the expected output..can you please help me in this -? — AlienDarkside, Apr 24 '19 at 16:44
Yes, show your existing code? Also give details on why you want to use UTL_FILE? Reading the CSV with an external table and writing a new one with Spool would be a few lines of code (are you using sqlplus for this?) — Caius Jard, Apr 24 '19 at 17:00
Hi , I have added the code which I am using .. I have excluded the logic and kept the file related part only.... Hi @CaiusJard I am using utl_file because it is an already existing code which I was provided and asked to do the changes...please let me know incase of any other easier approaches to this...if it is not possible with utl_file — AlienDarkside, Apr 24 '19 at 17:24
I note that you use PUT for males but PUT_LINE for females. This will probably disturb the line structure of the file? — Caius Jard, Apr 24 '19 at 20:15
Sorry for that might be some copy paste error which i overlooked...I am using put_line in both the cases... — AlienDarkside, Apr 25 '19 at 00:35

score 2 · Answer 1 · edited Apr 24 '19 at 20:13

2

You will need to strip off the new-line character at the end of the string that you have read using GET_LINE. Try:

l_line := rtrim (f_line, ' ' || CHR (10) || CHR (13))

edited Apr 24 '19 at 20:13

Caius Jard

72,509
5
49
80

answered Apr 24 '19 at 18:26

Steven Feuerstein

1,914
10
14

Interesting - I agree that it seems stray newline chars are making their way in, I'm just surprised because I didn't think GET_LINE returned strings with a [CR]LF on the end - from the 10g docs: *"GET_LINE Procedure This procedure reads text from the open file identified by the file handle and places the text in the output buffer parameter. **Text is read up to, but not including, the line terminator**, or up to the end of the file, or up to the end of the len parameter. It cannot exceed the max_linesize specified in FOPEN."*.. – Caius Jard Apr 24 '19 at 20:12
@steven This one worked for me..thanks everyone for helping out... :) – AlienDarkside Apr 25 '19 at 11:04

score 0 · Answer 2 · answered Apr 24 '19 at 23:33

Most likely your oracle is unix (line terminator character is chr(10)) and your file is binary transferred windows (line terminator chr(13)+chr(10))

And after utl_file read chr(13) is left over. You can use dump function to analyze the contents of the buffer.

How to append a word to a line in a file without moving to next line -?

2 Answers2