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

- E-Commerce, Magento 2, Our extensions

Magento 2 Excel import export

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 the Magento 2 doesn’t support. 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.

'

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.

Importing Excel and ODS files to Magento 2 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.

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

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

How to import and export XLSX, XLS, ODS, and other files to Magento 2 automatically?

If you need to import or export XLSX, XLS, ODS, and other files between Magento 2 and third parties or other Magento 2 websites on a regular basis use the Improved Import & Export extension. The module uses a cron scheduler for automating data transfers. As a result, you get the ability to import and export XLSX, XLS, ODS, and other files between Magento 2 and external destinations automatically. Create a schedule of updates and let the module do everything for you.

How to import XLSX, XLS, ODS, and other files with third-party attributes to Magento 2?

While Magento 2 blocks the import of third-party data that doesn’t follow its requirements, the Improved Import & Export extension lets you run any transfer. You only need to create a mapping scheme that matches third-party attributes and values to the corresponding ones used within the system. As a result, any external data is easily adapted to the native standards of your e-commerce website, no matter if it is XLSX, XLS, ODS, or other file types. Note that you can also apply mapping to export processes to generate the output your partners require, freely transferring any files to and from Magento 2.

How to import and export data to Magento 2 via ODS files?

Improved Import & Export lets you move data via ODS files so that you no longer need to convert them to CSV to fit the Magento 2 requirements. You can also export data using this file format.

How to import and export data to Magento 2 via XLSX files?

Improved Import & Export lets you move data via XLSX files so that you no longer need to convert them to CSV to fit the Magento 2 requirements. You can also export data using this file format.

How to import and export data to Magento 2 via JSON files?

Improved Import & Export lets you move data via JSON files so that you no longer need to convert them to CSV to fit the Magento 2 requirements. You can also export data using this file format.

How to import and export data to Magento 2 via XML files?

Improved Import & Export lets you move data via XML files so that you no longer need to convert them to CSV to fit the Magento 2 requirements. You can also export data using this file format.

How to import data to Magento 2 via compressed files?

If your XLSX, XLS, ODS, or files of other types are compressed, you can also transfer them to Magento 2 with the help of the Improved Import & Export extension. However, there is a requirement to follow: the compression should follow ZIP or TAR standards. As for the supported file types, they include CSV, JSON, ODS, XML, and XLSX files.

How to import data to Magento 2 from Google Sheets?

With Improved Import & Export, you can freely transfer data straight from Google Sheets. No data files are required! If you use the default tools of Magento 2, it is necessary to create a CSV output and then import it to Magento 2. The Improved Import & Export lets you move the corresponding information straight from Google Sheets to your e-commerce website after you specify the address of the data table you want to transfer.

How to import and export files to Magento 2 from Dropbox?

In addition to Google Sheets, the Improved Import & Export module lets you use Dropbox as a data source. Your XLSX or ODS files should be stored there. Other supported formats include CSV, JSON, and XML. If it is a compressed file, it should be in a ZIP or TAR format.

How to import and export XML files to Magento 2 from Google Drive?

The same opportunities are associated with Google Drive when you transfer data files to/from Magento 2. You can use this file storage for CSV, XML, JSON, ODS, and XLSX files that can be compressed in a ZIP or TAR format.

How to import and export data to Magento 2 via API?

If you want to avoid using default file transfers, the Improved Import & Export Magento 2 extension lets you create API connections. As a result, it is possible to move information between Magento 2 and any external system in a bi-directional API transfer. Set the parameters necessary to sync the two systems in corresponding import and export jobs, specify mapping for unsupported attributes, create a schedule of updates, and let the plugin create a new API connection.

'