Exporting stored procedure data to Excel

[Former Member]

I want to export a stored procedures query results, that will be run on a daily basis to a Microsoft excel file.

I can do this through ISQL with the 32 bit version using the following code: SELECT * FROM SalesOrders; OUTPUT USING 'Driver=Microsoft Excel Driver (*.xls); DBQ=c:\\test\\sales.xls; READONLY=0' INTO "newSalesData";

From the example in DocCommentXchange.

But on a 64 bit Sybase database, can I compile the stored procedure to use 32 bit?

I am aware that I can do this as a csv file, but was wondering whether it could be done to Excel with a Microsoft 32-bit driver on a 64 Bit Server.

[VolkerBarth]

Confine that similar FAQ:

• What are my options to export query results as Excel sheets? and the cited article from Breck.

What do you mean with "compile the stored procedure to use 32 bit"? Are you relating to an external stored procedure written in C/Java/PHP etc.? These are run in external environments that can have a different bitness than the database engine.

Note that you can use the proxy table feature to import and export data to MS Excel, as documented here for v16. Nevertheless, AFAIK, the remote data access runs with the same bitness as the SQL Anywhere engine, so I'm not sure whether that works well (or at all) with a 32-bit Excel version...

Please add more information as whether this will run in the contents of a server, triggered by a user that could also use DBISQL and the like...

[Former Member]

Hi Volker,

Ultimately I am trying to schedule a query from within a database server, to export the results to Excel, and then be able to email those results, all from within the database on a daily basis.

So Create a database event, to run a stored procedure that unloads the data into an Excel spreadsheet.

I can create a csv file from within a stored procedure and unload the data, but it has no column headers.

[Former Member]

I have just managed to get the header row added by Unloading a select 'Col1','Col2','Col3' into temp table, then appending the main select data into another temporary table, but this is still as csv file.

[VolkerBarth]

but it has no column headers

A common way to work around that is to use two UNLOAD statements, the first to select the column names (i.e. one single row, possibly based on the results of sa_describe_query for the real query) and a second one with the real query and option APPEND ON. (With v17, you could do it in one step with the new "WITH COLUMN NAMES clause").

Note, you could probably also use the event to run a xp_cmdshell session and then

• run DBISQL to use the OUTPUT statement, if that gives better formatted results, or
• run some VBA code and use Excel itself to generate a fitting sheet...

[Former Member]

I am trying your suggestion Volker.

Starting with the dbisql command: dbisql -c "UID=DBA;PWD=sql;Server=MyServer;Database=TestData;LINKS=tcpip" select * from vExportTest; OUTPUT USING 'Driver=Microsoft Excel Driver (*.xls); DBQ=c:\\temp\\queryresults.xls; READONLY=0' INTO "Results";

I get Microsoft ODBC Driver Manager Data source name not found and no default driver specified.

What have I missed?

[Former Member]

Ah! Helps if you go to the Bin32 folder to execute the command from

[Former Member]

This is what I now have, but alas no output file created.

call xp_cmdshell('"C:\\Program Files\\SQL Anywhere 12\\Bin32\\dbisql" -c "UID=DBA;PWD=sql;Server=MyServer;Database=TestData;LINKS=tcpip" select * from vExportTest; OUTPUT USING ''Driver=Microsoft Excel Driver (*.xls); DBQ=c:\\temp\\queryresults.xls; READONLY=0'' INTO "Results"');

The dbisql onward part does if run in cmd.

[VolkerBarth]

Yep, that's documented (though not easily too find) here:

If the Excel ODBC driver is a 32-bit driver then you must use a 32-bit version of Interactive SQL to avoid an architecture mismatch error.

(I would conclude that in case it's a 64-bit driver (rarely for Excel, AFAIK), the 64-bit DBISQL must be used.)

[VolkerBarth]

Hm, I would test with a simple test file output first (omitting the EXCEL part) and try to log the CMD shell output via adding a "> MyLog.txt" output redirection to check whether the command itself was run successfully.

Does the database engine run under a different account so that may matter here (access rights to c:\\temp)?

[Former Member]

Hi Volker, not familiar with the ">MyLog.txt" addition.

Running this in cmd as administrator works: "C:\\Program Files\\SQL Anywhere 12\\Bin32\\dbisql" -c "UID=DBA;PWD=sql;Server=MyServer;Database=TestData;LINKS=tcpip" UNLOAD select * from vExportTest TO 'c:\\temp\\employee_data.csv'

What is the correct syntax when dealing with xp_cmdshell('') when there is already ' characters in the command line. Should they be delimited with '''?

[VolkerBarth · Accepted solution]

Ah, the pitfalls of quoting:

• You need to double any single quote within your SQL statement when using it within a string.
• You need an enclosing double quote pair around the whole CMD, that seems to be necessary for the CMD interpreter.

The following works for me when run against the SA16 demo database (note, I'm using the SQLANY16 environment variable and the 64-bit binary here, and are using the "2>" stderr redirection):

call xp_cmdshell('""%SQLANY16%\\\\Bin64\\\\dbisql" -q -c "UID=DBA;PWD=sql;Server=demo16;Database=demo" UNLOAD select * from Employees TO ''c:\\\\employee_data.csv'' 2> C:\\\\MyTest.log"', 'no_output');

The call creates a CSV file and an empty MyTest.log file.

In case your command contains an error (say, you scripted the table name in the SQL statement wrong), the via "2>" generated log file will contain an error message:

call xp_cmdshell('""%SQLANY16%\\\\Bin64\\\\dbisql" -q -c "UID=DBA;PWD=sql;Server=demo16;Database=demo" UNLOAD select * from EmployeesX TO ''c:\\\\employee_data.csv'' 2> C:\\\\MyTest.log"', 'no_output');

I still would think running the UNLOAD statement without xp_cmdshell would be easier. When using xp_cmdshell, it might also be easier to put the command in a batch file and make xp_cmdshell run that.

---

Here's a short code snippet of how to check for errors (and you would usually delete the log file afterwards):

begin
   declare strLog long varchar;
   declare bSuccess bit = 0;
   call xp_cmdshell('""%SQLANY16%\\\\Bin64\\\\dbisql" -q -c "UID=DBA;PWD=sql;Server=demo16;Database=demo" UNLOAD select * from Employees TO ''c:\\\\employee_data.csv'' 2> C:\\\\MyTest.log"', 'no_output');
   set strLog = cast(xp_read_file('C:\\\\MyTest.log') as long varchar);
   if strLog = '' then
      set bSuccess = 1;
   end if;
   select bSuccess, strLog;
end;

This returns "1, ''" in the above case but might return the following if you spelt the table wrong:

"0, 'Anweisung konnte nicht ausgef\\x81hrt werden.\ \ Tabelle 'EmployeesX' nicht gefunden\ SQLCODE=-141, ODBC 3-Status="42S02"\ Zeile 1, Spalte 1\\x0d\ '"

[Former Member · Accepted solution]

Hi Volker, You ripper.

I modified the code to work with Excel OUTPUT what a beauty.

Thanks

[Breck_Carter · Accepted solution]

@Volker: Google Translate doesn't have "Strine to German" does it?

"You ripper" is a good thing!

[reimer_pods · Accepted solution]

An attempt of such a dictonary can be found here: http://tinyurl.com/oxuqm74

[VolkerBarth · Accepted solution]

Thanks for the clarification - and that sounds clearly better than my original European-based (and London-related) understanding:)