I would have suggested to start with another statement:

SELECT  ID;LASTNAME;FIRSTNAME;DOB  FROM dummy;
OUTPUT TO  C:\temp\sometable.csv  FORMAT ASCII DELIMITED BY  ;  QUOTE   ;

and add the APPEND option on your query... but I can t get APPEND to work (but I m using a ASA 11 engine).

Try this one

SELECT  ID , LASTNAME , FIRSTNAME , DOB  union
SELECT string(ID),LASTNAME,FIRSTNAME,DOB FROM SomeTable;
OUTPUT TO  C:\temp\sometable.csv  FORMAT ASCII DELIMITED BY  ;  QUOTE   ;

Simply add the option

WITH COLUMN NAMES

to your statement and it adds a header line with the column names.

The complete statement is therefore:

SELECT * FROM SomeTable; OUTPUT TO  C:	empsometable.csv  FORMAT ASCII DELIMITED BY  ;  QUOTE    WITH COLUMN NAMES

See sybase documentation.

I am able to use the isql command to output quoted CSV.

Example

$ isql $DATABASE $USERNAME $PASSWORD -b -d, -q -c
select username, fullname from users

gives the result:

username,fullname
"jdoe","Jane Doe"
"msmith","Mark Smith"

Command-line flags

(copied from the man page)

-b: Run isql in non-interactive batch mode. In this mode, the isql processes its standard input, expecting one SQL command per line.

-dDELIMITER: Delimits columns with delimiter.

-c: Output the names of the columns on the first row. Has any effect only with the -d or -x options.

-q: Wrap the character fields in double quotes.

Escaping Issue

You might run into problems if the query results contain double-quotes, though. The quotes aren t escaped properly, so they result in invalid CSV:

> select  string","with"quotes  as quoted_string
quoted_string
"string","with"quotes"

You are already familiar with the OUTPUT options. There is no option that gives you what you want.

Ok, the problem is the receiving end does not accept standard CSV files, it needs semi-colons.

If you are scripting, then you are better off getting the output in the format that is closest to what you need, and then awk-ing the output file. Very fast and you can change anything you need. I think your best option is ASCII or default output format, which will provide Comma (not colon) Separated Values, in an ASCII character text file, and includes column Headers. Then use a single awk command to convert the commas to semi-colons.

Found an easier solution, Place the headers in one file say header.txt ( it will contain a single line "col_1|col_2|col_3") then to combine the header file and your output file run:

cat header.txt my_table.txt > my_table_wth_head.txt

isql -S<Server> -D<Database>-U<UserName> -s ; -P<password>$1 -w 10000 -iname.sql > output.csv

If you use the FORMAT EXCEL option, it will output the rows with the column name in the first row. Then once you get it into excel you can save it into another format if you need to.

SELECT * FROM SOMETABLE;
OUTPUT TO  C:	empsometable.xls  FORMAT EXCEL DELIMITED BY  ;  QUOTE   

Recently I needed to solve similar issue with some prehistoric ASA7 which does not support the WITH COLUMN NAMES for .CSV output.

The solution for me was the .DBF file, which has the columns structure in it and can be processed automatically, much better than .XLS

SELECT * FROM SomeTable;
OUTPUT TO  C:	empsometable.dbf  FORMAT DBASEIII;