Question Re: export data using the output command from interactive sql in Technology Q&A

export data using the output command from interactive sql

kimon_moschandreou — Thu, 12 Nov 2015 16:33:51 GMT

It is requested to export data from ASE in a csv file having all the data quoted. In Interactive SQL GUI we can use this

OUTPUT TO 'C:\tmp\data.csv' FORMAT ASCII

DELIMITED BY ',' QUOTE '''' ALL ENCODING cp1253

and it returns the desired result. However we cannot create a script executed from command line that produces this file, because the GUI dbisql cannot take any arguments in order to connect to an ASE instance and execute an SQL file and the command line isql cannot accept the output command. I think we cannot achieve this using only the ASE client tools

A supposed solution could be to create a string for each row and export it using bcp or even isql. I would prefer to avoid such an approach.

Thank you in advance

Re: export data using the output command from interactive sql

former_member89972 — Thu, 12 Nov 2015 17:29:39 GMT

Both bcp and isql can create rows where columns are separated by tab.

This typically suffices for exporting to excel etc.

You can also use some other character like '|" pipe symbol or "~" tilde etc. to separate columns. When you import in excel you can use that as a column separator.

For bcp use character mode output, tab is default column separator.

For isql space is the default column separator

If you want to use different column separator (e.g. "|" ) then

for bcp use -c -t '|' option

for isql use -s '|' option

Output of isql and bcp can be directed into a file. Similar to what you describe above.

You can also easily script wrappers to accept server, database and table/view names to run for a specific purpose.

HTH

Avinash

Re: export data using the output command from interactive sql

kimon_moschandreou — Fri, 13 Nov 2015 08:35:27 GMT

Thank you for your answer. I am aware of bcp properties, but the problem here is the request to have all the data in quotes, something that is not an option for bcp.

Probably we have to use a third party tool.

Thank you again

Re: export data using the output command from interactive sql

Former Member — Fri, 13 Nov 2015 11:28:48 GMT

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00168.1530/doc/html/san1276279417813.html

Hi Kimon,

are you speaking about this interactive SQL tool?

If yes then you should be able to connect via script .

HTH

Re: export data using the output command from interactive sql

kimon_moschandreou — Fri, 13 Nov 2015 12:05:51 GMT

Hello,

This comes from the IQ documentation and indeed with this interactive SQL I can create a script. However I cannot with the interactive SQL coming with ASE

Kimon

Re: export data using the output command from interactive sql

former_member89972 — Fri, 13 Nov 2015 14:51:16 GMT

You are welcome to look out for a third party tool

Being a passionate developer, I tend to use the lego blocks provided first !!

So while you are looking for the tool,

you can use powerful object called view !

view also allows you projections (set of columns) and selection (set of rows) and rearrange the columns, column names, etc.

Both bcp and isql can get data out from a view.

As an example : To get 4 coulmns out of master..syslogins

create view v_mysyslogins

select

'"' + name + '"' as name,

'"' + convert(varchar(5), suid) + '"' as suid,

'"' + fullname + '"' as fullname,

'"' + convert(varchar(30), crdate, 109) + '"' as crdate

from master..syslogins

Sample output is :

1> select top 3 * from v_mysyslogins

2> go

name suid fullname crdate

-------------------------------- ------- -------------------------------- --------------------------------

"sa" "1" "" "Nov 18 2010 4:16:10:886PM"

"probe" "2" "" "Nov 18 2010 4:16:35:026PM"

"locke" "4" "" ""

(3 rows affected)

And you can use isql or bcp to get data using this view.

You can also craft your script(s) to automate creation of this type of views on the fly by providing just the table name.

HTH

Avinash

Re: export data using the output command from interactive sql

ChrisBaker — Fri, 13 Nov 2015 15:29:38 GMT

Don't confuse 'isql' which is the command-line tool that comes with ASE with 'DBISQL or dbisql' (Interative SQL GUI) that comes with ASE, IQ and SQL Anywhere.

You are refererencing dbisql/DBISQL in your use of 'output' statements, which 'isql' does not use.

Also from some of the other responses, there is no reason that you cannot use other delimiters (e.g. tab, '|', etc) in the BCP tool (even through views) to output the data.

Tools such as Excel can easliy import different delimiters and ignore the double quotes. Often other delimiters are used as sometimes the data also contains double quotes (and then you need to find a way to 'escape' the embedded quote). Better to just skip needing double quotes around the strings and use a different delimiter that doesn't show in the data.

Are you sure you really need double quotes around strings and commas as the delimiter? What is the file being generated for?

Chris

Re: export data using the output command from interactive sql

kimon_moschandreou — Mon, 16 Nov 2015 12:06:13 GMT

Thank you for the answer. I totally agree on the remark for the quotes, however it is a customer request I cannot change. They already have a query exporting the correct file using dbisql with the output statement, but unfortunately it cannot be part of a script.

Thank for your help

Kimon

Re: export data using the output command from interactive sql

kimon_moschandreou — Mon, 16 Nov 2015 12:08:49 GMT

Thank you for your effort. This is an approach I want to avoid because they are going to create huge reports of hundreds of thousands of rows and probably wide.

This is actually the only solution using exclusively the SAP (Sybase) provided tools

Kimon

Re: export data using the output command from interactive sql

ChrisBaker — Mon, 16 Nov 2015 12:30:21 GMT

Have you tried calling 'dbisql' with the '-nogui' option (along with -'c ...) and passing the script?

If you are using this to extract hundreds of thousands of rows then there is definitely something wrong here as other things, such as network traffic to the dbisql client, will impact performance (you can set a larger server packet size for this).

How often is this being performed? Are the 'reports' only extractions or the results of SQL queries? What is the destination system - is it another database?

Have they looked at other tools such as Data Services or SRS?

Chris

Re: export data using the output command from interactive sql

kimon_moschandreou — Mon, 16 Nov 2015 12:35:52 GMT

Thank you Chris for your effort. Actually they are supposed to have realized these risks. Actually dbisql manages to create such big files running locally on the ASE machine. Concerning the -nogui option I have tried and failed to write a valid -c connection string for ASE. Even when using DSN it fails to connect

Any ideas on this is welcome.

Kimon

Re: export data using the output command from interactive sql

simon_ogden — Mon, 16 Nov 2015 17:44:54 GMT

Using the output command as per your example, create a file sql.sql containing something like:

select 1

output to 'c:\temp\output.txt' FORMAT ASCII

DELIMITED BY ',' QUOTE '''' ALL ENCODING cp1253

Then run:

# ./dbisql -host <host> -port <port> -c "UID=<user>;PWD=<pwd>" -nogui sql.sql;cat /cygdrive/c/temp/output.txt

'1'

Re: export data using the output command from interactive sql

kimon_moschandreou — Tue, 17 Nov 2015 07:23:00 GMT

Thank you a lot Simon, this is it!!

Re: export data using the output command from interactive sql

former_member89972 — Tue, 17 Nov 2015 16:20:28 GMT

Cool.

I have depended mostly on isql for DBA work and frankly had not paid enough attention to dbisql so far.

So it was a refreshing segway to learn about it

Thanks Kimon for raising the expectation bar !

And thanks to Simon for providing inputs on what really works.

Thanks and regards

Avinash