Transfer Data from Excel to IBM Cloud Object Storage

Ready to get started?

Download for a free trial:

Download Now

Learn more:

Excel Add-In for IBM Cloud Object Storage

The IBM Cloud Object Storage Excel Add-In is a powerful tool that allows you to connect with live IBM Cloud Object Storage data, directly from Microsoft Excel.

Use Excel to read, write, and update IBM Cloud Object Storage IBMCloudObject, etc. Perfect for mass imports / exports / updates, data cleansing & de-duplication, Excel based data analysis, and more!



This article explains how to transfer data from Excel to IBM Cloud Object Storage using the Excel Add-In for IBM Cloud Object Storage.

The CData Excel Add-In for IBM Cloud Object Storage enables you to edit and save IBM Cloud Object Storage data directly from Excel. This article explains how to transfer data from Excel to IBM Cloud Object Storage. This technique is useful if you want to work on IBM Cloud Object Storage data in Excel and update changes, or if you have a whole spreadsheet you want to import into IBM Cloud Object Storage. In this example, you will use the Objects 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 IBM Cloud Object Storage connection by clicking From IBM Cloud Object Storage on the ribbon.

Register a New Instance of Cloud Object Storage

If you do not already have Cloud Object Storage in your IBM Cloud account, follow the procedure below to install an instance of SQL Query in your account:

  1. Log in to your IBM Cloud account.
  2. Navigate to the page, choose a name for your instance and click Create. You will be redirected to the instance of Cloud Object Storage you just created.

Connecting using OAuth Authentication

There are certain connection properties you need to set before you can connect. You can obtain these as follows:

API Key

To connect with IBM Cloud Object Storage, you need an API Key. You can obtain this as follows:

  1. Log in to your IBM Cloud account.
  2. Navigate to the Platform API Keys page.
  3. On the middle-right corner click "Create an IBM Cloud API Key" to create a new API Key.
  4. In the pop-up window, specify the API Key name and click "Create". Note the API Key as you can never access it again from the dashboard.

Cloud Object Storage CRN

If you have multiple accounts, you will need to specify the CloudObjectStorageCRN explicitly. To find the appropriate value, you can:

  • Query the Services view. This will list your IBM Cloud Object Storage instances along with the CRN for each.
  • Locate the CRN directly in IBM Cloud. To do so, navigate to your IBM Cloud Dashboard. In the Resource List, Under Storage, select your Cloud Object Storage resource to get its CRN.

Connecting to Data

You can now set the following to connect to data:

  • InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
  • ApiKey: Set this to your API key which was noted during setup.
  • CloudObjectStorageCRN (Optional): Set this to the cloud object storage CRN you want to work with. While the connector attempts to retrieve this automatically, specifying this explicitly is recommended if you have more than Cloud Object Storage account.

When you connect, the connector completes the OAuth process.

  1. Extracts the access token and authenticates requests.
  2. Saves OAuth values in OAuthSettingsLocation to be persisted across connections.

Retrieve Data from IBM Cloud Object Storage

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

  1. Click the From IBM Cloud Object Storage button on the CData ribbon. The Data Selection wizard is displayed.
  2. In the Table or View menu, select the Objects 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 IBM Cloud Object Storage

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, =MyObjectsSheetInExcel!A1.
  2. After using a formula to reference the cells you want to add to IBM Cloud Object Storage, 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 Objects sheet.
  3. Highlight the rows you want to insert and click the Insert 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.