Skip to Main Content
  • Questions

  • How to add blank line in 2nd row in CSV

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, saurabh.

Asked: June 12, 2019 - 9:28 am UTC

Last updated: June 14, 2019 - 3:00 am UTC

Version: Oracle 12c

Viewed 1000+ times

You Asked

I have a requirement to add a blank row after header (First row) in csv file.
I have written procedure and returning data through sys ref cursor.
ultimately it is generating csv file.
I tried to map NULL value for first row of data but when i open file in txt, i get ,
here client don't want , in first row

for Exp :

select null as a, 
       null as b
     FROM Dual 
union all
  select a,
         b
        from table


Output will be like

a,b
---
,,
1,2

here client don't want ,, value in first row of data set



and Chris said...

How exactly are you generating the CSV?

I'm not aware of a way to do this in one query without post-processing the file to strip out the commas.

Alternatively you could run three queries:

- The header names
- The blank line
- The records

e.g., in SQLcl:

SQL> create table t (
  2    a int, b int
  3  );
SQL>
SQL> insert into t values ( 1, 1 );
SQL> commit;
SQL>
SQL> set sqlformat
SQL Format Cleared
SQL> set heading off
SQL> set feed off
SQL> set echo off
SQL> set pages 0
SQL>
SQL> spool out.csv
SQL> select 'A,B' from dual;
A,B
SQL> select null from dual;

SQL> set sqlformat csv
SQL> select * from t;
1,1
SQL> spool off
SQL>
SQL> ho type out.csv
A,B

1,1

SQL>
SQL> set sqlformat
SQL Format Cleared
SQL>
SQL> set heading off
SQL> set feed off
SQL> set echo off
SQL> set pages 0
SQL>
SQL> spool out.csv
SQL> select 'A,B' from dual;
A,B
SQL> select null from dual;

SQL> select /*csv*/a, b
  2  from t;
1,1
SQL> spool off
SQL>
SQL> ho type out.csv
A,B

1,1

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Add blank line after header

Saurabh, June 13, 2019 - 4:00 pm UTC

Hi Chris,
I am using CMC tool which directly call oracle stored procedure to generate CSV/xlsx file.
However I tried in below way to achieve the requirement

Create table T1 (
A number,
B number
);
Insert into T1 values (1,2);
Insert into T1 values (3,4);

Select * from T1;

A. B
-------
1 2
3 4

Query
----------
With ab as (
Select a,b,rownum as rnum from T1),
T11 as (
Select chr(13) || chr(10) || a as a,b from ab where rnum =1),
T12 as
(Select a,b from ab where rnum > 1)
Select a,b from t11
Union
Select a,b from t12;
Chris Saxon
June 13, 2019 - 5:09 pm UTC

CMC tool?

Chuck Jolley, June 13, 2019 - 9:25 pm UTC 

select source_row || decode(rownum,
                            1, chr(13)||chr(10),
                            '')
  from source


The blank line can be part of the first row because you are outputting to text
Connor McDonald
June 14, 2019 - 3:00 am UTC

nice touch
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library