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 through the OceanBase ODBC driver to efficiently query and analyze data.

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

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).
• You have installed the OceanBase ODBC driver (version ≥ 2.0.9.5hotfix). The driver is not publicly available. Contact OceanBase Technical Support for more information.
• You have deployed OceanBase Database and created an Oracle or 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 an ODP connection, use the ODP IP address. For a direct connection, use the OBServer IP address.
• $port: the connection port. For an ODP connection, the default value is 2883. For a direct connection, the default value is 2881.
• $database_name: the database name.

  Notice

  The user who connects 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 an ODP connection, the format is User@Tenant#Cluster or Cluster:Tenant:User. For a direct connection, 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 uses an OceanBase Database user tenant in Oracle mode as an example to configure the ODBC DSN. The procedure for an OceanBase Database user tenant in MySQL mode is similar.

1. Open the system ODBC Data Source Administrator.
2. In the System DSN or User DSN tab, click Add.
3. Select the OceanBase ODBC 2.0 Driver driver.
4. Configure the connection parameters:
   • Data Source Name: enter the DSN name (for example, OBORACLE).
   • Description: optional description.
   • Server name: the OceanBase Database server address (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. Navigate to 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 enter an SQL statement to query.
6. Preview and complete the data import.

Microsoft Excel

1. Open Microsoft Excel.

2. Navigate to 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 enter 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