What are my options to export query results as Excel sheets?

[VolkerBarth]

I'm just planning a facility to export varying query results as Excel worksheets. This will be implemented in an older VBA application and is focussed on end users. Therefore I would like to have a "ready" Excel sheet with appropriate column formats - e.g., when dealing with date columns, users should be able to sort chronologically without first having to format those columns as dates (vs. text).

Basically I seem to have at least those options with help of SQL Anywhere's features (with unsatisfying results so far):

• Use DBISQLG's OUTPUT statement with FORMAT EXCEL (or with USING an Excel-DSN)

  This works generally (and could be used with options "-nogui -q" to run in the background). Drawbacks: Old Excel 2.1 format, columns seem to be regarded as text (with possibly "wrong" sort order), requires the Java ISQL (which is not wanted in my case).

• Use DBISQLC's OUTPUT statement with FORMAT LOTUS

  Drawbacks: As dbisqlc does not support the EXCEL format, format LOTUS seems to be the most similar. However, this format gives a very uncommon excel experience w.r.t. to layout and seems to get blocked completely under Excel 2003 and above.

• Use proxy tables based on Excel ODBC driver

  This requires to setup an Excel ODBC DSN beforehand and to declare an according remote server (cf. Breck's article with a similar approach to import Excel data.).

  Drawbacks: For each worksheet with different schema, a table has to be created beforehand in order to specify column names and types. This might require to parse/interpret an "on-the-fly" generated SQL statement, which is not trivial IMHO. (SA 10 and above support this with the sa_describe_query procedure.) So this might require to temporarily create and drop remote tables just to create a remote resultset.

  Besides that, the Excel ODBC driver seems quite restricted in the way it recognizes column types, and therefore "wrong column types" might result here, too.

My questions:

• Are there better ways to export Excel data with the help of SQL Anywhere?
• Or do you use client functionality to get an appropriate worksheet (with the help of Excel VBA or other libraries?
• How do you export such data?

[VolkerBarth · Accepted solution]

Based on the very helpful suggestion by TDuemesnil, I re-organized my approach:

I compared a few facilities to export data from MS Access to MS Excel (both V2003) and found out that even then, the resulting Excel files are not well formatted IMHO. Whereas numerical columns are mostly o.k., dates are usually exported as text and therefore cannot by ordered as expected without first reformatting them. Given there are several builtin ways in MS Access to export to Excel (primarily DoCmd.TransferSpreadsheet and DoCmd.OutputTo), I was really surprised of the rather poor results.

So I decided that I should take the opposite direction and think this is the better way:

If I want an optimal Excel formatting, I should not rely on any RDBMS's Excel export. Instead it is better to use Excel VBA from the calling application to create an worksheet, fill it with the resultset (by means of Excel's Range.CopyFromRecordset method), and explicitely format the wanted columns with Excel's own tools.

So my solution now uses something like the following code snippet:

   ' rs is an open recordset.
   ' xlSht an open Excel.Worksheet, xlRng an open Excel.Range
   If Not rs.EOF Then

Call FormatExcelSheetColumns(rs, xlSht)

' Import data starting with the 2nd row
      Set xlRng = xlSht.Cells(2, 1)
      Call xlRng.CopyFromRecordset(rs)
   End If

The mentioned VBA subroutine FormatExcelSheetColumns does the interesting formatting part:

Based on the resultset's column names and types, it creates a first row with the column headings and then formats the columns accordingly.

Sub FormatExcelSheetColumns(rs As Recordset, xlSht As Excel.Worksheet)
   Dim xlCol As Excel.Range

Call xlSht.Rows(1).Insert(xlShiftDown)
   ' Note: rs.Fields-Collection is 0-based, xlSht.Columns is 1-based
   Dim nFieldIndex As Integer
   Dim nColIndex As Integer
   For nFieldIndex = 0 To rs.Fields.Count - 1

Dim strFieldName As String
      Dim fld As Field

nColIndex = nFieldIndex + 1
      Set fld = rs(nFieldIndex)

' get column headings
      strFieldName = fld.Name

' make column headings bold and centered
      xlSht.Cells(1, nColIndex).Value = strFieldName
      xlSht.Cells(1, nColIndex).Font.Bold = True
      xlSht.Cells(1, nColIndex).HorizontalAlignment = xlCenter

Set xlCol = xlSht.Columns(nColIndex)
      With xlCol
         Select Case fld.Type
            Case dbText
               ' adjust text fields to a reasonable size
               .ColumnWidth = fld.Size / 2
            Case dbDouble
               ' Based on user-defined types or column name prefixes, it is possible
               ' to refine the format, i.e. choose different formats for monetary,
               ' percentual and ordinary numerical values, here given as flags:
               If bIsPercentage Then
                  .Style = "Percent"
               ElseIf bIsMoney Then
                  .Style = "Currency"
               Else
                  .Style = "Comma"
               End If
            Case dbLong
               ' e.g. use a fixed number without thousand separator
               .NumberFormat = "#;-#;0"
               .HorizontalAlignment = xlRight
            Case dbByte, dbBoolean
               If bIsFlag Then
                  ' Boolean values as Yes/No
                  .NumberFormat = """Yes"";;""No"""
                  .HorizontalAlignment = xlCenter
              End If
            Case dbDate, dbTimeStamp
                  .NumberFormat = "dd.mm.yyyy" ' German conv.
                  .HorizontalAlignment = xlRight
         End Select
      End With
   Next nFieldIndex
End Sub

[thomas_duemesnil]

I have regular Spreadsheet that i need to fill with Data from our ASA Server. I use this VBA Script in Excel 2003 that connects with a ADODB connection, takes a connectionstring and a select statement.

Then i loop through the result set and push the Data in the correct fields. Existing Field formats will be preserved.

Private Function GetRecordSet(sheetName As String, strSQL1 As String, UserName As String, Password As String) As Boolean
    Dim cnn As ADODB.connection
    Dim rs1 As ADODB.Recordset
    Dim Form As PasswordForm
    Dim strConn As String
    Dim i As Integer

Rem Stop Excel from recalculate with each Cell change
    Application.Calculation = xlCalculationManual

Rem Connection String
    strConn = "Provider=ASAProv.90;ENG=xxx;DBN=yyy;LINKS=TCPIP{host=x.x.x.x:2638,DOBROAD=NONE}"

Rem Setup Error Handlung
    On Error GoTo ConnectError

Rem Prepare Connection and Login
    Set cnn = New ADODB.connection
    cnn.Open strConn, UserName, Password

Rem Error Handling
    On Error GoTo RetrivalError

Rem Prepare Recordset
    Set rs1 = New ADODB.Recordset
    rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly

Dim sheet As Worksheet
    Set sheet = Sheets(sheetName)

sheet.Visible = True

Rem Only remove existing content from the worksheet !!
    sheet.Cells.ClearContents

Dim field As ADODB.field
    Dim fieldIndex As Integer
    fieldIndex = 1
    For Each field In rs1.Fields
       sheet.Cells(1, fieldIndex).Value = field.Name
       fieldIndex = fieldIndex + 1
    Next field

Dim colCount As Integer
    colCount = rs1.Fields.count

Dim rowIndex, index As Integer
    rowIndex = 2
    count = 0

While Not rs1 Is Nothing And Not rs1.BOF And Not rs1.EOF
       For index = 1 To colCount
          sheet.Cells(rowIndex, index).Value = rs1(index - 1).Value
       Next index
       rowIndex = rowIndex + 1
       count = count + 1
       rs1.MoveNext
   Wend

rs1.Close
   Set rs1 = Nothing

On Error GoTo 0

cnn.Close
   Set cnn = Nothing

Rem Restart Excel automatic recalculate
   Application.Calculation = xlCalculationAutomatic

GetRecordSet = True
   Exit Function

ConnectError:
    MsgBox ("Connection Error")
    GetRecordSet = False
    Exit Function

RetrivalError:
    MsgBox ("Error during data retrival")
    GetRecordSet = False
    Exit Function

End Function

I think you can adopt this to your needs

[Former Member]

Though the DBISQL option will work, I almost always do one of 2 things.
1) If it is a one time thing, I use UNLOAD TABLE/SELECT which dumps to CSV. Then I just use Excel to open the csv file and convert it to the native excel format
2) If it is a regular query/report I want to mov to excel, I use the "From Other Sources" option on the data tab and use "From Microsoft Query" which lets me choose a DSN and then write the query I want to execute to get my data.

[VolkerBarth]

@Jason: That's what I would do, too (and have often used the first option with automated Excel "import macros"). But my goal here is to create a programmatic facility for end users who are not at all supposed to work with data conversion tools or MS Query. Therefore my focus is on automatically "well-formatted" worksheets...

[Former Member]

You could try using the server http support and use the excel "from web" data source. The only issue there is you have to build an HTML table out of your result set in SA.