export data using the output command from interactive sql

[kimon_moschandreou]

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

GO

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

[former_member89972 · Accepted solution]

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

[kimon_moschandreou · Accepted solution]

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

[former_member185199 · Accepted solution]

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

Hi Kimon,

are you speaking about this interactive SQL tool?

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

HTH

DJ

[kimon_moschandreou · Accepted solution]

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

[former_member89972 · Accepted solution]

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

as

select

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

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

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

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

from master..syslogins

go

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

[c_baker · Accepted solution]

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

[kimon_moschandreou · Accepted solution]

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

[kimon_moschandreou · Accepted solution]

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

[c_baker · Accepted solution]

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

[kimon_moschandreou · Accepted solution]

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

[simon_ogden · Accepted solution]

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

select 1

go

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'

[kimon_moschandreou · Accepted solution]

Thank you a lot Simon, this is it!!

[former_member89972 · Accepted solution]

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