Export Opencart Products in Excel

Recently I developed E commerce website in opencart. It have more then 5,000 products in different categories. My client asked for the Product name, Its model No and price in Excel sheet. I checked a lot of extensions to export opencart products, some are free, some we have to buy and they did not completely fulfill my requirement. So i decide to dig in its database.

Export Opencart Products

I found my required columns in 2 tables i.e product_description and product. Product Name in product_description ,its price and model no in product table. MySQL Join Query made my life easy.

MySQL join

A MySQL join is a method of linking data between one (self-join) or more tables based on values of the common column between tables.

Example:

SELECT column_name(s)

FROM table1

JOIN table2

ON table1.column_name=table2.column_name;

According to definition both table have product_id common column, so I Run SQL query in SQL pan of phpmyadmin. In following query oc_ is my table prefix.

Select
oc_product_description.product_id, oc_product_description.name, oc_product.model, oc_product.price From oc_product_description
JOIN oc_product
ON oc_product_description.product_id= oc_product.product_id

The above query give exact data which is actually my client’s requirement. Now my next step is to shift this data in a table ,so I am running the ‘create table query’. I have created table with name of tweeks.

 

 

CREATE Table tweeks as
Select
oc_product_description.product_id, oc_product_description.name, oc_product.model, oc_product.price From oc_product_description
JOIN oc_product
ON oc_product_description.product_id= oc_product.product_id

Now my table is created with the name of tweeks and i export these tweeks table in CSV for Excel format.

You got the tweeks.csv file and it is a time to open in Excel. Iwithout breaking it, here’s how to do it:

1. Open a new Excel sheet, select the Data tab, then click ‘From Text’ in the Get External Data group.

• Browse to the CSV file and select ‘Import’.
• In step 1 of the Import Wizard choose ‘Delimited’ as the original data type. Click ‘Next’.

• In step 2 of the Import Wizard choose Comma as the delimiter (deselect the Tab check box) and click ‘Next’.

• If you are happy with the Data preview, then click the Finish button.