Spire Data Linked to Excel

Posted inBlog

Excel can pull live Spire data into Excel. You can refresh this data from Spire at any time. This is possible without special tools but this article shows the easiest way and includes some lesser known tips & tricks.

Breeze Excel Query is a simple free utility that helps you create Power Queries in Excel. Any user can do this knowing three things:

  • The name or IP address of the Spire Server
  • The port number that Postgres uses on your Spire Server (almost always 5432)
  • The name assigned to the database for your company in Spire
  • The data password for either user “spireapp” or user “postgres” (The location of the password for user spireapp is discussed on this page)

How To Use

  1. Download the Breeze Excel Query sheet.
  2. Open the spreadsheet and enter the Server, Port and Database information.
  3. Select any cell in the blue table of sample SQL queries.
  4. Hit Ctrl-Shift Q on the keyboard.
  5. Enter the user id (usually “spireapp”) and the password.

These steps will create a Power Query in your spreadsheet and create a worksheet linked to the Power Query:

In addition to saving time navigating the Excel menus to do this yourself, this tool has these benefits:

  1. You are do not need to create an ODBC datasource on every computer that needs to use this spreadsheet.
  2. You can easily “re-point” all your sheets to a new server.
  3. You can store the customized SQL queries you use often in this spreadsheet and anyone can use them at any time. Simply add a new entry into the table for each SQL query.
  4. The sample SQL statements included show some lesser known features of Spire SQL:
  • You can split elements Spire stores in an array into separate columns.
  • You can pull elements from the UDF (User Defined Fields) and format them properly.
  • There are ‘hidden’ built in functions that do things like format phone numbers.
  • There are data retrieval functions like ar_aged_list that accepts a date and ages data according to that date.
  • You can read data from a ‘view’ called V3. This is the data view that Spire uses on Crystal Reports. Using a V3 view makes it less likely your query will later fail if Spire makes changes to the database.

The sample queries in Breeze Excel Query demonstrate some of these advanced query concepts.

Changing the Spire Company Settings

Once you have created queries, and perhaps modified them in Power Query, you may want to change the Server or the Company they point to. To do this, simply enter the new settings and hit Ctrl-Shift-U. This will update all existing Power Query queries that use Postgres to use these new settings. You will again be prompted to enter the user and password. (Do not use this feature if your workbook points to multiple Spire companies at once!)

Fixing Wrong Passwords

When Excel prompts you for a user and password, it does not verify them before it saves them. So if you make a mistake, it’s not simple just to enter them correctly next time. To force Excel to re-prompt, do the following:

  1. In Excel, choose Data -> Get Data -> Data Source Settings
  2. Select the datasource that has the wrong settings.
  3. Click Clear Permissions

If a future post, I will discuss the easiest way to create and test more complicated PostgreSQL queries.