Home » SQL & PL/SQL » SQL & PL/SQL » How to append a header on the top of the CSV file (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0, Windows 10 Pro)
How to append a header on the top of the CSV file [message #685908] Thu, 21 April 2022 14:19 Go to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
Hi all,

I am trying to append a string (aka header) in a CSV file that has data. While the string is getting appended it is appending at the bottom. Is there a way to have it on the 1st line itself. The reason for this having it on 1st line I will then be able to do further validations.

Here is the file (before appending the text):
66032180,1,8/7/2021 11:58:00 PM,1530,1.54E+15,Purchase,200740,46628234,Visa,1,27,Approved,6.60E+17,hpp1628395039b4ICQGM,6.60E+17
66032180,1,8/12/2021 3:34:00 PM,1530,4.30E+15,Purchase,36317,45144565,Visa,1,27,Approved,6.60E+17,hpp1628796818kljGQT9,6.60E+17
66032180,1,8/12/2021 10:40:00 PM,1530,7.13E+15,Purchase,282624,37673000,Amex,1,27,Approved,6.60E+17,hpp1628822340RZkav8n,6.60E+17
66032180,1,8/24/2021 8:30:00 PM,1530,2.29E+15,Purchase,01340Z,51918734,MC,1,27,Approved,6.60E+17,hpp1629851399t7h4Zoz,6.60E+17
66032180,1,8/28/2021 2:28:00 PM,1530,6.26E+15,Purchase,676900,52898843,MC,1,27,Approved,6.60E+17,hpp1630175205p6diDD6,6.60E+17
66032180,1,8/5/2021 10:26:00 AM,1530,1.25E+15,Purchase,90010,46469799,Visa,1,27,Approved,6.60E+17,hpp1628173517Yregb53,6.60E+17
66032180,1,8/9/2021 12:35:00 PM,1530,2.06E+15,Purchase,48077,49024436,Visa,1,27,Approved,6.60E+17,hpp1628526833XcAwaqw,6.60E+17
66032180,1,8/5/2021 9:59:00 PM,1530,5.56E+15,Purchase,07467J,54462263,MC,1,27,Approved,6.60E+17,hpp1628214979SKftQnC,6.60E+17
66032180,1,8/16/2021 10:33:00 AM,1530,7.46E+15,Purchase,195338,43507725,Visa,1,27,Approved,6.60E+17,hpp16291244015GFEqze,6.60E+17
66032180,1,8/12/2021 10:14:00 PM,1530,2.09E+15,Purchase,744023,51207019,MC,1,27,Approved,6.60E+17,hpp16288205574H62FRN,6.60E+17
66032180,1,8/5/2021 3:21:00 PM,1530,8.18E+15,Purchase,152144,55836386,MC,1,27,Approved,6.60E+17,hpp162819125070VQevG,6.60E+17
66032180,1,8/6/2021 1:51:00 PM,1530,7.16E+15,Purchase,703344,40335737,Visa,1,27,Approved,6.60E+17,hpp1628272202nElKZ6g,6.60E+17
66032180,1,8/9/2021 11:28:00 PM,1530,2.61E+14,Purchase,613678,45526402,Visa,1,27,Approved,6.60E+17,hpp1628566038zfD6DgY,6.60E+17
66032180,1,8/10/2021 1:17:00 AM,1530,6.43E+15,Purchase,02328Z,54156592,MC,1,27,Approved,6.60E+17,hpp1628572617X0KLlI6,6.60E+17
66032180,1,8/10/2021 3:09:00 AM,1530,5.08E+13,Purchase,135633,41356666,Visa,1,27,Approved,6.60E+17,hpp1628579170QEQToCN,6.60E+17
66032180,1,8/18/2021 11:46:00 AM,1530,9.44E+15,Purchase,68224,51865074,MC,1,27,Approved,6.60E+17,hpp1629301487CERPrKv,6.60E+17
66032180,1,8/11/2021 12:54:00 PM,1530,8.52E+15,Purchase,02685C,46400169,Visa,1,27,Approved,6.60E+17,hpp1628700784RMd7pvb,6.60E+17
66032180,1,8/17/2021 5:20:00 PM,1530,1.19E+15,Purchase,01213I,45209219,Visa,1,27,Approved,6.60E+17,hpp1629235178LrR8ucr,6.60E+17
This is the header that I am trying to add:

TERMINAL_ID,BATCH_NUMBER,DATE_TIME,ORDER_ID,TRANSACTION_TYPE,CARD,CARD_NUMBER,AUTHORISATION_CODE,ISO_CODE,RESPONSE_CODE,RESULT,REFERENCE_NUMBER,CUSTOMER_ID,TRANSACTION_NUMBER,SYSTEM_ID,DETAIL_CODE,MCGILL_ID,TERM_CODE,TRANS_DATE,PAYMENT_ID,AMOUNT,APPROVAL_CODE,TRANS_AMT,ORDER_NUMBER,TYPE_IND,DR_CR_IND,DESCRIPTION,TYPE
Here is how it looks (after appending the text):
66032180,1,8/7/2021 11:58:00 PM,1530,1.54E+15,Purchase,200740,46628234,Visa,1,27,Approved,6.60E+17,hpp1628395039b4ICQGM,6.60E+17
66032180,1,8/12/2021 3:34:00 PM,1530,4.30E+15,Purchase,36317,45144565,Visa,1,27,Approved,6.60E+17,hpp1628796818kljGQT9,6.60E+17
66032180,1,8/12/2021 10:40:00 PM,1530,7.13E+15,Purchase,282624,37673000,Amex,1,27,Approved,6.60E+17,hpp1628822340RZkav8n,6.60E+17
66032180,1,8/24/2021 8:30:00 PM,1530,2.29E+15,Purchase,01340Z,51918734,MC,1,27,Approved,6.60E+17,hpp1629851399t7h4Zoz,6.60E+17
66032180,1,8/28/2021 2:28:00 PM,1530,6.26E+15,Purchase,676900,52898843,MC,1,27,Approved,6.60E+17,hpp1630175205p6diDD6,6.60E+17
66032180,1,8/5/2021 10:26:00 AM,1530,1.25E+15,Purchase,90010,46469799,Visa,1,27,Approved,6.60E+17,hpp1628173517Yregb53,6.60E+17
66032180,1,8/9/2021 12:35:00 PM,1530,2.06E+15,Purchase,48077,49024436,Visa,1,27,Approved,6.60E+17,hpp1628526833XcAwaqw,6.60E+17
66032180,1,8/5/2021 9:59:00 PM,1530,5.56E+15,Purchase,07467J,54462263,MC,1,27,Approved,6.60E+17,hpp1628214979SKftQnC,6.60E+17
66032180,1,8/16/2021 10:33:00 AM,1530,7.46E+15,Purchase,195338,43507725,Visa,1,27,Approved,6.60E+17,hpp16291244015GFEqze,6.60E+17
66032180,1,8/12/2021 10:14:00 PM,1530,2.09E+15,Purchase,744023,51207019,MC,1,27,Approved,6.60E+17,hpp16288205574H62FRN,6.60E+17
66032180,1,8/5/2021 3:21:00 PM,1530,8.18E+15,Purchase,152144,55836386,MC,1,27,Approved,6.60E+17,hpp162819125070VQevG,6.60E+17
66032180,1,8/6/2021 1:51:00 PM,1530,7.16E+15,Purchase,703344,40335737,Visa,1,27,Approved,6.60E+17,hpp1628272202nElKZ6g,6.60E+17
66032180,1,8/9/2021 11:28:00 PM,1530,2.61E+14,Purchase,613678,45526402,Visa,1,27,Approved,6.60E+17,hpp1628566038zfD6DgY,6.60E+17
66032180,1,8/10/2021 1:17:00 AM,1530,6.43E+15,Purchase,02328Z,54156592,MC,1,27,Approved,6.60E+17,hpp1628572617X0KLlI6,6.60E+17
66032180,1,8/10/2021 3:09:00 AM,1530,5.08E+13,Purchase,135633,41356666,Visa,1,27,Approved,6.60E+17,hpp1628579170QEQToCN,6.60E+17
66032180,1,8/18/2021 11:46:00 AM,1530,9.44E+15,Purchase,68224,51865074,MC,1,27,Approved,6.60E+17,hpp1629301487CERPrKv,6.60E+17
66032180,1,8/11/2021 12:54:00 PM,1530,8.52E+15,Purchase,02685C,46400169,Visa,1,27,Approved,6.60E+17,hpp1628700784RMd7pvb,6.60E+17
66032180,1,8/17/2021 5:20:00 PM,1530,1.19E+15,Purchase,01213I,45209219,Visa,1,27,Approved,6.60E+17,hpp1629235178LrR8ucr,6.60E+17
TERMINAL_ID,BATCH_NUMBER,DATE_TIME,ORDER_ID,TRANSACTION_TYPE,CARD,CARD_NUMBER,AUTHORISATION_CODE,ISO_CODE,RESPONSE_CODE,RESULT,REFERENCE_NUMBER,CUSTOMER_ID,TRANSACTION_NUMBER,SYSTEM_ID,DETAIL_CODE,MCGILL_ID,TERM_CODE,TRANS_DATE,PAYMENT_ID,AMOUNT,APPROVAL_CODE,TRANS_AMT,ORDER_NUMBER,TYPE_IND,DR_CR_IND,DESCRIPTION,TYPE
The code that I used was:

v_file_type:=Gzkutil.f_open_utl_file_append(v_path,v_file_name);

  FOR i in 1..1
  LOOP
  UTL_FILE.PUT(v_file_type,'TERMINAL_ID');
  UTL_FILE.PUT(v_file_type,','||'BATCH_NUMBER');
  UTL_FILE.PUT(v_file_type,','||'DATE_TIME');
  UTL_FILE.PUT(v_file_type,','||'ORDER_ID');
  UTL_FILE.PUT(v_file_type,','||'TRANSACTION_TYPE');
  UTL_FILE.PUT(v_file_type,','||'CARD');
  UTL_FILE.PUT(v_file_type,','||'CARD_NUMBER');
  UTL_FILE.PUT(v_file_type,','||'AUTHORISATION_CODE');
  UTL_FILE.PUT(v_file_type,','||'ISO_CODE');
  UTL_FILE.PUT(v_file_type,','||'RESPONSE_CODE');
  UTL_FILE.PUT(v_file_type,','||'RESULT');
  UTL_FILE.PUT(v_file_type,','||'REFERENCE_NUMBER');
  UTL_FILE.PUT(v_file_type,','||'CUSTOMER_ID');
  UTL_FILE.PUT(v_file_type,','||'TRANSACTION_NUMBER');
  UTL_FILE.PUT(v_file_type,','||'SYSTEM_ID');
  UTL_FILE.PUT(v_file_type,','||'DETAIL_CODE');
  UTL_FILE.PUT(v_file_type,','||'MCGILL_ID');
  UTL_FILE.PUT(v_file_type,','||'TERM_CODE');
  UTL_FILE.PUT(v_file_type,','||'TRANS_DATE');
  UTL_FILE.PUT(v_file_type,','||'PAYMENT_ID');
  UTL_FILE.PUT(v_file_type,','||'AMOUNT');
  UTL_FILE.PUT(v_file_type,','||'APPROVAL_CODE');
  UTL_FILE.PUT(v_file_type,','||'TRANS_AMT');
  UTL_FILE.PUT(v_file_type,','||'ORDER_NUMBER');
  UTL_FILE.PUT(v_file_type,','||'TYPE_IND');
  UTL_FILE.PUT(v_file_type,','||'DR_CR_IND');
  UTL_FILE.PUT(v_file_type,','||'DESCRIPTION');
  UTL_FILE.PUT(v_file_type,','||'TYPE');
  END LOOP;
  UTL_FILE.FCLOSE(v_file_type);
Code for F_OPEN_UTL_FILE_APPEND IS:
FUNCTION  f_open_utl_file_append (p_path      IN  VARCHAR2,
                             p_file_name IN  VARCHAR2)
    RETURN UTL_FILE.FILE_TYPE
  IS
     file_handle              UTL_FILE.FILE_TYPE;
    l_path                   VARCHAR2(200);
  BEGIN
    l_path := f_return_utl_path_or_dir(p_path);
    file_handle :=  UTL_FILE.FOPEN( l_path
                                  , p_file_name
                                  , 'A'
                                  , 32767);
    RETURN(file_handle);

END f_open_utl_file_append;

Report message to a moderator

Re: How to append a header on the top of the CSV file [message #685910 is a reply to message #685908] Sat, 23 April 2022 07:49 Go to previous messageGo to next message
John Watson
Messages: 8804
Registered: January 2010
Location: Global Village
Senior Member
You need something like

utl_file.fseek(v_file_type,1,null)

to position the pointer to the start of the file before your utl_file.put but I don't know that it will actually work for a file opened in write mode.
You may find that utl_file just cannot do what you need.

[Updated on: Sat, 23 April 2022 07:54]

Report message to a moderator

Re: How to append a header on the top of the CSV file [message #685911 is a reply to message #685910] Sat, 23 April 2022 08:16 Go to previous message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
Hello John - Thanks for your reply and suggestion regarding FSEEK function to be used.
I was told (though I did not try) that "UTL_FILE.FSEEK to seek to start of file and write to it, but I think it is only usable in read mode.". This was quoted by Paul on Oracle OTN.
That said I took a different approach and fixed the issue.

Here was my approach which ensured that a new file with a header was created (moneri.csv) and then all the data from original file was copied line by line into new (moneri.csv) file.

SET SERVEROUTPUT ON SIZE 1000000
SET LINESIZE 200
SET VERIFY OFF 

WHENEVER SQLERROR EXIT SQL.SQLCODE;

DECLARE
    v_path         VARCHAR2(30) := 'UTL_DEV'; --'&&so_inpath';
    v_source        VARCHAR2(30) := 'Moneri.csv';--'&&so_infile';
    v_target        VARCHAR2(30) := 'Moneris.csv'; --'&&so_outfile;
    v_file_name       VARCHAR2(130) := 'Moneris.csv';--'&&so_infile';
    v_file_type_moneri   UTL_FILE.file_type; 
    v_file_type_moneris   UTL_FILE.file_type;
    v_line         VARCHAR2(32767) := NULL;
    v_total_read      INTEGER := 0;

BEGIN
  --Adding the requird headers into source file (Moneri.csv)
    v_file_type_moneri:=Gzkutil.f_open_utl_file(v_path,v_source);
   
  FOR i in 1..1
  LOOP
    UTL_FILE.PUT(v_file_type_moneri,'TERMINAL_ID');
    UTL_FILE.PUT(v_file_type_moneri,','||'BATCH_NUMBER');
    UTL_FILE.PUT(v_file_type_moneri,','||'DATE_TIME');
    UTL_FILE.PUT(v_file_type_moneri,','||'AMOUNT');
    UTL_FILE.PUT(v_file_type_moneri,','||'ORDER_ID');
    UTL_FILE.PUT(v_file_type_moneri,','||'TRANSACTION_TYPE');
    UTL_FILE.PUT(v_file_type_moneri,','||'AUTHORISATION_CODE');
    UTL_FILE.PUT(v_file_type_moneri,','||'CARD_NUMBER');
    UTL_FILE.PUT(v_file_type_moneri,','||'CARD'); 
    UTL_FILE.PUT(v_file_type_moneri,','||'ISO_CODE');
    UTL_FILE.PUT(v_file_type_moneri,','||'RESPONSE_CODE');
    UTL_FILE.PUT(v_file_type_moneri,','||'RESULT');
    UTL_FILE.PUT(v_file_type_moneri,','||'REFERENCE_NUMBER');
    UTL_FILE.PUT(v_file_type_moneri,','||'CUSTOMER_ID');
    UTL_FILE.PUT(v_file_type_moneri,','||'TRANSACTION_NUMBER');
  END LOOP;
  UTL_FILE.FCLOSE(v_file_type_moneri);
   
  
  --Now reading all the contents of Moneris.csv file and putting it into Moneri.csv file (one line at a time)
   v_file_type_moneris:=Gzkutil.f_open_utl_file_read(v_path,v_target); 
   LOOP
     BEGIN
        
        UTL_FILE.GET_LINE(v_file_type_moneris,v_line); 
        v_total_read := v_total_read + 1;
         
        
        v_file_type_moneri:=Gzkutil.f_open_utl_file_append(v_path,v_source);        
        UTL_FILE.PUT_LINE(v_file_type_moneri,v_line);
        
        IF UTL_FILE.IS_OPEN(v_file_type_moneri) THEN
          UTL_FILE.FCLOSE(v_file_type_moneri);
        END IF;
           
        EXCEPTION
        WHEN NO_DATA_FOUND then
        IF UTL_FILE.IS_OPEN(v_file_type_moneris) THEN
          UTL_FILE.FCLOSE(v_file_type_moneris);
        END IF;           
        EXIT;
     END;  
   
   END LOOP;
  
END;

Report message to a moderator

Previous Topic: Is there a better way to do this in Oracle - Generate series with other entries
Next Topic: Query to create data instead of storing it
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Mar 28 03:19:45 CDT 2023
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.