July 11, 2005

Coding Excel File Output

Last week's post discussed two of the most common formats for Excel-ready files--the CSV and tab-delimited formats. Today I'll show how to write the code to build them and describe four different methods for writing files for Excel.

The first method is to open an output file and explicitly write both the data and the delimiters. If using SQR, be sure to create the file using the OPEN, WRITE and CLOSE statements--not PRINT.

open 'myfile.csv' as 1 for-writing record=1000:vary
...
write 1 from $emplid  ','
             '"' $name '",'
             '"' $address '",'
             ...
! Output record looks like this:
! 12345,"Doe,John","123 Main St."   ...
close 1

This could be simplified if we used the tab character as a delimiter, since then the surrounding quotes wouldn't be necessary. I'll also change the code to use a STRING statement instead of specifying the delimiter after each field:

let $tab = chr(9)
string $emplid
       $name
       $address
       by $tab into $record
write 1 from $record

The second way to write a CSV file works with SQR only. If the user always chooses output type "CSV" when submitting the request to the Process Scheduler, we can use very simple code to create the file. This time we use PRINT instead of WRITE. Just PRINT the values as if you were writing a normal report. The column positions don't matter--we could simply PRINT the first value in column 1, the second in column 2, and so on. SQR will then automatically add the comma delimiters and surround values with quotes where necessary.

One disadvantage of all of the options discussed so far is that they don't allow for any type of control over the appearance of the output. You can't specify colors or fonts, for example. However, there are ways of dealing with this issue. We can create a SYLK (symbolic link) file or an XML file.

The SYLK and XML formats are understood by Excel and support many different types of cell formatting. These file formats are a little complicated, though. Fortunately, some people have done the heavy lifting for us and have created procedure libraries for us to use.

Take a look at the following. From each link you can download the procedure libraries (SQCs), examples, and other documentation. After trying the examples, you should be able to choose the method that is easiest for you to use and gives you the formatting control you need.

XML method, by David Vandiver: www.sourceforge.net/projects/sqr2xml
SYLK method, by James Womeldorf: www.ontko.com/sqr/sylk.html

Until next time...