Jump to content
  • 0
musicmaster

Exporting CSV in 1.5.4

Question

1.5.4 offers the possibility to export csv files for products, categories, orders, etc next to the save button. However, the function looks completely dysfunctional. No spreadsheet recognizes the exported files as valid csv files and when you look at them they use the semi-colon both for separating fields and for separating records - what obviously doesn't work. And that isn't the only problem...

 

Also I looked in vain for a place where I could configure the csv files (telling which fields to export and what to use as separator). Did I miss something?

 

Can someone enlighten me about this function, what I can do with it and what are the future plans of PS for it?

Share this post


Link to post
Share on other sites

70 answers to this question

Recommended Posts

  • 0

I went to the products section and hit the export button. Most every program I have ever used uses csv. so I can populate the correct columns and then import in the same place since there is an import and export button right next to each other.

I have no idea what to do with this file.

 

Thank you,

Steven

 

Sorry the products button in the catalog section.

Edited by steven@ssjewels.com

Share this post


Link to post
Share on other sites
  • 0

Steven, I have not had the time to complete the process of adding all the product data using this method. However, I did add most of my product data using an CSV export from PhpMyAdmin. I exported the "ps_product" and "ps_product_lang" tables and merged my new data with them using Excel. Then dropped the tables and uploaded the new CSV files to populate the DB with ne wdata ... then, had to go to the BO and select each product to finish adding the data I missed by not exporting all the ps_product tables.

Share this post


Link to post
Share on other sites
  • 0

Steven, I have not had the time to complete the process of adding all the product data using this method. However, I did add most of my product data using an CSV export from PhpMyAdmin. I exported the "ps_product" and "ps_product_lang" tables and merged my new data with them using Excel. Then dropped the tables and uploaded the new CSV files to populate the DB with ne wdata ... then, had to go to the BO and select each product to finish adding the data I missed by not exporting all the ps_product tables.

Mak

 

make backups or you risk destroying your site! :)

Share this post


Link to post
Share on other sites
  • 0

Mak

 

make backups or you risk destroying your site! :)

 

Thank you for your reply, however, I do not think I am smart enough to do it the way you did. I can probably do the part using the phpmyadmin and get the template out that way, but after that I am out of my league. Isn't there any program that can do that?

Or, can I populate my excel spreadsheet and drop it back in the database?

 

Thank you,

Steven

Share this post


Link to post
Share on other sites
  • 0

Thank you for your reply, however, I do not think I am smart enough to do it the way you did. I can probably do the part using the phpmyadmin and get the template out that way, but after that I am out of my league. Isn't there any program that can do that?

Or, can I populate my excel spreadsheet and drop it back in the database?

 

Thank you,

Steven

 

Hi Steven, are you asking about how to backup your mySQL database and PrestaShop files? The best way to do it is to do it manually. Backing up is very very very important.

 

Once you go into your phpmyadmin, find the corresponding database that connects to your store. Next you want to find the "Export" tab and export all the tables. Make to save it in a safe place.

 

To backup your PrestaShop files, you find your FTP program and simply copy all the folders (admin, config, classes etc...) and paste it on your computer hardrive or cloud service like Google Drive.

 

If you need help, just let us know.

Share this post


Link to post
Share on other sites
  • 0

Hi Steven, are you asking about how to backup your mySQL database and PrestaShop files? The best way to do it is to do it manually. Backing up is very very very important.

 

Once you go into your phpmyadmin, find the corresponding database that connects to your store. Next you want to find the "Export" tab and export all the tables. Make to save it in a safe place.

 

To backup your PrestaShop files, you find your FTP program and simply copy all the folders (admin, config, classes etc...) and paste it on your computer hardrive or cloud service like Google Drive.

 

If you need help, just let us know.

No not backing up, trying to import the my products directly into the database. I have no reason to back up my current database, if I cannot figure out how to import bulk products, I cannot use the program.

 

Thank you,

Steven

Share this post


Link to post
Share on other sites
  • 0

Thank you for your reply, however, I do not think I am smart enough to do it the way you did. I can probably do the part using the phpmyadmin and get the template out that way, but after that I am out of my league. Isn't there any program that can do that?

Or, can I populate my excel spreadsheet and drop it back in the database?

 

Thank you,

Steven

 

Hi Steven, there are tutorials on youtube on how to edit and merge cells and columns in Excel if this is the problem. However, Since I have edited my DB using this method I have had issues with the "New Product" label not showing on new products, New updated products not showing in the right column as expected and get DB error when I try to add a new product using the BO.... so, I would recommend finding another method as this does not seem to be a solid solution. Good Luck and post back if you find a better way please.

 

"K"

 

Hi Steven, okay I understand.

 

Are you familiar with CSV file at all? If not, then I would suggest to use a module that can do this for you.

 

Here is an Example of a pretty in depth CSV file http://themaveness.com/example.csv

 

Benjamin, I would hope that there is a better solution for importing (X) number of products all at once rather than what you have recommended here. This is vital to an ecommerce framework and I would think that this has already been a issue many times over ??? Why has this not been addressed?

Share this post


Link to post
Share on other sites
  • 0

Hi KSteele, the two ways to import mass data would be a CSV file or a module that would perform that function for you.

 

The example I posted is a confirmed working CSV for 1.5.4.1, I grabbed it from this thread, http://www.prestashop.com/forums/topic/251434-solvedcsv-product-import-features-not-importing/

 

The reason why I posted it is because it includes many of the more difficult aspects of the CSV, such as importing multiple features.

 

What issue specifically are you asking should be addressed? I will do my best to help.

Share this post


Link to post
Share on other sites
  • 0

I am pretty good with csv files, but the whole point of finding a program to do it is to spend the least amount of time on this aspect.

The part I am not going to be good with is probably going to be merging with a mysqladmin database. I would think exporting to a database would be easy. I will check out what you posted, it should help.

 

Thank you.,

Steven

Share this post


Link to post
Share on other sites
  • 0

Hi KSteele, the two ways to import mass data would be a CSV file or a module that would perform that function for you.

 

The example I posted is a confirmed working CSV for 1.5.4.1, I grabbed it from this thread, http://www.prestasho...-not-importing/

 

The reason why I posted it is because it includes many of the more difficult aspects of the CSV, such as importing multiple features.

 

What issue specifically are you asking should be addressed? I will do my best to help.

 

Thanks for responding Benjamin. I think what everyone here is asking and/or looking for is an "easy" method of importing multiple products. The example CSV method you posted here is more of an advanced user/programmer method and not likely to be used by anyone posting here, including myself.

Do you know of a module that will address this? I have limited knowledge of MySQL but I can see from looking at the tables that each product's data references are scattered amongst several different tables making adding a product using my method into an advanced task... BTW, I wonder if you can help me get my PS back working correctly again? LMK

 

A module that has all the necessary fields to properly add a product using CSV and import/export is all we need. The Attributes can be added at a later time as well as the images. One place to fill the data to all the tables that apply... does this exist?

Share this post


Link to post
Share on other sites
  • 0

Hi KSteele, I know a pretty good service here, https://www.file2cart.com/app/estimator/show/estimates/ The one time import of 1000 products is $19.00

 

Alternatively, you could post on the Job Offer forum and see what kind of quotes you get.

 

There are export products and orders for CSV on the Addons store, I think this is the best one, http://addons.prestashop.com/en/export-modules/6927-advanced-csv-export-for-15-produtsorders-cron-csv.html

Share this post


Link to post
Share on other sites
  • 0

Hi KSteele, I know a pretty good service here, https://www.file2car...show/estimates/ The one time import of 1000 products is $19.00

 

Alternatively, you could post on the Job Offer forum and see what kind of quotes you get.

 

There are export products and orders for CSV on the Addons store, I think this is the best one, http://addons.presta...s-cron-csv.html

 

That 1st solution looks like it could work. I have an upcoming client I will try this with. As for my issue... I have sent you a PM and my GTalk is ksteele36

Share this post


Link to post
Share on other sites
  • 0

KSteele,

 

This post is so long I've kind of lost track of what you are trying to get done. CSV product import is part of the standard PS. The trick is to know what columns are required and which are not. If you don't want to get involved with a helper, just start with one product and create a CSV with the product code and the name fields. Try to import that and see what errors out. You can have many more of the fields but only a few are actually required. I'm not sure this is what you want to know.

 

I can upload all of your products for you for a reasonable fee. If you want a continuing quantity/price update from a supplier's data I can do that also (cheaply).

Share this post


Link to post
Share on other sites
  • 0

KSteele,

 

This post is so long I've kind of lost track of what you are trying to get done. CSV product import is part of the standard PS. The trick is to know what columns are required and which are not. If you don't want to get involved with a helper, just start with one product and create a CSV with the product code and the name fields. Try to import that and see what errors out. You can have many more of the fields but only a few are actually required. I'm not sure this is what you want to know.

 

I can upload all of your products for you for a reasonable fee. If you want a continuing quantity/price update from a supplier's data I can do that also (cheaply).

 

Hi Richard, thanks for responding. I think I have a handle on it... the built-in import function is just a learning curve that I am trying to avoid (mostly because I do not see this as a viable solution for importing your products - this should be a simple CSV with headers that make sense... anything else is a fail in my eyes) That being said, even if I wanted to take you up on your offer... my db remains broken for the moment and I need to figure a solution before I can move forward with adding more product. Not to mention the EZ Retail Manager is $39 and if it works as expected will be a "long term" solution rather than a one time fix/fee.

 

"K"

Share this post


Link to post
Share on other sites
  • 0

KSteele,

 

This post is so long I've kind of lost track of what you are trying to get done. CSV product import is part of the standard PS. The trick is to know what columns are required and which are not. If you don't want to get involved with a helper, just start with one product and create a CSV with the product code and the name fields. Try to import that and see what errors out. You can have many more of the fields but only a few are actually required. I'm not sure this is what you want to know.

 

I can upload all of your products for you for a reasonable fee. If you want a continuing quantity/price update from a supplier's data I can do that also (cheaply).

 

The issue I am having with the PS export/import is I tried to list 1 product so I can see which fields need to be populated then I did an export.

The problem is, here is the csv file I got from them

 

 

ID;Photo;Name;Reference;Category;Base price;Final price;Quantity;Status;

8;Diamond Double Dolphin Engagement Ring 25pts.;x12erd;Dolphin Engagement Rings;799.000000;799;3;1;

 

Both of these lines were in 1 column, (Heading- inventory item below)

this is not a csv file that I have seen, much less usuable.

i think in excel I could probably cut and paste each section into new columns, but before I do all that and then try to import isn't there a way to make the program export to a proper csv file?

 

Thank you,

Steven

Share this post


Link to post
Share on other sites
  • 0

Steven,

 

The sample you sent does not look like a PS product export. This came from a PS product export?

 

Ignoring that issue for the time being, you should be able to import that file into excel if that's what you are trying to do. I don't use excel so I can't give you the exact way to get the csv into excel. But you should be able to open excel and then either go through the file > open dialog and specify a CSV. OR find the data import (depends on version). It should open the file and put up a dialog box asking you what delimiter to use - you would specify the semicolon (;).

Share this post


Link to post
Share on other sites
  • 0

...had to take that back. You can use that CSV export Steven if you can decipher it.... probably the best thing to do is add a few products manually to set the template for the rest. This however does not work in my situation and I'm thinking you probably have the same issue. You should consider making that $39 purchase I posted.

Edited by KSteele

Share this post


Link to post
Share on other sites
  • 0

Steven,

 

The sample you sent does not look like a PS product export. This came from a PS product export?

 

Ignoring that issue for the time being, you should be able to import that file into excel if that's what you are trying to do. I don't use excel so I can't give you the exact way to get the csv into excel. But you should be able to open excel and then either go through the file > open dialog and specify a CSV. OR find the data import (depends on version). It should open the file and put up a dialog box asking you what delimiter to use - you would specify the semicolon ( ;).

 

.That is the file I get when I export from prestashop, I did it twice. I would expect the export function would have been a csv file, but this is what I get. I am worried that if I "fix" it up in excel and then try to import it, then it won't work.

Does any documentation from prestashop state what format their export function is supposed to be?

 

thank you,

Steven

Share this post


Link to post
Share on other sites
  • 0

Steven,

 

It looks like there is no way in a standard prestashop to export products in the same form that is required for import (WTF?)

 

The only option you have at this point is use the sql query available in the "advanced parameters" section of admin or write some PHP to get the data or buy an import/export module.

 

Sorry I couldn't help you more.

Share this post


Link to post
Share on other sites
  • 0

Steven,

 

It looks like there is no way in a standard prestashop to export products in the same form that is required for import (WTF?)

 

The only option you have at this point is use the sql query available in the "advanced parameters" section of admin or write some PHP to get the data or buy an import/export module.

 

Sorry I couldn't help you more.

 

 

Well getting a bad answer is better than no answer so thank you.

 

That being said, the only reason I did the export was to try to populate the fields so when I imported it would go to the proper place.

I tried playing with the import/export last night, and it looks like when you import you can "match the columns you want.

If that is the case can I just import my own csv file and match the fields I need in their respective columns?

 

Thanks again for your help.

 

Steven

Share this post


Link to post
Share on other sites
  • 0

Yet another problem with this function is that it does not "escape" characters. So if you have quotes in your descriptions, which I do, the file can't be imported properly.

 

Right now I'm going through all 450 products and taking all all the double-quotes. Fun, joy.

Share this post


Link to post
Share on other sites
  • 0

It appears that many are having problems opening the CSV exported from PS. Do not try to opens this file from Excel, instead import it.

 

Here is what I find that works best-

 

> Open a new Excel spreadsheet

> Select the Data tab and "Get External Data from Text"

> Import your CSV file

> On Step 1 of the Wizard - use the Delimited

> Step Two of the Wizard - select Semicolon only

> Step Three accept all defaults and select finish

 

I use a single " in the inches ( i.e. 8") and it imports fine, but if I have double "" ( i.e. "John" )it drops them.

 

When finished editing, do a "Save As" and select the CSV (MS-DOS) *.csv format

 

Hope this helps!

-MM

Edited by electric-2000

Share this post


Link to post
Share on other sites
  • 0

I want sample product combination csv file will all fields.

is there any?

I don't know if there's another way but the way I do it is to split that in two operations: the normal products import, with all fields that you need, then the combinations import to the previously imported products

Share this post


Link to post
Share on other sites
  • 0

Hi

 

i want to export Categories butr i get an error.

 

i using this one

 

select cl.id_category,
		   cl2.name as parent_name,
		   cl.name,
		   cl.description,
		   cl.link_rewrite,
		   concat('http://', ifnull(conf.value,'example.com'), '/img/c/', c.id_category, '.jpg') as url_image,
		   cl.id_lang
from ps_category c
inner join ps_category_lang cl on cl.id_category = c.id_category
inner join ps_category_lang cl2 on cl2.id_category = c.id_parent and cl2.id_lang = cl.id_lang
left join ps_configuration conf on conf.name = 'PS_SHOP_DOMAIN'
order by cl.id_lang, c.level_depth
and i get this error

 

 

The SQL works fine for me just pasting it into phpMyAdmin - So it must be that when you paste it in the return between the "c.id_category" and the "inner" is getting lost.

 

For the error The attribute "id_categoryINNER" does not exist in the table: ps_category that appears when trying to create the Categories only rule.

 

I had the same problem, and I see no-one answered it so here's my fix: simply remove the word INNER from INNER JOIN, so it's JOIN only. Leave the left join as is.

Edited by phew72

Share this post


Link to post
Share on other sites
  • 0

Hello,

 

I was having the exact same issue with the default product export within PS 1.5.4.1. i did a little research and found out that PS does offer the functionality we all want, it is just hidden, and it enables you to export any information within any table to CSV.

 

In the back office and go to advanced paraneters > SQL Manager.

 

Select Add New

 

4UCRgg2.jpg

 

Then Give the query a name, i called mine Products.

Then In the List of MySQL Tables: find the table you require info from, mine all have the default names so for product info I need ps_products.

 

When you select the table you require a new window will appear next to it listing each column from the table as an attribute. Select each of the attributes you require from the table and they will be added to the query.

 

PrestaShop will not add the statement, so to return the values of the columns just add SELECT at the beginning, and at the end add FROM ps_product you will also need to separate each column within the query with a comma as these are not automatically added.

 

bNDRA58.jpg

 

Then save the query and you can download the CSV, if you open in Excel make sure to open excel first, and them import data from text, and on the dialog select semi colon deliminator and it will all open up fine.

 

This is extremely useful!!

 

OMG !! RCP90 You saved me from pulling out my hair! LOL

I could not for the life of me figure out how to download ALL my categories including subs!

When I would export the categories via cvs export on the main categories page, I was only getting the parent categories, never any of the sub categories. (I am not SQL savy at ALL ! LOL )

You post was a life saver! It should be pinned somewhere :)

Thanks again RCP90!  You ROCK !

( I quoted your entire post so it's easy to find for me in the future!)

Share this post


Link to post
Share on other sites

×