Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to output data from iSQL to csv file _with_ headings?

I'm trying to query a Sybase ASA 8 database with the iSQL client and export the query results to a text file in CSV format. However the column headings are not exported to the file. There is no special option to specify that, neither in the iSQL settings nor in the OUTPUT statement.

The query and output statement looks like this:

SELECT * FROM SomeTable;
OUTPUT TO 'C:\temp\sometable.csv' FORMAT ASCII DELIMITED BY ';' QUOTE ''

The result is a file like

1;Miller;Steve;1980-06-28
2;Jones;Martha;1965-11-02
3;Waters;Richard;1979-10-15

while I'd like to have

ID;LASTNAME;FIRSTNAME;DOB
1;Miller;Steve;1980-06-28
2;Jones;Martha;1965-11-02
3;Waters;Richard;1979-10-15

Any hints?

like image 361
Oliver Jakoubek Avatar asked Dec 04 '10 18:12

Oliver Jakoubek


3 Answers

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).

like image 118
pascal Avatar answered Nov 14 '22 02:11

pascal


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 '';
like image 26
Olzia Avatar answered Nov 14 '22 01:11

Olzia


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:\temp\sometable.csv' FORMAT ASCII DELIMITED BY ';' QUOTE '' WITH COLUMN NAMES

See sybase documentation.

like image 3
eci Avatar answered Nov 14 '22 00:11

eci