How to Import & Export Data from Office 365 Excel Online to Magento 2

Do you use Office 365? Perhaps, your e-commerce partners do that? Anyhow, we have some good news for you: it is possible to connect Magento 2 to Office 365 Excel Online directly. By default, Magento 2 doesn’t provide this opportunity. Furthermore, it doesn’t offer support for native Excel files, but we know a reliable solution that enables the data import and export from Office 365 Excel Online in Magento 2.

In the following article, we briefly describe what Microsoft Office 365 Excel is. Consider it a reliable alternative to Google Sheets! After that, you will learn how to import and export data from Office 365 Excel Online into Magento 2. We describe the features of the Magento 2 Office 365 Excel integration and provide a step-by-step guide on how to import data from the popular service into your e-commerce website. In addition, there is a chapter that describes the benefits of this integration. 

The last section of the article is dedicated to the Improved Import & Export extension since it is the foundation of the integration. We describe such features as cron-based automation, mapping, supported file formats, and sources, as well as alternative ways to exchange data between Magento 2 and other systems.

Microsoft Office 365 Excel

Microsoft Excel has been used by multiple generations of specialists, enthusiasts, and anyone else you could even imagine worldwide. It is the most popular software for creating and managing sheets. The feature-rich functionality combined with simplicity is the key factor of the vast popularity of Excel, but it is still software that is necessary to be installed. Furthermore, it generates multiple files which you store on your computer. The ability to work with the cloud steps with time while desktop applications become more and more outdated.

Guys at Microsoft wouldn’t be so successful if they didn’t understand such simple truths. Perhaps, it is their pragmatism or the rising popularity of Google Sheets that moves Microsoft Excel further, but there is no escaping the fact that you can reach your favorite sheet-editing tools online. Subscribe to Office 365 and get all the benefits of its online suite. Note that you can try it for free and compare it with Google Sheets if necessary.

Furthermore, there is even the Excel mobile application – an unprecedented luxury for Excel 2010. Thus, you can turn data into insights and get its best possible representation right under your fingertips. Share your workbook with others and work on the latest version in real-time. Does this ring any bells?

To make the long story short (you can find more information about Office 365 somewhere on the internet), let’s return to the crux of the matter.

How to Import & Export Data from Office 365 Excel Online into Magento 2

As we’ve mentioned above, you cannot import/export data from the online version of Office 365 Excel into Magento 2 by default. To achieve this goal, you need a third-party tool, so meet the Improved Import & Export Magento 2 extension. This module enables the desired functionality as well as adds support for native Excel files. Let’s take a look at Office 365-specific features.

Magento 2 Office 365 Excel Online Integration Features

• Full support of Office 365 Excel Online – paste links and import directly from the cloud service;
• Tier price import with products – imports both products and tier prices from a single source;
• Configurable products on the fly – specify SKU of a parent product for a simple product and a configurable product will be created automatically;
• Import/export schedules for data synchronization with Office 365 Excel Online;
• Asynchronous import and export processes;
• Full Magento 2.x compatibility, Open Source, Commerce and Commerce Cloud;
• Language and Locale settings per import/export job;
• ‘Only update’ import job behavior;
• ‘Reindex after import’ behavior;
• ‘Generate a unique URL if duplicate’ behavior;
• Advanced mapping and filters;
• Category id on files instead of name.

If this doesn’t impress you, we’d like to draw your attention to the import process directly.

Import Data from Office 365 Excel Online into Magento 2

Let’s assume that your supplier provides you with data updates on a weekly basis on Mondays. You get all the necessary information via Microsoft 365 Excel where the supplier submits all the changes. The Improved Import & Export Magento 2 extension lets you automate the process of data transfers in the following manner:

1. First of all, you need to go to the extension admin and create a new import job there.
2. Next, schedule it enabling fully automated data import every Monday.
3. Now, you have to paste the link to the file and save the job.

That’s it! You’ve just configured data import from Office 365 Excel to Magento 2. Note that this was only a basic configuration, and multiple other parameters can be enabled/disabled, but this one is enough to start the synchronization.  

Get Improved Import & Export Magento 2 Extension

Benefits of Office 365 Excel Online

With the support for Office 365, you face a new level of importing products into Magento 2.  Along with the support for Google Sheets introduced in Improved Import and Export extension, this one enables even more extensive opportunities than the module currently offers. What it means is:

1. Office 365 Excel is one of the most convenient tools for editing data sheets;
2. You can share a single import table with multiple colleagues simultaneously;
3. The Improved Import & Export extension enables an only step import;
4. Schedule the import job to upload table contents to your store automatically or specify a particular event to launch the import;
5. Add or remove columns, create new sheets relevant to your current tasks, etc.

Improved Import & Export Features

Now, we’d like to concentrate on multiple features of the Improved Import & Export extension that provide the ability to transfer data from Office 365 Excel into Magento 2 in a more smart way.

How to Import & Export Data from Office 365 Excel Online into Magento 2 Automatically

Our module introduces two ways to automate data updates. First of all, you can create a schedule of import and export processes. The second provided opportunity is based on system and custom events used as triggers.

Schedules

The Improved Import & Export Magento 2 extension supports cron allowing you to create any custom update schedule. You only need to learn the default cron syntax which is very straightforward. If you don’t want to do that, the plugin offers an opportunity to select one of the predefined intervals. Note that they are fully customizable as well.

If you don’t need to automate updates, the Improved Import & Export Magento 2 extension provides the ability to create jobs with no schedule, which are efficient for one-time data transfers, migrations, or asynchronous updates. It is also possible to launch every scheduled process manually whenever you want. Use the asynchronous mode if a new update is provided before the schedule.

Below, you can see how to configure a section dedicated to schedules:

Events

To make the Improved Import & Export Magento 2 extension more flexible, we’ve introduced the ability to run data updates after a specific even automatically. Note that the module supports both default and custom events, so you only need to specify a trigger and create conditions to make import and export processes more precise. For further information about this feature, read the following article: How to Run Magento 2 Import or Export After Specific System Event or Process.

How to Import & Export Data from Office 365 Excel Online into Magento 2 with Third-Party Attributes

Another feature introduced in the Improved Import & Export Magento 2 extension is the ability to import data with third-party attributes into Magento 2 without any headaches. Our plugin offers two ways to achieve this goal. First of all, you can apply mapping presets – the most straightforward way. Alternatively, the module allows creating a mapping scheme manually.

Mapping Presets

Forever forget about manual data file editing. Our module eliminates this process due to mapping presets. You can effortlessly apply them within just a few clicks. The Improved Import & Export extension will analyze the external data and match third-party attributes to ones used in Magento 2. As a result, attributes matching will be reduced to just a few clicks like illustrated below:

Manual Matching

At the same time, the Improved Import & Export Magento 2 extension provides the ability to match attributes manually within a convenient interface. You need to select an external attribute and match it to the corresponding internal one. Apply this tactic to all third-party attributes. Below, you can see a gif image that shows how to do that:

Note that the mapping grid includes one more column where you can specify hardcoded attribute values that are added to all items automatically.

In both cases, you solve the problem of different attribute standards that prevents two external systems from being directly connected without data editing. As you might have noticed, even Magento 1 and Magento 2 use different attributes. And above, we’ve illustrated how to link one system to another in the most seamless manner via the corresponding mapping preset. Even if you import data from Office 365 Excel into Magento 2, there is a possibility that you will face the problem. Luckily, Improved Import & Export helps to solve it completely. Furthermore, there are multiple more features in this area.

Attribute Values Mapping

Additionally to attributes, you can map their values with the Improved Import & Export extension. The procedure is just the same except the presets. You select an external attribute value and match it to the one used internally. Note that it is possible to map multiple external values to the one utilized in your database. For instance, you may use “green” instead of “lime-green” and “avocado-green”. Thus, it is necessary to map the two external values to the corresponding internal one. You can find more information regarding attribute values mapping here: Attribute Values Mapping.

Attribute Values Editing

If the functionality mentioned above is not enough to manage attribute values as much as your particular business situation requires, we have some good news! You can edit attribute values in bulk. The Improved Import & Export Magento 2 extension offers multiple rules that provide the desired changes.

You can either add a suffix or prefix to many values in bulk. Also, these rules can be combined. And it is possible to create special conditions to edit values in a more precise manner.

At the same time, the Improved Import & Export Magento 2 extension lets you split or merge data which can be handy as well. If information regarding multiple attribute values is stored in the same block, our module provides the ability to split into different cells. The corresponding features are described here: How to Modify Attribute Values During Import and Export in Magento 2.

Category Mapping

Another feature we’d like to draw your attention to is the ability to map categories. Every time you import data from an external system (whether it is stored in Excel 365 or somewhere else), you may face the situation when external categories are not suitable for your catalog. Luckily, the Improved Import & Export Magento 2 extension will quickly fix the problem.

Our module is designed to make data import/export processes as seamless as possible and different categories are no longer a problem. With our tool, you can map third-party categories to ones used in your catalog right during the import process. Furthermore, the Improved Import & Export extension lets you create new categories within just a few clicks. It is only necessary to specify a parent category and the new name. For further information, follow this link: Category Mapping.

Attributes On The Fly

And you can freely create attributes on the fly with the help of our module If any data is absent in the import file, the Improved Import & Export Magento 2 extension provides the ability to specify parameters necessary to create the missing attributes. Follow this link to discover the full algorithm of attributes creation on the fly: Product attributes import.

Other Ways of Import & Export

In addition to the ability to import and export data from Office 365 Excel, the Improved Import & Export Magento 2 module lets you establish the connection between your e-commerce store and any external systems in multiple other ways. Let’s take a look at standard file transfers first.

File Transfers

While Magento works with CSV files only, our extension adds a lot of new formats into the game. As a result, you no longer need to convert data files to the only supported file type to make them suitable for import. With the Improved Import & Export Magento 2 extension, you can freely transfer information in XML, JSON, ODS, and Excel formats to and from your store.

File Sources

It is also worth mentioning that our plugin supports numerous file sources. Thus, you can transfer data files using several different options.

First of all, the Improved Import & Export module enables FTP/SFTP integration. It means that you can transfer data files using a local or remote server. At the same time, there is support for Dropbox so that an account in the cloud can be utilized for the same purpose.

When it comes to the import only, our extension lets you transfer data files right via a direct URL. Specify the file address and get the update transferred to your e-commerce website. In case of compressed data, the extension will unpack it automatically.  

Alternative Ways of Import & Export

Not only the Office 365 integration allows avoiding data files – the Improved Import & Export Magento 2 extension supports several more ways of connections.

First of all, we need to mention the Google Sheets integration since it is almost the same as the one related to Office 365. Since the online version of Microsoft’s product resembles Google Sheets, it inspired us to extend the features of the module with the corresponding functionality described in this article. As for the Google Sheets import, it looks as follows:

Alternatively, you can avoid any third-party systems from data transfers due to the REST API, SOAP API, or GraphQL API integration. Connect to the database of your partner directly and apply all the features mentioned above: schedules, event-based imports, mapping, etc. Note that the external system should support one of these API types.

For further information about the Improved Import & Export Magento 2 extension, watch the following video:

Final Words

As you can see, it is quite easy to import/export data from Office 365 Excel Online into Magento 2. You get a fast way of data transfers enhanced with multiple features. Both import & export processes can be fully automated and launched by cron or specified events. You can map any external attributes to ones used internally and apply the same procedure to their values.

Furthermore, even categories can be mapped within only a few clicks. And if for some reason you no longer need to transfer data from Office 365, the Improved Import & Export Magento 2 extension offers multiple more ways to connect your e-commerce store with any external systems. If you still have any questions, follow the link below and contact our support:

Get Improved Import & Export Magento 2 Extension

Magento 2 Office 365 Excel Online Import FAQ