How to Connect Excel to BigCommerce

Excel is a widely known platform among professionals across various fields. It is used daily by everyone from students to CEOs of top companies. Due to its familiarity, it can be of use even when it comes to managing and analyzing BigCommerce data. All you need to do is connect Excel to BigCommerce using Devart Excel Add-Ins, and your business will be able to import data and updates directly within Excel in real time. This integration streamlines the process of analyzing sales, inventory, and customer data and provides features like data filtering, sorting, and reporting, enhancing efficiency and decision-making.

Before installing any Excel add-ins, ensure your system meets the necessary requirements. This may include having the correct version of Excel and sufficient permissions for installation.

1. To begin working with the Excel Add-in, open Microsoft Excel and start a new workbook or open an existing one.

2. Locate the toolbar at the top of the window. There, you should see the Devart tab. This tab is added automatically once the Devart add-in is successfully installed. If you do not see it, you might need to restart Excel or check if the add-in is enabled in options under File > Options > Add-Ins.

3. Once you locate the Devart tab, click the Get Data button. This option allows you to import data into Excel.

By clicking this button, you will open a wizard that guides you through connecting to your data source.

4. In the Import Data Wizard that opens, you will need to enter the BigCommerce connection parameters. First, select BigCommerce and the Data Source.

5. Select the API Version. Devart Excel Add-ins allow you to connect to BigCommerce using BigCommerce API v2 or API v3. In our today's example, we are going to stick with v3. For more information regarding v2, please refer to our documentation.

6. For API v3, Devart Excel Add-ins use OAuth authentication, which requires the following parameters:

• Store Id — the store hash from the API Path.
• Client Id — The Client Id of your API account.
• Access Token — the OAuth access token to log in with.

Such parameters as Client Id and Access Token can be obtained only when creating an API account in BigCommerce. Therefore, if you do not have these parameters, you need to create a new API account.

As to the Store Id, when you log in to your BigCommerce store Control Panel, a similar URL opens in your browser: https://store-kj3jh4c.mybigcommerce.com/manage/dashboard. The highlighted part (kj3jh4c) is the required store ID.

7. In addition to entering the parameters, you can specify whether to:

• Allow saving add-in specific data in the Excel worksheet.
• Allow saving connection strings in the Excel worksheet.
• Allow saving security info.
• Allow reusing connections in Excel.

8. Enter the Connection Name.

9. The Import Data Wizard allows you to include some advanced options in the connection string. To open the Advanced Connection Editor, click Advanced.

10. Once all the parameters are entered, click Test Connection. You will see the Successfully connected message.

11. Click Next.

Having connected to the BigCommerce, let us pave our way into actually getting all the data from there into Excel.

1. In the Import Data Wizard, switch to the Visual Query Builder tab and select the needed database object in the Object list. Here, we are going to select the Customers table.

2. When you are ready to proceed, click Finish to import data and start editing.

Load Data Using SQL Query

The Visual Query Builder is not the only way to load BigCommerce data to Excel. To preview or customize the SQL query, you can switch to the SQL Query tab in the Import Data Wizard.

On the Preview Data page, preview the data and specify the Excel import options:

• Create a new worksheet for the imported data.
• Replace data on the existing worksheet with the imported data.

4. Once done, click Finish.

Devart Excel Add-Ins for BigCommerce allows you to conveniently edit data directly in the worksheet. To edit data in the table, you will need to start an update session. To do this, click the Edit Mode button. Now, you can edit data in the imported table like you usually do.

• Modify data

Modify separate cells. After modification, those cells will be highlighted in olive.

• Add new rows

By default, the empty row at the bottom of the table with empty cells will be highlighted in green. In order to add new rows, simply fill in the cells of the said row and press Enter.

Alternatively, right-click on the number of the row to insert a new row before the selected one. Then, click Insert and enter the required data. The new row will be highlighted in yellow.

• Delete rows

Select the rows you wish to remove, make a right click, and select Delete from the menu. The deleted row(s) will be highlighted in dark yellow.

Note: You can change all the highlighting colors in the Appearance section of Options.

Now that we know how to import data from BigCommerce into Excel and how to manipulate it, we can move on to how to upload the modified data back to BigCommerce. The Commit button serves this exact purpose. Thus, as soon as you are content with the looks of your data, feel free to click this button:

In case you have committed something that you didn't mean to, there's always an escape route. To undo what was done, click the Rollback button.

Integrating Microsoft Excel with BigCommerce streamlines data management by allowing you to easily connect, import, edit, and load data between platforms. This enhances efficiency, giving you more control over your e-commerce data directly from Excel. The Devart Excel Add-Ins for BigCommerce provide a powerful solution for managing this connection, offering an intuitive interface and robust features that simplify the entire process. To maximize your workflow, consider downloading the Universal Pack.