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

Magento 2 Excel import export

If you have an Excel file (XLS or XLSX) with data that should be imported to Magento 2, you’ve come to the right place. Although the e-commerce platform supports neither XLS nor XLSX by default, we know how to achieve your goal. The following article describes the most user-friendly way of Magento 2 Excel files import.

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 buy native Excel files on import to Magento 2.

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 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.

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.

Attribute mapping

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.

File type Number of rows Number of columns
XLS 65536 256
XLSX 1048576 16384
ODS 3.0 1048576 1024

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 choosing XLS/XLSX and selecting imported entity.
  • Create a schedule of updates.
  • Apply a mapping scheme.
  • Launch the profile.

The 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

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, the 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 that the predecessor. At the same time, it does not support Excel macros in order to provide a better security level.

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

Magento 2 Excel import export

And you can easily import such file to Magento 2 with the help of the Improved Import & Export extension.