Export and Import Products and Categories with Automatic SEO URLS in OpenCart

OpenCart has been around for a good while. It became popular because it's easy to use, lightweight, and a good start for small and medium size e-commerce businesses. It has many features including SEO permalinks.

What OpenCart lacks by default compared to other platforms such as Magento is the functionality of mass product import and export. This tutorial will show you how to make OpenCart export and import multiple products into an Excel table.

We will go through the following steps:

Install vQmod, a standard extension interface.
Install the free OpenCart export/import tool.
Enable an SEO URL creation feature for Importing and Exporting data.
Define the main options in the import table.
Define the known issues and set a request for suggestions.

Installing vQmod Extension Interface

If you've ever tried to find plugins for OpenCart, you've likely seen vQmod plugins everywhere. It is a fairly simple interface to write plugins for OpenCart without modifying core files. You can find the latest version at www.vqmod.com.

After downloading, extract the content of the archive (the vqmod folder) to your OpenCart root directory. The vqmod folder should be on the same level as the admin or system directory. Navigate to www.yourshop.com}/vqmod/install. After a succesful installation, you can navigate back to admin or store front.

Also, as the vQmod site's wiki states:

Be sure the vqmod folder and the vqmod/vqcache folders are writable (either 755 or 777).

Installing Free OpenCart Import/Export Tool

Go to the Opencart Export/Import extension page and download the plugin. Extract the contents of the plugin folder (admin, override, system, vqmod) to the OpenCart root directory.

When asked if you to overwrite files choose yes. If all folders are copied to the root folder, navigate to the webshop's admin, and then System > Users > User Group. Here, select Top Administrator and add Modify and Access permission in the multi-select dropdown list, and save it. Log out and log into the admin area to see the newly installed extension in the System dropdown menu. Now you can import or export multiple products.

Except, notice that there is no feature for SEO URL generation. Why? This is how this tools works right now. Don't worry, we'll solve this problem with the next step.

Enabling SEO URL Auto-Creation Feature for the Export/import Tool

At this step, we need to utilize a simple function to convert the products name from the Excel table to a nice, clean URL. To do this, I made a simple vQmod script to do it for us.

<modification>
    <id><![CDATA[Export/Import generate url]]></id>
	<version><![CDATA[1.0.0.]]></version>
	<vqmver><![CDATA[2.4]]></vqmver>
	<author><![CDATA[Zsolt Gere - http://code.tutsplus.com]]></author>
	<file name="admin/model/tool/export.php">
		<operation error="log" info="Add seo url generator function">
			<search position="top" offset="2" index="2"><![CDATA[Add to Top of File]]></search>
			<add><![CDATA[setlocale(LC_ALL, 'en_US.UTF8');
function toAscii($str, $replace=array(), $delimiter='-') {
	if( !empty($replace) ) {
		$str = str_replace((array)$replace, ' ', $str);
	}

	$clean = iconv('UTF-8', 'ASCII//TRANSLIT', $str);
	$clean = preg_replace("/[^a-zA-Z0-9\/_|+ -]/", '', $clean);
	$clean = strtolower(trim($clean, '-'));
	$clean = preg_replace("/[\/_|+ -]+/", $delimiter, $clean);

	return $clean;
}]]></add>
		</operation>
		<operation error="abort" info="applying the seo function for product url">
			<search position="replace"><![CDATA[$keyword = $database->escape($product['seo_keyword']);]]></search>
			<add><![CDATA[if (empty($product['seo_keyword'])) { $keyword = toAscii(html_entity_decode($product['name'], ENT_QUOTES)); } else {$keyword = $database->escape($product['seo_keyword']);} ]]></add>
		</operation>
		<operation error="abort" info="Generate seo urls for category names">
			<search position="replace"><![CDATA[$keyword = $database->escape($category['seo_keyword']);]]></search>
			<add><![CDATA[if (empty($category['seo_keyword'])) { $keyword = toAscii($category['name'], ENT_QUOTES); } else {$keyword = $database->escape($category['seo_keyword']);}]]></add>
		</operation>
	</file>
</modification>

This small plugins adds a simple "any string to url" function to the Export/Import tool, and then finds the name of the products and categories and converts them to clean URLs if the seo_keyword cell in the import sheet is empty.

This way we still have the ability to manually add our own URL in the table and it won't be overwritten by this function. The string-to-URL function and its detailed description can be found at cubiq.org.

Save the code as export-import-generate-url.xml and save it in the vqmod/xml folder, from where the vQmod plugin will automatically load when next importing an .xls table.

Using the Export-Import Tool: Preparing Your Table for Importing

The best way to find out how the import table should be formatted is to create an export of your products. In your admin area navigate to System > Export/Import section and click the Export button. When exporting with this tool, it exports all your products. When importing, it imports everything from your table and overwrites the existing collection of categories and products in your OpenCart webshop. Remember this, and always tell your client. Open your downloaded .xls file and check out the columns in each worksheet.

Being familiar with the admin panel's category and product panels are important when understanding the worksheets.

Here's a quick review of them:

Additional Images

If you want additional images or a gallery add new image paths to the product's id. All these fields are optional.

Options

This refers to the same tab in your admin's product edit page. I would suggest to create the initial options in the admin panel and make an export to see and catch the logic in the worksheet. Basically everything refers to the product_id and language_id. You should only use this if you use the options feature for products.

Attributes

These are optional and have similar logic to options, so create the first attributes in the product admin manually and then make an export to see its equivalent in the worksheet.

Specials

These options are the very same as can be found in the admin panel's products section. Don't forget to create other user groups first in the admin panel if you want to use other than Default.

Discounts

This set of options are very similar to specials logically, so manipulating them in the worksheet is also easy.

Rewards

Again, this has the same logic as the last two, very similar input fields as in the admin area.

Known Issues and Suggestions

The import tool uses 3rd party tool called PHPExcel, which requires an amount of server memory. Here's an example to illustrate the ideal situation:

If a store has 13,500 products and 300 categories, an XLS file size can be 9 to 10MB, memory usage at export can be up to 13MB, memory usage of import can be up to 355MB, the following PHP settings are required for the plugin to work (in the php.ini file):

memory_limit 512M
post_max_size 16M
upload_max_filesize 16M

If you have fewer products and less memory on a shared hosting plan, I suggest you try the Export/Import tool to see if it works, before getting a larger hosting plan.

Since PHPExcel is being developed and optimized all the time, the plugin may work fine with less than 512 MB memory. If you've been using the Export/Import plugin for a while, I recommend you download a newer version of PHPExcel and upload to here: {yourwebshop.com}/system/PHPExcel/Classes/PHPExcel

Conclusion

We have installed a very useful import and export tool for OpenCart and extended it with a small script to fit our needs. Ideally, this, or a similar feature, should be part of the core functions in OpenCart.

I hope that you will successfully utilize this method in your next OpenCart project.

Any questions or recommendations are welcome!

HIGHLIGHTS OF THE DAY