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?

asked 28 Sep, 06:44

bluefrog's gravatar image

bluefrog
183114
accept rate: 0%


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, 06:52

Justin%20Willey's gravatar image

Justin Willey
6.9k113147218
accept rate: 21%

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, 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, 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, 14:08) Breck Carter

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

(28 Sep, 15:29) bluefrog
Replies hidden
1

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

(28 Sep, 16:47) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×40

question asked: 28 Sep, 06:44

question was seen: 65 times

last updated: 28 Sep, 16:48