Connect to OceanBase Database in Excel and retrieve data

Overview

Excel is a widely used spreadsheet and data visualization tool. You can directly connect to OceanBase Database using the OceanBase ODBC driver and perform efficient data queries and analysis.

This topic describes how to connect to OceanBase Database using ODBC data sources in WPS Office Excel and Microsoft Excel.

Version compatibility

• OceanBase Database version: ≥ V4.2.5 BP2.

Prerequisites

Before you connect to OceanBase Database in Excel, make sure that you have:

• Installed Excel (WPS Office Excel or Microsoft Excel).
• Installed The OceanBase ODBC driver (version ≥ 2.0.9.5hotfix). You can contact Technical Support for the driver.
• Deployed OceanBase Database and created an Oracle/MySQL mode user tenant. For more information about how to create a user tenant, see Create a tenant.

Procedure

Step 1: Obtain the database connection string

Obtain the connection string from the OceanBase Database deployment engineer. For example:

obclient -h$host -P$port -u$user_name -p$password -D$database_name

Parameter description:

• $host: the connection IP address. For ODP connections, use the ODP address. For direct connections, use the OBServer IP address.
• $port: the connection port. For ODP connections, the default port is 2883. For direct connections, the default port is 2881.
• $database_name: the database name.

  Notice

  The user connecting to the tenant must have the CREATE, INSERT, DROP, and SELECT privileges on the database. For more information about user privileges, see Privilege types in MySQL mode.

• $user_name: the connection account. For ODP connections, the format is User@Tenant#Cluster or Cluster:Tenant:User. For direct connections, the format is User@Tenant.
• $password: the account password.

For more information about the connection string, see Connect to an OceanBase tenant by using OBClient.

Here is an example:

obclient -h$host -P2881 -u$user_name -p$password -D$database_name

Step 2: Configure the ODBC DSN

This topic describes how to configure the ODBC DSN for an Oracle mode user tenant of OceanBase Database. You can configure the ODBC DSN for a MySQL mode user tenant in the same way.

1. Open the ODBC Data Source Administrator.
2. On the System DSN or User DSN tab, click Add.
3. Select OceanBase ODBC 2.0 Driver.
4. Configure the connection parameters:
   • Data Source Name: specify the DSN name (for example, OBORACLE).
   • Description: (Optional) specify a description.
   • Server name: the IP address of the OceanBase server (corresponding to $host).
   • Port: the port number (corresponding to $port).
   • Database: the database name (corresponding to $database_name).
   • User name: the username (corresponding to $user_name).
   • Password: the password (corresponding to $password).

Step 3: Connect to OceanBase Database

WPS Office Excel

1. Open WPS Office Excel.
2. Choose Data > Get Data > Import Data > ODBC DSN.
3. In the data source selection dialog box, select the configured ODBC DSN (for example, OBORACLE).
4. Select the tables and fields to import.
5. In the data filtering and sorting step, you can directly input an SQL statement for querying.
6. Preview and complete the data import.

Microsoft Excel

1. Open Microsoft Excel.

2. Choose Data > Get Data > From Other Sources > From ODBC.

3. Select the configured ODBC DSN (for example, OBORACLE).

4. In the advanced options, you can directly input an SQL statement, for example:

   SELECT * FROM T1;
   

5. Click OK and load the data.

FAQ

How to fix garbled characters when connecting to OceanBase Database? Cause: Outdated ODBC driver version Solution: Upgrade to OceanBase ODBC 2.0.9.5 hotfix or later.

References

• OceanBase ODBC documentation