How to Import Products from Ebay to Magento 2

What to do if you need to import products from Ebay to Magento 2? In the following article I am going to explain how to perform this task.

Ebay export file is represented by *.xlsx file with 62 columns. To decrease development time we suggest couple of decisions:

At the first stage: manually convert this file into *.csv format, then create a new table in database and transfer all the data from the document into it.
At the 2nd stage: reorganize the table to import file for Magento 2 according to the required structure in *.csv.

At first we should determine the following names:

Table name
Source file name
New file name (new file that we will generate)
Source folder name
New files folder name (where new files that generated by our script will be stored)
Images folder name

In the third block we enter data for connecting with database, in which we are going to transfer all the data from the source file. We change table name, old file name and new file name for each product.

const TABLE_NAME = 'table_name';
const OLD_FILE_NAME = 'source_file_name';
const NEW_FILE_NAME = 'new_file_name';

const SOURCE_FOLDER_NAME = 'source';
const NEW_FILES_FOLDER_NAME = 'import';
const IMAGES_FOLDER_NAME = 'new_images';

const HOST_NAME = 'localhost';
const DB_USER_NAME = 'user_name';
const DB_PASSWORD = 'password';
const DB_NAME = 'db_name';

const TABLE_NAME = 'table_name';

const OLD_FILE_NAME = 'source_file_name';

const NEW_FILE_NAME = 'new_file_name';

const SOURCE_FOLDER_NAME = 'source';

const NEW_FILES_FOLDER_NAME = 'import';

const IMAGES_FOLDER_NAME = 'new_images';

const HOST_NAME = 'localhost';

const DB_USER_NAME = 'user_name';

const DB_PASSWORD = 'password';

const DB_NAME = 'db_name';

Further we connect up two libraries.

include 'slugify.php';
include 'loader_img.php';

1 2	include 'slugify.php'; include 'loader_img.php';

The first- default Symphony library for conversion product name into url-key.

The second- small library, that includes only one function: downloadlmg, which receives url images and name of the folder where we save product images:

function downloadImg($url_files, $folderName){
    $nm = substr($url_files,strripos($url_files,'/'));
    if($name = strstr($nm, '?', true)) $nm = $name;
    if (preg_match("/http/",$url_files)){
        $ch = curl_init($url_files);
        curl_setopt($ch, CURLOPT_HEADER, 0);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($ch, CURLOPT_BINARYTRANSFER,1);
        $out = curl_exec($ch);
        $image_sv = $folderName.$nm;
        $img_sc = file_put_contents($image_sv, $out);
        echo $nm."\n";
        curl_close($ch);
    }
    return substr(urldecode($nm),1);
}

function downloadImg($url_files, $folderName){

$nm = substr($url_files,strripos($url_files,'/'));

if($name = strstr($nm, '?', true)) $nm = $name;

if (preg_match("/http/",$url_files)){

$ch = curl_init($url_files);

curl_setopt($ch, CURLOPT_HEADER, 0);

curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

curl_setopt($ch, CURLOPT_BINARYTRANSFER,1);

$out = curl_exec($ch);

$image_sv = $folderName.$nm;

$img_sc = file_put_contents($image_sv, $out);

echo $nm."\n";

curl_close($ch);

}

return substr(urldecode($nm),1);

}

At first let’s create a table and transfer all data from the source file into it.

Partner With Us Let's discuss how to grow your business. Get a Free Quote.

Talk to Igor

$db = new mysqli(HOST_NAME, DB_USER_NAME, DB_PASSWORD, DB_NAME);
//Parsing file
$file = SOURCE_FOLDER_NAME.'/'.OLD_FILE_NAME.'.csv';
$row = 1;
$sql = 'INSERT INTO `'.TABLE_NAME.'` VALUES';
if (($handle = fopen($file, "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) {
        if ($row == 1) {
            //Create table
            $attrEbay = (explode(';',$data[0]));
            $createSql = 'CREATE TABLE IF NOT EXISTS `'.TABLE_NAME.'`( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, ';
            foreach($attrEbay as $i=>$title){
                if($title == '') $attrEbay[$i] = 'empty_title_'.$i;
                $attrEbaySql[$i] = '`'.$db->real_escape_string($attrEbay[$i]).'` TEXT';
                if((count($attrEbay) - 1) != $i) $attrEbaySql[$i].= ', ';
                $createSql .= $attrEbaySql[$i];
            }
            $createSql .= ');';
            if ($db->query($createSql) === TRUE) {
                echo "Table ".TABLE_NAME." created successfully";
                $db->query('TRUNCATE TABLE '.TABLE_NAME);
            } else {
                echo "Error creating table: " . $db->error;
            }
            $row++;
            break;
        }
    }
    //Add table titles
    while (($data = fgetcsv($handle, 100000, ";")) !== FALSE) {
        foreach ($data as $k => $v) {
            $data[$k] = $db->real_escape_string($v);
        }
        $sqlData = '"' . implode('","', $data) . '"';
        $query =  $sql . '(NULL, '.$sqlData .');';
        $res = $db->query($query);
        if (!$res) {
            echo $data[0].' ';
            printf("Errormessage: %s\n", $db->error);die;
        }
        echo $row."\r\n";
        $row++;
    }
    fclose($handle);
}

$db = new mysqli(HOST_NAME, DB_USER_NAME, DB_PASSWORD, DB_NAME);

//Parsing file

$file = SOURCE_FOLDER_NAME.'/'.OLD_FILE_NAME.'.csv';

$row = 1;

$sql = 'INSERT INTO `'.TABLE_NAME.'` VALUES';

if (($handle = fopen($file, "r")) !== FALSE) {

while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) {

if ($row == 1) {

//Create table

$attrEbay = (explode(';',$data[0]));

$createSql = 'CREATE TABLE IF NOT EXISTS `'.TABLE_NAME.'`( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, ';

foreach($attrEbay as $i=>$title){

if($title == '') $attrEbay[$i] = 'empty_title_'.$i;

$attrEbaySql[$i] = '`'.$db->real_escape_string($attrEbay[$i]).'` TEXT';

if((count($attrEbay) - 1) != $i) $attrEbaySql[$i].= ', ';

$createSql .= $attrEbaySql[$i];

}

$createSql .= ');';

if ($db->query($createSql) === TRUE) {

echo "Table ".TABLE_NAME." created successfully";

$db->query('TRUNCATE TABLE '.TABLE_NAME);

} else {

echo "Error creating table: " . $db->error;

}

$row++;

break;

}

//Add table titles

while (($data = fgetcsv($handle, 100000, ";")) !== FALSE) {

foreach ($data as $k => $v) {

$data[$k] = $db->real_escape_string($v);

}

$sqlData = '"' . implode('","', $data) . '"';

$query = $sql . '(NULL, '.$sqlData .');';

$res = $db->query($query);

if (!$res) {

echo $data[0].' ';

printf("Errormessage: %s\n", $db->error);die;

}

echo $row."\r\n";

$row++;

}

fclose($handle);

}

Let’s define product attributes array, that we will import to Magento 2.

$importArray[] = [
    'store_view_code','attribute_set_code','product_type','product_websites','tax_class_name','website_id','sku',
    'name','description','short_description','weight','product_online','visibility','price','special_price','url_key',
    'qty','is_in_stock','additional_attributes','configurable_variations','configurable_variation_labels',
    'base_image','small_image','thumbnail_image','swatch_image'
];

$importArray[] = [

'store_view_code','attribute_set_code','product_type','product_websites','tax_class_name','website_id','sku',

'name','description','short_description','weight','product_online','visibility','price','special_price','url_key',

'qty','is_in_stock','additional_attributes','configurable_variations','configurable_variation_labels',

'base_image','small_image','thumbnail_image','swatch_image'

];

And for the record, all the attributes and their values that we import, must be in Magento 2 already.

Products that we have imported could be divided into 3 groups: configurable, simple that are included in configurable, and independent simple products. Each of these types we should add separately. I won’t give you as example entire code, because of its size, but I’ll consider the basic parts (entire code is available here: https://github.com/morfij/importer-ebay-magento2).

We sort out simple products, that are included in configurable, by the having of “parent_sku”:

$sqlSimple = 'SELECT * FROM '.TABLE_NAME.' WHERE `parent_sku` != "" ';

1	$sqlSimple = 'SELECT * FROM '.TABLE_NAME.' WHERE `parent_sku` != "" ';

Simple:

$sqlSimpleSimple = 'SELECT * FROM '.TABLE_NAME.' WHERE `parent_sku` = "" AND `variation_type` = "" ';

1	$sqlSimpleSimple = 'SELECT * FROM '.TABLE_NAME.' WHERE `parent_sku` = "" AND `variation_type` = "" ';

Configurable:

$sqlConf = 'SELECT * FROM '.TABLE_NAME.' WHERE `parent_sku` = "" AND `variation_type` != "" ';

1	$sqlConf = 'SELECT * FROM '.TABLE_NAME.' WHERE `parent_sku` = "" AND `variation_type` != "" ';

«Variation_type» column shows us what attributes describe the configurable product features and it has “attribute1- attribute2” format. Let’s configure attribute set that we need:

 $attributes = explode('-', $row['variation_type']);
foreach ($attributes as $key=>$attributeName){
    if(strripos($attributeName, 'Size')){
        if(array_key_exists('variation:'.addslashes($attributeName), $subSimpleRow)) {
            $sizeSimple = 'size='.$subSimpleRow['variation:'.addslashes($attributeName)];
        }
        if(array_key_exists('Variation:'.addslashes($attributeName), $subSimpleRow)) {
            $sizeSimple = 'size='.$subSimpleRow['Variation:'.addslashes($attributeName)];
        }
        if($sizeSimple == ''){
            $sizeSimple = 'size='.$subSimpleRow['variation:Size'];
        }
        $sizeSimple = str_replace(',','.',$sizeSimple);
        $variationLabels[] = 'size=Size';
        $delSimpleSize = ',';
    }elseif($attributeName == 'Width'){
        $widthSimple = 'width='.$subSimpleRow['variation:Width'];
        $delSimpleWidth = ',';
        $variationLabels[] = 'width=Width';
    }elseif ($attributeName == 'Color'){
        $subSimpleColor = '';
        if(array_key_exists('Variation:'.$attributeName,$subSimpleRow)) $subSimpleColor = $subSimpleRow['Variation:'.$attributeName];
        if(array_key_exists('custom:'.$attributeName, $subSimpleRow))  $subSimpleColor = $subSimpleRow['custom:'.$attributeName];
        $colorSimple = ',color='.$subSimpleColor;
        $delSimpleColor = ',';
        $variationLabels[] = 'color=Color';
    }
}
$confProd .= 'sku='.$subSimpleRow['products_sku'].$delSimpleSize.$sizeSimple.$delSimpleWidth.$widthSimple.$delSimpleColor.$colorSimple.$delimiter;

$attributes = explode('-', $row['variation_type']);

foreach ($attributes as $key=>$attributeName){

if(strripos($attributeName, 'Size')){

if(array_key_exists('variation:'.addslashes($attributeName), $subSimpleRow)) {

$sizeSimple = 'size='.$subSimpleRow['variation:'.addslashes($attributeName)];

}

if(array_key_exists('Variation:'.addslashes($attributeName), $subSimpleRow)) {

$sizeSimple = 'size='.$subSimpleRow['Variation:'.addslashes($attributeName)];

}

if($sizeSimple == ''){

$sizeSimple = 'size='.$subSimpleRow['variation:Size'];

}

$sizeSimple = str_replace(',','.',$sizeSimple);

$variationLabels[] = 'size=Size';

$delSimpleSize = ',';

}elseif($attributeName == 'Width'){

$widthSimple = 'width='.$subSimpleRow['variation:Width'];

$delSimpleWidth = ',';

$variationLabels[] = 'width=Width';

}elseif ($attributeName == 'Color'){

$subSimpleColor = '';

if(array_key_exists('Variation:'.$attributeName,$subSimpleRow)) $subSimpleColor = $subSimpleRow['Variation:'.$attributeName];

if(array_key_exists('custom:'.$attributeName, $subSimpleRow)) $subSimpleColor = $subSimpleRow['custom:'.$attributeName];

$colorSimple = ',color='.$subSimpleColor;

$delSimpleColor = ',';

$variationLabels[] = 'color=Color';

}

$confProd .= 'sku='.$subSimpleRow['products_sku'].$delSimpleSize.$sizeSimple.$delSimpleWidth.$widthSimple.$delSimpleColor.$colorSimple.$delimiter;

After all the data was collected in $importArray, we write it in our file.

$fp = fopen(NEW_FILES_FOLDER_NAME.'/'.NEW_FILE_NAME.'.csv', 'w');
foreach ($importArray as $fields) {
    fputcsv($fp, $fields);
}
fclose($fp);

$fp = fopen(NEW_FILES_FOLDER_NAME.'/'.NEW_FILE_NAME.'.csv', 'w');

foreach ($importArray as $fields) {

fputcsv($fp, $fields);

}

fclose($fp);

Partner With Us Looking for a partner to grow your business? We are the right company to bring your webstore to success. Talk to Igor

Tags:

How to Import Products from Ebay to Magento 2

Table of contents:

Download Free Site
Audit Template

1 comment

Post a new comment Cancel

How to Import Products from Ebay to Magento 2

Get interesting stuff and updates to your email inbox

Table of contents:

Download Free Site Audit Template

1 comment

Post a new comment Cancel

Download Free Site
Audit Template