How to do export csv categories in Prestashop 1.6?

[daniem]

Hi!

I have problem trouble with export to csv in Prestashop 1.6. I want do export all categories, but I can't.

[PSfever.com]

Hi Daniem,

 

what's holding you back from exporting all categories? You probably need to be in the root category in order to export all categories through the CSV export file.

[daniem]

Thank's PSfever.com for answer My main categories is: brake discs and brake pads. I can do export csv categories, but only main categories... I want all categories.

 

This is categories tree:

 

Brake pads

- Standard

-- Mark car

--- Model car

- Sport

-- Mark car

--- Model car

 

Brake discs

- Red

-- Mark car

--- Model car

- Blue

-- Mark car

--- Model car

 

 

I can do export, but not all categories, only main categories.

[daniem]

Help me...

[PSfever.com]

How about exporting tables right from phpmyadmin? What do you need those tables for? Import into another Prestashop?

Or for some other work? You should be able to export .csv files from phpmyadmin, you will just need to link some of the .csv files together.

[daniem]

I want do export all categories from admin management Prestashop.

all categories = main cat. + sub-cat. + sub-sub-cat...

Image 1: http://awesomescreenshot.com/06233x9054

Image 2: http://awesomescreenshot.com/08b33x9l40

[PSfever.com]

I got an idea. In your backoffice, Go to Advanced Parameters - SQL Manager and put this syntax as a SQL query.

SELECT * FROM ps_category, ps_category_lang WHERE ps_category.id_category = ps_category_lang.id_category;

This will render you a CSV file with all the columns from your backoffice which are related to categories. You can then delete unnecessary columns right in Excel.

[Bewitching]

THANK YOU ! (i was following this thread hoping someone would give a solution.)
I entered exactly what you suggested, and it worked PERFECTLY !!!
The resulting csv had ALL my categories AND sub-categories!

Thank you again! The PrestaShop forum ROCKS 

 

I got an idea. In your backoffice, Go to Advanced Parameters - SQL Manager and put this syntax as a SQL query.

SELECT * FROM ps_category, ps_category_lang WHERE ps_category.id_category = ps_category_lang.id_category;

This will render you a CSV file with all the columns from your backoffice which are related to categories. You can then delete unnecessary columns right in Excel.

Edited July 11, 2014 by Bewitching (see edit history)

[jose_rui_sa]

HI there

 

Im tired to try migrate from 1.5.6.2 to 1.6...

 

 

I already made a presta update but store keeps different data on new presta.

 

 

For example the 1.6 theme assume different configuration  regarding the 1.5

 

 

Can I migrate just products, clients.. and all needed information from clients? Or just reset after migration the theme?

[NanMar]

What would the SQL code be for using this method to export orders? Thank you.

I would like to be able to run a sql report and get the list of all the products and the total quantity ordered. 

Edited January 9, 2015 by NanMar (see edit history)

[Serge Goujon]

Once again... The default functionality (such as export/import categories/products) just doesn't work as intended. Why you ask me? Because they need to sell their paid export/import modules.

 

For me (1.6.0.11) it exports only main categories and above mentioned SQL Query just resulting in error "Unknown error "checkedWhere".

 

I did that anyway in an external program, but I think it should work as intended out of the box, shouldn't it?

 

EDIT: that query just denied to work without aliases. So this one works properly now:

 

SELECT * FROM ps_category as AA, ps_category_lang as BB WHERE AA.id_category = BB.id_category;

 

I still hope they will fix default export code in BO.

Edited February 17, 2015 by Serge Goujon (see edit history)

[Nixnix]

I got an idea. In your backoffice, Go to Advanced Parameters - SQL Manager and put this syntax as a SQL query.

SELECT * FROM ps_category, ps_category_lang WHERE ps_category.id_category = ps_category_lang.id_category;

This will render you a CSV file with all the columns from your backoffice which are related to categories. You can then delete unnecessary columns right in Excel.

 

Sorry for bumping this old threat but your advise is excellent and I wanted to say thanks!

I have a question though - I want to export all categories and include their text descriptions, but they are in html format with some language specific html characters (such as é) and hence the semi-colon character completely garbles up the exported file since semicolon seems to be the csv cell separator.

 

How can I export it with another character as cell delimiter?

 

Thanks!

[Doudoupf]

hi i am french people , i want to do the same .. export my categories presta 1014

SELECT * FROM ps_category, ps_category_lang WHERE ps_category.id_category = ps_category_lang.id_category;

 

dont work for me , i have replace WHERE by fr

 

export work , but empty file .. idea ? thank

[SunFun]

I got an idea. In your backoffice, Go to Advanced Parameters - SQL Manager and put this syntax as a SQL query.

SELECT * FROM ps_category, ps_category_lang WHERE ps_category.id_category = ps_category_lang.id_category;

This will render you a CSV file with all the columns from your backoffice which are related to categories. You can then delete unnecessary columns right in Excel.

 

Using this in Prestashop 1.6.1.6 returns "Undefined "checkedWhere" error"

 

Any idea about whats wrong?

 

Thanks in advance.

Edited July 16, 2016 by SunFun (see edit history)

[RemcoDr]

Using this in Prestashop 1.6.1.6 returns "Undefined "checkedWhere" error"

 

Any idea about whats wrong?

 

Thanks in advance.

 

Same here!

[Fred85100]

Same here!

 

Using this in Prestashop 1.6.1.6 returns "Undefined "checkedWhere" error"

 

Any idea about whats wrong?

 

Thanks in advance.

 

Hi there, the reason why it doesn't export all by default, it's just because of SQL Manager basic request :

 

By default :

SELECT
								a.`id_category`, `name`, `description`, sa.`position` AS `position`, `active`
			, sa.position position
			FROM `ps_category` a 
			LEFT JOIN `ps_category_lang` b ON (b.`id_category` = a.`id_category` AND b.`id_lang` = 1 AND b.`id_shop` = 5)
			 LEFT JOIN `ps_category_shop` sa ON (a.`id_category` = sa.`id_category` AND sa.id_shop = 5)  
			 WHERE 1   AND sa.`id_shop` = 5  AND `id_parent` = 161 
			
			 ORDER BY sa.`position` ASC

You can see the line WHERE 1, by default it's select only sa.`id_shop` = 5, to resolve it just erase this line, and replace by WHERE 1 only, add information you need to export (see below)

SELECT
a.`id_category`, `active`, `name`, a.`id_parent`, a.`is_root_category`, `description`, `meta_title`, `meta_description`, a.`id_shop_default`
FROM `ps_category` a 
LEFT JOIN `ps_category_lang` b ON (b.`id_category` = a.`id_category` AND b.`id_lang` = 5 AND b.`id_shop` = 5)
LEFT JOIN `ps_category_shop` sa ON (a.`id_category` = sa.`id_category` AND sa.id_shop = 5)  
WHERE 1


ORDER BY a.`id_category` ASC

And it's done, thanks for watching

Edited September 22, 2016 by Fred85100 (see edit history)

[vucumbra]

 

Hi there, the reason why it doesn't export all by default, it's just because of SQL Manager basic request :

 

By default :

SELECT
								a.`id_category`, `name`, `description`, sa.`position` AS `position`, `active`
			, sa.position position
			FROM `ps_category` a 
			LEFT JOIN `ps_category_lang` b ON (b.`id_category` = a.`id_category` AND b.`id_lang` = 1 AND b.`id_shop` = 5)
			 LEFT JOIN `ps_category_shop` sa ON (a.`id_category` = sa.`id_category` AND sa.id_shop = 5)  
			 WHERE 1   AND sa.`id_shop` = 5  AND `id_parent` = 161 
			
			 ORDER BY sa.`position` ASC

You can see the line WHERE 1, by default it's select only sa.`id_shop` = 5, to resolve it just erase this line, and replace by WHERE 1 only, add information you need to export (see below)

SELECT
a.`id_category`, `active`, `name`, a.`id_parent`, a.`is_root_category`, `description`, `meta_title`, `meta_description`, a.`id_shop_default`
FROM `ps_category` a 
LEFT JOIN `ps_category_lang` b ON (b.`id_category` = a.`id_category` AND b.`id_lang` = 5 AND b.`id_shop` = 5)
LEFT JOIN `ps_category_shop` sa ON (a.`id_category` = sa.`id_category` AND sa.id_shop = 5)  
WHERE 1


ORDER BY a.`id_category` ASC

And it's done, thanks for watching

 

thx work good

[miki22]

How to get images url's of each category?

[marie12]

Hello,
I need to export all the categories, included their name and link_rexrite.
 

I tried the solution given above :

SELECT
a.`id_category`, `active`, `name`, a.`id_parent`, a.`is_root_category`, `description`, `meta_title`, `meta_description`, a.`id_shop_default`
FROM `ps_category` a 
LEFT JOIN `ps_category_lang` b ON (b.`id_category` = a.`id_category` AND b.`id_lang` = 5 AND b.`id_shop` = 5)
LEFT JOIN `ps_category_shop` sa ON (a.`id_category` = sa.`id_category` AND sa.id_shop = 5)  
WHERE 1


ORDER BY a.`id_category` ASC

But infortunately, some columns are empty : active, name, description... all the values without the alias "a.".

But this is specially the ones I need !

 

I tried to rewrite this code to obtain the data in my csv files.

Il tried to write :

FROM `ps_category_lang` a

because it's in this table we find the data I need (and I changed some other things in this code...)

I tried to use UNION to have data from the 2 tables `ps_category_lang` and `ps_category`

...but without success...

because I'm not an SQL expert...

 

I need the list of all the categories with the name, the id and the link_rewrite, to delete many of them, create some new one, and re-organize the tree of categories.

 

So somenone can help to complete this code (that is an interesting code for beginning).

 

Regards

[Fred85100]

Hello,

I need to export all the categories, included their name and link_rexrite.

 

I tried the solution given above :

SELECT
a.`id_category`, `active`, `name`, a.`id_parent`, a.`is_root_category`, `description`, `meta_title`, `meta_description`, a.`id_shop_default`
FROM `ps_category` a 
LEFT JOIN `ps_category_lang` b ON (b.`id_category` = a.`id_category` AND b.`id_lang` = 5 AND b.`id_shop` = 5)
LEFT JOIN `ps_category_shop` sa ON (a.`id_category` = sa.`id_category` AND sa.id_shop = 5)  
WHERE 1


ORDER BY a.`id_category` ASC

But infortunately, some columns are empty : active, name, description... all the values without the alias "a.".

But this is specially the ones I need !

 

I tried to rewrite this code to obtain the data in my csv files.

Il tried to write :

FROM `ps_category_lang` a

because it's in this table we find the data I need (and I changed some other things in this code...)

I tried to use UNION to have data from the 2 tables `ps_category_lang` and `ps_category`

...but without success...

because I'm not an SQL expert...

 

I need the list of all the categories with the name, the id and the link_rewrite, to delete many of them, create some new one, and re-organize the tree of categories.

 

So somenone can help to complete this code (that is an interesting code for beginning).

 

Regards

 

 

How to get images url's of each category?

 

Hi,

I will list step of the process I use for each export from prestashop admin :

 

First :

 

 

Next :

 

 

Now if we start from the code I pasted before :

SELECT
a.`id_category`, `active`, `name`, a.`id_parent`, a.`is_root_category`, `description`, `meta_title`, `meta_description`, a.`id_shop_default`
FROM `ps_category` a 
LEFT JOIN `ps_category_lang` b ON (b.`id_category` = a.`id_category` AND b.`id_lang` = 5 AND b.`id_shop` = 5)
LEFT JOIN `ps_category_shop` sa ON (a.`id_category` = sa.`id_category` AND sa.id_shop = 5)  
WHERE 1


ORDER BY a.`id_category` ASC

If I need to export name and link_rewrite values like marie12 wants just change :

SELECT
a.`id_category`, `active`, `name`, `link_rewrite`, a.`id_parent`, a.`is_root_category`, `description`, `meta_title`, `meta_description`, a.`id_shop_default`
FROM `ps_category` a 
LEFT JOIN `ps_category_lang` b ON (b.`id_category` = a.`id_category` AND b.`id_lang` = 1 AND b.`id_shop` = 1)
LEFT JOIN `ps_category_shop` sa ON (a.`id_category` = sa.`id_category` AND sa.id_shop = 1)  
WHERE 1


ORDER BY a.`id_category` ASC

if your fields are empty your id_lang and your id_shop can be false, you need to change it or to export every language and shop, delete the id_lang and id_shop values :

SELECT
a.`id_category`, `active`, `name`, `link_rewrite`, a.`id_parent`, a.`is_root_category`, `description`, `meta_title`, `meta_description`, a.`id_shop_default`
FROM `ps_category` a 
LEFT JOIN `ps_category_lang` b ON (b.`id_category` = a.`id_category`)
LEFT JOIN `ps_category_shop` sa ON (a.`id_category` = sa.`id_category`)  
WHERE 1


ORDER BY a.`id_category` ASC

For miki22, I don't know, i didn't found in which tab the category get the id_image, sorry... maybe an other people can help you.

[necks.pk]

Hi I need help  to make a csv files for my categories but I am unable to do after 1 subcategory

I need category-subcategory-sub-subcategory

 

for example:

Mens

-Tops

  -- T-Shirts

    --- Round neck

    --- Full Sleeves

  -- Dress Shirts

    --- Casual Wear

    --- Office Wear

Womens

  -- T-Shirts

  -- Kurta

  -- Dresses 

   --- Party Wear

    --- Casual Wear

 

Please Help

[Bewitching]

I had to jump back in and once again thank PSfever.com for sharing his SQL query!
(I am not syntax  savvy at all. I can find stuff if need be, but to put together a query? Not a chance  )
I am building a new store on Presta 1.7 but wanted to use my old categories and subcategories from my soon to be closed old site.
It would take me hours to hand type all that crap all over again.
I just ran the query, and in mere seconds I had a csv with a complete list of categories, subcategories and all their descriptions.

Old store running ver. 1.6.1.12

 

Once again, the PrestaShop forum rocks!
 

 

PSfever.com, on 08 Jul 2014 - 3:57 PM, said:

I got an idea. In your backoffice, Go to Advanced Parameters - SQL Manager and put this syntax as a SQL query.

 

SELECT * FROM ps_category, ps_category_lang WHERE ps_category.id_category = ps_category_lang.id_category;

 

This will render you a CSV file with all the columns from your backoffice which are related to categories. You can then delete unnecessary columns right in Excel.

 

THANK YOU ! (i was following this thread hoping someone would give a solution.)
I entered exactly what you suggested, and it worked PERFECTLY !!!
The resulting csv had ALL my categories AND sub-categories!

Thank you again! The PrestaShop forum ROCKS