Post by daledude » Wed Jun 29, 2011 2:18 am

Is it possible without seriously screwing everything else up to set defaults for some of the things in Export/Import within the model files, so that we can get rid of the columns in the spreadsheet?

I know you can take out the expected headers in the export file, and I've tried also removing some of the lines of code that are associated with the unwanted columns, but it results in messing up the database.

For example, on this OpenCart site I'm making, the manufacturer is always the same company, the one that will run this website. So there is no reason to have this manufacturer field in the Excel spreadsheet because it will never be anybody else, just lines and lines of the same company's name if you were to put it there, an empty, useless column taking up space if you don't. We also have no use for the Location, Quantity, Requires Shipping, Price, Date_Added/Modified/Available, Tax_Class_ID, Viewed, Stock_Status_ID, Cost, etc. columns. This is basically just going to be a catalog, no buying, no pricing, just for browsing. However their thousands of products can be controlled through an Excel database, so they want to be able to adjust the catalog via the spreadsheet. Their product is sold through third-parties. It would be nice to strip those out of the spreadsheet entirely, and have basic defaults built-in and hidden to satisfy OpenCart's need for some sort of value for those fields. I'm sure this is possible, but after tinkering I can't seem to figure it out.

Any suggestions?

New member

Posts

Joined
Thu Dec 17, 2009 1:30 am

Post by mattappleton » Tue Jul 05, 2011 8:27 pm

Hello, just downloaded this tool, i am receiving this error though when trying to import:

Fatal error: Uncaught exception 'Exception' with message 'Unknown codepage: 0' in /hermes/bosweb/web032/b322/my-domain/system/PHPExcel/Classes/PHPExcel/Shared/CodePage.php:91 Stack trace: #0 /hermes/bosweb/web032/b322/my-domain/system/PHPExcel/Classes/PHPExcel/Reader/Excel5.php(1042): PHPExcel_Shared_CodePage::NumberToName(0) #1 /hermes/bosweb/web032/b322/my-domain/system/PHPExcel/Classes/PHPExcel/Reader/Excel5.php(517): PHPExcel_Reader_Excel5->_readSummaryInformation() #2 /hermes/bosweb/web032/b322/my-domain/admin/model/tool/export.php(1481): PHPExcel_Reader_Excel5->load('/tmp/phpd0icZr') #3 /hermes/bosweb/web032/b322/my-domain/admin/controller/tool/export.php(13): ModelToolExport->upload('/tmp/phpd0icZr') #4 [internal function]: ControllerToolExport->index() #5 /hermes/bosweb/web032/b322/my-domain/system/engine/front.php(47): call_user_f in /hermes/bosweb/web032/b322/my-domain/system/PHPExcel/Classes/PHPExcel/Shared/CodePage.php on line 91
Fatal Error: Uncaught exception 'Exception' with message 'Unknown codepage: 0' in /hermes/bosweb/web032/b322/my-domain/system/PHPExcel/Classes/PHPExcel/Shared/CodePage.php:91 Stack trace: #0 /hermes/bosweb/web032/b322/my-domain/system/PHPExcel/Classes/PHPExcel/Reader/Excel5.php(1042): PHPExcel_Shared_CodePage::NumberToName(0) #1 /hermes/bosweb/web032/b322/my-domain/system/PHPExcel/Classes/PHPExcel/Reader/Excel5.php(517): PHPExcel_Reader_Excel5->_readSummaryInformation() #2 /hermes/bosweb/web032/b322/my-domain/admin/model/tool/export.php(1481): PHPExcel_Reader_Excel5->load('/tmp/phpd0icZr') #3 /hermes/bosweb/web032/b322/my-domain/admin/controller/tool/export.php(13): ModelToolExport->upload('/tmp/phpd0icZr') #4 [internal function]: ControllerToolExport->index() #5 /hermes/bosweb/web032/b322/my-domain/system/engine/front.php(47): call_user_f in /hermes/bosweb/web032/b322/my-domain/system/PHPExcel/Classes/PHPExcel/Shared/CodePage.php on line 91

How can i resolve this?

Thank you for any help in advance.

Newbie

Posts

Joined
Tue Jul 05, 2011 8:05 pm

Post by extigo » Wed Jul 06, 2011 1:48 pm

I am using your Export Import tool for quite a while now and I recently added a new tab at the products page. When I export the XLS file I do not get the Specs with it. But when I import the tool, the specs are gone! How is this possible and is there a way to fix it?

Op al uw computervragen een antwoord -- Extigo Computers
http://www.extigo.nl

Using OC 1.4.9.4


Active Member

Posts

Joined
Thu Dec 09, 2010 5:04 pm

Post by JNeuhoff » Fri Jul 08, 2011 6:23 pm

mattappleton wrote:Hello, just downloaded this tool, i am receiving this error though when trying to import:

How can i resolve this?

Thank you for any help in advance.
The XLS file you sent me does not work for Opencart 1.4.9, among others 2 worksheets for 'Specials' and 'Discounts'. If you are using an older version of Opencart, please upgrade to at least 1.4.9.x or 1.5.0.x, chances are your issues are already fixed for these later versions.

Override Engine * Integrated VQMod * Unused Images Manager * Instant Option Price Calculator * Number Option * Google Rich Snippets * Google Tag Manager * Export/Import Tool * SpamBot Buster * Survey Plus


User avatar
Expert Member

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by JNeuhoff » Fri Jul 08, 2011 6:24 pm

extigo wrote:I am using your Export Import tool for quite a while now and I recently added a new tab at the products page. When I export the XLS file I do not get the Specs with it. But when I import the tool, the specs are gone! How is this possible and is there a way to fix it?
You can't add worksheets or columns, it has to obey a specific format, otherwise it won't work.

Override Engine * Integrated VQMod * Unused Images Manager * Instant Option Price Calculator * Number Option * Google Rich Snippets * Google Tag Manager * Export/Import Tool * SpamBot Buster * Survey Plus


User avatar
Expert Member

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by v-v » Tue Jul 26, 2011 3:22 am

I have reached limit for import, around 750 products :-( Any suggestions? Thanks a lot for any
When I try to import more, some error occured (dont remember what specific).

v-v
Active Member

Posts

Joined
Wed Jun 16, 2010 10:09 pm

Post by ZAP » Tue Jul 26, 2011 7:33 pm

Arrgh!! Has anybody used this to add 'Options' to their products? I fill in the cells but when I import it back they dont show up, and even if I immediately re-export the fields that I edited in excel have been deleted but the ones I add via the web show up fine. Help??!

ZAP
Newbie

Posts

Joined
Sat Jul 16, 2011 1:19 am

Post by 333.is » Mon Aug 01, 2011 9:02 pm

1.5.1.1 support it? Where can I download it?
OK I found it ---> http://www.mhccorp.com/opencart/export-import-tool
thank you. :)

Newbie

Posts

Joined
Mon Aug 01, 2011 8:36 pm

Post by 333.is » Tue Aug 02, 2011 10:02 am

Hello, JNeuhoff
How Chinese language pack product data export / import?
Such as "category_description" , "product_description" and more export / import?

Newbie

Posts

Joined
Mon Aug 01, 2011 8:36 pm

Post by mommaroodles » Tue Aug 02, 2011 8:46 pm

First of all, I'd like to thank JNeuhoff for creating this module - obviously it has helped quite a number of people so for those persons - the module can and does work.

My experience using the module is as follows:

First of all - I'm using OC version 1.4.9.5. The module was installed successfully and the settings in the php.ini is as follows.

memory_limit = 512M;
post_max_size = 30M;
upload_max_filesize = 999M;

Firstly I exported the file to see the file layout etc. We have 24 416 products - and still much more to come. Now what I didnt know and it does not seem to be documented in the Read.me file - is the fact that you CANNOT just import your new products, it has to be appended to the existing export file. If you have as many products as I do - the import process takes forever despite the fact that the limits have been increased in the php.ini file.

Now what has happened to me is - being unaware of the fact that you have to append your new products to the existing export file - I just added the new products and imported them - this resulted in all my categories (over 300 of them) and all the products being deleted. The only thing that imported successfully was the new category the actual products did not import! I've been trying to import the original export file that lists the 24 416 products and its been more than 30 minutes and it still has not imported which leaves me with the question of how I'm gonna get all those products back again.

In addition to the above - I visited your website and sent an email inquiring whether it was possible to have the module allow me to select which categories I want to export or import, which attributes I want to export or export for eg image only, product pricing only, product description only or ALL and etc. - to this I have not received a reply - I am more than willing to pay for this!

Ideally the module would work a lot better if one was able to just import the new products without it deleting all the categories and products in the database, and not have to append the new products to an existing file. In time that file obviously will become larger and the export/import process will be subject to potential problems which when a person gets to that amount of products in your store - that's exactly the time when one does not need problems with the import or export. In my case, the module cannot import the original export file and as a result I've lost over 300 categories and 24 416 products.!!!

User avatar
Newbie

Posts

Joined
Fri Jun 10, 2011 2:51 am
Location - South Africa

Post by JNeuhoff » Tue Aug 02, 2011 10:20 pm

It is actually documented in the 'readme.txt' file that the Import can be quite memory hungry.

Adding an incremental Import where products are added and/or replaced is on our to-do list, quite a few users have asked for this feature.

Override Engine * Integrated VQMod * Unused Images Manager * Instant Option Price Calculator * Number Option * Google Rich Snippets * Google Tag Manager * Export/Import Tool * SpamBot Buster * Survey Plus


User avatar
Expert Member

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by funt » Fri Aug 05, 2011 3:47 pm

Dear JNeuhoff,

i sent you a PM but i dont know for sure if this message has been sent.

I used your module with success on OC 1.4.9 and now i have installed it on OC 1.5.1.1. When i want to export i get this warning:

Warning: set_time_limit() [function.set-time-limit]: Cannot set time limit in safe mode in /public/sites/www.stoelenco.nl/login258_sc/controller/tool/export.php on line 76

I have only 1 item in the shop at this moment so thats not an issue i think.

Thanx a lot for your answer.

John

User avatar
New member

Posts

Joined
Thu Feb 03, 2011 10:23 pm

Post by funt » Fri Aug 05, 2011 6:56 pm

funt wrote:Dear JNeuhoff,

i sent you a PM but i dont know for sure if this message has been sent.

I used your module with success on OC 1.4.9 and now i have installed it on OC 1.5.1.1. When i want to export i get this warning:

Warning: set_time_limit() [function.set-time-limit]: Cannot set time limit in safe mode in /public/sites/www.stoelenco.nl/login258_sc/controller/tool/export.php on line 76

I have only 1 item in the shop at this moment so thats not an issue i think.

Thanx a lot for your answer.

John
By comment out this rule it works perfect! Thanx for your quick anwer JNeuhoff.

User avatar
New member

Posts

Joined
Thu Feb 03, 2011 10:23 pm

Post by daledude » Tue Aug 16, 2011 3:52 am

I'm trying to get the Custom Product Image Titles mod (http://www.opencart.com/index.php?route ... on_id=1243), which allows you to create custom alt text and title text for additional images, to work with the Export/Import tool... I have tried to contact the maker of Custom Product Image Titles after paying for their mod but can't get any help.

Here's the model code modifications... this only reads the changes, which are manually added in the backend through the modified product_form.tpl, I'm trying to figure out how to re-work this so that it instead posts the changes from the export.php-generated spreadsheet?

Code: Select all

admin/model/catalog/product.php
// Change lines (starting around line 12, in addProduct function)
		foreach ($data['product_description'] as $language_id => $value) {
			$this->db->query("INSERT INTO " . DB_PREFIX . "product_description SET product_id = '" . (int)$product_id . "', language_id = '" . (int)$language_id . "', name = '" . $this->db->escape($value['name']) . "', meta_keywords = '" . $this->db->escape($value['meta_keywords']) . "', meta_description = '" . $this->db->escape($value['meta_description']) . "', description = '" . $this->db->escape($value['description']) . "'");
		}
// to
		foreach ($data['product_description'] as $language_id => $value) {
			$this->db->query("INSERT INTO " . DB_PREFIX . "product_description SET product_id = '" . (int)$product_id . "', language_id = '" . (int)$language_id . "', name = '" . $this->db->escape($value['name']) . "', meta_keywords = '" . $this->db->escape($value['meta_keywords']) . "', meta_description = '" . $this->db->escape($value['meta_description']) . "', description = '" . $this->db->escape($value['description']) . "', alt_text = '" . $this->db->escape($value['alt_text']) . "', title_text = '" . $this->db->escape($value['title_text']) . "'");
		}

// Change lines (starting around line 59, in addProduct function)
			foreach ($data['product_image'] as $image) {
        		$this->db->query("INSERT INTO " . DB_PREFIX . "product_image SET product_id = '" . (int)$product_id . "', image = '" . $this->db->escape($image) . "'");
			}
// to
			foreach ($data['product_image'] as $image) {
        		$this->db->query("INSERT INTO " . DB_PREFIX . "product_image SET product_id = '" . (int)$product_id . "', image = '" . $this->db->escape($image['image']) . "', sort_order = '" . (int)$image['sort_order'] . "'");

                $product_image_id = $this->db->getLastId();

				foreach ($image['language'] as $language_id => $language) {
					$this->db->query("INSERT INTO " . DB_PREFIX . "product_image_description SET product_image_id = '" . (int)$product_image_id . "', language_id = '" . (int)$language_id . "', product_id = '" . (int)$product_id . "', alt_text = '" . $this->db->escape($language['alt_text']) . "', title_text = '" . $this->db->escape($language['title_text']) . "'");
				}
			}

// Change lines (starting around line 113, in editProduct function)
		foreach ($data['product_description'] as $language_id => $value) {
			$this->db->query("INSERT INTO " . DB_PREFIX . "product_description SET product_id = '" . (int)$product_id . "', language_id = '" . (int)$language_id . "', name = '" . $this->db->escape($value['name']) . "', meta_keywords = '" . $this->db->escape($value['meta_keywords']) . "', meta_description = '" . $this->db->escape($value['meta_description']) . "', description = '" . $this->db->escape($value['description']) . "'");
		}
// to
		foreach ($data['product_description'] as $language_id => $value) {
			$this->db->query("INSERT INTO " . DB_PREFIX . "product_description SET product_id = '" . (int)$product_id . "', language_id = '" . (int)$language_id . "', name = '" . $this->db->escape($value['name']) . "', meta_keywords = '" . $this->db->escape($value['meta_keywords']) . "', meta_description = '" . $this->db->escape($value['meta_description']) . "', description = '" . $this->db->escape($value['description']) . "', alt_text = '" . $this->db->escape($value['alt_text']) . "', title_text = '" . $this->db->escape($value['title_text']) . "'");
		}

// Change lines (starting around line 170, in editProduct function)
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_image WHERE product_id = '" . (int)$product_id . "'");

		if (isset($data['product_image'])) {
			foreach ($data['product_image'] as $image) {
        		$this->db->query("INSERT INTO " . DB_PREFIX . "product_image SET product_id = '" . (int)$product_id . "', image = '" . $this->db->escape($image) . "'");
			}
		}
// to
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_image WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_image_description WHERE product_id = '" . (int)$product_id . "'");

		if (isset($data['product_image'])) {
			foreach ($data['product_image'] as $image) {
        		$this->db->query("INSERT INTO " . DB_PREFIX . "product_image SET product_id = '" . (int)$product_id . "', image = '" . $this->db->escape($image['image']) . "', sort_order = '" . (int)$image['sort_order'] . "'");

                $product_image_id = $this->db->getLastId();

				foreach ($image['language'] as $language_id => $language) {
					$this->db->query("INSERT INTO " . DB_PREFIX . "product_image_description SET product_image_id = '" . (int)$product_image_id . "', language_id = '" . (int)$language_id . "', product_id = '" . (int)$product_id . "', alt_text = '" . $this->db->escape($language['alt_text']) . "', title_text = '" . $this->db->escape($language['title_text']) . "'");
				}
			}
		}

// Change lines (starting around line 252, in copyProduct function)
			foreach ($results as $result) {
				$data['product_image'][] = $result['image'];
			}
// to
			foreach ($results as $result) {
                $descriptions = $this->getProductImageDescriptions($result['product_image_id']);

				$data['product_image'][] = array(
                    'image'     => $result['image'],
                    'sort_order'=> $result['sort_order'],
                    'language'  => $descriptions
                );
			}

// Add after (around line 282, in deleteProduct function)
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_image WHERE product_id = '" . (int)$product_id . "'");
// line
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_image_description WHERE product_id = '" . (int)$product_id . "'");

// Change lines (starting around line 413, function getProductDescriptions)
	public function getProductDescriptions($product_id) {
		$product_description_data = array();

		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_description WHERE product_id = '" . (int)$product_id . "'");

		foreach ($query->rows as $result) {
			$product_description_data[$result['language_id']] = array(
				'name'             => $result['name'],
				'meta_keywords'    => $result['meta_keywords'],
				'meta_description' => $result['meta_description'],
				'description'      => $result['description']
			);
		}

		return $product_description_data;
	}
// to
	public function getProductDescriptions($product_id) {
		$product_description_data = array();

		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_description WHERE product_id = '" . (int)$product_id . "'");

		foreach ($query->rows as $result) {
			$product_description_data[$result['language_id']] = array(
				'name'             => $result['name'],
				'meta_keywords'    => $result['meta_keywords'],
				'meta_description' => $result['meta_description'],
				'description'      => $result['description'],
				'alt_text'         => $result['alt_text'],
				'title_text'       => $result['title_text']
			);
		}

		return $product_description_data;
	}

// Change (around line 482, in getProductImages function)
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_image WHERE product_id = '" . (int)$product_id . "'");
// to
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_image WHERE product_id = '" . (int)$product_id . "' ORDER BY sort_order ASC");

// Add after (around line 500, after getProductImages function) lines

	public function getProductImageDescriptions($product_image_id) {
		$product_image_description_data = array();

		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_image_description WHERE product_image_id = '" . (int)$product_image_id . "'");

		foreach ($query->rows as $result) {
			$product_image_description_data[$result['language_id']] = array(
				'alt_text'      => $result['alt_text'],
				'title_text'    => $result['title_text']
			);
		}

		return $product_image_description_data;
	}
I'm guessing I could create two columns in the Excel, alt_text and title_text, and somehow rework this code to work. But I've been fiddling with it for weeks and am not coming up with anything workable... argh Any help/ideas?

New member

Posts

Joined
Thu Dec 17, 2009 1:30 am

Post by deejunit » Fri Sep 30, 2011 4:44 am

mattappleton wrote:Hello, just downloaded this tool, i am receiving this error though when trying to import:

Fatal error: Uncaught exception 'Exception' with message 'Unknown codepage: 0' in /hermes/bosweb/web032/b322/my-domain/system/PHPExcel/Classes/PHPExcel/Shared/CodePage.php:91 Stack trace: #0 /hermes/bosweb/web032/b322/my-domain/system/PHPExcel/Classes/PHPExcel/Reader/Excel5.php(1042): PHPExcel_Shared_CodePage::NumberToName(0) #1 /hermes/bosweb/web032/b322/my-domain/system/PHPExcel/Classes/PHPExcel/Reader/Excel5.php(517): PHPExcel_Reader_Excel5->_readSummaryInformation() #2 /hermes/bosweb/web032/b322/my-domain/admin/model/tool/export.php(1481): PHPExcel_Reader_Excel5->load('/tmp/phpd0icZr') #3 /hermes/bosweb/web032/b322/my-domain/admin/controller/tool/export.php(13): ModelToolExport->upload('/tmp/phpd0icZr') #4 [internal function]: ControllerToolExport->index() #5 /hermes/bosweb/web032/b322/my-domain/system/engine/front.php(47): call_user_f in /hermes/bosweb/web032/b322/my-domain/system/PHPExcel/Classes/PHPExcel/Shared/CodePage.php on line 91
Fatal Error: Uncaught exception 'Exception' with message 'Unknown codepage: 0' in /hermes/bosweb/web032/b322/my-domain/system/PHPExcel/Classes/PHPExcel/Shared/CodePage.php:91 Stack trace: #0 /hermes/bosweb/web032/b322/my-domain/system/PHPExcel/Classes/PHPExcel/Reader/Excel5.php(1042): PHPExcel_Shared_CodePage::NumberToName(0) #1 /hermes/bosweb/web032/b322/my-domain/system/PHPExcel/Classes/PHPExcel/Reader/Excel5.php(517): PHPExcel_Reader_Excel5->_readSummaryInformation() #2 /hermes/bosweb/web032/b322/my-domain/admin/model/tool/export.php(1481): PHPExcel_Reader_Excel5->load('/tmp/phpd0icZr') #3 /hermes/bosweb/web032/b322/my-domain/admin/controller/tool/export.php(13): ModelToolExport->upload('/tmp/phpd0icZr') #4 [internal function]: ControllerToolExport->index() #5 /hermes/bosweb/web032/b322/my-domain/system/engine/front.php(47): call_user_f in /hermes/bosweb/web032/b322/my-domain/system/PHPExcel/Classes/PHPExcel/Shared/CodePage.php on line 91

How can i resolve this?

Thank you for any help in advance.
I get the exact same error. Is there a solution?

New member

Posts

Joined
Sat Jul 21, 2007 5:40 am

Post by deejunit » Fri Sep 30, 2011 5:00 am

deejunit wrote: I get the exact same error. Is there a solution?
Never mind. Found a fix that solved it ;) https://gist.github.com/762095

New member

Posts

Joined
Sat Jul 21, 2007 5:40 am

Post by panvulcon » Tue Oct 04, 2011 1:11 am

For all who receive 'error - 126 incorrect key file for table'.

This is a mysql error which usually referrences a temporary table and is caused by one of the querys trying to write a massive temporary table. In my case the query tried to write a 2GB table!!! :choke:

For me this was a result of having thousands of categories but then isnt this the point of the module?! You wouldnt need such a module if you were only adding a few items in one or two categories.

Can anybody recommend a simular module that parses on the fly?

Andy

Newbie

Posts

Joined
Mon Apr 25, 2011 2:15 am

Post by smoothie » Mon Oct 24, 2011 11:22 pm

Hi,

Just looking for a bit of advice on the import / export tool. I have 13000 products to import which naturally with a large number of products like this trying to import the data in one go is not a viable option or advised. What I have attempted to do was import the products in blocks of 500 products at a time. Whilst doing this I am having the following issues

I upload the first 500 products and check the admin to ensure they are there along with images and all the relevant data. These products have copied over correctly without any issues.
Now when I import the next 500 products this overwrites the first 500 products. No matter what I do I have the same problem every time I attempt to import the products it over the existing products in my store.

does anyone have any idea of what I am doing wrong or how to over come this issue

thanks in advance

Newbie

Posts

Joined
Mon Oct 24, 2011 10:58 pm

Post by speedimpex » Sat Nov 05, 2011 8:36 pm

Can someone help please. Trying to import the spreadsheet in import/export module and we get the following error message: -
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 'sSciencePlanFelineMatureAdult7ActiveLongevityChicken2kg.jpg',1,12.49,'2011-07-12' at line 1
Error No: 1064

I saw a post that said to change the $imageName to something other than = $product[7]; but I don't know if this is right for this error.

We had over 800 products on our website, and now after trying the import and getting this message, there are only 244 now showing.

Please help. We're already live.

Many thanks

Gill

New member

Posts

Joined
Tue Jul 12, 2011 2:17 am
Who is online

Users browsing this forum: No registered users and 2 guests