Forum Stats

  • 3,827,750 Users
  • 2,260,818 Discussions
  • 7,897,365 Comments

Discussions

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

Uday_N
Uday_N Member Posts: 318 Bronze Badge

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

Tagged:

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy
    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
    Uday_N Member Posts: 318 Bronze Badge

    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