Magento Forum

A suggestion to import configurable products + categories
 
Wajking
Jr. Member
 
Avatar
Total Posts:  10
Joined:  2010-04-15
 

Hi!

I have upgraded to 1.4.1.1 and now my import script fails. It fails when it should save the configurable products and with it\’s associated products.

$product -> setConfigurableProductsData($productsIds) -> save();

This line gives the error:
\"Uncaught exception \’Zend_Db_Statement_Exception\’ with message \’SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`shop/catalog_product_super_link`, CONSTRAINT `FK_SUPER_PRODUCT_LINK_ENTITY` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CA)\’\”

Please help!

 
Magento Community Magento Community
Magento Community
Magento Community
 
Wajking
Jr. Member
 
Avatar
Total Posts:  10
Joined:  2010-04-15
 

Hi!

I have upgraded to 1.4.1.1 and now my import script fails. It fails when it should save the configurable products and with it\’s associated products.

$product -> setConfigurableProductsData($productsIds) -> save();

This line gives the error:
\"Uncaught exception \’Zend_Db_Statement_Exception\’ with message \’SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`shop/catalog_product_super_link`, CONSTRAINT `FK_SUPER_PRODUCT_LINK_ENTITY` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CA)\’\”

Please help!

 
Magento Community Magento Community
Magento Community
Magento Community
 
isumi
Jr. Member
 
Total Posts:  21
Joined:  2010-09-13
 
energytools - 23 October 2010 12:48 PM


Answer to Isumi Config products don’t have stock, to import stocks for simple use the standard magento “import stock” profile.

Hey, thanks for the answer, but i rather mean that the simple products which are associated with configurable products, they have as qty 0 and aren’t in stock eather. Or should i load first all simple products with the normal magento “Import Products” and then load with Advance Import only the configurable products, where this simple products are associated?
Would it work?
Thank you!

 
Magento Community Magento Community
Magento Community
Magento Community
 
Carlon
Jr. Member
 
Total Posts:  2
Joined:  2010-06-23
 
isumi - 28 October 2010 02:12 AM

energytools - 23 October 2010 12:48 PM


Answer to Isumi Config products don’t have stock, to import stocks for simple use the standard magento “import stock” profile.

Hey, thanks for the answer, but i rather mean that the simple products which are associated with configurable products, they have as qty 0 and aren’t in stock eather. Or should i load first all simple products with the normal magento “Import Products” and then load with Advance Import only the configurable products, where this simple products are associated?
Would it work?
Thank you!

Hi

I have the same problem, everything in working fine exept for visibility ,status, is_in_stock and qty.  (1.4.1.1 )

 
Magento Community Magento Community
Magento Community
Magento Community
 
emmenko
Sr. Member
 
Total Posts:  96
Joined:  2010-10-20
 

Hi everybody.

After 4-5 days of going crazy and intensive research about these topics, I finally write too on this discussion.
First of all, I’d like to say (like many others) that it’s incredible that this sort of problem (Configurable Products) is not fixed by Magento Developers yet, and it is going on after so many releases.

I’m working with the last version of magento (1.4.1.1) on my Macbook in localhost.
I already open a topic about my problems Tutorial - How to use Import / Export profile and manage .csv or .xml data format and how I’m fixing them (I hope this could be an additional help for somebody).

Anyway, I’d like to report the steps that I made and the problems I have right now

1. when I started to try the Import/Export with magento I crushed immediately to a BIG problem with the format. In fact, my Excel didn’t seem to appreciate the csv format. I tried many ways to make it work (both xml and csv) but there were some seriously issues with and after the import.
I found later that Excel has the beauty to change some hidden parameters (that I couldn’t be able to change).

2. I downloaded Open Office and this time I could work with csv format without problems.

3. So, I started to create some samples to import: 3 Sample Products + 1 Configurable Product.
Also I added these attributes (I think you all know that rasberry) to the table (config_attributes, associated) that they didn’t appear in the default “Export.csv”.

4. Then I followed these Wiki Guide Wiki - How to import simple, grouped and configurable products.

5. At first trial, I had an error during the import process.
To fix that, I copied the Productimport.php also in the app/code/core/Mage/Catalog/Model/Covert/Adapter/
I don’t know why, but when I tried again, it worked and the problem with the Configurable Product was fixed.

6. Now I have found other issues. In fact, if I repeat the Import, the “Super product attributes configuration” and the “images” are REPEATED. (see screenshot in which I made 3 times the import).
HOW CAN THAT BE POSSIBLE?

7. The next step will be (for me) to fix that (hoping with yours help) and manage to fix the “custom price” (for ex. the size xxl has a incremented price).

I hope someone can give me some hints how to fix that.

Thx everybody

Nicola

Image Attachments
super-attr.jpg
 
Magento Community Magento Community
Magento Community
Magento Community
 
emmenko
Sr. Member
 
Total Posts:  96
Joined:  2010-10-20
 

Ok, I fixed also the problem with the duplicating box of super product attributes.

Here is how I did it

Nicola

 
Magento Community Magento Community
Magento Community
Magento Community
 
videohead
Jr. Member
 
Total Posts:  22
Joined:  2010-10-14
 

Thanks for the code, AMartinez.

Here was my process for import and associate

My configurable products are all sub-skus, so they have short skus minus the product attributes (size, and color).
First I built an example of each product in Magento.
Then I ran the stock exporter to look at the CSV file.
Then I formatted my existing simple products into a CSV and imported.
Then I formatted my existing configurables into a second CSV and imported.
Then I figured out which attributes I needed to access for color and size.

Next I wrote some PHP. I had to truncate and INSERT to update four tables.

Here is the code:

private function runQueries() {
        
//  remnant code - stick around a while  - length(sku) < 10
        
$query1 "SELECT * FROM mage_catalog_product_entity WHERE type_id= 'configurable'";
        
//Find the parent id
        
$statusMessage "Ok, found a product with a confgurable attribute";
        
$result1 $this->runQuery($query1"query1"$statusMessage);
        while (
$row1 mysql_fetch_assoc($result1)) //entering the first loop where products are configurable
            
$this->parentId $row1['entity_id'];
            
$this->parentSku $row1['sku'];
            
            echo 
"The SKU was $this->parentSku"<br />";
            
        
//insert these into the link table for association
        
$query2 "SELECT * FROM mage_catalog_product_entity WHERE type_id= 'simple' AND sku LIKE '" $this->parentSku "%';";
        
// find the child ids that belong to the parent
        
$statusMessage "Found some children for $this->parentSku";
        
$result2 $this->runQuery($query2"query2"$statusMessage);
        while (
$row2 mysql_fetch_assoc($result2)) {//entering the second loop where SKU is like model sku
            
$this->childId $row2['entity_id'];
            
$this->childSku $row2['sku'];

            echo 
"Now we're working with a child SKU $this->childSku"<br />";
            
            
//"INSERT INTO catalog_product_super_attribute SET product_id='".$product->entity_id."', attribute_id='".$attribute->attribute_id."', position='".$position."'";
            
$query3 "INSERT INTO mage_catalog_product_super_attribute  (product_id, attribute_id, position) VALUES ('" $this->childId "', '76', '0');";
            
$message3 "Inserted attribute for color for ID $this->childId SKU $this->childSku";
            
$result3 $this->runQuery($query3"query3"$message3);
            
            
$query4 "INSERT  INTO mage_catalog_product_super_attribute_label (product_super_attribute_id, store_id, use_default, value) VALUES (LAST_INSERT_ID(), '0', '0', 'Color');";
            
$message4 "Inserted attribute for Color  SKU $this->childSku ID was $this->db->insert_id";
            
$result4 $this->runQuery($query4"query4"$message4);
            
            
$query5 "REPLACE  INTO mage_catalog_product_super_attribute  (product_id, attribute_id, position) VALUES ('" $this->childId "', '529', '0');";
            
$message5 "Inserted attribute for Product Size SKU $this->childSku";
            
$result5$this->runQuery($query5"query5"$message5);


            
$query6 "INSERT  INTO mage_catalog_product_super_attribute_label (product_super_attribute_id, store_id, use_default, value) VALUES (LAST_INSERT_ID(), '0', '0', 'Size');";
            
$message6 "Inserted attribute for Size SKU $this->childSku ID was $this->db->insert_id";
            
$result6 $this->runQuery($query6"query6"$message6);
            
            
$query7 "INSERT INTO mage_catalog_product_super_link (product_id, parent_id) VALUES ('" $this->childId "', '" $this->parentId "');";
            
$message7 "Inserted $this->childId and $this->parentId into the link table";
            
$result7 $this->runQuery($query7"query7"$message7);
            
            
$query8 "INSERT INTO mage_catalog_product_relation (parent_id, child_id) VALUES ('" $this->parentId "', '" $this->childId "');";
            
$message8 "Inserted $this->childId and $this->parentId into the link table";
            
$result8 $this->runQuery($query8"query8"$message8);

            
//end while row 2 the child ID
        
                
//end while row 1 the parent id
        
         
//end function RunQueries
            

private function runQuery($sql$queryName$successMessage{
        $result 
mysql_query($sql,$this->db);
        if (!
$result{
            $message  
"<p class=error>Invalid query $queryName: " mysql_error() . "<br />";
            
$message .= "Whole query: " $sql "</p><br />";
            die(
$message);
            
$this->error++;
            return 
false;
        
else {
            
echo $successMessage "<br />";
            
$this->status++;
            return 
$result;
        
//end elsesuccessMessage
    
//end RunQuery
 
Magento Community Magento Community
Magento Community
Magento Community
 
dbaldin
Jr. Member
 
Total Posts:  2
Joined:  2010-09-07
 

I replaced this:

protected function userCSVDataAsArray$data )
    
{
        
return explode','str_replace" """$data ) );
    
}

with this:

protected function userCSVDataAsArray$data )
    
{
        
return explode','trim$data ," ") );
    
}

I noticed that the generator for simple products in the edit/create dialog of the configurable product concatenates the id with a space. I think to trim a csv string due to reasons of clean formating is ok, but replacing all spaces within the content is not ok, in doing so you alter the import-data. I’d been facing problems with this, plain magento-generated data (sku), this problem could maybe happen to others too.

Have alot of fun!

d.

 
Magento Community Magento Community
Magento Community
Magento Community
 
emmenko
Sr. Member
 
Total Posts:  96
Joined:  2010-10-20
 

Uff, I’m getting tired to write every day in this forum because of continuously errors…

Hi everybody,

I’m trying to get the cron job to work...I have magento 1.4.1.1 with the Productimport.php script...the import goes perfect, so I decided to test the cron job with the file cron_import_products.php
I work on a Mac with MAMP as a server.
After some trials with the terminal to run the script, I found that my PHP version installed by default in the OS is different from the version in the server MAMP. Actually, there isn’t the pdo_mysql driver (I get an error when I try to run it) but the MAMP has it.
So, I decided to try to run it via browser.

At first I got an error about the STORE ID.
To fix it, I went to the cron_import_products.php and change this

Mage :: app"default" ) -> setCurrentStoreMage_Core_Model_App :: ADMIN_STORE_ID );

whit this

Mage :: app"admin" ) -> setCurrentStoreMage_Core_Model_App :: ADMIN_STORE_ID );

as my store default id is ‘admin’.

I run again the file and it seemed to work...it loads the import_file.csv but I have the error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ 0)’ at line 1

Here is the entire error

Starting Mage_Dataflow_Model_Convert_Adapter_Io :: load Loaded successfully: “/Users/path/to/magento/var/import/import_products.csv”. Starting Mage_Dataflow_Model_Convert_Parser_Csv :: parse Found 13 rows. Starting catalog/convert_adapter_productimport :: parse ROW 1, SKU test1-db-s - SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ 0)’ at line 1 ROW 1, SKU test1-db-s - SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ 0)’ at line 1 ROW 1, SKU test1-db-s - SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ 0)’ at line 1 ROW 2, SKU test1-db-m - SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ 0)’ at line 1 ROW 2, SKU test1-db-m - SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ 0)’ at line 1 ROW 2, SKU test1-db-m - SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ 0)’ at line 1 ROW 3, SKU test1-db-l - SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ 0)’ at line 1 ROW 3, SKU test1-db-l - SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ 0)’ at line 1 ROW 3, SKU test1-db-l - SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ 0)’ at line 1
Fatal error: Call to undefined method Mage_Catalog_Model_Product_Type_Simple::getUsedProducts() in /Users/path/to/magento/app/code/core/Mage/Catalog/Model/Resource/Eav/Mysql4/Product/Type/Configurable/Attribute/Collection.php on line 161

Any ideas what it might be?

Thx

Nicola

 
Magento Community Magento Community
Magento Community
Magento Community
 
emmenko
Sr. Member
 
Total Posts:  96
Joined:  2010-10-20
 

NOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO

I will kill someone or something!!!! è_é

It can’t happen really… T___________T

I was trying to run the cron job via browser....I made some changes in the file cron_import_products.php and it seemed to work...in the meantime I was doing some manually imports to be sure the import worked fine.

But now I get this error:

<br /> <b>Fatal error</b>: Call to a member function getId() on a non-object in <b>/Users/path/to/magento/app/code/core/Mage/Catalog/Model/Product/Type/Configurable.php</b> on line <b>267</b><br />

I haven’t done any changes in the file Productimport.php, so why doesn’t work anymore????

I was able to do the import without problems but now this f****** error appears....If anyone can help it would be really great
I’m really said right now :(

Nicola

UPDATE:

I ended up deleting all the products and re-importing them...now it works again the Productimport.php
That was really strange and made me scary!

Tomorrow I continue investigating how to run the cron_import_products.php via browser...for today is enough!

...and sorry everybody for the crazy reaction before....

 
Magento Community Magento Community
Magento Community
Magento Community
 
Curt Maybury
Jr. Member
 
Avatar
Total Posts:  13
Joined:  2007-11-20
 

Attached is my version of the code after weeks of hunting, testing, swearing and being beaten by my gf (her store!) for breaking things…

I’m trying to figure out automatic stock updates without reinserting images and attributes, this seems to work so far but I’m a long way from the end of the import (62,000 products).

Worth noting the following problems which have been overcome on the fly:

CSV Issues:

1.  Non UTF8 characters coming from my supplier feed, these were fixed using a download script which converts the characters on the fly:

function clearUTF($s)
{
    $s
=str_replace("|","-",$s);
    
$r '';
    
$s1 iconv('UTF-8''ASCII//TRANSLIT'$s);
    for (
$i 0$i strlen($s1); $i++)
    
{
        $ch1 
$s1[$i];
        
$ch2 mb_substr($s$i1);

        
$r .= $ch1=='?'?$ch2:$ch1;
    
}
    
return $r;
}

e.g.  $description = clearUTF($description);

2. Spaces in my SKU’s.... ARGH!!!  Replaced with underscore.

3. Comma delimiter - don’t trust it, I switched mine to Pipe delimited.  e.g. |

4. Weird enclosure issues.  Why oh why does fputcsv cause so many problems?  I have to parse my feed on the fly because the supplier isn’t a fan of autoupdates and makes things awkward - if you’re doing the same, try this function in your generator script:

function dumbcsv($file_handle$data_array$enclosure$field_sep$record_sep)
 
{
     dumbescape
(false$enclosure);
     
$data_array=array_map('dumbescape',$data_array);
     return 
fputs($file_handle
         
$enclosure 
         
implode($enclosure $field_sep $enclosure$data_array)
         . 
$enclosure $record_sep);
 
}
 
function dumbescape($in$enclosure=false)
 
{
    
static $enc;
    if (
$enclosure===false{
        
return str_replace($enc'\\' $enc$in);
    
}
    $enc
=$enclosure;
 
}

Usage:  dumbcsv($outputfile, $line,’“‘,"|”,"\r\n"); // pipe delimiter, “ enclosure, \r\n record terminator.

(edited for extra notes)

Using 1.4.1.1

PS:  I’m using the remote download script to get my images - too lazy to unzip them manually!

File Attachments
Productimport.php  (File Size: 20KB - Downloads: 203)
 
Magento Community Magento Community
Magento Community
Magento Community
 
Quinty
Jr. Member
 
Total Posts:  12
Joined:  2010-11-15
 
emmenko - 11 November 2010 08:40 AM

<br /> <b>Fatal error</b>: Call to a member function getId() on a non-object in <b>/Users/path/to/magento/app/code/core/Mage/Catalog/Model/Product/Type/Configurable.php</b> on line <b>267</b><br />

I’ve had that too. I think it’s because a misconfiguration of an attribute. ie when the collumn config_attributes has a non existent attribute (or that of a simple attribute) listed.
I couldn’t get the attribute OK again. My best solution also was to delete the products , change the csv (or attributes in admin) and try again.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Quinty
Jr. Member
 
Total Posts:  12
Joined:  2010-11-15
 

Another tip, when you try to import an item for more than one gender, you have to take the separator of magento in mind.
It\’s this constant
// app/code/core/Mage/Catalog/Model/Convert/Adapter/Product.php
const MULTI_DELIMITER = ‘ , ‘;

Mind the space! A field with “Men,Women” won’t work, but “Men , Women” will.
This delimiter probably influences all “multiselect” attributes.

 
Magento Community Magento Community
Magento Community
Magento Community
 
emmenko
Sr. Member
 
Total Posts:  96
Joined:  2010-10-20
 
Quinty - 15 November 2010 04:15 AM

Another tip, when you try to import an item for more than one gender, you have to take the separator of magento in mind.
It\’s this constant
// app/code/core/Mage/Catalog/Model/Convert/Adapter/Product.php
const MULTI_DELIMITER = ‘ , ‘;

Mind the space! A field with “Men,Women” won’t work, but “Men , Women” will.
This delimiter probably influences all “multiselect” attributes.

I know that...thx anyway for the tipp smile

But my problem isn’t in the import...in the backend I can import products without problems, even with associated prices!

The problem right now is in the cron configuration.
As you all know, amartinez has implemented the script Productimport.php (thx again for that) and 3 other files for the cron job: cron_import_products, cron_refresh_cache, cron_import_customers.

With the new magento version (I use 1.4.1.1) some of these files doesn’t work properly without changes...in the last days I’ve tried to configure the cron_import_products.php but I got always errors (to a detailed discussion about this problem, please click on this link).
I contacted amartinez and he told me he would have take a look at the code...I’m still waiting (and hoping) that he finds a solution.

About the cron_refresh_cache, this file is not more needed with the version 1.4+ because of the the implemented script that magento offers...in the magento/shell/ directory there is a file called “indexer.php” (for more infos, see this guide by Yireo).
This works with a cron job without problems....

Nicola

 
Magento Community Magento Community
Magento Community
Magento Community
 
Quinty
Jr. Member
 
Total Posts:  12
Joined:  2010-11-15
 
emmenko - 15 November 2010 05:02 AM

I doubt that it’s the “cron” part that doesn’t work, it’s an attribute that can’t get processed. In Magento (core) not at all places those variables or objects are verified before they are called with methods like getId(), that’s what ‘s causing the fatal error.

I discovered Magento recently. It’s a shame these import features aren’t implemented in the core.. So many thanks to amartinez!

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top