9

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?

Danny Beckett
  • 20,529
  • 24
  • 107
  • 134
Oliver Jakoubek
  • 416
  • 1
  • 3
  • 6

9 Answers9

10

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

pascal
  • 3,287
  • 1
  • 17
  • 35
  • Yes, that's the workaround of my choice. On ASA 8 the APPEND clause works for me. And I use a "GO" to execute both statements, the headings and the data export. – Oliver Jakoubek Jan 28 '11 at 11:39
10

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 '';
animuson
  • 53,861
  • 28
  • 137
  • 147
Olzia
  • 101
  • 1
  • 2
3

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.

eci
  • 2,294
  • 20
  • 18
  • I know the user is using Sybase ASA 8.x but WITH COLUMN NAMES is not recognized by Sybase iAnywhere 10.0 unfortunately. Just thought I'd mention this for those like myself who struggled with a similar issue. – tale852150 Mar 08 '16 at 18:23
3

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"
graysonwright
  • 514
  • 2
  • 8
1

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
fedorqui
  • 275,237
  • 103
  • 548
  • 598
Zahir Khan
  • 11
  • 1
1
isql -S<Server> -D<Database>-U<UserName> -s \; -P<password>\$\1 -w 10000 -iname.sql > output.csv
FelixSFD
  • 6,052
  • 10
  • 43
  • 117
Sathiyadasan
  • 33
  • 7
1

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:\temp\sometable.xls' FORMAT EXCEL DELIMITED BY ';' QUOTE ''
Matthew Haugen
  • 12,916
  • 5
  • 38
  • 54
Jeff Gibson
  • 61
  • 2
  • 1
    Yes and no. The "FORMAT EXCEL" does output the rows AND the column names. But the resulting file is a ... yes Excel file. I didn't mention it in my question. The workflow needs to be automated: a nightly script output data from the db into CSV files and another script processes them. So no Excel in between those two parts. – Oliver Jakoubek Dec 06 '10 at 12:17
1

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.

PerformanceDBA
  • 32,198
  • 10
  • 64
  • 90
  • Using format ascii doesn't include headers, at least, not on SQL Anywhere 5. Switching to semicolons doesn't seem to be a problem, including the headers seems so. – Pablo Fernandez Jun 23 '11 at 13:08
0

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:\temp\sometable.dbf' FORMAT DBASEIII;
kristinapoj
  • 1