How to Connect Excel to Adobe Commerce (Magento)

Microsoft Excel is one of the top spreadsheet apps used every day by students and CEOs alike. Moreover, it can become your perfect candidate for analyzing and managing Adobe Commerce data with ease and simplicity. The best way to connect Excel to Adobe Commerce is Devart Excel Add-in for Magento, which helps you work with products, categories, orders, attributes and other Adobe Commerce data objects as if with regular Excel worksheets.

Before installing Devart Excel Add-in for Magento, make sure your system meets the necessary requirements. This may include having an appropriate version of Excel and sufficient permissions for installation.

1. Your first step is to open Microsoft Excel and start a new workbook or open an existing one.

2. Proceed to the menu at the top of the window. There you should see the Devart tab, which is added automatically once the Devart Add-in is successfully installed. If you do not see it, you might need to restart Excel or double-check whether the Add-in is enabled in File > Options > Add-ins.

3. Open the Devart tab and click Get Data. Thus you will be able to import data into Excel.

4. In the Import Data Wizard that opens, enter the Adobe Commerce connection parameters. First, select Adobe Commerce as the Data Source.

5. Next, select the Service Version. The version depends on whether you are connecting to Adobe Commerce 1.x or 2.0. The default version is Ver1, suitable for for Adobe Commerce 1.x. If you want to connect to Adobe Commerce 2.0, select Ver2. For further information, refer to product documentation.

6. Next, enter the following parameters:

• Domain — The address of your Adobe Commerce store
• User Id — The user name that you will sign in with
• Password — The password for token-based authentication (available for connections to Adobe Commerce 2.0)

7. In addition to that, you can enable the following options by selecting the corresponding checkboxes:

• 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. Specify 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. After you enter all the parameters, click Test Connection. If everything is correct, you will see the Successfully connected message.

11. Click Next.

Having connected to Adobe Commerce, you can start importing data to Excel.

1. In the Import Data Wizard, go to the Visual Query Builder tab and pick the required database object(s) from the Object list. In our case, we are going to work with the Products table.

2. Afterwards, click Finish to import data.

Load data using a SQL query

The Visual Query Builder is not the only way to import Adobe Commerce data into Excel. You can do it with a SQL query. Simply switch to the SQL Query tab in the Import Data Wizard and enter your query.

Next, on the Preview Data page, you can check the data and specify the following import options:

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

4. Once you are ready, click Finish.

You can conveniently edit data directly in the worksheet. To do that, you need to start an update session by clicking Edit Mode. Now, the entire imported table is at your fingertips.

• Modify data

You can edit data in separate cells. Modified 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. To add a new row, enter data into the cells of the said row and hit Enter.

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

• Delete rows

To delete rows, select and right-click them, and then 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.

Let's proceed to upload the modified data back to Adobe Commerce. It's easily done by clicking Commit.

In case you want to roll back something you have committed, simply click Rollback.

Integration of Microsoft Excel with Adobe Commerce helps you connect, import, edit, and load data in the easiest and most convenient way. This gives you more flexibility and control over your data. You can get started by downloading Devart Excel Add-in for Adobe Commerce for a free trial. And if you work with multiple data sources beyond Adobe Commerce, consider downloading the exhaustive Devart Excel Add-in Universal Pack.