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
Related Articles
Microsoft Query Is Missing on Excel After Office 365 Update
Release Date: 06/07/2024 Version: N/A Q: We use Microsoft Query extensively to retrieve data from Elliott database. After the last update for Office 365, my Microsoft Query is missing on Excel. How can I recover this feature? A: The solution is to go ...Using the Export Processor to Export Data
Q: It has been a while since we used the Export Processor to Export Data from Elliott. I remember how to select the category and choose the search data, but I can't remember how to select all the fields that need to be processed for export. Can you ...Feature - Extra Item Data Last Access Update
Release date: 8/10/2021 Version: 8.5 & Up The purpose of these changes is related to the update of the following two fields in the Item Master file: ITEM-DATE-LAST-ACCESS and ITEM-USER-LAST-ACCESS. These two fields are updated when a user adds or ...Installation of ElliottService, NETcellent’s Web Services for Elliott
Installation to New Server 1. Server Location: Decide if you are going to install the web services on the same machine running Elliott, or a separate server on your network. The machine must reside on your network. 2. Set Up Server: If your ...How to Use Pivot Table in Excel
Release Date: 07/18/2024 Version: 8.6 & Up In this video, we demonstrate a powerful feature in Excel called the "Pivot Table." While this subject is not directly related to Elliott Business Software, we do have a Report Desk feature that can export ...