PrestaShop how to import from CSV, XML or EXCEL file

In PrestaShop, let's discover how to import from CSV, XML, or EXCEL files starting with the format to use.

This operation is often vital not only in the context of dropshipping but also when it is often the only possible way to import and synchronize our catalog with our management system. PrestaShop has its own native function for data import, but it is very limited. While the APIs of PrestaShop are more complex and intended for developers.

Requirements that the file must have to import the catalog.

Regardless of the chosen format, there are minimum requirements that the file must meet to import the product catalog into PrestaShop; these requirements are also essential for importing into other systems.

The requirements are as follows:

  • Character encoding must be in UTF-8, this UNICODE encoding has been around since 1992 and is compatible with almost all systems.
  • It must contain the minimum data required to perform the import or synchronization of products, so it is essential that the file contains a unique code for the product (ID, EAN13, Reference, etc.) and the product name.
  • It must not contain any special characters not allowed in the respective fields by PrestaShop, or these must be filtered out before import.
  • The price must not have a thousands separator, and decimals must be separated by a point and not by a comma. It must therefore be in numeric format (float or integer) without currency symbols, or it must be filtered out before import.
  • Any image fields must be in .jpg or .png, must present the full file name, respecting case sensitivity, and the corresponding path must be accessible via HTTP protocol.

Which format do you prefer?

The most widespread format that offers the best compatibility is CSV (comma-separated values), which has been used since before the advent of personal computers; the format was already supported in 1972 in IBM Fortran. Its close relationship with SQL makes it the simplest format to use. However, it has several limitations that have led to the use of data interchange formats: XML, JSON, and YAML today. It is also possible to import from the Microsoft Excel format, which is more practical for less experienced users who use Windows and MS Office, but it is important to emphasize that for the transfer, the XLSX file must contain only text and numbers, no formulas or images. If we want to work with CSV files, I recommend installing LibreOffice on your PC to easily modify the files.

To summarize, the format to choose for data interchange is among the following:

  • CSV (comma-separated values) for its greater diffusion and compatibility; with this format, one must be very careful that the fields do not contain the character used as a text separator and column delimiter. A particular feature of CSV is also the ease with which it can be split into multiple parts (see here), which is very convenient to avoid timeouts (error 502) from the server, if we use the native import of PrestaShop or a module that performs the import without AJAX,
  • XML (eXtensible Markup Language) is much more robust, widely used, and allows the management of any type of information. It is no coincidence that XML was chosen for electronic invoices. It is important to use the CDATA syntax for HTML and text fields.

The JSON format (Javascript Object Notation) can be used to import data, where the simplicity of representation, serialization, and transmission gives the format better agility in certain contexts. While it is excellent when paired with technologies like Ajax and Javascript, and very useful for structured data and rich snippets for which JSON-LD is used today, I do not consider it valid for importing product catalogs, customers, and data into management systems and PrestaShop, as it is poorly used for such activities.

Unfortunately, it may happen that some suppliers provide you access to APIs that give you data in this format. The PrestaShop APIs also allow data to be provided in JSON; the reason is simple: it is natively supported by many languages and requires less work than XML in generation.

The YAML format is not very widespread; it is easier for humans to read and extends the JSON format. You will rarely find yourself importing data from this format into PrestaShop.

Example file for catalog import in XML

Below is an example of an XML file used to send the product catalog to Criteo, the format is very similar to that of Google Merchant Center. XML is indeed widely used for data synchronization between web platforms, so you will often find the need to export your catalog in this format to marketplaces and price comparison sites, e.g. Trovaprezzi.it. Many of them, however, allow you to alternatively use the CSV format. If you want to explore this aspect further, you can find the guide here.

import XML into Excel

You can notice the fields necessary for a correct and complete import of the catalog:

  • Product ID (a unique code) can also be omitted if a unique code such as SKU (see guide) or EAN13 is present
  • Title, the name of the product
  • Product description
  • GTIN, the EAN13 code see guide here, now essential.
  • Image URL, in case of multiple images we can either insert them in multiple fields or simply list them separated by a comma (e.g.: urlimage1,urlimage2)
  • Categories, as with images there are different methods, the one present in the example is the simplest for import.
  • Product price

Example file for catalog import in CSV

Opening a CSV file with a text editor will not allow us to read almost anything, in fact, it is a text format with delimiters and lacks line breaks. However, as mentioned, it is widely supported; both Microsoft Excel and LibreOffice and OpenOffice can allow you to read this format.

CSV catalog

Use LibreOffice

LibreOffice when we open a CSV file allows us to select the character encoding and the type of separator, there is no standard so it can be any character, generally a semicolon or a comma is used, in the example it is a CSV file for exporting the catalog to trovaprezzi.it, and the separation character is: | which we indicated as Other. Other very useful parameters are the selection of the string delimiter. LibreOffice also allows us to specify the type of field before opening. This function is very useful with codes such as GTIN (EAN13) which need to be set as text.

Importing Excel CSV

import CSV Excel

Excel will directly open the CSV file, but using a comma as the default delimiter. Once opened, we will need to select all the rows in column A and then go to DATA -> Text to Columns and follow the instructions to correctly display our CSV.

I recommend using the more convenient LibreOffice or OpenOffice to work on CSV files.

Use LibreOffice to open a CSV.
Using XML Notepad

Useful tools for working with CSV and XML

There are various utilities that allow us to work with these files, being text files any editor allows us to open and modify them, including Notepad++, but it is not always convenient, especially when the files are of considerable size. Below are some useful programs:

  • LibreOffice or OpenOffice, already mentioned in this guide.
  • XML Notepad, a handy XML editor equipped with incremental search and tree and text view.
  • Simple Text Splitter Files, allows you to split a CSV into multiple files.

Note: regarding the electronic invoice as mentioned, it is in XML format and can be opened today by various software and also by apps for both Android and iOS.

Conclusions

The simplest format remains the CSV since 1972 to this day, you can easily modify it with any editor and with spreadsheet software from Excel to Libre Office Calc. I used this format in the '80s with Lotus 1-2-3 and IBM DB2, I even learned it in the first computer science courses on the Commodore 64. Today XML is gaining more ground and is excellent for data access via APIs and data interchange. If you are planning to export data from your management software, these two formats will allow you to be compatible with any system, and when in doubt, I recommend exporting the catalog with fields and formatting similar to that required for Google feeds.

Author: Loris Modena

Loris Modena

SENIOR DEVELOPER

Per Ind Loris Modena, owner of Arte e Informatica, started working in the IT sector in 1989 as a system administrator responsible for the maintenance and installation of computer systems. He began programming for the web in 1997, focusing on CGI programming in PERL and later transitioning to programming in PHP and JavaScript. During this time, he became familiar with the Open Source world and the management of Linux servers.

Product added to wishlist