Export Opencart Product CSV

This tutorial explains how to export opencart product CSV. Here I am using SQL queries to export the data.

Export Opencart Product CSV

Please also refer other tutorials which explain Magento 2 categories and products imports.

Export Opencart Product CSV

Here I am using the sql queires to export the data.

Initialise Database Connection

$servername = "localhost";
$username = "";
$password = "";
$dbname = "";
$conn = connect($servername, $username, $password, $dbname);
getproducts($conn);

function connect($servername, $username, $password, $dbname) {
    
    $conn = new mysqli($servername, $username, $password, $dbname);

    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    
    return $conn;
    
}

Export products opencart

function getproducts($conn) {
    
    $allData = array();
    $sql = "SELECT p.product_id as Id, model, p.sku, upc, quantity,stock_status_id,image,manufacturer_id,shipping,"
            . "price,weight,weight_class_id,length_class_id,subtract,minimum,status,name,tag,"
            . "meta_title,meta_description,meta_keyword, pd.description as description"
            . " FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id)"
            . " WHERE  pd.language_id = '1' GROUP BY p.product_id ORDER BY pd.name ASC";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
       
        while($row = $result->fetch_assoc()) {

            $full_description = html_entity_decode($row["description"]);
            $str = preg_replace('/style="(.*?)"/','',$full_description);
            $str = str_replace(' ', '', $str);
            $str = str_replace('class="MsoNormal"', '', $str);
            $str = str_replace('align="center"', '', $str);
           
            $row["description"] = $str;
            $allData[$row["Id"]] = $row;
            
        }
    }
    $conn->close();
    $sampleheaders = ['Id', 'Model', 'Sku', 'Upc', 'Quantity', 'Stock_status_id','Image','Manufacturer_id','Shipping','Price','Weight','Weight_class_id','Length_class_id','Subtract','Minimum','Status','Name','Tag','Meta_title','Meta Keyword','Meta description','Description'];    
    createCSV( $allData, 'insidetechproducts.csv', $sampleheaders);
    
}

Export Opencart Categories

function getcategories($conn) {
    
    $allData = array();
    $sql = 'SELECT category_id,name,description,meta_title,meta_keyword,meta_description FROM oc_category_description ORDER BY name ASC ';
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
       
        while($row = $result->fetch_assoc()) {

            $allData[$row["category_id"]] = $row;
            
        }
    }
    $conn->close();
    $sampleheaders = ['Category Id', 'Name', 'Descripton','Meta title', 'Meta Keyword', 'Meta Description'];    
    createCSV( $allData, 'insidetechcategories.csv', $sampleheaders);
    
}

Export Additional Images Opencart

For exporting the additional images replace the above code with the following query.

    $sql = 'SELECT product_image_id as Id,product_id,image FROM oc_product_image  ORDER BY product_id ASC';

Export Product Options

    $sql = 'SELECT o.option_id as Id,od.name as name FROM `oc_option` o LEFT JOIN oc_option_description od ON (o.option_id = od.option_id) WHERE od.language_id = 1 ORDER BY Id ';

Export Manufacturers

    $sql = 'SELECT * FROM oc_manufacturer ORDER BY name ASC ';

Export Options

$sql = 'SELECT po.product_option_id as Id,po.product_id ,po.option_id,od.name'
            . ' FROM `oc_product_option` po LEFT JOIN `oc_option` o ON (po.option_id = o.option_id) '
            . 'LEFT JOIN `oc_option_description` od ON (o.option_id = od.option_id) ORDER by product_id ASC ';

Export Category Product Relation

    $sql = 'SELECT product_id,category_id FROM oc_product_to_category';

Export Related Products

    $sql = 'SELECT product_id,related_id FROM oc_product_related order by product_id ASC';

Export Product Discounts

    $sql = 'SELECT product_special_id,product_id,price FROM oc_product_special where date_end=\'0000-00-00\' ORDER BY product_id ASC';

Export Product Options Value

 $sql = "SELECT pov.product_id,pov.option_id,pov.option_value_id,pov.quantity,pov.price,"
         . "pov.price_prefix,ovd.name FROM oc_product_option_value pov "
         . "LEFT JOIN oc_option_value ov ON (pov.option_value_id = ov.option_value_id) "
         . "LEFT JOIN oc_option_value_description ovd ON (ov.option_value_id = ovd.option_value_id)order by product_id,pov.option_id";

Create CSV

 function createCSV($data, $fileName, $headers) {
       
    $file = fopen( $fileName, 'w');    
    fputcsv($file, $headers);
    
    foreach ($data as $row) {
        
        fputcsv($file, $row);
        
    }  
    fclose($file);
    
}

Share this Tutorial

Leave a Reply

Your email address will not be published. Required fields are marked *

*