[GUIDE] Import CSV files - part2 - Importing of Products and Combinations - PS 1.3.x

[uddhava]

Importing Products

Index :
Part 1 - Intro, bugs, features and more
Part 2 - You are here

Prestashop version : 1.3.1.1
Only use this version for this thread.

General Notes

Note : You cannot import a product without a quantity.
Note : Use ID's in the parent category field instead of text (names)
Note : The product ID can only be an integer (number). So no letters or other characters

Helpfull Code Changes
Skip "0" Lines and how to change that
See below or Click here

Change Price excl TAX to Price incl Tax
See below or click here


Bugs / Features:
bug : 4933 - Reduction from / Reduction To field incorrect after import
Fixed in : 1.3.2.3

When importing products you have to use the reduction_from and reduction_to date fields. If you dont use this then later when (re)editing the product, a default of 0000-00-00 00:00:00 is automatically filled in for this product. Then saving will give an error (reduction_from is invalid. reduction_to is invalid).
You can prevent this to use the columns. Keep both dates equal.


bug: 4953 - Import creates temporary images that are not deleted.
During the import temporary images are created in /img/tmp folder. But not all images are removed after import.
This has been fixed in the SVN. Expect this fis in PS 1.3.2 or 1.4.x.
See below for a workaround

feature 4961 - Re-import creates duplicate product images
I am using csv files to import products.
When i re-import to update the products all the product images get duplicated.
In the image directory multiple images appear for the same product like this :

701-400.jpg
701-440-home.jpg
...
And the duplicates

701-448.jpg
701-448-home.jpg

And when i try another import even more images are created..

See my workaround below


Core Changes to import

bug: 4953 - Import creates temporary images that are not deleted.
I found a solution to the temporary import files bug. (see up)
I have added another php delete command (unlink) to the code in the AdminImport.php file.

File : /admin/tabs/AdminImport.php
Line : 389-404
Old code

if (@copy($url, $tmpfile))
       {
           deleteImage($id_entity,$id_image);
           imageResize($tmpfile, $path.'.jpg');
           $imagesTypes = ImageType::getImagesTypes($entity);
           foreach ($imagesTypes AS $k => $imageType)
               imageResize($tmpfile, $path.'-'.stripslashes($imageType['name']).'.jpg', $imageType['width'], $imageType['height']);
       }
       else
       {
           unlink($tmpfile);
           return false;
       }
       return true;
   }    

New code :

if (@copy($url, $tmpfile))        {
           deleteImage($id_entity,$id_image);
           imageResize($tmpfile, $path.'.jpg');
           $imagesTypes = ImageType::getImagesTypes($entity);
           foreach ($imagesTypes AS $k => $imageType)
               imageResize($tmpfile, $path.'-'.stripslashes($imageType['name']).'.jpg', $imageType['width'], $imageType['height']);
       }
       else
       {
           unlink($tmpfile);
           return false;
       }
       unlink($tmpfile);
       return true;
   }    

[uddhava]

Importing Combinations
When importing combinations (Group attributes and attributes) for each product we can do a few things.
You can import all attributes in 1 combination or multiple combinations.

Importing combinations and linking them to the correct images is not (yet) possible. Damn.
(Read about it here

[uddhava]

next item

[uddhava]

Item for savings

[uddhava]

Skip x Lines during import
Whenever i import, the 1st line should always be skipped because my headers are there.
But the default is 0 in Prestashop. If you want to change that then you have to edit the AdminImport.php file :

edit /admin/tabs/AdminImport.php on line 1278

old code

'.$this->l('Skip').' <input type="text" size="2" name="skip" value="0" /> '.$this->l('lines').'.

new code

'.$this->l('Skip').' <input type="text" size="2" name="skip" value="1" /> '.$this->l('lines').'.

[uddhava]

This is a workaround for the duplicating product 'bug' / 'feature'. (See 1st post)

I found out that when a product has images (which is always the case during a re-import) the image cover bit does not get set. To overcome this and to delete all the previous product images i have changed the AdminImport file.

The new code below will delete all images for the current product (ID) and then import the images from the CSV file. If you have specified multiple images, it will import them all, but only the first one gets set as the "image cover".

Here is the new code : (/admin/tabs/AdminImport.php from line 720 - PS 1.3.1.1)

if (isset($product->image) AND is_array($product->image) and sizeof($product->image))
               {
                   $productHasImages = (bool)Image::getImages(intval($cookie->id_lang), intval($product->id));
                   $coverset = false;
                   foreach ($product->image AS $key => $url)
                       if (!empty($url))
                       {
                           $image = new Image();
                           $image->id_product = intval($product->id);
                           $image->position = Image::getHighestPosition($product->id) + 1;
                           if (($productHasImages) AND ($coverset == false))
                               {
                                 $product->deleteImages();
                                 $image->cover = true;
                                 $coverset = true;
                               }
                           else
                           $image->cover = (!$key AND !$productHasImages) ? true : false;
                           $image->legend = self::createMultiLangField($product->name[$defaultLanguageId]);
                           if (($fieldError = $image->validateFields(UNFRIENDLY_ERROR, true)) === true AND ($langFieldError = $image->validateFieldsLang(UNFRIENDLY_ERROR, true)) === true AND $image->add())
                           {
                               if (!self::copyImg($product->id, $image->id, $url))
                                   $this->_warnings[] = Tools::displayError('Error copying image: ').$url; 
                           }
                           else
                           {
                               $this->_warnings[] = $image->legend[$defaultLanguageId].(isset($image->id_product) ? ' ('.$image->id_product.')' : '').' '.Tools::displayError('cannot be saved');
                               $this->_errors[] = ($fieldError !== true ? $fieldError : '').($langFieldError !== true ? $langFieldError : '').mysql_error();
                           }
                       }
               }

If you dont want to edit your file then you can replace the AdminImport.php with the one below.

AdminImport.php

[James R]

Thanks for that uddhava, but I can't seem to get it to work. I've deleted all current products and re-imported them with images but the old covers from the demo store seem to appear and for another product I get a '?' in a box for the cover image.

Any ideas on what could be happening?

[uddhava]

I guess you know that the importing and subsequently deleting of images only will work properly if you work with fixed product ID's !!!
So if you dont then you will get images that will be displayed with other products.
The only solution is to delete all images (either by deleting all products or delete products during import).
Another way is to delete all images in the /img/p folder + products

[James R]

I've been using the same ID's on each import as it's the same three products I'm testing the store with. I've also been selecting the 'delete all products' option during each import but still some strange images appear as the covers. So are you saying that these strange images might perhaps be related to the original products that were in the demo store that would have had the same ID as the new ones I'm putting in? I think I'll try deleting all those images in that folder you mentioned and see how that works.

Thanks!

[James R]

I deleted all the images from that folder 'img/p' and did a fresh import. All images and products went in fine APART from on the screen attached. So I had a look at the image path and saw that those images were in 'img/tmp'. Deleted the offending images and all is looking good.

[uddhava]

A default install of Prestashop install the products images that are in the demo.
Every product image is placed in the /img/p folder with the ID nr in the name.
If you import an article with ID 1 then it will display the old image of the demo (unless you delete the previous image).

are you using PS 1.3.1 ?? My workaround is only tested on that version.

[uddhava]

I deleted all the images from that folder 'img/p' and did a fresh import. All images and products went in fine APART from on the screen attached. So I had a look at the image path and saw that those images were in 'img/tmp'. Deleted the offending images and all is looking good.

You should take a look at this bug :
bug: 4953 – Import creates temporary images that are not deleted.
(see above for the workaround by me)

[James R]

Arrggh that's what I presumed the latest code you posted did. Thanks I'll try the 4953 code!!

[uddhava]

You can change the default setting of EXCL TAX to INCL TAX with the code below.

Code for Price incl TAX default

    private function getTypeValuesOptions($nb_c)
   {
       $i = 0;
       $noPreSelect = array('price_tex', 'feature');

       $options = '';
       foreach ($this->available_fields AS $k => $field)
       {            
           $options .= '            if ($k === 'price_tex')
               ++$nb_c;
           if ($i === ($nb_c + 1) AND (!in_array($k, $noPreSelect)))
               $options .= ' selected="selected"';
           $options .= '>'.$field.'';
           ++$i;
       }
       return $options;
   }

CODE for Price excl TAX default

    private function getTypeValuesOptions($nb_c)
   {
       $i = 0;
       $noPreSelect = array('price_tin', 'feature');

       $options = '';
       foreach ($this->available_fields AS $k => $field)
       {            
           $options .= '            if ($k === 'price_tin')
               ++$nb_c;
           if ($i === ($nb_c + 1) AND (!in_array($k, $noPreSelect)))
               $options .= ' selected="selected"';
           $options .= '>'.$field.'';
           ++$i;
       }
       return $options;
   }

[jcmil]

@uddhava I have an odd issue, used your google template, deleted the entries & entered my own, then tried to import - result - "Column Name * must be set"....
I have a name in there & it shows on the csv preview prior to clicking import csv data. (But the data I'm seeing is wrong I think see below)

I shall try making a sheet from scratch in case I've accidentally added something but I am puzzled.

Thanks for the effort you've put into this guide as it will save me a lot of time once I figure out what I'm doing.


update -
As I couldn't get it to recognize my csv I tried your template as is - this is what I see - I should see columns shouldn't I? Any ideas?

[uddhava]

In the previous screen you have to select a comma "," as the separator. Default it is semi-colon ;
Then it should work

[jcmil]

In the previous screen you have to select a comma "," as the separator. Default it is semi-colon ;
Then it should work

Thanks uddhava I didn't realise that I should switch that - had a severe muppet moment!! :ohh:

This just goes to show the downside of being weened on Excel!! No idea of CSVs - so I blame Bill...! :ahhh:

[uddhava]

Yes Bill must have mis-heard it when they told him "Comma Seperated Files". And excel keeps using semi-colons...
Or is it my stupid knowlegde of excel???

[jcmil]

I wanted to share a little eureka moment I had with the .csv -

Entry of Description text with formatting via csv

Using the html codes it's possible to add formatting & emphasis etc

Entry of Description text with formatting via csv

Using the html codes it's possible to add formatting & emphasis etc

i.e. it becomes a string the csv will accept immediately.

News or history? :-)

*

On the Reduction date field issue I decided to swap 0000-00-00 00:00:00 with 1111-11-11 11:11:11, on the assumption this will never be triggered. Or will this create an error somehow?

[uddhava]

On the Reduction date field issue I decided to swap 0000-00-00 00:00:00 with 1111-11-11 11:11:11, on the assumption this will never be triggered. Or will this create an error somehow?

PS 1.3.1.1 has a bug when importing the reduction date fields. This has been solved in 1.3.2 according to the bug squad. So as a workaround i use a date like this : "2010-09-09". That is enough for the import to work properly.
But remember that google (if you use docs) will convert this date to its own format. So always put a single quote infront of the text like this : '2010-09-09

Entry of Description text with formatting via csv
Using the html codes it’s possible to add formatting & emphasis etc

Is it possible to add html now ? Where did you get this?

ys

[jcmil]

On the Reduction date field issue I decided to swap 0000-00-00 00:00:00 with 1111-11-11 11:11:11, on the assumption this will never be triggered. Or will this create an error somehow?

PS 1.3.1.1 has a bug when importing the reduction date fields. This has been solved in 1.3.2 according to the bug squad. So as a workaround i use a date like this : "2010-09-09". That is enough for the import to work properly.
But remember that google (if you use docs) will convert this date to its own format. So always put a single quote infront of the text like this : '2010-09-09

Entry of Description text with formatting via csv
Using the html codes it’s possible to add formatting & emphasis etc

Is it possible to add html now ? Where did you get this?

ys

I saw the note in the thread that there was an issue & the '1's was my idea to get round it with an 'unusable' trigger. Hope I will be able to alter my date fields to a null value when the patch/update comes through. (Or I'm going to be typing for a while)

The html values I used were an experiment based on the way prestashop uses html in its own editor - so it was just lateral thinking on my part.
It feels more like BB forum code than regular html to me, I typed in the data & formatted it as req'd, then copied it out via PS's native html editor, pasted it into the CSV & it worked! :cheese:

[uddhava]

I saw the note in the thread that there was an issue & the ’1’s was my idea to get round it with an ‘unusable’ trigger. Hope I will be able to alter my date fields to a null value when the patch/update comes through. (Or I’m going to be typing for a while)

In 1.3.2 this bug is fixed so you dont need to use it anymore. But for now you have to use both fields. If you keep the content of these fields similar then the reduction feature is disabled.

[jcmil]

I saw the note in the thread that there was an issue & the ’1’s was my idea to get round it with an ‘unusable’ trigger. Hope I will be able to alter my date fields to a null value when the patch/update comes through. (Or I’m going to be typing for a while)

In 1.3.2 this bug is fixed so you dont need to use it anymore. But for now you have to use both fields. If you keep the content of these fields similar then the reduction feature is disabled.

Do the existing entries remain when the upgrade is made? Wondering if there is a quick way of editing all the specific fields simultaneously...

[uddhava]

I do not really understand your question about the upgrade. And also not the editing all fields simultaneously. Could you clear it up ?
When you upgrade to 1.3.2 you will loose all changes to the core files (so also to the AdminImport file.)
But concerning the Reduction fields, it will not matter.

[jcmil]

I had been thinking that if one had the reduction date fields filled in, with either useless data or required data, would the upgrade impact these entries directly?
i.e.:- if one has useless data - like mine, will it be returned to default empty state; if yes what happens if you need the fields completed, do you then lose those data entries?

I wondered if the upgrade would therefore not edit existing data in reduction date fields.
(How could the upgrade differentiate bad from good entires? Regex for identical field contents? )
I imagined the 'fix' will be to the code generating the fields of 'wrong' data, not to the actual data present as a result.

So .....in that case what would be the quickest way of eliminating my useless data...?

Hope that is a bit clearer, tho I am making several assumptions on the nature of the problem & the upcoming fix...
Sorry for absorbing all your time with my curiosity! The prospects presented by the csv as you outline here got me wondering about the possibilities.... :cheese:

[uddhava]

After an upgrade to 1.3.2, and after you try an re-import, your data of existing products will be updated. So if you put another date into the reduction fields this date will be used. So if you would delete all the dates from your csv file and do a re-import, then the existing dates (in the database) will stay. (unless you delete all products before with the delete option).

In any case it will not matter i think. If you want to start using reductions, then you change the fields and it should be imported.
But all the dates that are put in the csv sheet will stay in the database. The quickest way to eliminate is to delete all products...

[jcmil]

Thanks Uddhava, that makes sense.

[chineseboy]

quick question, when updating my products i keep getting these errors:

Warning: preg_match() expects parameter 2 to be string, array given in C:\xampp\htdocs\classes\Validate.php on line 575

Warning: explode() expects parameter 2 to be string, array given in C:\xampp\htdocs\classes\Tag.php on line 80

Warning: array_map() [function.array-map]: Argument #2 should be an array in C:\xampp\htdocs\classes\Tag.php on line 80

Warning: array_unique() expects parameter 1 to be array, null given in C:\xampp\htdocs\classes\Tag.php on line 80

Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\classes\Tag.php on line 82
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2

INSERT INTO `ps_product_tag` (`id_tag`, `id_product`)
VALUES

Any ideas why this is happening?

I'm using 1.2.5 btw.

[uddhava]

This look like a serious compatibility problem. But since this thread looks at 1.3.1 in detail i cannot be sure.

[howard]

Hi Uddhava
I am still using PS 1.2.5 only because I am working more on mu csv files rather than upgrading.
Mu problem is with the tax each time I add a product it adds up to 3 more tax levels per item I am up to 126 levels.
My tax is item 1, NAME IVA 18% Rate 18.00, I have tried different things in the "Tax rate" column, including
"1" this added 3 more levels at 1% Item code Nº
18 this added 3 more levels at 18%
18% this added 3 more levels at 0%
IVA 18% this added 3 more levels at 0%
can anyone help I cant find anything on this in the forum

[uddhava]

PS 1.2.5 has too many serious bugs in the import handling to consider it seriously. And the multiple tax problems is one of them.
So stop bothering with 1.2.5. It can only be solved by hacking the code yourself.
See here : http://www.prestashop.com/bug_tracker/view/3541/

ys

[Jubri]

Thanks uddhava, very very usueful guide. Keep good work!!

One question, exists automatic import module? (e.g.: via cron)



Thanks.


Best Regards.

[uddhava]

there are some people on the forum who have various solutions, but i never saw a module

[fwed]

Hi,

Firstly, thank you for this thread, it's a great idea.

Otherwise I didn't see anyone speaking about the reduction percent problem. Actually the import saves the reduction percent but does not apply it (or thinks that the given price includes the reduction percent).

Then if you go edit the product in the back office and save it without modifying anything, now the reduction percent applies.

I did some research and found this on the SVN of PrestaShop 1.4 : http://svn.prestashop.com/trunk/admin-dev/tabs/AdminImport.php

If you look lines 701, there is a whole block dedicated to the reduction percent. Block which is not present in the 1.3.2 version of AdminImport.php.

Any idea on how doing it in 1.3.2 properly ? The "SpecificPrice" PHP class does not seem to exist in 1.3.2.

I'll keep you in touch if I find something.

[uddhava]

The html values I used were an experiment based on the way prestashop uses html in its own editor - so it was just lateral thinking on my part.
It feels more like BB forum code than regular html to me, I typed in the data & formatted it as req'd, then copied it out via PS's native html editor, pasted it into the CSV & it worked! :cheese:

Thanks. I will try it out soon !!

[uddhava]

Hi,

Firstly, thank you for this thread, it's a great idea.

Otherwise I didn't see anyone speaking about the reduction percent problem. Actually the import saves the reduction percent but does not apply it (or thinks that the given price includes the reduction percent).

Then if you go edit the product in the back office and save it without modifying anything, now the reduction percent applies.

I did some research and found this on the SVN of PrestaShop 1.4 : http://svn.prestashop.com/trunk/admin-dev/tabs/AdminImport.php

If you look lines 701, there is a whole block dedicated to the reduction percent. Block which is not present in the 1.3.2 version of AdminImport.php.

Any idea on how doing it in 1.3.2 properly ? The "SpecificPrice" PHP class does not seem to exist in 1.3.2.

I'll keep you in touch if I find something.

Yeah, the PS team find a lot of bugs with the importing. I noticed also some weird things. But i will start focussing on 1.4.x soon. Will create a new topic for that soon

[fwed]

Ok actually the problem was due to the missing "reduction from" and "reduction to" fields.

Now I've set both of them to "1111-11-11" and the reduction percent applies perfectly

[uddhava]

There is a bug in 1.3.2.3 when importing products and you imported in a language different than English.
during import you will receive an error about the price. The price will not be calculated properly and rounded of ex VAT and with a comma. No products will be imported due to this.

It has been reported on the Tracker and supposedly fixed in 1.4. BUT that does not help for those on 1.3.2.3 at all.
I compared the 1.3.1 and the 1.3.2.3 files and i notice a warning (notepad++) around line 1135.

File : admin/tabs/AdminImport.php
Line : 1135 (1.3.2.3)

1.3.1 :

echo ''.$lang['name'].'';

1.3.2 : 
echo 'id_lang ? 'selected="selected"' : '').' >'.$lang['name'].'';

As you can see there is an extra " in the code. That causes the problems.
you can use the 1.3.1 code to workaround the problem. This bug has been fixed in 1.4

[dark_effie]

Hi,

How to import products and combinations in Prestashop 1.4.1? Is there anyone who has a sample of CSV file to be imported? I will be grateful to any help.

Thanx

[nuttis]

Hi

It works the same except i think there was some more fields. Try to upload a file and the "old" menu will come up with all the fields, etc. Sorry i dont have any sample right now.

[uddhava]

I have started a thread for 1.4 already. But it is in the beginning stage. When i am finished designing my new template for 1.4 i can focuson this thread again.

http://www.prestashop.com/forums/viewthread/97127/

[vigierex]

I admit that excel is a good program to use to get an overview and to edit csv files.....

But besides the EAN column, (or any big number for that matter) also the special characters get messed up.

So better not use microsoft excel. OpenOffice is a good substitute that is being used by others.

Or you can use Google docs for example.