I regularly work on large catalogs, like those of Sol’s, with a view to imports, updates, or the creation of complete e-commerce sites. The supplier makes several CSV files available, which must be processed sooner or later in order to obtain the right data format to put all the products, all the variations, in place on Prestashop.

The import is going well, I do from time to time some alterations via SQL queries on the products allowing me to save precious time (and allow three full days for a Prestashop site including the entire Sol’s catalog for example), but importing PDFs associated with product sheets is regularly a step that I have to perform manually.

Here is the problem : I have a / FR directory containing all the PDFs, which are named with the product name, followed by an underscore, followed by the product reference. Just as much information could not be more useful, and we will therefore use it. Prestashop does not allow (even under its version 1.7) to import PDFs linked to products, it will have to be managed manually.

My weapons are therefore:

• an Excel table containing the name of the product, and its reference
• a directory containing all the PDFs
• a little elbow grease :-), well especially PHP and SQL skills

I will therefore proceed in three steps:

1 / Preparation of files

2 / Creation of import and import functions

3 / SQL retouch for association with products

“It’s Darty my kiki!” ”

1 / Preparation of files

So I downloaded the PDFs from the supplier’s site. By the way, thank you to them for having had the intelligence to name their files properly, it will save me a lot of work, and that’s good.

So I place the files in a / download / FR directory on my Prestashop 1.7. It’s a bit long, there are more than 400, and not the lightest. I could do this language by language, but for now I only have one language available, so I am going on this principle.

My catalog is well integrated into Prestashop 1.7 , and above all I have all the product references (a very important step, I do not fully understand how an e-merchant can process products without this unique information per product). So I’m going to modify the Attachment class a little to add a small function. In the Prestashop database, the useful information is therefore: file name, name, and description that you can view in the ps_attachment_lang table .

2 / Creation of import and import functions

Import functions

Here is the little function, taken from the Prestashop forum (when I told you that there are a lot of things there …)

Class Attachment.php

public static function addAttachmentImport($filename, $name, $description)
{
$attachment = new Attachment();

$languages = Language::getLanguages();
foreach ($languages as $language)
$attachment->name[$language[‘id_lang’]] = strval($name);
$attachment->description[$language[‘id_lang’]] = $description;

$attachment->file = sha1($filename);
$attachment->file_name = $filename;

$path_file = _PS_DOWNLOAD_DIR_.’FR/’.$filename;
//Notez ci-dessus le /FR, du nom de mon répertoire personnalisé
$attachment->file_size = filesize($path_file);

$finfo = finfo_open(FILEINFO_MIME_TYPE);
$attachment->mime = finfo_file($finfo, $path_file);

$attachment->add();
$sqlfile = ‘SELECT file FROM `’._DB_PREFIX_.’attachment` WHERE file_name = “‘.$filename.'”‘;
$plop = Db::getInstance()->getValue($sqlfile);
$newpath = _PS_DOWNLOAD_DIR_.$plop;
if (copy($path_file, $newpath)) {
return (int)$attachment->id;
}
}

Well, my function is in place, I will have to send the data back to it. Nothing could be simpler now, since not only is my catalog in place (and I can therefore extract the necessary information from it) but on top of that I always have the precious Excel file available.

On the basis of the latter, I therefore construct a table as follows:

File name Product name + reference
REGENT_12345.pdf REGENT_12345
ACE_01196.pdf ACE_01196

I extract it in CSV format, and I just have to convert it manually to a PHP array (a good IDE like Atom or Sublime Text does the trick, it didn’t take me more than ten minutes with the right keyboard shortcuts).

So I still have to add my PHP table to Prestashop and use the function mentioned above. I admit I was really lazy, I just put the store in maintenance quickly, and modify the IndexController, in / controllers / front.

So I added this just before $this->setTemplate(‘index’);

$pdfarray = (
‘REGENT_12345.pdf’=>’REGENT_12345’,
‘ACE_01196.pdf’=>’ACE_01196’
);

You can imagine that I usually have a lot more data than just two products :-). My PHP table will therefore be processed before the home page is displayed. All that remains is to import everything.

And to return all the data one by one to my import function, nothing could be simpler! a foreach will do (make sure you have a high enough max_execution_time on your hosting ).

Which now gives me an IndexController like this:

class IndexControllerCore extends FrontController
{
public $php_self = ‘index’;

/**
* Assign template vars related to page content
* @see FrontController::initContent()
*/
public function initContent()
{
parent::initContent();
$this->context->smarty->assign(array(
‘HOOK_HOME’ => Hook::exec(‘displayHome’),
));
$pdfarray = (
‘REGENT_12345.pdf’=>’REGENT_12345’,
‘ACE_01196.pdf’=>’ACE_01196’
);
foreach ($pdfarray as $key => $value) {
Attachment::addAttachmentImport($key, $value, $value)
}
$this->setTemplate(‘index’);
}
}

Come on, do we import all this?

Import PDFs

Now all you have to do is go to the home page to import the products. Be careful, if a file is incorrectly named, or if something is wrong in the PHP table, Prestashop will display a blank page. It is therefore useful to display the errors of your store , so as on the one hand to find the culprit, and on the other hand to warn the supplier if it comes from their generated files or their catalog.

The import can take longer or shorter depending on the amount of PDF you want to import. For my part, it lasted less than five minutes. If you have to restart the import, remember to empty the ps_attachment and ps_attachment_lang tables from your database, otherwise you will have duplicates.

Once the import is finished – and believe me, this step is not the easiest because it is really here that you will encounter errors – go to the backoffice of your Prestashop to view the attached files, in Simply “Catalog” then “Attachments”. The list of your PDFs should be there.

All that remains is to associate them with the products, so we are going to make some very useful SQL queries .

3 / SQL retouch for association with products

Right away, I detected a problem. This% $! & Of Excel made me skip all the zeros at the beginning of the product reference in my table :-(. Instead of ACE_01196 for example, I end up with ACE_1196. I am therefore missing one value, the SKU is no longer good at all and cannot be matched.

No worries, we’ll fix that (the day Microsoft comes up with something that doesn’t crash, it’ll be a nail).

First, let’s remove the name of the product, in order to keep only the reference, even if it would be more or less randomly distorted. So this happens in the ps_attachment_lang table:

UPDATE ps_attachment_lang
SET name = REPLACE(name, LEFT( name, LOCATE(‘_’,name) ), ”)

Now, in the ps_attachment_lang table again, we will have to add these missing zeros, but only if the length of the reference is less than five characters. LPAD and LENGTH in SQL will help me:

UPDATE
ps_attachment_lang
SET
name = LPAD(name, 5, ‘0’)
WHERE
LENGTH(name) != 5;

And voila, I now have my own bieeeeeeen product reference ready to be associated with the products.

And finally, let’s associate the imported PDFs with the products ! Which gives us the following query:

INSERT INTO ps_product_attachment
SELECT
pp.id_product,
pal.id_attachment
FROM ps_attachment_lang pal, ps_product pp
WHERE pal.name = pp.reference

From now on, my PDFs are indeed linked to product sheets, and you just have to go to the backoffice on a product to see in the “Options” tab that there is indeed a linked document. Except that…

Except that go to the front-office, the PDF document is absolutely not visible , there is no download link …

Well, let’s also correct this in database! The ps_product table has a cache_has_attachment column , which I’m going to update at once. For my part, all of my Sol’s products have an attached file, which gives me this:

UPDATE ps_product SET cache_has_attachments = 1

If you do not have attached files on all your products, you will need to add a WHERE in the above request, which will have the effect of only linking the attached files if the products really have one.

Submit this last and final request, and you’re done! Occasionally, I would also do a tutorial to import pictograms for the combinations, Sol’s offers this and it is actually very easy to import them en masse, as long as the catalog and the variations are properly integrated.

Stroke of luck! This tutorial is also a (still) free module of Team Ever!

Read More:  Top Free Page Builder of Prestashop