1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

PLSQL code to count number of rows in .CSV file

Discussion in 'SQL PL/SQL' started by Vikram S, May 12, 2014.

  1. Vikram S

    Vikram S Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Hi,

    Can anyone please help me in writing code to get number or rows in csv file??

    The below code is not returning the proper number of rows.

    CREATE OR REPLACE function number_of_records( p_filename in varchar2 )
    return number
    as
    l_bfile bfile;
    l_lrecl number;
    l_recs number;
    begin
    l_bfile := bfilename( 'MY_FILES', p_filename );
    dbms_lob.fileopen( l_bfile );
    l_lrecl := dbms_lob.instr( l_bfile, utl_raw.cast_to_raw(chr(10)));
    l_recs := dbms_lob.getlength( l_bfile ) / l_lrecl;
    DBMS_LOB.FILECLOSE( L_BFILE );
    return l_lrecl;
    END;
    /

    If there are 1000 rows in a file, it's returning as 492.

    Please help me with this.


    Thanks & Regards,
    Vikram S
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Use GET_LINE
    Simple example :

    Code (SQL):


    DECLARE
    l_infile utl_file.file_type;
    l_buffer VARCHAR2(1024);
    l_cnt_rows pls_number :=0;

    BEGIN
    -- open a file to read
    l_infile := utl_file.fopen('TEST_DIR', 'mytst.txt', 'r');
    -- check file is opened
    IF utl_file.is_open(l_infile)
    THEN
    -- loop lines in the file
    LOOP
    BEGIN
    utl_file.get_line(l_infile, l_buffer);
    l_cnt_rows := l_cnt_rows + 1;
    EXCEPTION
    WHEN no_data_found THEN
    EXIT;
    END;
    END LOOP;
    END IF;
    utl_file.fclose(l_infile);

    END;


     

    See also : http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lob.htm