It's strange, I hadn't seen any other errors posted as I keep trying the import. I wonder if it's some kind of problem reading a certain character type and then it fails. It sure would be nice if the import function would append to the existing database, or at least give the option of replacing or appending the data.
I originally had entered about 400 product to my site manually.
I exported this file and used my suppliers list to create the new file.
I made a .xls file of 18mb with just under 40,000 products.
However it would fail to import every time. No errors, just looks like its uploading then the browser says done.
But no new prods in my store. I have checked my php config and it's only got 64M memory_limit so I am suspecting this may be the issue here. My file works fine when I chop it down to 100 prods ( so its not my file)
Anyway I decided to restore my original file (400 prods) but upon import I kept getting an error.
Error: 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 's Sweetie.jpg',1,39.99,'2010-08-12 18:20:26','0000-00-00 00:00:00','2010-08-11',' at line 1
Error No: 1064
SO I guessed that it has a problem importing image names with a ' (apostrophe) in them. I had to delete every image name with a ' in it and I got it back up.
Please is there a fix for both of these?? or do I have to resort to asking my host for an increase in memory limit and not using image names with ' in them??
I exported this file and used my suppliers list to create the new file.
I made a .xls file of 18mb with just under 40,000 products.
However it would fail to import every time. No errors, just looks like its uploading then the browser says done.
But no new prods in my store. I have checked my php config and it's only got 64M memory_limit so I am suspecting this may be the issue here. My file works fine when I chop it down to 100 prods ( so its not my file)
Anyway I decided to restore my original file (400 prods) but upon import I kept getting an error.
Error: 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 's Sweetie.jpg',1,39.99,'2010-08-12 18:20:26','0000-00-00 00:00:00','2010-08-11',' at line 1
Error No: 1064
SO I guessed that it has a problem importing image names with a ' (apostrophe) in them. I had to delete every image name with a ' in it and I got it back up.
Please is there a fix for both of these?? or do I have to resort to asking my host for an increase in memory limit and not using image names with ' in them??
most shared sites won't support a php file upload that large. Additionally, reading that much data at once will likely timeout the processor as well. I was thinking that perhaps a better method to get around the php upload issue would be for JNeuhoff to support FTP uploading of the xls file, then read the file from the server directly. But that may or may not fix the issue with running out of allowed memory for processing the file.
well I don't think it would affect the memory one way or the other since php has to load the entire file before processing it when you are using excel.
OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter
Xsecrets is right.
It would be possible to write up an Import tool which uses CSV files, much lilke Zencart, but in that case we might as well use PHPMyAdmin which, as far as I know, allows the import of individual DB tables from CSV files, too.
It would be possible to write up an Import tool which uses CSV files, much lilke Zencart, but in that case we might as well use PHPMyAdmin which, as far as I know, allows the import of individual DB tables from CSV files, too.
Export/Import Tool * SpamBot Buster * Unused Images Manager * Instant Option Price Calculator * Number Option * Google Tag Manager * Survey Plus * OpenTwig
If the php.ini mods didn't work try this line in your .htaccess file
RLimitMem 128000000
Hope it helps.
RLimitMem 128000000
Hope it helps.
I had to upload about 4000 items. I made SQL query in excel file, replaced all ' with \' , " with \" , ; with \; and started uploading. I uploaded about 700 rows to table 'product' (because it has many columns) at a time, and about 1000 rows to 'product_description' at a time. Don't forget to import product_to_category and product_to_store tables. There is 3 languages at my store, so I got about 12000 rows in product_description table.
Now everything is working
When I open main page or category first time, it is quite slow. So I am going to optimize queries, then make 3 different product_description tables for each language and then make cashing for main and category pages with about 2 hours of live time.
Now everything is working
When I open main page or category first time, it is quite slow. So I am going to optimize queries, then make 3 different product_description tables for each language and then make cashing for main and category pages with about 2 hours of live time.if you figure out a way to optimize the queries better let us know. Also the categories query is already cached if you are on 1.4.9 or later.biz0wn wrote: Now everything is working
OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter
I have imported 11,000+ products from zencart without any issue. Ofcourse I exported a SQL script from zencart and then ran the script using bigdump. This nice utility and is much fater than phpmyadmin.
Surely there are some performance issue when you load lot of products in OC. I see that five times slower with my 11,000+ products compared to the demo content.
Surely there are some performance issue when you load lot of products in OC. I see that five times slower with my 11,000+ products compared to the demo content.
Extensions: Multi-vendor extension
Shipping Modules: SuperShip Pro
Multi-Vendor Marketplace:Opencartmarketplace.com
IceCat to Opencart: Import millions of IceCat products to Opencart
well concidering you have 1000 times more products I don't think a five times decrease is unreasonable.cartpro wrote: Surely there are some performance issue when you load lot of products in OC. I see that five times slower with my 11,000+ products compared to the demo content.
OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter
Well, if it performs same as other carts, then what is the real differentiator other than MVC model?
I see many places OC uses distinct which should be removed. Also, I think the caching should be moved to the controller level and cache the rendered html output rather than db query result. Isn't it reading from the disk is slower than reading from DB?
I see many places OC uses distinct which should be removed. Also, I think the caching should be moved to the controller level and cache the rendered html output rather than db query result. Isn't it reading from the disk is slower than reading from DB?
Extensions: Multi-vendor extension
Shipping Modules: SuperShip Pro
Multi-Vendor Marketplace:Opencartmarketplace.com
IceCat to Opencart: Import millions of IceCat products to Opencart
I don't think you understand what DISTINCT does it's there for a reason. unless you want your products to show up multiple times for no reason. No reading from the db is not faster than reading from the hard drive. That's the whole point of caching. Yes of course you can cache in ram which is the fastest, but not really feasible particularly on very large sites.cartpro wrote:Well, if it performs same as other carts, then what is the real differentiator other than MVC model?
I see many places OC uses distinct which should be removed. Also, I think the caching should be moved to the controller level and cache the rendered html output rather than db query result. Isn't it reading from the disk is slower than reading from DB?
OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter
I understand what DISTINCT means and it's last thing I use in my sql query after exploring other choices. My question is do we really need DISTINCT when you select a product by id as the one in catalog/model/catalog/product.php?
Hey, I am no expect in MySQL but in Micrsoft SQL this is the worst performing query that too when you use DISTINCT *.
Also, I saw increase in speed when I remove the DISTINCT in Zencart.
Hey, I am no expect in MySQL but in Micrsoft SQL this is the worst performing query that too when you use DISTINCT *.
Also, I saw increase in speed when I remove the DISTINCT in Zencart.
Code: Select all
public function getProduct($product_id) {
$query = $this->db->query("SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, ss.name AS stock FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.date_available <= NOW() AND p.status = '1'");
return $query->row;
}
Extensions: Multi-vendor extension
Shipping Modules: SuperShip Pro
Multi-Vendor Marketplace:Opencartmarketplace.com
IceCat to Opencart: Import millions of IceCat products to Opencart
you'll find very few developers who use distinct when it's not needed. I would have to take some time to look through all those joins, but I'm fairly certain that you could end up with duplicate data otherwise I highly doubt that distinct would have been added. Sometimes distinct is simply the best way to get what you want.
OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter
Who is online
Users browsing this forum: No registered users and 14 guests
