How to Access Elliott's Data by Using Query in Microsoft Office Excel 2019

Release Date: 06/30/2020
Version: 7.x & Up

Elliott's data is based on the PSQL database engine.  In Excel, you can access the PSQL database through an ODBC interface with the Microsoft "Query" function.  The following procedure outlines how you can do so and use Excel's powerful feature to analyze or process Elliott's data.

Verify If You Are Using 32-bit or 64-bit Excel
The 32-bit version uses a different ODBC driver than the 64-bit version. So the first thing you should do is to verify whether you are using the 32-bit or 64-bit version of Excel.  To do so,  you should bring up Excel and right click on the "Task Bar." Choose "Task Manager" from the popup menu.  In the "Task Manager," find "Excel" and see whether it is 32 bits or 64 bits. See sample screen below:


Verify PSQL Database Name Through PSQL Control Center
Bring up PSQL Control Center. You should see a list of databases defined.  If you don't, you will have to first define the necessary database. See other articles under the "Pervasive PSQL" topic for more information.  The following are a list of KB articles on how to Elliott databases:

The following is an example of databases created for the PSQL engine:


Create ODBC Database Names on the Client Side

You'll need to create database names on the PSQL server one time for each Elliott company.  For each workstation that you want to access that database from, you will need to create the database names on each of those workstations through the ODBC Data Sources application.  You can typically do so by clicking on the "search" button (looks like a magnifying glass) on the bottom left of our task bar.  Then type "ODBC."  See the following example of the search result:


As in the previous example, we are using the 32-bit version of Excel, so we will choose "ODBC Data Sources (32-bit)."  In the ODBC Data Source Administrator window, go to the "System DSN" tab, and choose "Add" to add a new DSN. See sample screen below:


In the Client New Data Source window, you will see a list of interfaces to choose from.  Choose "Pervasive ODBC Client Interface" and click "Finish":


In the Pervasive ODBC Client DSN Setup, you will see the following areas to be filled out:
Data Source Name: It is also called "DSN." Typically, I would enter the same Data Source Name as the previous Database Name in the PSQL Control Center.  In this case, I intend to set up the ODBC client on my machine for accessing the ELIDATA database on the server.  So I just enter the  DSN as "ELIDATA."
Server Name/IP: You will enter the PSQL server name (NETBIOS Name) or its IP Address.  In this case, I am setting up the ODBC directly on the PSQL server, so I use "localhost."
Database Name: Initially, you will not see any name under the Database Name drop down.  Click on the "Get List" button to populate the drop down.  Then you can click on the drop down to find the database you want to use.

Finally, you will click "OK" to create the ODBC name.  Check the DSN you just created. Then click "OK" to exit. See sample screen below:


Use Excel 2019 Query to Access Elliott's Data Through ODBC

The sample screens and procedure we are outlining below is for Microsoft Excel 2019. But the Query feature is not limited to Excel 2019 only.

In Excel, you will choose the "Data" tab first, then choose "Get Data" -> "From Other Sources" -> "From Microsoft Query." See sample screen below:


In Choose Database Source, choose the DSN name you just created and click OK:


In Query Wizard, identify the table or view that you wish to retrieve data from, then expand it.


Choose from the list of columns and move to the right side and click "Next":


Choose the filter condition.  For example, in this case, we choose only customer in the state of "CA" and click "Next":


You can choose to sort the return data in a certain order.  In this example, we choose to sort the data by ascending "CUS_ZIP" (customer zip or postal code) sequence, and click "Next":


Then we choose "Return Data to Microsoft Excel" in the following screen, and Import Data to the top left corner (cell: $A$1) by default.  The following sample screen shows the result of the returned data:


Above is just a quick example of how to use the Microsoft Query feature. To find out more detailed information about the Query feature, please refer to Microsoft documentation.


EMK

Pervasive PSQL

  1. Btrieve Error Codes 001 - 199
  2. Btrieve Error Codes 3000 - 3099
  3. Btrieve Error Codes 3100 - 3199
  4. PSQL Version Required by Each Elliott Version
  5. Do I Need to Change PSQL Server Engine Default Parameters After Installing It?
  6. New Elliott PSQL Server Processor and RAM Suggestions
  7. Can I Dynamically Adjust Elliott / PSQL 11 Server Memory?
  8. Received "Your Computer Does Not Have PSQL 10 or 11 Client " Even though PSQL Client Is Just Installed
  9. Btrieve Error 161 on Password File When Starting Up Elliott
  10. Problems with Using Pervasive Rebuild Utility on APOPNFIL and AROPNFIL Tables
  11. Security Issue with Installing PSQL Client Remotely on User's Workstation
  12. PSQL and Distributed File System (DFS)
  13. How Do I Turn on PSQL Relational Engine Security?
  14. An Example of Debugging NOTE_ORD_VIEW PSQL Expression Evaluation Error
  15. Btrieve Error 025 on COP Open Order by Salesman Report
  16. What Is the *.^01 File for My PSQL Btrieve Table?
  17. Suggested Files to be Monitored by Audit Master
  18. Pervasive Backup Agent Is Not Compatible with Creating Work Files
  19. Hardware Recommendations for Your PSQL Database Server
  20. How to Optimize SQL SELECT Statement When Retrieving Data from Invoice History
  21. New User-Defined Functions in Elliott DDF
  22. How to Improve Query Performance When Retrieving Data from Notes & Invoice History
  23. How to Retrieve Tracking Number for an Order from Notes
  24. Actian PSQL Not Started Automatically After Server Reboot
  25. Create a New Database in the PCC for Relational Engine Access
  26. Slow PSQL Relational Engine Performance
  27. IPV6 May Cause Problem for PSQL 11 Relational Query
  28. DDF Files in DATA Folder May Confuse PSQL
  29. What to Do When PSQL 11 License Is Disabled
  30. Quick Installation Guide for Audit Master
  31. Quick User Guide for Audit Master
  32. PSQL 13, Micrsoft SQL Integration Service & Pervasive PSQL OLE DB Provider
  33. Your Firewall Needs to Allow Outbound Traffic to the Netherlands for PSQL Licensing Server Purposes
  34. A Case of Btrieve Error 046 on ARCRCLOG A/R Credit Card Log File
  35. A Support Case of Migrating to Different Version of DDF
  36. How to Clear the Message "Unable to Read your Users record (9/068)"
  37. Setup of the PSQL 13 Report Engine
  38. How to Create CPHSTTRX_VIEW with Left Join to CPINVHDR Due to Invoice Database Archive
  39. How to Access Elliott's Data by Using Query in Microsoft Office Excel 2019
  40. Elliott Database Naming Convention
  41. What Does Btrieve Error 080 Mean?

Feedback and Knowledge Base