How to update AlloyDB from Excel

The CData Excel Add-In for AlloyDB enables you to edit and save AlloyDB data directly from Excel. This article explains how to transfer data from Excel to AlloyDB. This technique is useful if you want to work on AlloyDB data in Excel and update changes, or if you have a whole spreadsheet you want to import into AlloyDB. In this example, you will use the Orders table; however, the same process will work for any table that can be retrieved by the CData Excel Add-In.

Establish a Connection

If you have not already done so, create a new AlloyDB connection by clicking From AlloyDB on the ribbon.

The following connection properties are usually required in order to connect to AlloyDB.

• Server: The host name or IP of the server hosting the AlloyDB database.
• User: The user which will be used to authenticate with the AlloyDB server.
• Password: The password which will be used to authenticate with the AlloyDB server.

You can also optionally set the following:

• Database: The database to connect to when connecting to the AlloyDB Server. If this is not set, the user's default database will be used.
• Port: The port of the server hosting the AlloyDB database. This property is set to 5432 by default.

Authenticating with Standard Authentication

Standard authentication (using the user/password combination supplied earlier) is the default form of authentication.

No further action is required to leverage Standard Authentication to connect.

Authenticating with pg_hba.conf Auth Schemes

There are additional methods of authentication available which must be enabled in the pg_hba.conf file on the AlloyDB server.

Find instructions about authentication setup on the AlloyDB Server here.

Authenticating with MD5 Authentication

This authentication method must be enabled by setting the auth-method in the pg_hba.conf file to md5.

Authenticating with SASL Authentication

This authentication method must be enabled by setting the auth-method in the pg_hba.conf file to scram-sha-256.

Authenticating with Kerberos

The authentication with Kerberos is initiated by AlloyDB Server when the ∏ is trying to connect to it. You should set up Kerberos on the AlloyDB Server to activate this authentication method. Once you have Kerberos authentication set up on the AlloyDB Server, see the Kerberos section of the help documentation for details on how to authenticate with Kerberos.

Retrieve Data from AlloyDB

To insert data into AlloyDB, you will first need to retrieve data from the AlloyDB table you want to add to. This links the Excel spreadsheet to the AlloyDB table selected: After you retrieve data, any changes you make to the data are highlighted in red.

1. Click the From AlloyDB button on the CData ribbon. The Data Selection wizard is displayed.
2. In the Table or View menu, select the Orders table.
3. In the Maximum Rows menu, select the number of rows you want to retrieve. If you want to insert rows, you need to retrieve only one row. The Query box will then display the SQL query that corresponds to your request.
4. In the Sheet Name box, enter the name for the sheet that will be populated. By default the add-in will create a new sheet with the name of the table.

Insert Rows to AlloyDB

After retrieving data, you can add data from an existing spreadsheet in Excel.

1. In a cell after the last row, enter a formula referencing the corresponding cell from the other spreadsheet; for example, . 👁 A local copy of a table. One row will be inserted. (QuickBooks is shown.)
   
2. After using a formula to reference the cells you want to add to AlloyDB, select the cells that you are inserting data into and drag the formula down as far as needed. The referenced values you want to add will be displayed on the Orders sheet. 👁 The range of changes to update. (QuickBooks is shown.)
   
3. Highlight the rows you want to insert and click the Update Rows button.

As each row is inserted, the Id value will appear in the Id column and the row's text will change to black, indicating that the record has been inserted.