csv Fomat issue when writing a sql query as an output file

Uday_N

Hi All,

I have a sql file . I am giving the sample data below

create table test_format ( v_data varchar2(100), dat_date date);

insert into test_format values ('SONY TV',to_date('10/01/2022','dd-mm-yyyy'));

insert into test_format values ('SONY TV 49"',to_date('10/01/2022','dd-mm-yyyy'));

Query :

SET echo OFF ver OFF pages 0 trimspool ON feed OFF und OFF;

SET term OFF;

SET linesize 30000;

SET sqlblanklines ON;

SET WRAP OFF;

spool &5

prompt v_data,dat_date

select ' " '|| v_data

||' "," '

|| dat_date||' " ' AS output from test_format ;

spool off;

exit ;

Question :

When i run it , it produces the below output .

"SONY TV","10-JAN-22"

"SONY TV 49"","10-JAN-22"

If i open in notepad++ it looks good but if i open in ms excel for the second row data both columns are appended . it will be like

v_data dat_date

"SONY TV", "10-JAN-22"

"SONY TV 49", 10-JAN-22"

Is there any issue win my query or excel . My column values should not get appended . Please kindly advise

Regards,

Uday

User_H3J7U

SQL> set markup csv on
SQL> select 'SONY TV 49"' x, date '1-2-3' y from dual;

"X","Y"
"SONY TV 49""","0001-02-03 00:00:00"

Uday_N

Hi h3j7u,

Thanks for your reply. It is still not coming as separate column when i opened in excel. It is coming us

v_data dat_date

SONY TV 49",10-JAN-22

all values coming in v_data column and data_date column is empty . Is there any condition I am missing to make it as separate in excel ? Please advise

Regards,

Uday