Single Blog

PrestaShop – 12 Tips to importing a CSV file

4 February, 2013, Written by 0 comment

Prestashop is a powerful, popular and easy to use open source ecommerce solution for small to medium size businesses. Out of the box it comes with many essential features that would be difficult and/or expensive to create from scratch. One of the handy tools available to website owners is the ability to create a table in Excel holding all your product catalog data and import this as a CSV file into the PrestaShop database. Saves a LOT of tedious data entry.

Following is for Prestashop 1.4.x (Prestashop 1.5.x shouldn’t be radically different).

Before we begin you might want to check out an excellent series of YouTube videos posted here:
http://www.youtube.com/user/gpgiuit/videos
Pace is very sloooooow, and the accent a bit hard to pick out at times but still a good resource.

First you will need to download and install on your local computer a version of Open Office. There might be a way to do this with Excel but we won’t cover it in this tutorial. In the PrestaShop back office on the “Import Products” screen it offers a sample list of fields which you need to populate in order for the import to work effectively. The trick is to paste the list of product fields into an Open Office spreadsheet and use the “Paste Special Transpose” function to paste the fields names horizontally as field headers.

Here is the blow-by-blow version:

1. Copy the list of field headings from the PrestaShop “Import Products” page as described above.
2. Open a new Open Office spreadsheet.
3. Click in cell A1
4. Right click “Paste Special” then choose “unformated text”
5. A dialogue window will open. Probably best to choose UTF8 and the correct language option. The other default options should be correct.
6. Click “OK”. You will now have a vertical list.
7. Highlight the list and right-click “Cut”
8. Click on cell A1 again.
9. Right-click select “Paste Special”. Make sure “Transpose” is checked in the options panel at the bottom of the dialogue screen.
10. Select “OK”.
11. You should now have a horizontal list of field headings ready for your CSV file.

Then enter what you want under each of  the fields and export as CSV. Remember to choose “edit filters” in the export options and set your file encoding type to UTF-8, your separator to semi-colon [;] and text delimiter to quote marks (“).

Now you are ready to import the CSV file into PrestaShop.

In PrestaShop use the Tools, Import functions to upload your file.
Choose the type of CSV file you are importing (“Products” in this case).

Next you’ll need to go through the process of mapping each column in your CSV to the corresponding database field. Remember to select “Ignore the column” if the column is blank or you might get some Fatal error. Dont panic if you do. Just repeat using the correct steps.

Importing “Features” in PrestaShop is a bit non-intuitive but it’s a breeze when you know what the workflow is. First you need to have your unique Feature description column names in your CSV. Then in the import process you need to assign each column the field name “Features”. THEN a second box will open up allowing you to input the actual Feature name and press OK.

 

12 Useful Tips

 

TIP! When configuring the Import routine remember to “Skip 1” so that the import ignores the first title row otherwise you will just confuse the hell out of PrestaShop.

TIP! There doesn’t seem to be a way to import attachments associated with products yet (i.e. downloadable PDF files etc). Check the PrestaShop Modules repository to see if there is a custom module if this aspect is critical to your situation.

TIP! If you are importing a file to overwrite existing products you MUST include the ID field.

TIP! Another big “gotcha” is NOT to assume that the CSV file really doesn’t need to include those columns that are irrelevant to your setup. For example I dont need the “Wholesale price” or “Ecotax” values, so why bother adding them into the CSV file? Well it seems like Prestashop INSISTS on those columns being part of the CSV even if you select “ignore column” later on in the import process.

TIP!  Maybe this is just a fact of life about working with CSV files, but the first column of your spreadsheet really needs to contain values for every row. If your first column is, say “Active (0/1)” and you choose not to populate that column, Prestashop will import the file, but when you try to match up the data to the PrestaShop fields, everything will look very funky. In fact it probably wont work at all.

TIP!  Importing products for a different language version (e.g. English/Japanese store) works fine. Again, just remember to include the ID field, the name field and whatever fields have translations. Your can ignore fields that have say numeric values because they should be reading your default language data anyway. No need to repeat those but you do need the column headings as explained above.

TIP! Features – You should import the feature set in your default language and then go to the Features tab in your back office and translate each feature there.

TIP! Front End Data Sheet – Unfortunately the features are listed on the product data sheet in seemingly random order. You’ll need to write some custom PHP code if you want the feature set in any particular order.

TIP! It’s possible to import anchor tag html and break tag html (and more) as part of your CSV file. The important tip is wherever you have a tag attribute (e.g. src= , title= , class= ) you should use single quotes NOT double quotes if your CSV file is set to use double quotes as the text delimiter (e.g. src=’../pdf/specs.pdf’)

TIP!  If you are importing images and some products have existing images which you dont want to duplicate, make sure the “delete existing images” fields in your CSV have a value of “1”. This is the default option but it seems best to explicitly set it anyway.

TIP! If you are importing a CSV with more than 140 images you may get a memory time out issue (admin screen with the headers and a blank page). This can be solved by increasing your server memory limit to say 256MB using php.ini
And while you are at it, might as well increase the number of Post Variables if you need to do a lot of back end translation. By default the max_input_vars is probably set to 1000.
So your new php.ini file may look like this:

;;;;;;;;;;;;;;;;;;;
; Resource Limits ;
;;;;;;;;;;;;;;;;;;;
max_execution_time = 600 ; Max execution time, in seconds
max_input_time = 600 ; Max parsing request time in seconds
memory_limit = 256M ; Max memory a script may consume
max_input_vars = 3000; Max number of Post variables
suhosin.post.max_vars = 3000;
suhosin.request.max_vars = 3000;

 

TIP!  If you are migrating to a new domain and have some images in your Description field, the SRC attribute may point to the old domain. In which case you can run the following SQL query on your database through phpMyAdmin.

UPDATE ps_product_lang SET description = REPLACE(description, ‘olddomain’ , ‘newdomain’);

Change the values of “olddomain” and “newdomain” above as is appropriate to your situation.

Do NOT follow this link or you will be banned from the site!