Now that you have installed the bulk import/ export tool for OpenCart, you can begin using it to add or modify products in your OpenCart store. The first step is to export your existing products so you know how the spreadsheets work. Then you can add products to the spreadsheet offline and upload the file once you have finished.
- Log into the OpenCart Dashboard
- Go to System > Export/ Import
- Click “Export” to download an Excel spreadsheet of your current products
- Open the spreadsheet with the appropriate software such as Open Office or Microsoft Excel
Notice at the bottom of the spreadsheet, there is 7 separate sheets to work with (categories, products, options, attributes, specials, discounts, and rewards)
- Once you are done adding the products you want to add, in the correct format as your current products are laid out on the spreadsheet, save the file
- Now, go back to your OpenCart dashboard to: System Export/ Import
- Choose the file you just saved on your local computer by clicking the “Choose File” button next to “Import from spreadsheet file:”
- Once you save selected a file, click “Import”
Once you have successfully imports your categories and products you will get a message on the top of your screen letting you know it has completed.
If you need further assistance please feel free to ask a question on our support center.
It sounds like you are having trouble using the import function. We are happy to assist you, but we need more information, such as the exact error you are receiving, what type of browser you are using, what version of OpenCart you are running.
Also, could you provide a the specific steps you are taking, so we can attempt to replicate the issue, such as where you are clicking?
If you have any further questions, feel free to post them below.
2013-05-21 12:12 pm EST
Hey Tim, great article. One question: When you add your new items, ones that aren't yet in the OpenCart database, do you also add the fields that opencart populates, like _id, time_created, etc?
2013-05-21 2:08 pm EST
Thanks for your question. Yes, when you import data into another database, it needs to match the fields, otherwise the import will fail because of the data mismatch. It's important to note that the field data should match. For example, in OpenCart, the product id is a NUMERICAL value. This means that you cannot use text in the ID.
I hope this clarifies the issue . Please let us know you require any further assistance.
am getting an error sitename.product_tag doesnt exists in excel file , after i downloaded it. although this table exists
We are happy to assist you, but we need additional information to provide a specific answer. Please provide specific steps you are taking, and include information such as the full error message and version of OpenCart you are using.
This way we can attempt to replicate the problem that is occurring.
If you have any further questions, feel free to post them below.
i have installed opencart 1.5.5, then opencart1.5.3.x export import 11
i made all changes as specified , changes in 3 files , along with changes in user group
import/export option appeared in system
but in chrome export button didnt gave me any response , and export option in firefox worked but excel file didnt contained any data but gave following error
<b>Notice</b>: Error: Table 'sitename.product_tag' doesn't exist<br />Error No: 1146<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; in <b>/home/content/30/9520830/html/India2bharat11/system/database/mysql.php</b> on line <b>50</b>
copy the query into your phpmyadmin and make correction what errors there...
Table 'sitename.product_tag' doesn't exist
should i rename the table product to product_tag
i created another table product_tag , by using query create table select * , but now error is
Error: Unknown column 'pt.tag' in 'field list'<br />Error No: 1054<br />
2013-06-20 3:57 pm EST
It appears you're still trying run an export using existing OpenCart code that do not include the tables that you have added. The problem may be that even you added the table, that you're still missing some dependencies. If you're using an an add-on that has created the extra tables, then I would recommend not including it in order to get the bulk of the data exported and then imported into your newer version. You may need to consult with OpenCart (forum.opencart.com - their forums are great for issues like this), on the best way to include the extra data. You may have to rebuild it. OpenCart will only be responsible their tables and structures. If you're adding something, they will not be aware of it or it's affect on import export operations. If you're using an add-on then you should speak with the developer that has caused the extra table to be added. If you have added it yourself, then again, the best thing to do in this case is to simplify and remove the table until you can complete the export or import operations. Then add to the new database. Otherwise, you would be better off consulting with OpenCart directly for the database operations that are not operating correctly.
Apologies that we cannot provide a direct answer for the matter. Please let us know if you still have any further questions.
I'm running OC v1.5.6 and downloaded opencart-1-5-6-x-export-import-16.zip, extracted the files and dropped them as instructed into my root folder using Filezilla. I then selected the 'tool/export' options in the Top Administrator section of User Groups in both Access and Modify Permission tables. However once saved down I am not getting the 'Export/Import' option appear in the System menu.
Please let me know what you suggest my next step is.
Hello timwilcox, and thanks for your comment.
The System > Export / Import
menu option should display even prior to you giving the User Groups
permission to it, you'd just get a permission denied message.
It sounds like possibly you might have missed the step about installing vQmod for OpenCart
. As prior to uploading the admin
, and vqmod
directories from the opencart-1-5-6-x-export-import-16.zip
file, there should already be a vqmod
directory on the server.
If you do already have vQmod
installed, there might be another issue at hand but it would be a bit hard to guess at what that was without taking a look at the site.
You can let us know what your website is here publicly, or if you'd prefer, you can send an email directly to us at email@example.com
asking for more specific help to your website.
I'm running OC v 18.104.22.168. When I click on the export button I just get taken to a white screen and nothing happens (both in Chrome and IE).
Appreciate any help provided!
2013-09-25 8:34 pm EST
I'm sorry to hear that you're having problems. The white screen is very general - do you get any specific errors? I looked on your account and did not see an Opencart installation. If you have it elsewhere with InMotion can you please provide a URL? It's difficult to say what's causing the error, without seeing the files. Check out this post for info on this type of error: blank white pages
I hope this helps to get you pointed in the right direction. If you can provide more information we would be glad to investigate the issue further.
Followed all instructions in the Bulk Import Export tutorial for opencart. Received the following error after I downloaded the excel file:
Fatal error: Class 'ZipArchive' not found in /home/gremilli/public_html/system/PHPExcel/Classes/PHPExcel/Writer/Excel2007.php on line 234
Hello mgremillion09, and thanks for your comment.
What version of OpenCart are you running, and which version of the OpenCart Export/Import Tool are you running?
When I downloaded opencart-1-5-4-x-export-import-13.zip and took a look at the /system/PHPExcel/Classes/PHPExcel/Writer/Excel2007.php file on line 234 there is no mentions of a ZipArchive class. It sounds like possibly you don't have all of the files uploaded, or have the improper version for your version of OpenCart.
Let us know if you had any other questions.
Hi, i already success installs extension Bulk import/export but when I export files from my opencart my excel sheet cannot open? Has some error appear when I try open excel file... " Excel cannot open the file'backup_product. xlsx because the file format or file extension is not valid. Verify that the file has not been corrupted and that file extension matches the format of the file." i used word 2007.
Anyone can help me?
2013-11-13 8:51 am EST
.XLSX files are typically not a format in which you would be able to open them up in Word and would require Excel which is also part of the Microsoft Office suite. It is also possible that the file may not be in a compatible format for your version of Excel if you have tried it that way as well. Try the Excel viewer that is provided by Microsoft
which may also help.
Thx jeffMa for your answer, i has download and install Excel viewer but my excel still cannot open. But I find some error in Error Log at opencart. It mentions " Class 'ZipArchive' not found See also 'System > Error Logs' for more details."
do you have any idea about my problem?
Do you see anymore specific details when you navigate to System > Error Logs
It's sounding like the server your website is hosted on does not have the ZipArchive
PHP class which comes with the PHP Zip extension
If you're hosted with us, please find your server name in cPanel
and let us know what it is and we can see about having our system administration team ensuring that is available for you on that server.
2013-11-17 10:15 pm EST
Thank you JacoblMH for help me. I have already contacted my server to install ZipArchive and my Excel success to open. But when i wanted import file excel i already edited in opencart, it mentions "Success: you have successfully imported you categories and products!" but the product i add not appear on my website. And Error Logs mention "Export/Import: Invalid header in the Attributes worksheet".
2013-11-18 11:20 am EST
It sounds like your Excel file is not in the correct format that OpenCart expects. Withing your Excel file, you will need the required headers over each entry that determines what each field is. Within the readme.txt file inside the extension, you will see an explanation on what each of these are.
Hi jeffMa thank you for helping me to fix my problem. And i have another problem i cannot export file excel and this error appear
"2013-12-05 13:52:06 - PHP Warning: Cannot modify header information - headers already sent by (output started at /home/stylewed/public_html/styleweddingsmy.com/system/PHPExcel/Classes/PHPExcel/CachedObjectStorage/CacheBase.php:155) in /home/stylewed/public_html/domain name/admin/model/tool/export.php on line 50
2013-12-05 13:53:28 - PHP Fatal Error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 1048576 bytes) in /home/stylewed/public_html/domain name/system/PHPExcel/Classes/PHPExcel/CachedObjectStorage/CacheBase.php on line 155"
i hope you can help me to fix my problem.
2013-12-05 8:44 am EST
Login to comment.