Post by JNeuhoff » Sat Aug 06, 2011 12:07 am

Jojon wrote:This does not work for v.1.5.1.1
there are no any results from the export & Import
Uploaded file is not a valid spreadsheet file or its values ​​are not in the expected formats!
all my server and hosting runs with good accordance with the standards, but what happens in this module?
there are no any results from the export & Import
even though I only have 5 products, but why, none of these products are not included in the xls file,I appreciate the hard work, good job
You are reporting conflicting details here.
Uploaded file is not a valid spreadsheet file or its values ​​are not in the expected formats!
This refers to a failed Import. Have you checked your System > Error Log?
none of these products are not included in the xls file
Need more details, otherwise I am unable to reproduce and fix this error.

MHC Web Design
Override Engine * Integrated VQMod * Unused Images Manager * Instant Option Price Calculator * TrustPilot Reviews * Google Rich Snippets * Google Tag Manager * Export/Import Tool * Template Switcher PHP/Twig


User avatar
Expert Member

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by BDW » Wed Sep 21, 2011 7:50 pm

In searching this forum, while learning/installing OC (1.5.1) over the last week..., I noticed this was a common problem I was also experiencing. It appears some users never got a resolution. The solution was touched on in other posts:

(if using newer versions of excel)
"you must 'save as' xls (i.e. excel '97-2003 compatible).

Ok. so I did that, and it worked - once. So I changed something, hit save (since I was already in 'compatibility mode') - oops - WRONG...

You must use 'save as' as above (excel '97-2003 compatible) EVERY time you save your changes. Otherwise you will have an .xlsx file with a .xls extension...

This may be seem obvious (it is to me now...) but it sure had me baffled for a while...

...Now if only someone has a "Amazon (seller's inventory) import template" to use with this import/export tool, it will sure save me from writing a bunch of macroes in Excel or something... An alternative would be to import the data 'as is' then run an SQL script to massage the data into something more meaningful for OC (like changing the condition code numbers to the correct words/phrase, parsing the SKU to set category, mfg, etc...)

PS. I'm up and running on a Godaddy server (don't know why GoDaddy has such a bad rep...lol) - only had to make one godaddy change - I found all my answers here - Thanks, All!

BDW
Newbie

Posts

Joined
Wed Sep 21, 2011 7:17 pm

Post by frenzzz4eva » Thu Sep 22, 2011 12:47 pm

Hi I have installed the "export/import tool". Now when I try importing the xls sheet, the browser does show that the signs of % upload but when the entire process is 100% complete no confirmation is shown and no product is imported.

Please help in determining the problem.

Newbie

Posts

Joined
Thu Sep 22, 2011 12:44 pm

Post by JNeuhoff » Fri Sep 23, 2011 12:59 am

frenzzz4eva wrote:Hi I have installed the "export/import tool". Now when I try importing the xls sheet, the browser does show that the signs of % upload but when the entire process is 100% complete no confirmation is shown and no product is imported.

Please help in determining the problem.
Look into your server's error_log or opencart's system/logs/error.txt.
Your issue may also be due to your PHP settings, see the readme.txt for an example of the important PHP settings needed for it, e.g. the memory_limitisone of them.

MHC Web Design
Override Engine * Integrated VQMod * Unused Images Manager * Instant Option Price Calculator * TrustPilot Reviews * Google Rich Snippets * Google Tag Manager * Export/Import Tool * Template Switcher PHP/Twig


User avatar
Expert Member

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by frenzzz4eva » Fri Sep 23, 2011 2:18 am

I analyzed the problem and it seems to be the problem with the product description. There is some issue with the text being added as description. I am trying to enter following text in the description: Now if I upload the file without this text then it works fine but the moment I enter following text the data is not uploaded.


<p>
Nitro-Tech<sup>®</sup> Hardcore Pro Series is a Scientifically Advanced Musclebuilding Protein Formula. It is backed by research and development. Nitro-Tech<sup>®</sup> Hardcore Pro Series formula was ingeniously formulated to build upon the muscle and strength-building properties of whey protein – making it a truly technologically advanced musclebuilding whey protein formulation.</p>
<h2>
Description</h2>
<ol>
<li>
Protein Blend (Whey Protein Concentrate, Partially Hydrolysed Whey protein Concentrate, Hyper-Pure Whey Protein Isolate</li>
<li>
Each serving delivers 11 g of leucine and 13 g of additional BCAA&nbsp;</li>
<li>
Gain 143% more muscle strength than using whey protein</li>
<li>
&nbsp;</li>
</ol>
<p>
Mix 2 servings (2 scoops) of <em>Nitro-Tech Hardcore Pro Series</em> with at least 8 oz. (approximately 250 ml) of cold water or skim milk and drink. Taking this product without enough liquid may cause choking. For maximum results consume 2 servings of <em>Nitro-Tech Hardcore Pro Series</em> 3 times daily for 6 weeks</p>
<p>
&nbsp;</p>
<p>
<strong>Serving size &nbsp;1 scoop (33g)</strong></p>
<p>
<strong><span style="font-family: arial,helvetica,sans-serif;">Serving per container 54 (4 lb pack)</span></strong></p>
<table border="1" cellpadding="1" cellspacing="1" style="width: 500px;">
<tbody>
<tr>
<td>
Amount per serving</td>
<td style="text-align: justify;">
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;Units &nbsp;</td>
<td style="text-align: justify;">
&nbsp; &nbsp; &nbsp;DV%</td>
</tr>
<tr>
<td style="text-align: justify;">
Calories (cal)</td>
<td style="text-align: justify;">
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 130</td>
<td style="text-align: left;">
&nbsp; &nbsp; &nbsp; &nbsp;-</td>
</tr>
<tr>
<td style="text-align: justify;">
Calories from Fat&nbsp;</td>
<td style="text-align: justify;">
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15</td>
<td style="text-align: justify;">
&nbsp; &nbsp; &nbsp; -</td>
</tr>
<tr>
<td style="text-align: justify;">
Fat(g)</td>
<td style="text-align: justify;">
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1.5</td>
<td style="text-align: justify;">
&nbsp; &nbsp; &nbsp; &nbsp;2</td>
</tr>
<tr>
<td style="text-align: justify;">
Saturated fat(g)</td>
<td style="text-align: justify;">
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1</td>
<td style="text-align: justify;">
&nbsp; &nbsp; &nbsp; &nbsp;5</td>
</tr>
<tr>
<td style="text-align: justify;">
Cholesterol(mg)</td>
<td style="text-align: justify;">
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;65</td>
<td style="text-align: justify;">
&nbsp; &nbsp; &nbsp; 22</td>
</tr>
<tr>
<td style="text-align: justify;">
Potassium(mg) &nbsp;</td>
<td style="text-align: justify;">
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8</td>
<td style="text-align: justify;">
&nbsp; &nbsp; &nbsp; &nbsp;2</td>
</tr>
<tr>
<td style="text-align: justify;">
Sodium(mg)</td>
<td style="text-align: justify;">
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;70</td>
<td style="text-align: justify;">
&nbsp; &nbsp; &nbsp; &nbsp;3</td>
</tr>
<tr>
<td style="text-align: justify;">
Protein (g)</td>
<td style="text-align: justify;">
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;28</td>
<td style="text-align: justify;">
&nbsp; &nbsp; &nbsp; 50</td>
</tr>
<tr>
<td style="text-align: justify;">
Sugar(g)</td>
<td style="text-align: justify;">
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3</td>
<td style="text-align: justify;">
&nbsp; &nbsp; &nbsp; &nbsp;-</td>
</tr>
<tr>
<td style="text-align: justify;">
Calcium(mg)</td>
<td style="text-align: justify;">
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;90</td>
<td style="text-align: justify;">
&nbsp; &nbsp; &nbsp; &nbsp;9</td>
</tr>
<tr>
<td style="text-align: justify;">
Vitamin B12 &nbsp;</td>
<td style="text-align: justify;">
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.4</td>
<td style="text-align: justify;">
&nbsp; &nbsp; &nbsp; &nbsp;7</td>
</tr>
</tbody>
</table>
<p>
<strong>Contains Wheat, Soy and Milk</strong></p>

Newbie

Posts

Joined
Thu Sep 22, 2011 12:44 pm

Post by victor.gatto » Fri Sep 23, 2011 11:17 am

if your uploading and you get an error, or, it says its uploaded and it doesnt appear, theres a good chance your imported spreadsheet has the wrong headings or values...


try creating categories and place products in the categories, then, export using the export import tool, open the xls file and have a look at how its formatted, there are tabs down the bottom aswell that show products etc, make sure the data is correct before you upload. please TRIPLE CHECK, as it is hard, or use any features you can (e.g find and replace) to assist you.

Active Member

Posts

Joined
Thu Sep 01, 2011 9:14 am

Post by frenzzz4eva » Mon Sep 26, 2011 1:07 am

There has been no luck on this for me... I am facing the following issues:

1. the file doesnt work on MS excel, I have to use openoffice calc
2. I am copying the data from xls (97-compatible) in the Openoffice calc and then uploading the data
3. Now the entire process works fine if I am copying the data up till 3 records, but copying more than 3records and then try uploading the file results in "NO RECORDS UPLOADED"
4. The other problem I am facing is when I upload product descriptions. I have to upload the products without product descriptions. Then go to admin module and copy 1 by 1 each description. But the problem doesnt end here.. Guess what when I export the xls file its of no use. As the file cannot be uploaded again..

This is not working at all.... Can anyone suggest how will this thing work....

Newbie

Posts

Joined
Thu Sep 22, 2011 12:44 pm

Post by JNeuhoff » Mon Sep 26, 2011 2:22 am

frenzzz4eva wrote:There has been no luck on this for me... I am facing the following issues:

1. the file doesnt work on MS excel, I have to use openoffice calc
2. I am copying the data from xls (97-compatible) in the Openoffice calc and then uploading the data
3. Now the entire process works fine if I am copying the data up till 3 records, but copying more than 3records and then try uploading the file results in "NO RECORDS UPLOADED"
4. The other problem I am facing is when I upload product descriptions. I have to upload the products without product descriptions. Then go to admin module and copy 1 by 1 each description. But the problem doesnt end here.. Guess what when I export the xls file its of no use. As the file cannot be uploaded again..

This is not working at all.... Can anyone suggest how will this thing work....
Send me a PM with your XLS file, otherwise I am not able to reproduce your error. As regards MS Excel: I am not able to fix MS Excel bugs. MS Excel has known issues, that's why I am not using it (let alone it only runs on Windows).
Last edited by JNeuhoff on Mon Sep 26, 2011 10:54 pm, edited 1 time in total.

MHC Web Design
Override Engine * Integrated VQMod * Unused Images Manager * Instant Option Price Calculator * TrustPilot Reviews * Google Rich Snippets * Google Tag Manager * Export/Import Tool * Template Switcher PHP/Twig


User avatar
Expert Member

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by BDW » Mon Sep 26, 2011 1:56 pm

Uploaded file is not a valid spreadsheet file or its values ​​are not in the expected formats!

I have found another 'gotcha' that can cause the above error and I am perplexed why...

Sometimes, I will drop (cut-n-paste) an entire column (that has a different heading field name) into the downloaded data spreadsheet as cut-n-pasting whole columns is easier than trying to mark all but the heading/field title in Excel. Then I would change (edit the cell) the heading back to match the OC field-name that was originally there. i.e. just re-type the text in the header row. but usually it doesn't 'format' exactly like it was originally (may not be centered in the box, etc...) - soooo...something is different other than the text I entered and it causes the above error.

The solution is to recopy (cut-n-paste from a similar spreadsheet) the entire header row after I have made my text changes...

BDW
Newbie

Posts

Joined
Wed Sep 21, 2011 7:17 pm

Post by anti91 » Mon Sep 26, 2011 6:05 pm

when i exported it. i have problem in excel :

Error: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay<br />Error No: 1104<br />SELECT p.product_id, pd.name, GROUP_CONCAT( DISTINCT CAST(pc.category_id AS CHAR(11)) SEPARATOR "," ) AS categories, p.sku, p.upc, p.location, p.quantity, p.model, m.name AS manufacturer, p.image AS image_name, p.shipping, p.price, p.points, p.date_added, p.date_modified, p.date_available, p.weight, wc.unit, p.length, p.width, p.height, p.status, p.tax_class_id, p.viewed, p.sort_order, pd.language_id, ua.keyword, pd.description, pd.meta_description, pd.meta_keyword, p.stock_status_id, mc.unit AS length_unit, p.subtract, p.minimum, GROUP_CONCAT( DISTINCT CAST(pr.related_id AS CHAR(11)) SEPARATOR "," ) AS related, GROUP_CONCAT( DISTINCT pt.tag SEPARATOR "," ) AS tags FROM `product` p LEFT JOIN `product_description` pd ON p.product_id=pd.product_id AND pd.language_id=1 LEFT JOIN `product_to_category` pc ON p.product_id=pc.product_id LEFT JOIN `url_alias` ua ON ua.query=CONCAT('product_id=',p.product_id) LEFT JOIN `manufacturer` m ON m.manufacturer_id = p.manufacturer_id LEFT JOIN `weight_class_description` wc ON wc.weight_class_id = p.weight_class_id AND wc.language_id=1 LEFT JOIN `length_class_description` mc ON mc.length_class_id=p.length_class_id AND mc.language_id=1 LEFT JOIN `product_related` pr ON pr.product_id=p.product_id LEFT JOIN `product_tag` pt ON pt.product_id=p.product_id AND pt.language_id=1 GROUP BY p.product_id ORDER BY p.product_id, pc.category_id;

New member

Posts

Joined
Tue Aug 30, 2011 8:27 am

Post by JNeuhoff » Mon Sep 26, 2011 11:00 pm

frenzzz4eva wrote:There has been no luck on this for me... I am facing the following issues:

1. the file doesnt work on MS excel, I have to use openoffice calc
2. I am copying the data from xls (97-compatible) in the Openoffice calc and then uploading the data
3. Now the entire process works fine if I am copying the data up till 3 records, but copying more than 3records and then try uploading the file results in "NO RECORDS UPLOADED"
4. The other problem I am facing is when I upload product descriptions. I have to upload the products without product descriptions. Then go to admin module and copy 1 by 1 each description. But the problem doesnt end here.. Guess what when I export the xls file its of no use. As the file cannot be uploaded again..

This is not working at all.... Can anyone suggest how will this thing work....
I got your PM with the attached sample.xls and tested it on my own Opencart 1.5.1.2 server, and it works fine, so I am unable to reproduce your problem. The only thing I noticed is that there is a lot of rubbish in your product descriptions, e.g. using stand-alone LI-elements within your TD-elements, you need to clean up the HTML in your product descriptions. But none of this affect the Export/Import. Also, don't use MS Excel, if the latter is buggy, I am not here to fix MS bugs. Use OpenOffice instead which is more stable in general and has now better support for the BIFF8 XLS format than later versions of MS Excel.

MHC Web Design
Override Engine * Integrated VQMod * Unused Images Manager * Instant Option Price Calculator * TrustPilot Reviews * Google Rich Snippets * Google Tag Manager * Export/Import Tool * Template Switcher PHP/Twig


User avatar
Expert Member

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by JNeuhoff » Mon Sep 26, 2011 11:02 pm

anti91 wrote:when i exported it. i have problem in excel :

Error: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay<br />Error No: 1104<br />SELECT p.product_id, pd.name, GROUP_CONCAT( DISTINCT CAST(pc.category_id AS CHAR(11)) SEPARATOR "," ) AS categories, p.sku, p.upc, p.location, p.quantity, p.model, m.name AS manufacturer, p.image AS image_name, p.shipping, p.price, p.points, p.date_added, p.date_modified, p.date_available, p.weight, wc.unit, p.length, p.width, p.height, p.status, p.tax_class_id, p.viewed, p.sort_order, pd.language_id, ua.keyword, pd.description, pd.meta_description, pd.meta_keyword, p.stock_status_id, mc.unit AS length_unit, p.subtract, p.minimum, GROUP_CONCAT( DISTINCT CAST(pr.related_id AS CHAR(11)) SEPARATOR "," ) AS related, GROUP_CONCAT( DISTINCT pt.tag SEPARATOR "," ) AS tags FROM `product` p LEFT JOIN `product_description` pd ON p.product_id=pd.product_id AND pd.language_id=1 LEFT JOIN `product_to_category` pc ON p.product_id=pc.product_id LEFT JOIN `url_alias` ua ON ua.query=CONCAT('product_id=',p.product_id) LEFT JOIN `manufacturer` m ON m.manufacturer_id = p.manufacturer_id LEFT JOIN `weight_class_description` wc ON wc.weight_class_id = p.weight_class_id AND wc.language_id=1 LEFT JOIN `length_class_description` mc ON mc.length_class_id=p.length_class_id AND mc.language_id=1 LEFT JOIN `product_related` pr ON pr.product_id=p.product_id LEFT JOIN `product_tag` pt ON pt.product_id=p.product_id AND pt.language_id=1 GROUP BY p.product_id ORDER BY p.product_id, pc.category_id;
Switch your webhost. And if you have more than lets say 1000 products, a simple shared webhost won't do, you'd need a more dedicated webhost package then.

MHC Web Design
Override Engine * Integrated VQMod * Unused Images Manager * Instant Option Price Calculator * TrustPilot Reviews * Google Rich Snippets * Google Tag Manager * Export/Import Tool * Template Switcher PHP/Twig


User avatar
Expert Member

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by frenzzz4eva » Tue Sep 27, 2011 1:07 am

JNeuhoff wrote:
frenzzz4eva wrote:There has been no luck on this for me... I am facing the following issues:

1. the file doesnt work on MS excel, I have to use openoffice calc
2. I am copying the data from xls (97-compatible) in the Openoffice calc and then uploading the data
3. Now the entire process works fine if I am copying the data up till 3 records, but copying more than 3records and then try uploading the file results in "NO RECORDS UPLOADED"
4. The other problem I am facing is when I upload product descriptions. I have to upload the products without product descriptions. Then go to admin module and copy 1 by 1 each description. But the problem doesnt end here.. Guess what when I export the xls file its of no use. As the file cannot be uploaded again..

This is not working at all.... Can anyone suggest how will this thing work....
I got your PM with the attached sample.xls and tested it on my own Opencart 1.5.1.2 server, and it works fine, so I am unable to reproduce your problem. The only thing I noticed is that there is a lot of rubbish in your product descriptions, e.g. using stand-alone LI-elements within your TD-elements, you need to clean up the HTML in your product descriptions. But none of this affect the Export/Import. Also, don't use MS Excel, if the latter is buggy, I am not here to fix MS bugs. Use OpenOffice instead which is more stable in general and has now better support for the BIFF8 XLS format than later versions of MS Excel.
The rubbish description you are mentioning are called HTML codes that impact the the display of text once its uploaded on to the admin module.
Secondly, I am working on OpenOffice but I am copying the data from MS excel on to openoffice, does this have any impact?

Also,as I am working in openoffice do you suggest saving the file in xls or ods?

Newbie

Posts

Joined
Thu Sep 22, 2011 12:44 pm

Post by JNeuhoff » Tue Sep 27, 2011 1:39 am

frenzzz4eva wrote:
The rubbish description you are mentioning are called HTML codes that impact the the display of text once its uploaded on to the admin module.
Secondly, I am working on OpenOffice but I am copying the data from MS excel on to openoffice, does this have any impact?

Also,as I am working in openoffice do you suggest saving the file in xls or ods?
I am referring to the sample.xls file you sent me, it contains invalid HTML in your product description.

As I said, I tested the Import and Export using your sample.xls, and couldn't find any issues in the Export/Import module with your file, other than the faulty HTML markup, but the latter doesn't affect the Export/Import. And as is documented, only the XLS format is supported by the Export/Import module.

MHC Web Design
Override Engine * Integrated VQMod * Unused Images Manager * Instant Option Price Calculator * TrustPilot Reviews * Google Rich Snippets * Google Tag Manager * Export/Import Tool * Template Switcher PHP/Twig


User avatar
Expert Member

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by frenzzz4eva » Tue Sep 27, 2011 1:44 am

I dont know whats going wrong but its not working for me.. There is something I am doing wrong.. I have exported the file from the admin..
Open the exported file with openoffice
Then I am copying the text from xls file on to open office (product description field) and save the file

Then I am uploading the file in the admin module using import export

And no result.. dont know wats going wrong

this is not working with me..

Newbie

Posts

Joined
Thu Sep 22, 2011 12:44 pm

Post by JNeuhoff » Tue Sep 27, 2011 2:29 am

Open the exported file with openoffice
Then I am copying the text from xls file on to open office (product description field) and save the file
From where exactly do you copy the product desacription if not from the XLS file opened in OpenOffice? And is your sample.xls the one which already includes the copied product description?

MHC Web Design
Override Engine * Integrated VQMod * Unused Images Manager * Instant Option Price Calculator * TrustPilot Reviews * Google Rich Snippets * Google Tag Manager * Export/Import Tool * Template Switcher PHP/Twig


User avatar
Expert Member

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by frenzzz4eva » Tue Sep 27, 2011 10:06 pm

I am getting the following errors (as per the error log file):

Newbie

Posts

Joined
Thu Sep 22, 2011 12:44 pm

Post by JNeuhoff » Wed Sep 28, 2011 12:32 am

frenzzz4eva wrote:I am getting the following errors (as per the error log file):
This has nothing to do with the Export/Import module nor with standard OpenCart 1.5.1.2, please get in touch with the 3rd party module provider who created these 'featured3.tpl' and 'featured2.tpl'.

MHC Web Design
Override Engine * Integrated VQMod * Unused Images Manager * Instant Option Price Calculator * TrustPilot Reviews * Google Rich Snippets * Google Tag Manager * Export/Import Tool * Template Switcher PHP/Twig


User avatar
Expert Member

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by frenzzz4eva » Thu Sep 29, 2011 11:12 am

Hi.. Any update on the PM I sent you yesterday?

Newbie

Posts

Joined
Thu Sep 22, 2011 12:44 pm

Post by JNeuhoff » Fri Sep 30, 2011 9:37 pm

frenzzz4eva wrote:Hi.. Any update on the PM I sent you yesterday?
One of your image names is wrong, namely for product 51:

data/Bravia KDL-EX720 40'.jpg

It should be:

data/Bravia KDL-EX720 40.jpg

Don't use single quotes in image names.

MHC Web Design
Override Engine * Integrated VQMod * Unused Images Manager * Instant Option Price Calculator * TrustPilot Reviews * Google Rich Snippets * Google Tag Manager * Export/Import Tool * Template Switcher PHP/Twig


User avatar
Expert Member

Posts

Joined
Wed Dec 05, 2007 3:38 am

Who is online

Users browsing this forum: No registered users and 1 guest