Thanks for the question, Soumya.
Asked: March 10, 2021 - 8:29 am UTC
Last updated: March 15, 2021 - 5:09 am UTC
Version: 12c
Viewed 1000+ times
You Asked
Hi Sir,
I have a csv file in server, where there are multiline data like the below :
"ID","Sample_Data"
"A","This is a Test Data"
"B","This is a data
with newline char"
"C","This is a data with Tab, and Comma"
Now, what I need is to find the actual row count of this csv file using PLSQL, which for this case is 3 (excluding the header). If I use utl_file.get_line to find the row count, It is returning me 4, which is not correct. I can not use the logic of counting the number of comma and divide it by the number of columns (which I have) as there might be some data with comma itself.
Please advise.
and Connor said...
You need to decide on a rule that defines the commencement of a row. For example, I'll assume that a new row is defined by a concatenation of:
- carriage return (ascii 13)
- line feed ( ascii 10)
- double quote
Then I take the data as is, remove the sequence of characters above.
The difference in size between the two data sets is the number of new lines, eg
<code>
SQL> set serverout on
SQL> declare
2 l_dest_offset integer := 1;
3 l_src_offset integer := 1;
4 l_bfile_csid number := 0;
5 l_lang_context integer := 0;
6 l_warning integer := 0;
7
8 b bfile := bfilename('TEMP','sample.dat');
9 c clob;
10
11 l_len1 int;
12 l_len2 int;
13
14 begin
15 dbms_lob.createtemporary(c,true);
16 dbms_lob.fileopen(b, dbms_lob.file_readonly);
17
18 dbms_lob.loadclobfromfile (
19 dest_lob => c,
20 src_bfile => b,
21 amount => dbms_lob.lobmaxsize,
22 dest_offset => l_dest_offset,
23 src_offset => l_src_offset,
24 bfile_csid => l_bfile_csid ,
25 lang_context => l_lang_context,
26 warning => l_warning);
27 dbms_lob.fileclose(b);
28
29 l_len1 := length(c);
30 c := replace(c,chr(13)||chr(10)||'"');
31 l_len2 := length(c);
32
33 dbms_output.put_line('Lines = '||(l_len1-l_len2)/3);
34
35 end;
36 /
Lines = 3
</coder>
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment