SAP SQL Anywhere

Question

Hi

I am attempting to extract a table into csv format. I came across the unload statement in the documentation. The command appeared to be successful, but the file was not present.


$ cat unload_order.sh
/opt/sqlanywhere17/bin64s/dbisql -c "uid=sadb;pwd=sadb;eng=ftnode_sa;links=tcpip(host=10.11.12.17;port=49153);LOG=conn.log;dbn=ftnode_sa" "UNLOAD TABLE t3 to 't3_file.txt'"
$ ./unload_order.sh 
63 row(s) affected
$ ls -ltr t3_file.txt
ls: cannot access 't3_file.txt': No such file or directory

I was expecting to view the file in the same directory as where the script is executed, but there is no such file. What is the default directory that unload uses?

Answer 1

1

By default UNLOAD TABLE will try to write to a file on the database server (the file location is relative to the database server's starting directory) so rights may be an issue here.

For a client side unload you can use INTO CLIENT FILE if you have set the necessary database permissions - see the documentation on the UNLOAD statement for details.

An alternative is to use the dbisql OUTPUT TO command;

permanent link

answered 28 Sep '17, 06:52

Justin Willey
7.6k●137●179●249
accept rate: 20%

1

thanks, that worked nicely, after running

set option   allow_write_client_file ='On'

first.

Is there a way to specify column headers as part of the csv output?

(28 Sep '17, 13:26) bluefrog

Replies hidden

1

~~There's no built in functionality in that format, but you could do:~~

SELECT 'ColA','ColB', .... from dummy
  UNION 
SELECT .... your select statement

I forgot the WITH COLUMN NAMES functionality that came in v12 (that was a while ago :) Thank you Breck for the correction! However that only applies to OUTPUT, with UNLOAD you're stuck with the UNION approach.

(28 Sep '17, 13:30) Justin Willey

1

SELECT 'Hello' AS column_1, 'World' AS column_2;

OUTPUT TO 'c:\\temp\\local.txt' FORMAT TEXT WITH COLUMN NAMES;

'column_1','column_2'
'Hello','World'


SELECT row_num AS column_1,
       2 * row_num AS column_2
  FROM RowGenerator
 WHERE row_num <= 10
 ORDER BY row_num;

OUTPUT TO 'c:\\temp\\local.txt' FORMAT TEXT WITH COLUMN NAMES;

'column_1','column_2'
1,2
2,4
3,6
4,8
5,10
6,12
7,14
8,16
9,18
10,20

(28 Sep '17, 14:08) Breck Carter

ok, thanks. Is there a way to output without single quotes around varchar data ?

(28 Sep '17, 15:29) bluefrog

Replies hidden

1

Add the QUOTE '' clause, i.e. with an empty string.

(28 Sep '17, 16:47) Volker Barth

flat view

SAP SQL Anywhere

unload file dumping directory

Follow this question

Related questions