ImportODBC
The ImportODBC() function retrieves data from a specified ODBC source. It allows users to request specific columns that meet user-defined criteria.
Related functions: ImportCSV, ImportExcel, ImportText
Syntax:
Qx.ImportODBC(Source, Query, [Filter Expression], [Columns], [Arr1])- In Excel with Quintessence addin versions prior to 25.1, use:
ImportODBC() - In the Quintessence Editor (Studio) using Quintessence language, use:
ImportODBC() - In the Quintessence Editor (Studio) using Python language, use:
Qx.ImportODBC()
ⓘ See parameter descriptions
| Source | The full path and name of the ODBC source to import. A list of examples is provided below. |
| Query | The SQL query to execute against the ODBC source. |
| Filter Expression (optional) | The filter criteria to apply to the data. |
| Columns (optional) | The columns to return, represented as C1,C2,C3,…. |
| Arr1 (optional) | Allow wildcard filter expressions, where ? is used as the wildcard character. |
Examples of ODBC connection strings for different databases
| SQL Server | Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername; Pwd=myPassword; |
| MySQL | Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword; |
| Oracle | Data Source=MyOracleDB;User Id=myUsername;Password=myPassword; Integrated Security=no; |
| Access | Driver={Microsoft Access Driver (*.mdb)};Dbq=C:mydatabase.mdb;Uid=Admin;Pwd=; |
| Excel | Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:MyExcel.xls; DefaultDir=c:mypath; |
Remarks:
- The default parameters are:
- Filter Expression: No filter is applied.
- Columns:
*(all columns are imported). - Arr1: No wildcard is applied.
Examples:
The examples provided below are based on fictitious data for illustrative purposes only.
> Example 1: Select records from a database table that meet criteria
Suppose we have a database called Data_Warehouse in an SQL Server instance named Alpha_main. The database contains the table [Country].[Alpha_3_codes] with columns country (country name) and code (Alpha-3 code). Example data:
| country | code |
| South Africa | ZAF |
| Zimbabwe | ZWE |
| Botswana | BWA |
We need the Alpha-3 codes for South Africa and Zimbabwe. This can be done in three ways:
Method 1:
Request all data and apply a Filter Expression to get the Alpha-3 codes for South Africa and Zimbabwe (|| means OR). Use the Columns parameter to return only the code column:
=Qx.ImportODBC("Driver={SQL Server};Server=Alpha_main;Database=Data_Warehouse;Trusted_Connection=True;", "select * from [Country].[Alpha_3_codes]", "C1 = 'South Africa' || C1 = 'Zimbabwe'", "C2")Method 2:
Request the Alpha-3 codes directly via the Query parameter:
=Qx.ImportODBC("Driver={SQL Server};Server=Alpha_main;Database=Data_Warehouse;Trusted_Connection=True;", "select [code] from [Country].[Alpha_3_codes] where [country] in ('South Africa','Zimbabwe')")Method 3:
Suppose the following countries are listed in Excel range A1:A2:
| South Africa |
| Zimbabwe |
Use these as wildcards via the Arr1 parameter:
=Qx.ImportODBC("Driver={SQL Server};Server=Alpha_main;Database=Data_Warehouse;Trusted_Connection=True;", "select [code] from [Country].[Alpha_3_codes] where [country] = ? or country = ?", , , A1:A2)Result:
| ZAF |
| ZWE |