Converting excel files for Magento 2 (Magento Open Source 2, Adobe Commerce) import

Regardless of all the features and advanced functionalities, Magento 2 (Magento Open Source 2, Adobe Commerce) provides merchants with, it has a couple of things that at time nonplus merchants. One is that, default import tool has strict requirements to import file and, what is more pressing, accepts data, arranged in .csv files only.

What if you get data feeds from different sources or supplier cooperating with you sends product listings in Excel spreadsheets only? This is a widespread phenomenon and the way out is self evident - if you want to upload Excel to Magento 2 (Magento Open Source 2, Adobe Commerce) , you need to derive the same file format as this import utility supports, in other words, convert .xls/.xlsx files to .csv ones.

Excel VS Comma-Separated Values File

XSL/XLSX are proprietary binary file formats and are used for work with electronic tables and text within Microsoft Excel. Excel file stores information in worksheet and if there are numerous worksheets, they are arranged in workbook that comprises both content and formatting.

CSV files is commonly used file format that stores tabular data as plain text, detaching fields using delimiters. Different implementations of CSV allow various separators, namely comma, back or forward slash. CSV file differs from other delimiter separated formats since double quote characters enclose field values. Comma-separated values files are used for data transfer across various applications.

How Can I Reformat The File?

At first sight this seems to be simple and trouble-free procedure to transform .xsl files and perform Magento 2 (Magento Open Source 2, Adobe Commerce) import from xsl, but there are some buts that should be taken into consideration.

First, naturally, converter tool should be selected. Nowadays, you may resort to web-based converters, upload necessary file, select target file format and restructure Excel to .csv. If you have programming skills, you may get consistent with file transforming to .csv with the help of special scripts. Traditionally, shop possessors fall back upon manual file converting and reformat .xsl/xslx using Excel resave option. Basically, you have to do the following:

1. Switch to the File tab and select “Save As” option from the dropdown
2. Select .csv in “Save as type” dropdown below in the window




3. Choose destination folder (.csv file will be saved to)

When converting files you should keep in mind:

• Oftentimes, special characters used in .xsl/.xslx spreadsheets are not converted correctly. The reason might be in incorrect character set. Magento 2 (Magento Open Source 2, Adobe Commerce) appends only UTF-8 formatted .csv files. Excel does not always save files in UTF-8 encoding. Respectively, when converting files, you are supposed to change the encoding to UTF-8 if some other is used. Here you can also change file delimiters.




• Manual file converting requires you to resave each .xsl sheet separately if Excel workbook, you work with, consists of numerous sheets. If you have multiple sheets in the workbook, you will get warning messages notifying that .csv does not support multiple worksheets and some features inherent to Excel file are not compatible with .csv files. Only activate .xls sheet is being reformatted at a time.

Each of file converting means has its drawback. Online solutions, as a rule, have some restrictions, as for example, file size limitations or require payment, so that, to upload Excel to Magento 2 (Magento Open Source 2, Adobe Commerce) you are supposed to pay for file converting each time. When speaking about scripts used for .xsl data transforming, they are accessible for users who have technical skills and won’t suit average store owners.

Whenever you find all the points mentioned above time-consuming and think that file transforming is not the way out, you may get interested in the tool that bypasses converting. Store Manager for Magento will help you out, since lets upload products and associated details from various file formats, including .xls/.xlsx. Magento 2 (Magento Open Source 2, Adobe Commerce) import from xsl runs via special product import wizard, that does not require file resaving and has less requirements to the import file. How the procedure runs and what settings should be specified is described in the article mag-manager.com/magento-excel-import-is-not-a-question-any-more/