11/02/20 Franck Bugnet Category Tuto Video

Export data from PrestaShop

Exporting means reading and extracting data from the PrestaShop database and copying them in a structured form to an external file, such as CSV, Txt, Excel, xml, sql or other.
This type of file can then be used for various purposes, such as migrating the data to another database or system, modifying them in Excel to re-import them, doing one's accounting, distributing a newsletter, archiving a backup, selling on the market places...

The subject is therefore very vast, but we will try to go around it.
As an introduction below we're going to make a reminder of the prestashop native backup and export tools available for free in administration without requiring any additional module.

But you'll see that as very often with PrestaShop, these basic tools have their limits. Fortunately, there are alternatives in the form of additional export modules. This category therefore contains several posts presenting various solutions available to export all or part of the PrestaShop database.

Why and how to make a backup of the prestashop database


Not a week goes by without discussion with an e-merchant who wants to carry out complex mass operation on this data and is asked "do you have recent backup of your database", we got as an answer: "no I don't know how to do it but think my host makes backups"

Gloups! ok we have the right not to know how to do it, no worries, but just "believing" that someone else is doing it for you, without being sure, is suicidal. If this is the case for you, sorry, but we owe you a slap on the wrist. Because one day or another, you will have a crash of your database, or an attack of a hacker, or will make a false manipulation which will erase a part of your so precious data.
Stop everything you do, including reading this article, immediately make a phone call to your host to check that there is indeed a backup of your database made at least once a day, with a system of archiving backups over several days (being able to recall the data of last week for example).

If this is a chargeable billing option take it immediately, don't take any risk. And if your hosting provider does not offer this fundamental service run away

Note: take the opportunity to also check that host makes regular backups of your files folder containing installation prestashop frequency can be less than for database but not having such a backup when you get hacked by pirate it s assured death site.
Do not confuse the two, the database does not contain the PrestaShop files and PrestaShop does not contain your data.


OK, this point is checked and corrected if necessary? your web host makes regular backups of the database and the files of your PrestaShop site? perfect... well almost.

Imagine that your web host goes bankrupt or becomes a hacker himself you get angry with him and change the creamery dishonestly he doesn t answer anymore when ask to send backups case already seen several times his backup system is failing very often this why it also necessary make either even from time say once month at least of base which will keep on pc in one straps belt

It's essential and it's very easy and quick to do. Let's see it in pictures...

How?!



  1. Go to the prestashop admin and scroll down advanced settings
  2. click on database


Administration of the PrestaShop database


Start a backup of the PrestaShop database
After a few seconds (depending on the size of the database), the backup is created and appears in the list. But it is stored on the server, which is not secure in case of a server crash. You have to download it to keep a copy at home. To do this, click on the icon representing an eye :


Download backup
The download of the backup must start by itself, otherwise click "click here", then "OK"


Name the backup file
Here you have now a complete backup of your database, in sql format, re-importable in case of big problems, with PHPMyAdmin. This step is irrelevant and we won't detail it here, there are already a lot of tutorials because this type of work is not specific to PrestaShop.

How to export data with PrestaShop ?

Depending on the type of data the method may vary slightly

For example to export products from the catalogue :

  1. Click on catalogue
  2. Click on products
  3. Click on the small gear in upper right corner context menu select either export or to sql manager


Export of PrestaShop products

    If you have clicked on "Export", a CSV file is created under the name of your choice :


Choice of export file name

    So first bad surprise, it's CSV and nothing else. Neither xls, nor xlsx or xml,...>


    Second bad surprise, when you look at the content of the file, you quickly realise that most of the data is missing. There are neither summaries, nor descriptions, nor SEO fields, ... Basically this file cannot be used as a backup or as a source for re-importing :


Contents of the export file of PrestaShop products

      The second method, by choosing "Export to SQL Manager", is more powerful because it allows you to add the fields you want. But, have it sting, you have to know how to write SQL queries, which is not the case (nor the trade) of all sellers :


Export to query manager
      If you just need to add a simple heading, like the purchase price, it's still playable, just add a line p.`wholesale_price` AS wsprice, in the query. But if for example you want your file to also contain the product features, I doubt that most of you know how to make the right join. It's just too technical and not suitable.

To export categories :


      It is exactly the same principle, with the same limitations :

Export of categories


For suppliers and brands :


      It will be necessary to make two more exports using the same method, so two more files.


For attributes and features :


      This is more complicated, there not even a simple export button, you have to go through the sql query manager :


Export of attributes and characteristics
      But the worst of the worst is that the pre-written query doesn't even work :


Bug in the export query

And the declinations how to export them also ?


      Simple answer, you can't, unless you know how to write even the right sql query, and believe us, it's not simple at all for a name specialized in MySQL or if you don't know on your fingertips the structure and subtleties of the PrestaShop database.


Conclusion :

    Let's stop there the massacre, you will have understood it, PrestaShop will have to review its copy for all the export part.

    While waiting for it to improve, you will have to use other tools, that is to say modules specialized in PrestaShop export. There are many of them, but most of them are incomplete and not very flexible to use.

    Either if you don't want to spend a fortune with the purchase of a lot of micro modules and choose intelligently to do everything with a single module, you can use once again Merlin Backoffice which natively integrates several import tools hyper easy to use and especially to customize according to your needs.

    We invite you to take a closer look at the generic video below then various more specific articles in this category and discover another world

Prestashop export with the merlin module

32min


    This french only video and training covers a large part of the export tools of this module.

        It focuses in particular on the choice of the headings to be exported, in order to be able to migrate a catalogue between two versions of PrestaShop.

          It also presents the export tools for catalogues in PDF format. 


            Other topics covered:


          1. Customisation of exports for market places
          2. Exporting categories with their hierarchical structure.
          3. Export of declinations.
          4. Exports of specific prices.
          5. Customisation of the content by customers group or countries
          6. Export with multiple languages and shops.
          7. Choice of various file formats depending on the destination


resolution
Increase the resolution to 720p if video is too blurry replay it in auto freezes 
This article was useful ? Share it !