ImportODBC
The ImportODBC() function imports data from a specified ODBC source into Excel. The function allows users to import specific columns that satisfy user-defined criteria.
Related functions: ImportCSV, ImportExcel, ImportText
Syntax
ImportODBC (Source, Query, Filter Expression, Columns, Arr1)
| Source | The full path and name of the ODBC source to import, for example: “Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername; Pwd=myPassword;” |
| Query | The query to pass to the ODBC source, for example: “Select Asset_Code,Asset_Description,Value from Portfolio_Assets” |
| Filter Expression | The expression representing the filter criteria to apply to the data, for example: “where Portfolio_ID = ‘5039’ and Date = ’31 March 2015′” |
| Columns | The columns that need to be returned, represented as C1,C2,C3,… If the value is set to ‘*’ or omitted, then all columns are returned. |
| Arr1 | If the filter expression contains wildcards, for example, “where Portfolio_ID = ? and Date = ?”, arr1 contains the array of values to insert in place of the wildcards, in the order they occur in the filter expression. Note: this parameter can be a Quintessence function that returns an array. |
Examples of ODBC connection strings for different databases
| ODBC Source | Connection String |
| 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; |
Note:
Values are imported as text. To recast the text in the output columns to particular types, append a colon and a type, for example, “C1:N”, where N = number, T = text and D = date.
The most recently saved import source file is imported. If the import source file is being edited, any changes will not be imported until the file is saved and the import function is recalculated.
Examples
Example 1. Select all records from a database table
Example 2. Select all records from a database table, and output specific columns
Example 3. Select records from a database table that meet criteria contained in the SQL query string
Example 4. Select records from a database table and filter the output by adding specifying criteria
Example 5. Select records from a database table that meet criteria containing place holders
Note:
How your system administrator configured Quintessence determines which function parameters are required. In most implementations, administrators configure functions so that certain parameters default to specific values when left blank.
In the examples below, the parameter values are in Excel cells that are referenced by the ImportODBC function. This is for clarity, as the input strings can be quite long. All values could be passed directly to the ImportODBC function as strings, for example, ImportODBC(“Driver={SQL Server};Server=…” etc.
Example 1. Select all records from a database table
The ImportODBC() function is used to return all records from the Customer table.
| Parameters | Values | Return all records |
| Connection String | Driver={SQL Server};Server=myServer;Database=myDatabase;Uid=sa;Pwd=12345; | =ImportODBC(B2,B3,,,) |
| Sql | Select * from Customers | |
| Criteria | ||
| Columns | ||
| Arr1 |
| Parameters | Values | Output | ||
| Connection String | Driver={SQL Server};Server=myServer;Database=myDatabase;Uid=sa;Pwd=12345; | CID_100 | Smith | India |
| Sql | Select * from Customers | CID_110 | Jones | China |
| Criteria | CID_120 | Green | Russia | |
| Columns | CID_130 | Brown | India | |
| Arr1 | ||||
Example 2. Select all records from a database table, and output specific columns
The ImportODBC() function is used to return all records from the Customer table, but only certain columns are shown in the output.
| Parameters | Values | Return all records, show only C2 and C3 |
| Connection String | Driver={SQL Server};Server=myServer;Database=myDatabase;Uid=sa;Pwd=12345; | =ImportODBC(B2,B3,,B4,) |
| Sql | Select * from Customers | |
| Criteria | ||
| Columns | C2,C3 | |
| Arr1 |
| Parameters | Values | Output | |
| Connection String | Driver={SQL Server};Server=myServer;Database=myDatabase;Uid=sa;Pwd=12345; | Smith | India |
| Sql | Select * from Customers | Jones | China |
| Criteria | Green | Russia | |
| Columns | C2,C3 | Brown | India |
| Arr1 | |||
Example 3. Select records from a database table that meet criteria contained in the SQL query string
The ImportODBC() function is used to return all records from the Customer table with the name ‘Jones’.
| Parameters | Values | Return all records |
| Connection String | Driver={SQL Server};Server=myServer;Database=myDatabase;Uid=sa;Pwd=12345; | =ImportODBC(B2,B3,,,) |
| Sql | Select * from Customers where Customer_Name = ‘Jones’ | |
| Criteria | ||
| Columns | ||
| Arr1 |
| Parameters | Values | Output | ||
| Connection String | Driver={SQL Server};Server=myServer;Database=myDatabase;Uid=sa;Pwd=12345; | CID_110 | Jones | China |
| Sql | Select * from Customers where Customer_Name = ‘Jones’ | |||
| Criteria | ||||
| Columns | ||||
| Arr1 | ||||
Example 4. Select records from a database table and filter the output by adding specifying criteria
The ImportODBC() function is used to return all records from the Customer table. The output is filtered using criteria, in this case showing only records where the country is India.
| Parameters | Values | Return all records |
| Connection String | Driver={SQL Server};Server=myServer;Database=myDatabase;Uid=sa;Pwd=12345; | =ImportODBC(B2,B3,B4,,) |
| Sql | Select * from Customers | |
| Criteria | C3 = ‘India’ | |
| Columns | ||
| Arr1 |
| Parameters | Values | Output | ||
| Connection String | Driver={SQL Server};Server=myServer;Database=myDatabase;Uid=sa;Pwd=12345; | CID_100 | Smith | India |
| Sql | Select * from Customers | CID_130 | Brown | India |
| Criteria | C3 = ‘India’ | |||
| Columns | ||||
| Arr1 | ||||
Example 5. Select records from a database table that meet criteria containing place holders
The ImportODBC() function is used to return certain records from the Customer table. The query filter contains ‘place holder’ parameters, shown as ‘?’. The values for these parameters are supplied to ImportODBC() in the form of an array, where the order of the values corresponds with the order of the occurrence of the place holder in the query.
| Parameters | Values | Return all records |
| Connection String | Driver={SQL Server};Server=myServer;Database=myDatabase;Uid=sa;Pwd=12345; | =ImportODBC(B2,B3,B4,B5,B6:B7) |
| Sql | Select * from Customers where Customer_Name = ? or Customer_Name = ? | |
| Criteria | ||
| Columns | ||
| Arr1 | Smith | |
| Jones |
| Parameters | Values | Output | ||
| Connection String | Driver={SQL Server};Server=myServer;Database=myDatabase;Uid=sa;Pwd=12345; | CID_100 | Smith | India |
| Sql | Select * from Customers where Customer_Name = ? or Customer_Name = ? | CID_110 | Jones | China |
| Criteria | ||||
| Columns | ||||
| Arr1 | Smith | |||
| Jones | ||||