Tuesday, 25 November 2014

Magento CSV import error show which row value is skipped

During the Batch CSV import, some of the rows will be skipped due to missing required values or any such errors. On the import process page magento will throw error message like below by default.

From this error message we won't be able to identify exactly which row is skipped. So I have made a modification to the magento files and after that


Now we will know which row is skipped and can check for errors in it. Let me explain the few modification which i did to achieve this.

Step 1:

copy the file Customer.php which is under the path "app/code/core/Mage/Customer/Model/Convert/Adaptor/" to this path "app/code/local/Mage/Customer/Model/Convert/Adaptor/"


Step 2:

open the Customer.php in your editor and look for the function name "saveRow". It will be around line 418.
Inside this function you will see the error messages text. For Example:

 $message = Mage::helper('customer')->__('Skipping import row, required field "%s" is not defined. ', 'website');

Replace above line with this.

 $message = Mage::helper('customer')->__('Skipping import row, required field "%s" is not defined. Unique value to identify in CSV <strong>%s</strong> ', 'website', $importData['email']);

There will be similar error messages thrown for various reasons like customer group not found. website id does not exist and so on.. You can use the above customization to all those errors thrown.

The $importData['email'] is the one which contains the row value which is unique in case on Customer import. you can also use $importData['firstname'] or $importData['lastname'] according to your needs. The values inside the quotes are the column names from the CSV.

Similarly For Product batch import you can modify the file which is under. app/code/core/Mage/Ccatalog/Model/Convert/Adaptor/Product.php 

NOTE: When you import the CSV. look for any Special characters. This may cause errors during import. To import CSV with special characters edit your .htaccess file and add this line to it AddDefaultCharset UTF-8

Magento Customer Import - Convert plain text password to magento format.

One of my recent task is to import about 10k customers into Magento database.

First thing came into my mind is to export the existing customer from magento and use the csv file to fill in the new customer values and import them back.
But after exporting the few existing customers, i came to know that the CSV header fields which Magento exported is not suitable for the import and missing fields for password, customer address etc.

After few searches, i found this SAMPLE CSV file. click to download

Filled in the CSV file and for the password_hash field, I had to develop a customer script to convert plain text password to Magento format password.

My custom script is below. It reads the CSV file which contains plain text password, Encrypts it and writes to a new CSV file. After you CSV file is ready go to the admin panel. In the menu select System -> Import/Export -> Dataflow- Profiles. You will see "import customer". Click on it and upload your CSV file and click "save & continue edit" button. Then Choose the imported file from the dropdown and click Run.

All the customers will be imported with their previous password and addresses.

$row = 1;
$fp = fopen('var/log/ss1-pass.csv', 'w');
$csvHeader = array('Text','Encrypted');
fputcsv( $fp, $csvHeader,",");

//generate random string for salt
function generateRandomString($length = 2) {
    $characters = '123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    $randomString = '';
    for ($i = 0; $i < $length; $i++) {
        $randomString .= $characters[rand(0, strlen($characters) - 1)];
    return $randomString;

if (($handle = fopen("ss1.csv", "r")) !== FALSE) {

    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
      $plainText =  trim($data[0]);
      $salt = generateRandomString(2);
      $encrypted = md5($salt.$plainText).":".$salt;
      fputcsv($fp, array($plainText,$encrypted), ",");