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.

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:

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:

http://support.elliott.com/knowledgebase/articles/1825993-elliott-database-naming-convention 

http://support.elliott.com/knowledgebase/articles/1889998-how-to-use-el850db-exe-utility-to-create-psql-data 

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

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:

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