Post by smorelli » Tue May 25, 2010 10:21 pm

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.

New member

Posts

Joined
Wed Jan 27, 2010 6:28 am

Post by stevenator » Wed Aug 25, 2010 9:18 pm

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??

Newbie

Posts

Joined
Wed Aug 25, 2010 8:48 pm

Post by Qphoria » Wed Aug 25, 2010 10:28 pm

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.

Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by Xsecrets » Thu Aug 26, 2010 12:34 am

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


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by JNeuhoff » Thu Aug 26, 2010 12:47 am

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.

Export/Import Tool * SpamBot Buster * Unused Images Manager * Instant Option Price Calculator * Number Option * Google Tag Manager * Survey Plus * OpenTwig


User avatar
Guru Member

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by MisterBooth » Mon Sep 20, 2010 11:16 pm

If the php.ini mods didn't work try this line in your .htaccess file

RLimitMem 128000000

Hope it helps.

Newbie

Posts

Joined
Mon Sep 20, 2010 11:12 pm

Post by biz0wn » Sun Nov 14, 2010 6:32 pm

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.

Newbie

Posts

Joined
Sun Nov 14, 2010 5:57 pm

Post by Xsecrets » Sun Nov 14, 2010 10:11 pm

biz0wn wrote: 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.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by cartpro » Sun Nov 14, 2010 10:36 pm

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.

Extensions: Multi-vendor extension
Shipping Modules: SuperShip Pro

Multi-Vendor Marketplace:Opencartmarketplace.com
IceCat to Opencart: Import millions of IceCat products to Opencart


New member

Posts

Joined
Fri Oct 29, 2010 1:50 am

Post by Xsecrets » Mon Nov 15, 2010 12:08 am

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.
well concidering you have 1000 times more products I don't think a five times decrease is unreasonable.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by cartpro » Mon Nov 15, 2010 3:52 am

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?

Extensions: Multi-vendor extension
Shipping Modules: SuperShip Pro

Multi-Vendor Marketplace:Opencartmarketplace.com
IceCat to Opencart: Import millions of IceCat products to Opencart


New member

Posts

Joined
Fri Oct 29, 2010 1:50 am

Post by Xsecrets » Mon Nov 15, 2010 5:48 am

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?
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.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by cartpro » Mon Nov 15, 2010 8:10 am

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.

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


New member

Posts

Joined
Fri Oct 29, 2010 1:50 am

Post by Xsecrets » Mon Nov 15, 2010 8:52 am

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


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US
Who is online

Users browsing this forum: No registered users and 16 guests