[GUIDE] Import CSV files - Part 1 - Intro, products, category, combinations - PS 1.3.x

[uddhava]

Intro
After wasting too much time on the importing of CSV files (and encountering lots of bugs) i wanted to get
everybody on this forum to add their knowlegde and experience to this thread.
The wiki has some info on importing products, but not how to make a proper CSV file. There are many threads in the forum with people looking for an answer... Let it be collected here !!!

Index
Part 1 - You are here
Part 2 - Importing Product Combinations

Prestashop version : 1.3.1.1
Before posting questions/issues with importing make sure you use the latest Prestashop version!

Current known Import issues / bugs / features
1. solved Temporary image files are created in /img/tmp and not deleted
2. solved Product images are duplicated when a re-import is performed. This is called a 'feature' by the PS team. Basically, every time you try an import, all the images are duplicated. For example, when you re-import 6x, you also get 6 products images. #bug report #feature request 1 #feature request 2 + solution

Creating CSV Files

Creating an CSV
Excel
Excel does not support UTF-8 encoding (yet?)
It will mess up your special characters when saving it as an .csv.
Also when importing a .csv into excel it will reformat the EAN column unless you set the column type to "text" during the import
So stop using Excel!!

Notepad++ (not the windows not!pad)
You can create a nice .csv file with notepad++.
But be sure to encode it in UTF-8 (see >Format>Convert to UTF-8) and save it.
After that Prestashop will import the file correctly

CSVed Download here

Google Docs
With google docs you can create nice 'excel' like sheets that are also UTF-8. If you have an gmail account you can use Google Docs. I have included some sample files below for you to use.
Some important notes:
* Date fields have a fixed format that is not compatible with the date field in Prestashop
Google uses this : 2010/03/26
Presta uses this : 2010-03-26
If you want to use the prestashop format, be sure to use a ' in front of the date. Otherwise google docs will convert the date to its own format
* Multiple Field Seperator is , and cannot be changed

OpenOffice
Anybody has experience?

Multiple lines in your description field
PS 1.2.5 supports Multiple Lines import
PS 1.3.0.1, 1.3.0.2, 1.3.0.3 do NOT support multiple lines import.
1.3.1.1 Should support multiple lines import. It works BUT ONLY when your field is quoted " "
Google Docs always quotes multiple lines text.

What column names do you need
The best way to view the column names is to login to the back-end of Prestashop and go to >Tools>Import.
The field names are listed on the right side of the screen after selecting the type of import.

Importing Categories
Importing categories with identical names
By default you cannot import categories with identical names. You can change the import core file to create a workaround for this.

Commenting the following code will enable non-unique category names.

Looking at the categoryImport() function in admin/tabs/AdminImport.php, it appears it is the following code that checks whether a category with the same name already exists and then changes the category ID to it instead of what is in the CSV file:
Here is my code : (line 476 - 481)

// If category already in base, get id category back
               if ($categoryAlreadyCreated['id_category'])
               {
                   //$catMoved[$category->id] = intval($categoryAlreadyCreated['id_category']);
                   //$category->id =    intval($categoryAlreadyCreated['id_category']);
               }

(read more in this thread)

Now the PS team has to implement this into an option that you can enable/disable.

Importing Products
See part 2

Importing Product Combinations
See part 2

Core changes to import
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').'.

Downloads - Example files

Prestashop-Products-Template-GoogleDocs.csv

Prestashop-Categories-Template-GoogleDocs.csv

[Guest]

Thank you very much~

190 downloads but no one reply~~~????

[uddhava]

I have patience.. ;-)

I found another way of creating an easy CSV file. You can use Google Docs to do it for you.
And Google Docs is using UTF-8 encoding as a default. So just recreate the column labels in Google Docs and go from there.
When its time to export you can save your document as an csv file.
This will import nicely in Prestashop

[Guest]

That's a great idea!
I will try it later, because I am tired of changing the ENCODE for the excel export CSV file. :-S :-)

[Guest]

Well, I just try the GOOGLE DOC for edit a xls file, and then export the file as a CSV.
When i open it in the NOTEPAD++, it showed the encode of the CSV was not the UTF-8 but the ANSI.
What i am missing out? :question:

[Damainman]

Have any of you tried Open Office? Supposedly it creates and saves all it's files in UTF-8 by default.

[sergiodik]

A question: how I can add line breaks in features?

For example, I have a paragraph Combinations Combinations -> Options (Group: Value1, Valu2, Value3, etc.) I need to add the same thing in Products, such Products -> Feature1, Feature2, Feature3, etc.


How do I do? I hope that I may understand what I mean :-S

[Vaidas]

Can any body help? I try to add subcategories by importing csv file in my shop, but without success. I don't have experience in creating csv files.
thank's in advance

[WeaponsHot]

Great Topic! I hope this goes very far. Now for my question. Is it possible to get a flow chart of the steps required to create and maintain a database using the import function of prestashop. I'm basically brainstorming here.

I have a Cigar shop, the prices and availability are changing all the time This is how the current process is as I see it (which can be all wrong)

1. I start out by populating the shop using import csv
1A define categories and import.
1B define products and import.

Lets leave it at that for now to keep it simple. Now lets say I have a thousand items entered via the method above, the next day I get new price lists and inventory along with some new products.

Questions:

1. Do I use the same csv files that I used to upload originally and overwrite the old data or do I make new smaller ones with the specific changes and upload them?

How many lines can I have in a description field? Here is an example of one of my descriptions for a cigar which has multiple lines (can it be imported like this ):

Although the A. Turrent family is the cigar industry?s oldest family, they have continued to re-invent themselves by creating a brand that has been defined by many as their crowning achievement. Five generations, and 125 years make up the excellence that is A. Turrent. The A. Turrent family has been a long time benefactor of Mexican tobacco. Many companies in the industry today shy away from Mexico and instead use Dominican or Honduran tobacco, but A. Turrent saw no reason for changing locations. Makers of Te-Amo, Andrea?s, and Matacan, A. Turrent has become somewhat of a silent assassin, making quality cigars, but keeping far enough under the radar to avoid inflation.

its quite long.

Thanks for this forum

[uddhava]

When you use Google docs for the import file then during import into PS you need to set the delimiter to , instead the default ;

@Weapenshot

1. Do I use the same csv files that I used to upload originally and overwrite the old data or do I make new smaller ones with the specific changes and upload them?

What i have seen you just need to change the price and do a re-import of the products. Only the price should be changed. But i still have to confirm this. I will try this the coming days with PS 1.3.1

How many lines can I have in a description field? Here is an example of one of my descriptions for a cigar which has multiple lines (can it be imported like this ):

The description field is a TEXT field in the database. This is limited by MySQL to 64 KB. But that is a lot of text :-)

[uddhava]

Whenever i do an 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]

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

[jhnstcks]

Importing Products

Notes: 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.

Note 2 : You cannot import a product without a quantity.

I have never had problems with the reduction date fields on any of the imports I have done, while importing or after while editing the products, and I never define these columns.

You don't have to define a quantity, it will just put it down as 0.

Importing categories with identical names

By default you cannot import categories with identical names. You can change the import core file to create a workaround for this. Then you can import categories with identical names.

Easy solution, slightly change the category name, then just go back and edit it after you have imported.

Great Topic! I hope this goes very far. Now for my question. Is it possible to get a flow chart of the steps required to create and maintain a database using the import function of prestashop. I’m basically brainstorming here.

I have a Cigar shop, the prices and availability are changing all the time This is how the current process is as I see it (which can be all wrong)

1. I start out by populating the shop using import csv 1A define categories and import. 1B define products and import.

Lets leave it at that for now to keep it simple. Now lets say I have a thousand items entered via the method above, the next day I get new price lists and inventory along with some new products.

Questions:

1. Do I use the same csv files that I used to upload originally and overwrite the old data or do I make new smaller ones with the specific changes and upload them?

How many lines can I have in a description field? Here is an example of one of my descriptions for a cigar which has multiple lines (can it be imported like this ):

Although the A. Turrent family is the cigar industry?s oldest family, they have continued to re-invent themselves by creating a brand that has been defined by many as their crowning achievement. Five generations, and 125 years make up the excellence that is A. Turrent. The A. Turrent family has been a long time benefactor of Mexican tobacco. Many companies in the industry today shy away from Mexico and instead use Dominican or Honduran tobacco, but A. Turrent saw no reason for changing locations. Makers of Te-Amo, Andrea?s, and Matacan, A. Turrent has become somewhat of a silent assassin, making quality cigars, but keeping far enough under the radar to avoid inflation.

its quite long.

Thanks for this forum

Aslong as you add product_ids in the original import, and keep the ids the same you can re-upload the file whenever you want, and it will overwrite the products. You don't have to import every column, so you could tell it to ignore all the columns apart from product_id, name and price.

I use the productexport module that is available on the forum, you can define which columns to export, for example product_id, name and price. Then edit the csv in openoffice (you can define file formats to UTF-8 as a default), and then use the import function to import the csv back into your shop.

A few of my own tips:

When importing your products use the category id rather than the category name, especially if you have categories with the same name.
When importing manufacturers/suppliers also use the id, if you don't the manufacturer/supplier will be duplicated. (1000 products imported with manufacturer Apple, will create 1000 Apple manufacturers).
For some reason the import function dislikes html tables. It seems to add random column breaks which will screw up the csv.

[uddhava]

I have never had problems with the reduction date fields on any of the imports I have done, while importing or after while editing the products, and I never define these columns.

You don’t have to define a quantity, it will just put it down as 0.

Funny how these things work. I do have (with my current csv setup) problems with the date field. If it is caused by the csv, or server setup i dont know. For 1.3.1 this problem exists. I will look into it as time goes.

And for the quantities. I cannot import when the quantity field is empty. Again on 1.3.1 and maybe the same reasons as mentioned above...
Ill keep going.

Thanks for the tips. Using the category ID in your products is a good standard although not user friendly. I never actually tried to use category names.

When i import products the manufacturer does not get duplicated when using a name. Again on 1.3.1

Now i am just trying to figure out how to get line breaks in the Description field.....

[MrBaseball34]

Beware that opening the CSV files in Excel CAN screw up your EAN13 values.
If you do not set the column format of the EAN13 column in your CSV to Text,
when you save your CSV, the values will be recoded to a scientific formatted number.

We ran into this problem and it was causing big headaches.

[MrBaseball34]

Ok, the post says to stop using Excel, but, we use a custom import process and do not use the CSV import in PrestaShop but I was just giving a hint about how Excel mangles the files.

[uddhava]

We ran into this problem and it was causing big headaches.

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.

[uddhava]

Ok, the post says to stop using Excel, but, we use a custom import process and do not use the CSV import in PrestaShop but I was just giving a hint about how Excel mangles the files.

Hints are very appreciated!.
Since you use a special import (which one?) you might use excel for it?

[MrBaseball34]

No, we just use Excel to view the files when needed. We do not deal with foreign languages so UTF-8 is not really a concern of ours.

Our inventory file is downloaded from the vendor using ftp_get and then parsed using fgetcsv and the we iterate through the rows and all the pertinent info is inserted or updated in the database. The import process took 5+ hrs to run for 18,000+ products before I began to work on it and now it is under 2 hrs for 24,000+ products.

I have also written a new process that uses stored procedures and functions to do all the importing. I LOAD DATA INFILE our inventory file into a table and then use a cursor to iterate over each product and insert the data in the correct place using stored procedures and functions. When this goes live, it should take our 24,000+ product import down to < 1 hr.

[uddhava]

I have confirmed an issue (bug) that affects the importing of images.
you can read the bug here: http://www.prestashop.com/bug_tracker/view/4932/

but in summary : When you re-import a csv file, product images get duplicated and all images will be displayed in the front end...

[kingsinnersoul]

thanks for this great post. It helps a lot! :-)

[matte89thebest]

Any solutions for the image problem??

At the end of this, i think that can be better to use direct MYSQL script to load and update products from the server!

The only problem that i've is for the image!!

Image must processed from PS to create personal small image, but how i can do this?

[uddhava]

[update 12 jul 2010] This has been fixed in the SVN

I found a solution to the temporary import files.
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]

Does anybody have a clue how to change the AdminImport code so that the default Price field is always INCL TAX ?
Now it is default exclusive taxes.

[uddhava]

Product images get duplicated when you do a re-import. (see first thread for more info). But i have 'fixed' it.

I am so HAPPY...
I added some more code and now it works. 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".

Old 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));
                   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;
                           $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();
                           }
                       }
               }

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();
                           }
                       }
               }

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

AdminImport.php

[uddhava]

A question: how I can add line breaks in features?

For example, I have a paragraph Combinations Combinations -> Options (Group: Value1, Valu2, Value3, etc.) I need to add the same thing in Products, such Products -> Feature1, Feature2, Feature3, etc.


How do I do? I hope that I may understand what I mean :-S

That is a good question that i also need an answer for..
When i used excel before, i could make paragraphs (line breaks). But not in Google Docs.
There is some special characters that does the job, but which one i dont know...

[uddhava]

I have started part 2 of this guide.
Each guide covers a specific topic.

Part 1 - Intro, bugs, features and core changes
Part 2 - Importing products & Combinations

[Rusty_]

This is a very good thread, but once the basic csv format is mastered the main problem I see people complaining about is the image upload.
I am trying to bulk upload in a new store hosted on 1and1.com named poppshoppingmall.com
I did not think I would have any problems since I was successfully uploading items on another shop (I do not know where it is hosted), but I am consistently getting the error message ‘Error Copying Image’. In my file I have an image at http://www.poppshoppingmall.com/img/test/cgy-101GRTXPGAMESV2.jpg
On the other site I would have used:
http://www.poppshoppingmall.com/img/p/cgy-101GRTXPGAMESV2.jpg
In either case, once the graphic could be viewed in IE or Firefox the upload would work, but not now (oh, and this is the new version, 1.3.1.1)
1and1.com uses PHP version 5.2.13 and MYSQL version 5
Oh, and the file I attached does not update existing records but creates duplicates. Would populating the ID field correct this, or is duplication a feature?

ComputerGallery(Test02l).csv

[uddhava]

The image duplicating is "Expected Behavior" according to the PS team. Read more about this "bug" and the workaround for it in part 2.
I think the image duplication is annoying especially when you re-import a lot.
I would like that the PS team would implement a workaround as i suggested to them. It makes more "sense".

And indeed, people are getting problems with images a lot. The CSV is not that difficult and with the templates i provided it should even be easier.

The copying of images is done by prestashop during the import. If any folder permissions are not correct you can expect problems.

[jcmil]

Thanks for making this helpful thread.
One observation - the link to separate parts don't work for me as 'edit' is included in the url. I don't have permission :bug: :-)

[uddhava]

OK, thanks for the suggested fix. I have fixed it :-)

[uddhava]

The default Field Separator is set to a semi-colon ";" during import.
When you want to change that to something else (for example a comma) then you need to change this :

File : /admin/tabs/AdminImport.php (1.3.1.1)
Line : 1125

old code

<input type="text" size="2" value=";" name="separator"/>

New Code

<input type="text" size="2" value="," name="separator"/>

[jcmil]

It's interesting that the software says "Select your .CSV file:" & then by default gives ; as the separator... :-S
I think I will edit my AdminImport.php as you have shown.

Your work on this thread is invaluable Uddhava. :-)

[betobert]

Thanks Uddhava, this guide is very useful for newbies !

[overdrive]

Thank you very much uddhava

[nemani]

Hi Uddhava,

I see that you are very good in Prestashop. I am trying to sort ou mine problem, but nobody answering me. Could you please check out my question and let me know if you have seen this problem? This is the link to me question. Thanks in advance.
http://www.prestashop.com/forums/viewthread/71351/

[gx65]

Excellent work, thank you so much , I will try to solve my import problems according to your advise. :-)

[gx65]

The error message when I upload CSV: “Product->link_rewrite is empty for default language”. I used Notepad++ saved as UTF-8 format (before I just saved file in excel as CSV). 。 Could you help again.? I have attached my CSV file edited in excel.
Appreciate your time.

MyProductsExport-need help.csv

[abmag]

My english is too bad when i write, i answer in french Sorry

le champs "link_rewrite" est obligatoire et doit être renseigné En anglais le nom du champs est "URL rewrited"

[nuttis]

I do importationg very much right now in my new store and have url rewrited enabled. But i never write anything for that field in my importationg. The link rewrited fills in automaticaly with the product name that way. But if you want to have something else then the product name then you need to fill in this field too.

Pour abmag:
Tu nas pas besoin de remplir ce champs si tu veux avoir le nom du produit dans le link_rewrite. Sa se rempli automatiquemant quand tu fais ton import dans le bo. Mais si tu veux avoir autre chose que le nom du produit alors il faut que tu rempli ce champs.

[abmag]

"Tu nas pas besoin de remplir ce champs si tu veux avoir le nom du produit dans le link_rewrite. Sa se rempli automatiquemant quand tu fais ton import dans le bo. Mais si tu veux avoir autre chose que le nom du produit alors il faut que tu rempli ce champs."

Avec la version 1.3.2. j'ai eu la même erreur que gx65 sur des produits qui n'existaient pas encore dans la table product_lang et j'ai du faire comme indiqué çi-dessus pour l'import via csv et il m'a semblé que le module d'import ne savait pas remplir ce champs. mais je suis un néophyte en presta ....

in bad english:

I had this error with prest 1.3.2 (link_rewrite is empty) and i correct this with this solution, but i am bigginer in Presta :red:

[petras]

I have a big problem with importing products from CSV file.
I'm from Poland, and we have a typical "polish letters" like: ą,ę,ł,ć,ó,ż,ź...
When I try to import products where polish letters are included there's a problem.
If name of the product starts with a polish letter, it's cut in Step 2 Import. For example: product name should be: "Żółty sweter", but it's "ółty sweter".
The same problem I have with making "seo firndly urls". PrestaShop doesn't convert polish letter to "english" letter, for example: ą=>a, ć=>c.

I use PreStashop 1.3.1.1, Notepad++ (UTF-8 without BOM), Back Office -> Tools -> Import

[dreamerworks]

Hi, I use openoffice and works good. Seems to keep utf (althought I found some weired character on one product... don't know why) But in general it works ok!

I have an issue, I think it's not realted to the csv openoffice produced.
I haven't seen any post like this so I'll leave this here.
It's in the combinations import. I Import combinations and the combinations (color x size) get imported without errors.
I check the product combinations and all my combinations are listed.
Then I start setting the images for each combination. All is well so far.
Then, apparently ramdomly, when I click on the edit button for one combination the combination text-area on teh top appears empty.
If I try to add images anyway, it says Error: "you must add at least one attribute".
If I try to add the combination, it says combination already exists.
So far the only solution for this is... delete the imported combinatin that is not working and re-add it by hand.

In simplified observation it seeem to happen more frequently on XXL size combinations (but sometimes happens in others)
I double checked my csv to see it was something on the import file, but it seems everythins is ok...

"138";"Color:Light Blue,Size:03 - Men - XL";"701_21";"701_21";;;;"0";"5";"0.45";"0";
"138";"Color:Light Blue,Size:04 - Men - XXL";"701_21";"701_21";;;;"0";"5";"0.47";"0";

First line is good, second line has the problem.

If a moderator want to split this entry in 2 posts it's ok.
Post one - OpenOffice (on Ubuntu) works in creating csv. Set it to separate by ";" and add " to fields.
Post two - Problem importing (some) combinations.

[nuttis]

Hi

Is anyone using CSVed?

I use it now but when i was about to import my products it messed up with the swedish letters åäö. CSVed is saving in UTF-8. Is that the problem? When i do the import in BO i can only choose ISO-8859-1 file. I tried to change CSVed to iso-8859-1 but it doesnt seem to work.

Should i use another csv program?

Anyone has an idea?

Thanks for this great and useful topic!

[abmag]

try open office

[nuttis]

Thanks, i tried OpenOffice but it didnt work either. So far it only works with Google Docs. The problem is that it takes too long time for big files.

Is anyone that has experience of using åäö and other similar letters reading this please tell me what program you use. Thank you so much!

[uddhava]

During Import you can choose to use your own locale (language). But when you do so in 1.3.2 the import will not work unless you choose English. If you have disabled the english translation then you cannot import products anymore.

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 NOT been fixed in 1.4 SVN till now

[ruslan]

please Help!!!)How can I make it so that when the import process is started, all the products get "Not available" status?

[uddhava]

please Help!!!)How can I make it so that when the import process is started, all the products get "Not available" status?

I dont what you mean, but if you import with a 0 quantity, the product will say : out of stock.

[ruslan]

I need request, that will set “0” in goods quantity, to be performed before import starts.
The reason for this is:
1. Price lists come with different goods
2. The item that did not get in new price list should be left on site, but with “0” in quantity

So the import process will be as following:
First I set “0” in quantity for each item on site.
Then price list is imported with, for example, “10” value in quantinty.
So after import we will have new items added and existing items updated (either with “0” in quantity – if it is not in new price list, or with new value of quantity as in price list)

[uddhava]

This is very confusing.

First you want to set the quantity for all products in the database to 0.
Then you want to import a price list and all quantities will be 10

So far i understand. But then what with the new products ??

do you use fixed ID's for the products ??

[ruslan]

New products are added. ID,s not fix

It is necessary to input request
<?php Db::getInstance()->Execute('UPDATE '._DB_PREFIX_.'ps_product SET quantity'); ?>

but where can it be input in AdminImport.PHP file ????
(http://www.prestashop.com/?ACT=29&fid=104&aid=26691_99WpWDUJ9ZIX6dCYU9bZ&board_id=1)

must be reset before the submission new products from CSV

[uddhava]

New products are added. ID,s not fix

It is necessary to input request
<?php Db::getInstance()->Execute('UPDATE '._DB_PREFIX_.'ps_product SET quantity'); ?>

but where can it be input in AdminImport.PHP file ????
(http://www.prestashop.com/?ACT=29&fid=104&aid=26691_99WpWDUJ9ZIX6dCYU9bZ&board_id=1)

must be reset before the submission new products from CSV

You should try to use fixed ID's when possible.
I dont know how to insert these SQL statements. Usually the class AdminImport takes care of this (in /classes)
Be carefull messing up your database !

[ruslan]

where exactly should I include (‘UPDATE ‘.DB_PREFIX.‘ps_product SET quantity = 0’); in AdminImport.php ???

AdminImport.php file: http://www.prestashop.com/?ACT=29&fid=104&aid=26691_99WpWDUJ9ZIX6dCYU9bZ&board_id=1

[uddhava]

Like i said, any sql statements are handled by the classes and not the Admin Files.
So look into the classes instead of the AdminImport.php.

But since you want some pretty weird stuff, you are on your own.

[kingcheetah]

Hi uddhava,

You are an import-expert ;-)
I have a question;
When i import product descriptions with Notepad++ and CSV (with UTF-8 code) it will be a success, but it looses the layout (no white lines, bold, larger font).

Is there a solution?
Regards,

King cheetah

[uddhava]

Like i said somewhere else (http://www.prestashop.com/forums/viewreply/353845/)
All styling is removed during import (incl html etc). But during the import of categories white lines are preserved....

ys

[chris2407]

Hi guys,

i need your help. I have all my products in my Ebay auction software. I can export them in CSV Format but i think it is not compatible with prestashop...

Please look at my attached file. How can i import this file to prestashop? What is the best and easiest way? I want that prestashop loads all the photos, title and item description.

many thanks

Artikelexport.csv

[uddhava]

Chris,

You can use my csv template file (first post)
to make your csv compatible with PS.
And while you are at it, read all the info that i put there. I am sure that with this info you will manage.

[chris2407]

Hi Uddhava,

i did see your template but it looks much different to my export file... how can i get my file in same format like your template? I have about 400 products and always getting more so i need a automatical process... can you tell me will prestashop load my photos? Cause inside my CSV file all photos are already filled in with valid HTTP links....

many thanks

[uddhava]

It is of course most likely that your export file does look different then the PS file. And there is no automatic way to do this yet. As of PS 1.4 there will be an API available so until somebody develops a program to do it, you need to change your csv file manually.
And PS cannot load images from an external URL. (at least not version 1.3.x.)
You need to upload all the images into a folder in your web root folder and the use a relative PATH in your export file.

[chris2407]

OK I will find someone to write me a excel VBA script or macro which will modify my file so it will look like your example...

And PS cannot load images from an external URL. (at least not version 1.3.x.)
You need to upload all the images into a folder in your web root folder and the use a relative PATH in your export file.

How can i modify my prestashop so it will accept the external links? I dont want to have each photo 2 times on my server... or would it be possible to modify the links so they are relative paths? Cause my photos are saved on same webspace where prestashop is running: for example in my csv it looks like this right now: http://www.example.de/ebaypics/photo1.jpg

what if i modify this link like this: /ebaypics/photo1.jpg will prestashop accept this link then?

many thanks!!

[uddhava]

the PATH ../ebaypics/photo.jpg will work if your prestashop is installed in www.example.de/
if your shop is installed like this : www.expample.de/shop/ then you need to use this: ../../ebaypics/photo.jpg

And i dont know which version PS you are using. but 1.4 maybe works with URLS (not sure though)

[chris2407]

many thanks for your great help i really appreciate your support!!

I am still using Prestashop 1.3 i am very afraid to upgrade cause i did so many modifications in this shop... i dont want to use any functions or dont want to have problems...

[kingcheetah]

Let's talk about the problem with importing product descriptions (here we studied earlier (losing layout)):
I solved this by storing product descriptions in Word article number as the name of the file. In Excel, I have all my products with article number.
With Perfect Automation (free program that can record your actions and repeat) copy the article number from Excel and paste into PS, open product, then open the Word file, copy and paste text in PS. And then repeat the process automatically, here I leave the computer running all night. Until now, a reasonable solution for me and perhaps others?

Regards,

King cheetah

[giuly]

Sorry sure I didn't understand.
I have to update my catalogue, I have more than 2000 products and I have to upload other many products.
I have some questions plese:
- I don't see clicking on see part 2, it says link wrong. I need to know also for product combinations
- I have to specify also the url of images, is it possible? or create problems?

Someone uses csv to import new products? If yes it is all allright?

thanks in advance

[Umar Kaleem]

Intro
After wasting too much time on the importing of CSV files (and encountering lots of bugs) i wanted to get
everybody on this forum to add their knowlegde and experience to this thread.
The wiki has some info on importing products, but not how to make a proper CSV file. There are many threads in the forum with people looking for an answer... Let it be collected here !!!

Index
Part 1 - You are here
Part 2 - Importing Product Combinations

Prestashop version : 1.3.1.1
Before posting questions/issues with importing make sure you use the latest Prestashop version!

Current known Import issues / bugs / features
1. solved Temporary image files are created in /img/tmp and not deleted
2. solved Product images are duplicated when a re-import is performed. This is called a 'feature' by the PS team. Basically, every time you try an import, all the images are duplicated. For example, when you re-import 6x, you also get 6 products images. #bug report #feature request 1 #feature request 2 + solution

Creating CSV Files

Creating an CSV
Excel
Excel does not support UTF-8 encoding (yet?)
It will mess up your special characters when saving it as an .csv.
Also when importing a .csv into excel it will reformat the EAN column unless you set the column type to "text" during the import
So stop using Excel!!

Notepad++ (not the windows not!pad)
You can create a nice .csv file with notepad++.
But be sure to encode it in UTF-8 (see >Format>Convert to UTF-8) and save it.
After that Prestashop will import the file correctly

CSVed Download here

Google Docs
With google docs you can create nice 'excel' like sheets that are also UTF-8. If you have an gmail account you can use Google Docs. I have included some sample files below for you to use.
Some important notes:
* Date fields have a fixed format that is not compatible with the date field in Prestashop
Google uses this : 2010/03/26
Presta uses this : 2010-03-26
If you want to use the prestashop format, be sure to use a ' in front of the date. Otherwise google docs will convert the date to its own format
* Multiple Field Seperator is , and cannot be changed

OpenOffice
Anybody has experience?

Multiple lines in your description field
PS 1.2.5 supports Multiple Lines import
PS 1.3.0.1, 1.3.0.2, 1.3.0.3 do NOT support multiple lines import.
1.3.1.1 Should support multiple lines import. It works BUT ONLY when your field is quoted " "
Google Docs always quotes multiple lines text.

What column names do you need
The best way to view the column names is to login to the back-end of Prestashop and go to >Tools>Import.
The field names are listed on the right side of the screen after selecting the type of import.

Importing Categories
Importing categories with identical names
By default you cannot import categories with identical names. You can change the import core file to create a workaround for this.

Commenting the following code will enable non-unique category names.

Looking at the categoryImport() function in admin/tabs/AdminImport.php, it appears it is the following code that checks whether a category with the same name already exists and then changes the category ID to it instead of what is in the CSV file:
Here is my code : (line 476 - 481)

// If category already in base, get id category back
               if ($categoryAlreadyCreated['id_category'])
               {
                   //$catMoved[$category->id] = intval($categoryAlreadyCreated['id_category']);
                   //$category->id =    intval($categoryAlreadyCreated['id_category']);
               }

(read more in this thread)

Now the PS team has to implement this into an option that you can enable/disable.

Importing Products
See part 2

Importing Product Combinations
See part 2

Core changes to import
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').'.

Downloads - Example files

Hello

What program do you recommend to create CSV.

Im having problems still.

Do you have a step by step guide?

Thanks

[chris2407]

I opened my products with Open Office in UTF-8 format but like somebody else mentioned earlier it destroys all the german special characters like ä ö ü ... has anyone found a solution for this?

And another question: I have all my products description in HTML format how can i import this into prestashop?

many thanks

[ontljoshi]

Hi Udhava,

thanks for your help, but i am not able to upload features for the products.

basically, if you could help in that area it would be great help.

thanks,
om

[homesteadcastle]

Hi all,

I will be getting a weekly update from one of my suppliers with quantity updates. The name column in the file is exactly as I have it on my website. However, I need to know if there is a way to do this update without have a product id in the import.

Thanks for all of of the help.

[nikdagr]

Hi Udhava,

 

I'm trying to find a way to update products only by same ID (prestashop requires also field NAME). Any ideas?

 

Thanks

Nick

[mrsmith25]

nvm

[Pulpinator]

Hi,

 

Your links to example csv's on Google Docs are broken, can you give us working links?

 

/Hans

[franco.pentangeli]

Hi,

 

Your links to example csv's on Google Docs are broken, can you give us working links?

 

/Hans

 

It's the same for me. Thanks

[Peda]

Hi,

PS 1.4.7.3 version

Importing product multi language everything is ok except the product tags.

Is there any way import the product tags in different languages.

Regards

Edited May 3, 2012 by Peda (see edit history)

[prestanesia]

cant download the example files. Download link error. Can someone reupload the file ? thanks.

[BlizzardUK]

Great thread. Anyone got working URLs from the first post ? They don't seem to work. Thanks.

[Pany]

why I can't download

[sarahwhite0]

Beware that opening the CSV files in Excel CAN screw up your EAN13 values.

If you do not set the column format of the EAN13 column in your CSV to Text,

when you save your CSV, the values will be recoded to a scientific formatted number.

 

We ran into this problem and it was causing big headaches.

ah, met the similar issue for EAN13 barcode values in Excel...