APPX Software Library

Chapter 5: Read APPX Data with Excel, Access and LibreOffice

Load COUNTRY into Excel and Refresh It

Use the tested Legacy Microsoft Query route in 32-bit Excel. If the command is hidden, open File → Options → Data and enable From Microsoft Query (Legacy) under Show legacy data import wizards.

  1. Create a blank workbook and select Data → Get Data → Legacy Wizards → From Microsoft Query (Legacy).
  2. On Databases, select AppxODBC, keep Use the Query Wizard to create/edit queries checked, and select OK.
  3. Enter the APPX user and password in the Dharma login dialog.
  4. In the Query Wizard, expand dmo_dmo_country and add its nine columns to Columns in your query.
  5. Select Next, keep the first sample unfiltered, and sort by name.
  6. Select Next, keep Return Data to Microsoft Excel selected, and select Finish.
  7. In Import Data, choose Table, choose cell $A$1 on the existing worksheet, and select OK.
  1. Wait until Excel returns to Ready. The worksheet contains 7 data rows and 9 columns, plus the heading row.
  2. Select Table Design → Refresh, wait for Ready, and check the same seven complete records.
  3. Save the workbook locally. On reopening, refresh the external query and supply the APPX login when prompted.

The saved worksheet values match the native APPX sample after converting Excel date serials to dates. The workbook retains an external query that can refresh. Leave password saving disabled.

Edition verification: the saved Legacy Microsoft Query workbook was reopened and refreshed through AppxODBC against the 5.5.4 gateway. The refreshed, saved result contains all seven records and all nine columns, matching the native APPX sample after date and text-padding normalization. No password is stored in the workbook connection. The initial wizard sequence is the shared client recipe; this edition's fresh check was the reopen-and-refresh route.

Excel authenticates to the default appx dictionary with the password masked.
Figure 8. Excel authenticates to the default appx dictionary with the password masked.

Leave Service empty, as pictured. This native refresh passed with the tested Dharma 14.01 x86 compatibility driver; all seven records and nine fields match the sample. Blank selects the default sqlnw lookup, reaching this gateway on TCP 1992. Database appx selects the SQL dictionary. A saved workbook can retain Service=sqlnw even after the DSN is cleared: clear that field in its login dialog, finish the refresh, and save the workbook. Service and saved-connection overrides.

Excel's refreshed result contains all seven COUNTRY records.
Figure 9. Excel's refreshed result contains all seven COUNTRY records.