How to import catalogs from CSV, XML or EXCEL files

An operation that is often vital not only in the field 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 importing data, but it is very limited. While the APIs are from PrestaShop they are more complex and intended for developers.

Requirements the file must have to import the catalog

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

The requirements are as follows:

  • Character encoding that must be in UTF-8 , this UNICODE encoding, has been around since 1992 and is compatible with virtually all systems.
  • It must contain the minimum data to import or synchronize the products, so it is essential that the file contains a unique code for the product (ID, EAN13, Reference, etc.) and the name of the product .
  • It must not have special characters not allowed in the relevant fields by PrestaShop, or these must be filtered before importing.
  • The price must not have a thousand separator and the decimals must be separated by a period and not by a comma. It must therefore be in numeric format (float or integer) without currency symbols, or it must be filtered before importing.
  • Any image fields must be in .jpg or .png, must have the complete file name, respecting uppercase and lowercase letters and relative path reachable via HTTP protocol.

Which format to prefer?

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

In summary, therefore, the data exchange format to choose is among the following:

  • CSV (comma-separated values) for its greater diffusion and compatibility, with this format you must be very careful that in the fields there is no character used as text separator and column delimiter; A peculiarity of the CSV is also the ease with which it can be divided into several parts ( see here ), which is very convenient to avoid timeout (error 502) by the server, if we use the native import of PrestaShop or a module that performs the 'import without AJAX,
  • XML (eXtensible Markup Languag) is much more robust, widespread and allows the management of any type of information. It is no coincidence that XML was chosen for electronic invoices. It is important for HTML and text fields to use the CDATA system.

The JSON format (Javascript Object Notation), can be used to import data, where the simplicity of representation, in the serialization and transmission gives the format a better agility in certain contexts. While it is excellent combined with technologies such as Ajax and Javascript, 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 scarcely widespread for this activity.

Unfortunately, it may happen that some melter will give you access to APIs that provide you with data in this format. Even the PrestaShop APIs allow you to provide data 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 hardly find yourself importing data from this format into PrestaShop.

Example file for importing catalog 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 in fact widely used for the synchronization of data between Web platforms, so you will often find the need to export your catalog in this format to the marketplaces and price comparators eg. Trovaprezzo.it. However, many of them allow you to use the CSV format instead. If you want to learn more about this aspect, you can find the guide here .

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

  • Product ID (a unique code) can also be omitted if there is a unique code such as SKU (see guide) or EAN13
  • Title, the name of the product
  • Product description
  • GTIN, the EAN13 code see guide here , now indispensable.
  • URL of the image, in the case of multiple images we can either insert them in multiple fields, or simply divide them as a list with a comma (ex: urlimages1, urlimages2)
  • The categories, as for the images, there are different modes, the one in the example is the simplest for importing.
  • Product price

Example file for importing catalog in CSV

Opening a CSV file with a text editor we will not be able to read almost anything, in fact it is a text format with delimiters and without carriage returns. But as mentioned it is widely supported, both Microsoft Excel and LibreOffice and OpenOffice can allow you to read this format.

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, usually a semicolon or a comma is used, in the example it is a CSV file for exporting the catalog to trovaprezzo.it , and the separation character is: | which we have indicated with Other. Other very useful parameters are the selection of the string delimiter. LibreOffice also allows us to indicate the type of field before opening. This function is very useful with codes such as GTIN (EAN13) which must be set as text.

Use MS Excel

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

I recommend using the most comfortable LibreOffice or OpenOffice to work on CSV files.

Useful tools for working with CSV and XML

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

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

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

Conclusions

The simplest format remains CSV since 1972 , you can easily modify it with any editor and with spreadsheets 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 courses on Commodore 64. Today XML is gaining ground and is excellent for accessing data via API and data interchange. If you are planning to export data from your ERP these two formats will allow you to be compatible with any system and if 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

For Ind Loris Modena , owner of Arte e Informatica , he began working in the IT sector in 1989 as a system engineer in charge of the maintenance and installation of IT systems. He started programming for the web in 1997 dealing with CGI programming in PERL and then moving on to programming in PHP and JavaScript. In this period he approaches the Open source world and the management of Linux servers.

Product added to wishlist