Running an ASA 10 DB. Need to export the data to an XLXS file with headers. Does anyone have an example, if this can even be done?

asked 08 Nov '13, 14:40

Neo's gravatar image

Neo
16112
accept rate: 0%

edited 08 Nov '13, 17:59

Volker%20Barth's gravatar image

Volker Barth
30.6k304455662


Creating Excel files from "scratch" without an API can be tricky.

Question #1: Do you really need .XLS or .XLSX files?

Microsoft Excel can really only read generated CSV or HTML files as renamed direct inputs (*.XLS), but warns that these are not really XLS files if specified as file extensions. As Volker mentioned, there is a previous question addressing this topic.

The actual XLSX format itself is a ZIP archive of XML documents so it needs either an official API to generate it, or you need to do it yourself via some extra work to first generate the appropriate XML files and then zip it into that format. I would recommend an API-based approach.

Question #2: Does the XLSX file really need to be generated on the database server-side or can the client do it?

Most client approaches will attempt to do this from the client side, which is probably a lot easier to do given the variety of APIs available and the full scripting environment already available in Excel / .NET (and is the recommended approach in our other question also).

If you strictly need to generate an XLSX file on the database server side though, you could do this easily via launching some type of external process from the database server which makes use of an existing Excel API.

For SQL Anywhere 10 creating XLSX files on the database server side, you could try the approach:

  1. Launch an external Java call
  2. Use the SQL Anywhere JDBC driver to make a connection back to SQL Anywhere
  3. Use the Apache POI XSSF API in Java to generate the XLSX file.

For SQL Anywhere 16 creating XLSX files on the database server side, you could try the approach:

  1. Launch an external environment .NET CLR from an external stored procedure.
  2. Create an SAConnection back to SQL Anywhere.
  3. Use the connection object to populate Spreadsheets in the Open XML SDK.
permanent link

answered 14 Nov '13, 15:08

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

edited 14 Nov '13, 15:09

You may have a look at that similar question with several approaches (although it deals with the older .XLS file format):

permanent link

answered 08 Nov '13, 17:59

Volker%20Barth's gravatar image

Volker Barth
30.6k304455662
accept rate: 32%

edited 08 Nov '13, 18:00

In addition to the options already mentioned, you can use SQuirrel SQL client to query and export data from your server.

http://squirrel-sql.sourceforge.net/

In is generally much faster than the interactiveSQL from sybase when used via WAN (Server in datacenter/cloud)

The export will be in XLS format (so you are limited to 64k Rows) but otherwise its realy fast.

André

permanent link

answered 09 Nov '13, 05:14

ASchild's gravatar image

ASchild
777222740
accept rate: 14%

TOAD for ASA (www.quest.com)

permanent link

answered 15 Nov '13, 13:58

trexco's gravatar image

trexco
336111423
accept rate: 0%

@trexco,

TOAD is not only for ASE. I could not find for ASA. The tool does not connect in TOAD Sybase ASA (Adaptive Server Anywhere)? Connects, the version. Who can give me the link to download thanks. Thank you.

(09 Apr '14, 17:17) Walmir Taques
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:

×15
×13

question asked: 08 Nov '13, 14:40

question was seen: 1,629 times

last updated: 09 Apr '14, 17:17