How to Import Excel (XLS & XLSX) and OpenOffice (ODS) Files to Magento 2

We’ve already described hundreds of data import cases related to Magento 2 and many more are left to be mentioned in our blog. This time, we return to the topic of importing file standards that Magento 2 doesn’t support and highlight the Magento Excel integration. Unfortunately, the system works with CSV files only and it is not the only requirement you should follow to be able to transfer an update to your database. You also have to edit third-party data and columns to make the file suitable, but let’s return to the unsupported formats.   

If you have an Excel (XLS or XLSX) or ODS file with data that should be imported to Magento 2, you’ve come to the right place. Although Magento 2 doesn’t let you do that by default, there is a reliable alternative solution at your service. Meet the Improved Import & Export extension – a tool that offers the most user-friendly way of Magento 2 Excel and ODS files import.

In the following article, we shed light on what to do with XLSX, XLS, and ODS files if you want to move them to Magento 2 via the default data transfer tools. After that, you will find a guide on how to import Excel and ODS files to Magento 2 in a way more user-friendly manner. Of course, Magento’s export of products to Excel is also described below.

Importing Excel files to Magento 2 by default

In a situation when you have an XLS or XLSX file with the product or other data that should be imported to your Magento 2 store, there is a standard way to achieve your goal: you have to convert the existing file format to CSV which is supported by the platform. Unfortunately, the procedure is complicated and time-consuming but what if there is a better way to do so?

Meet the Improved Import & Export Magento 2 extension. This module will help you forever forget all headaches caused by native Excel files on import to Magento 2.

Magento Excel, ODS import with Improved Import & Export

The Improved Import & Export Magento 2 extension fully supports XLS or XLSX import and export allowing you to move data in both formats to and from your e-commerce store. Let’s see what entities are supported.

• Magento 2 XLS/XLSX Product Import. With the Improved Import and Export Magento 2 extension, you can easily transfer product data between your Magento 2 website and any third-party system with the help of native Excel files. Both XLS and XLSX formats are supported. Furthermore, you can leverage other features that are available in the case of other file formats (we describe them below).
• Magento 2 XLS/XLSX Customers Import. The Magento 2 customers’ import via native Excel files is also available with the Improved Import & Export Magento 2 extension. Seamlessly transfer all information about your registered clients from old stores or third-party systems to your Magento 2 website.
• Magento 2 XLS/XLSX Orders Import. Another entity supported by our extension is Orders. You can freely transfer them to and from your store in the form of Excel files with all the related data. The Improved Import & Export Magento 2 extension offers flexible settings and allows moving orders with credit memos, invoices, shipping documents, and tracking data.
• Magento 2 XLS/XLSX Categories Import. Categories in XLS and XLSX are no longer a problem for the Improved Import & Export module. With the help of the extension, you will import Excel categories to Magento 2 in a split second.
• Magento 2 XLS/XLSX CMS Pages Import. If you have CMS Pages-related data stored in native Excel files or need the corresponding output, our module will fully satisfy your needs.
• Magento 2 XLS/XLSX Coupons Import. Coupons and coupon codes are also fully supported. Feel free to import them as XLS or XLSX files or create the appropriate output with the help of the Improved Import & Export Magento 2 extension.
• Magento 2 XLS/XLSX Attributes Import. If attributes are stored in a native Excel file, you can use our module to avoid converting them into CSV to run the import process.

Note that the extension also offers the Magento export of products to Excel files. You just need to specify the corresponding file format while configuring your export profile.

As for the supported features, they include scheduling and mapping. Both provide the ability to automate data updates. Let’s take a look at each feature individually.

Since the Improved Import & Export Magento 2 module supports cron, you can quickly create any custom schedule of updates with the help of the standard cron syntax. Alternatively, the module provides a list of predefined intervals. Choose one that suits your needs.

The mapping functionality of the Improved Import & Export Magento 2 extension is extremely powerful and flexible. Due to a user-friendly interface, you can quickly create a mapping scheme, matching the default attributes of Magento 2 to any custom standard. This feature helps you eliminate manual data editing on every update. Instead of doing the same actions before every XLS/XLSX import, you create a scheme that is utilized for all further updates.

Excel, XLS, XLSX, and ODS file peculiarities

However, coming from Microsoft Excel XLS and XLS formats are different and their difference should be considered when performing import or export tasks. Let’s break down what it means to use XLS and XLSX.

XLS is an old file format supported by Excel. You probably won’t meet it anywhere these days, however, we have introduced its support to cover all use cases. XLS uses binary format of storing data. Binary format means that you can store a pretty limited number of rows and columns. For XLS you can store 65536 rows and 256 columns. For example if you are importing and exporting full order details, you will need to handle 400+ columns, and here XLS files won’t be an option.

XLSX file format is the format used by Excel by default. It is based of Open XML file format. Open XML allows XLSX files to store up to 1048576 rows and 16384 columns. Making XLSX a fine choice for importing and exporting Orders to Magento 2.

ODS format are also based of Open XML, however, their maximum column and row numbers are different. Modern ODS formats support up to 1048576 rows and 1024 columns.

Here is a file format table for quick reference.

If you are not familiar with the Microsoft Excel native file formats, we should continue this article by introducing you to the aforementioned XLS and XLSX. Until 2007 version, Excel used a proprietary binary file format called Excel Binary File Format. It is the standard XLS format. Initially, it was Excel’s primary form until 2007. Although the use of new XML-based formats is prevalent, backward compatibility with the traditional, binary formats is still available. That’s why the Improved Import & Export Magento 2 Extension also supports XLS.  

As for the XLSX format, it is the default format for Excel 2007 and the next workbook. Being a ZIP compressed archive with a directory structure of XML text documents, it provides broader opportunities than the predecessor. At the same time, it does not support Excel macros to provide a better security level.

Below, you can see the example of Excel orders data file:

You can easily import such files to Magento 2 with the help of the Improved Import & Export extension.

Magento 2 Import & Export FAQ

Let’s take a look at other nuances of importing and exporting XLSX, XLS, ODS, and other files in Magento 2 with the help of the Improved Import & Export Magento 2 extension.

Conclusion

To import native Excel files to Magento 2, you need to:

• Install the Improved Import & Export Magento 2 module.
• Configure a new import profile by choosing XLS/XLSX and selecting an imported entity.
• Create a schedule of updates.
• Apply a mapping scheme.
• Launch the profile.

A similar procedure is required to export XLS/XLSX from Magento 2. For further features of the Improved Import & Export Magento 2 extension, watch this video:

You can also find more information here:

Download/Buy Improved Import & Export Magento 2 Extension