Exporting Orixa data to an Excel Spreadsheet using ODBC 

ODBC stands for Open DataBase Communication. It is a standard for communication of data. In day-to-day use you will access all the data you have added to Orixa via your Orixa App. However, if you wish to go further and start to access Orixa data from other business software this can be done using Orixa's ODBC driver which can run an ODBC Service on your server computer.

This page details the steps needed start an ODBC Service to allow other applications to access your Orixa Data. The first step is to set up an "ODBC Connection" to the Orixa database on your Server Computer, which is described below.

Once the ODBC Service has been created, SQL Queries can be written in programs such as Excel to view and analyse Orixa data.

Excel starts from the assumption that you will be accessing a single data-table, but Orixa is highly relational, so it is seldom useful to link in this way. Therefore in this article the advanced Excel method of linking using a "Data Connection" with a "Definition" that includes SQL will be used. While this is more technical, once the steps are understood it is not difficult.

If you are not confident to write the SQL it may be possible to copy SQL that is suitable from a Resource in your App, or ask your Developer to craft SQL for you.
All Orixa App ODBC connections are Read-Only, meaning you can never damage or change Database data in Excel.

Different roles for Orixa and programs such as Excel

Your Orixa App provides a good way to enter and edit data. It organises the structure of your data and makes sure all data entry is valid. Day-to-day data-management is also easy with Orixa, and you can find data and link data much more easily. Staff can use data to enter data in your Orxia App, and much day-to-day analysis can be done with it.

However there are many situations where it is good to have data in other programs, particularly Excel. A very large number of Users understand Excel extremely well and many have good skills organising, presenting and analysing data in Excel, and Excel's features for data presentation and analysis are phenomenal.

Coupling Orixa and Excel creates a potential positive relationship. Excel is not so well suited to data management, data entry, control of data-integrity and validation. Also Excel is not built to be a multi-user data-entry system.

Once you have an ODBC service set up for Orixa Excel is not the only program that can be used to access the data, there are many other Business Information tools that can also use

Adding the ODBC driver on your computer

What you will need before you begin

1. The ODBC Installer.
2. Orixa Database Server running on your server computer.
3. The full connection details for the server, including its IP Address, Port Number and connection password.

This process has 3 steps. Installing the ODBC Driver, setting up the ODBC driver within Windows, and then setting the connection details for the ODBC driver so that users can see it. The process is technical, but it is not difficult if you do each step in order following this procedure.

Note that while there are a lot of steps, once the driver is set up and installed you never have to run through the steps again. The driver will run permanently on your server and will support as many connections as you want to any number of spreadsheets or other data-connections.

1. Install the driver onto the computer

ODBC Installer Utility  

Please ask Orixa to send you a copy of their ODBC installer utility. This will have a name such as "234b3edbdaccs.exe", double click on it to run it. Note that this sometimes triggers an anti-virus program, but this should not stop the installation. Please amend your anti-virus program to allow the Installer Utility to run if installation is interupted.

ODBC Driver Installation  

The window above will then show, click "next", and repeatedly click "Next" until the installation is complete. There are not usually any changes needed to be made in the installation process, but you can pick custom locations for the installed files if you wish.

 

2. Run the ODBC Installation Process to link the driver to your data

Accessing the ODBC Data Sources installation tool in Windows  

1. Press the Windows Key and type "ODBC"
2. Windows should show a list of installed programs (extra programs apart from those on the list above may appear on your computer). Click on "ODBC Datasources (64-bit) and the window in the following image will appear.

Windows ODBX Driver Utility  

1. Click "Add" to open the window that allows you to add additional ODBC Datasources.
2. Note the "Configure" button (currently greyed out) if you need to adjust the settings of your ODBC installation (for example to change the Port Number) you would click here.

New ODBC Datasource window  

1. New ODBC Data Source window. Select the Item with the name "ElevateDB ODBC Driver" and click "Finish", this creates the new data source window, but you must then set up the connection details in the following screens.

Configure ODBC step 1  

1. The Configure Data Source window opens. Enter a memorable name, this will be the name you see when you access the data source from Excel or other programs.
2. Click Next.

Configure ODBC Step 2  

1. Select "Remote" as the connection option.
2. Click "Next"

Configure ODBC Step 3  

1. Pick the Character Set used in your database. If you use a "Unicode" (non A to Z alphabet) language, pick this, otherwise use "ANSI".
2. Click Next

Configure ODBC Step 4  

1. Set the "Signature" for the server. Note this is 'orixa-server', a hypen is used between the two words.
2. Click Next.

Configure ODBC Step 5  

1. Add the connection location for the server program, usually this is an IP Address or 127.0.0.1 or 192.168.1.255, and a Port between 12010 and 12020. Please check the settings on your server before you enter this.
2. Click Next.

Configure ODBC Step 6  

Enter your User Name and Password, and click "Next."

The screen which follows setting the User Name and Password allows you to set other configuration setting such as "timeouts" and "compression", you will usually leave these settings as they are. The defaults are usually fine, just click Next.

ODBC Step 7  

1. Select the name of the Orixa database you wish to retrieve data from. This is usually the "Data" database, unless you have set up your Orixa App with a non-standard name.
2. Click Next.

NOTE: If an error is made in the previous steps, when you reach this stage the drop-down list of database names will be blank, and you may receive an error message "unable to connect", if this happens please use the "back" button(s) to go over prior steps and check all details have been entered correctly.

After the database name selection there is five more screens which rarely needs any changes. The first allows you to set a "row locking protocol", others enables setting of various fine-grained control of the database, leave all these screens unchanged and click Next.

ODBC Utility with Orixa Driver installed  

1. Newly Installed Driver present and available to be accessed by Windows Programs.
2. "Configure" button is now activated. Note that clicking this will take you back to the multi-page Installer utility shown in previous steps. If any changes are needed to your installation you can access them here.

 

3. Creating a spreadsheet with a link to your Orixa App 

Excel is always changing. By the time you read this the detailed information it provides may be out of date, if a new version of Excel has changed the precise steps.
This should not be a problem, if you search on-line you should find a document which shows the steps with the latest version of Excel. Search for "adding an ODBC data connection to excel" or "Importing data into Excel using ODBC".

Excel Data Importation Step 1  

1. Open a new spreadsheet and find the "Data", tab and click "Get Data" 
2. Select From Other Sources
3. Select From ODBC

Excel Add ODBC data source  

1. In the small window that appears, click the drop-down to show the list of data-sources.
2. Select the one you created in the previous steps.

Creating ODBC Query data source in Excel  

1. Once you have closed the Drop-down for the data source name, you can then click the tiny arrow on the left of "Advanced options" and the window will expand in size to show additional options.
2. Paste a SQL statement from your App or Developer in the SQL window.
3. Click OK. A page will open showing you a preview of the data, and giving you the option to "transform" it. Ignore this step and just import the data into your spreadsheet.

Resulting imported data in excel  

Normal Practice when using data from Orixa in Excel

1. Import one or more data-sets of data as shown above. These will appear in the spreadsheets as large tables of data. If the SQL is well written they will show data you need.
2. Save the spreadsheet. You can now add additional Sheets to this spreadsheet, or use references to this query-sheet in other spreadsheet files.
3. The data is up-to-date at the time you created the sheet. You may need to refresh data from time to time to ensure it is up-to-date.
4. Keep the newly imported dataset selected, then click the Insert tab in the Excel menu to show options to display the data in pivot table or chart format.

 

Excel Refresh data connection	Refreshing a connection Use the "Refresh All" button on the "Data tab
Excel Query Properties	Updating the Properties of the Query Click Properties in the "Data" tab Click the tiny "Properties" icon on the right of the Properties window. The Query Properties window opens, allowing you to make wide-ranging changes to the query.
Adding charts from Imported ODBC Data