Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

[SOLVED] Import a few thousand customer using MySQL
 
jschmahl
Jr. Member
 
Total Posts:  17
Joined:  2010-09-24
 

Hi folks,

I have to import some customer from an old shop to magento 1.7. But the import via SOAP or CSV is too slow (25minutes / 2500 customer of 265k).
SO thats why I’m want to try the import using the MySQL, But there are a lot of tables with some data here and some data there. Can anyone give me a little help to create a master query?

Thank you for your help and greetings
jschmahl

-----------------------------------------------------------------------------------------------------------------------

I’ve got it an my own =)

Here is the sample SQL for everyone who need this in the future:

/* Increment the Customer ID */
/* LPAD keeps the leading zeros */
UPDATE `eav_entity_storeSET `increment_last_id` = LPAD( `increment_last_id`+19'0'WHERE `entity_store_id` = 4 LIMIT 1 ;

/* Save incremented Customer ID in variable */
SET @customerId = (SELECT `increment_last_idFROM `eav_entity_storeWHERE `entity_store_id` = 4 LIMIT 1);

/* create customer */
/*
    entity_type_id = 1 => is the entity code for customer
    website_id = 1 => ID of the website
    group_id = 1 => default value for the group General
    increment_id = @customerId => see eav_entity_store
    store_id = 1 => ID of the store
    is_active = 1 => is customer usable
    disable_auto_group_change = 1 => customer doesn't switch the group on enter a VAT Id
*/
INSERT INTO `customer_entity` (`entity_type_id`, `website_id`, `email`, `group_id`, `increment_id`, `store_id`, `is_active`, `disable_auto_group_change`) VALUES
(11'email@address.com'1, @customerId011);

/* Save incremented Customer ID in variable */
SET @customerEntityId = (SELECT `entity_idFROM `customer_entityWHERE `email` = 'email@address.com' LIMIT 1);

/* defines customer values - INTEGER */
/*
    attribute_id => 18 = gender
    value => gender (1 = male, 2 = female)
*/
INSERT INTO `customer_entity_int` (`entity_type_id`, `attribute_id`, `entity_id`, `value`) VALUES
(118, @customerEntityId2);

/* defines customer values - VARCHAR */
/* 
    attribute_id => 3 = created_in, 4 = prefix, 5 = firstname, 7 = lastname, 12 = password_hash
*/
INSERT INTO `customer_entity_varchar` (`entity_type_id`, `attribute_id`, `entity_id`, `value`) VALUES
(15, @customerEntityId'firstname'),
(
17, @customerEntityId'lastname'),
(
112, @customerEntityId'password_hash:'),
(
13, @customerEntityId'Admin'),
(
14, @customerEntityId'prefix');

If you need more values in the “customer_entity_XYZ” you can simple add an additional line. You can find a list with nearly all attributes on the table “eav_attribute”. If you like to add an address you have to add entries in the tables:
- customer_address_entity
- customer_address_entity_int
- customer_address_entity_text
- customer_address_entity_varchar

I hope it helps someone
Lomion

 
Magento Community Magento Community
Magento Community
Magento Community
 
jschmahl
Jr. Member
 
Total Posts:  17
Joined:  2010-09-24
 

I’ve got it an my own =)

Here is the sample SQL for everyone who need this in the future:

/* Increment the Customer ID */
/* LPAD keeps the leading zeros */
UPDATE `eav_entity_storeSET `increment_last_id` = LPAD( `increment_last_id`+19'0'WHERE `entity_store_id` = 4 LIMIT 1 ;

/* Save incremented Customer ID in variable */
SET @customerId = (SELECT `increment_last_idFROM `eav_entity_storeWHERE `entity_store_id` = 4 LIMIT 1);

/* create customer */
/*
    entity_type_id = 1 => is the entity code for customer
    website_id = 1 => ID of the website
    group_id = 1 => default value for the group General
    increment_id = @customerId => see eav_entity_store
    store_id = 1 => ID of the store
    is_active = 1 => is customer usable
    disable_auto_group_change = 1 => customer doesn't switch the group on enter a VAT Id
*/
INSERT INTO `customer_entity` (`entity_type_id`, `website_id`, `email`, `group_id`, `increment_id`, `store_id`, `is_active`, `disable_auto_group_change`) VALUES
(11'email@address.com'1, @customerId011);

/* Save incremented Customer ID in variable */
SET @customerEntityId = (SELECT `entity_idFROM `customer_entityWHERE `email` = 'email@address.com' LIMIT 1);

/* defines customer values - INTEGER */
/*
    attribute_id => 18 = gender
    value => gender (1 = male, 2 = female)
*/
INSERT INTO `customer_entity_int` (`entity_type_id`, `attribute_id`, `entity_id`, `value`) VALUES
(118, @customerEntityId2);

/* defines customer values - VARCHAR */
/* 
    attribute_id => 3 = created_in, 4 = prefix, 5 = firstname, 7 = lastname, 12 = password_hash
*/
INSERT INTO `customer_entity_varchar` (`entity_type_id`, `attribute_id`, `entity_id`, `value`) VALUES
(15, @customerEntityId'firstname'),
(
17, @customerEntityId'lastname'),
(
112, @customerEntityId'password_hash:'),
(
13, @customerEntityId'Admin'),
(
14, @customerEntityId'prefix');

If you need more values in the “customer_entity_XYZ” you can simple add an additional line. You can find a list with nearly all attributes on the table “eav_attribute”. If you like to add an address you have to add entries in the tables:
- customer_address_entity
- customer_address_entity_int
- customer_address_entity_text
- customer_address_entity_varchar

I hope it helps someone
Lomion

 
Magento Community Magento Community
Magento Community
Magento Community
 
jschmahl
Jr. Member
 
Total Posts:  17
Joined:  2010-09-24
 

I’ve got it an my own =)

Here is the sample SQL for everyone who need this in the future:

/* Increment the Customer ID */
/* LPAD keeps the leading zeros */
UPDATE `eav_entity_storeSET `increment_last_id` = LPAD( `increment_last_id`+19'0'WHERE `entity_store_id` = 4 LIMIT 1 ;

/* Save incremented Customer ID in variable */
SET @customerId = (SELECT `increment_last_idFROM `eav_entity_storeWHERE `entity_store_id` = 4 LIMIT 1);

/* create customer */
/*
    entity_type_id = 1 => is the entity code for customer
    website_id = 1 => ID of the website
    group_id = 1 => default value for the group General
    increment_id = @customerId => see eav_entity_store
    store_id = 1 => ID of the store
    is_active = 1 => is customer usable
    disable_auto_group_change = 1 => customer doesn't switch the group on enter a VAT Id
*/
INSERT INTO `customer_entity` (`entity_type_id`, `website_id`, `email`, `group_id`, `increment_id`, `store_id`, `is_active`, `disable_auto_group_change`) VALUES
(11'email@address.com'1, @customerId011);

/* Save incremented Customer ID in variable */
SET @customerEntityId = (SELECT `entity_idFROM `customer_entityWHERE `email` = 'email@address.com' LIMIT 1);

/* defines customer values - INTEGER */
/*
    attribute_id => 18 = gender
    value => gender (1 = male, 2 = female)
*/
INSERT INTO `customer_entity_int` (`entity_type_id`, `attribute_id`, `entity_id`, `value`) VALUES
(118, @customerEntityId2);

/* defines customer values - VARCHAR */
/* 
    attribute_id => 3 = created_in, 4 = prefix, 5 = firstname, 7 = lastname, 12 = password_hash
*/
INSERT INTO `customer_entity_varchar` (`entity_type_id`, `attribute_id`, `entity_id`, `value`) VALUES
(15, @customerEntityId'firstname'),
(
17, @customerEntityId'lastname'),
(
112, @customerEntityId'password_hash:'),
(
13, @customerEntityId'Admin'),
(
14, @customerEntityId'prefix');

If you need more values in the “customer_entity_XYZ” you can simple add an additional line. You can find a list with nearly all attributes on the table “eav_attribute”. If you like to add an address you have to add entries in the tables:
- customer_address_entity
- customer_address_entity_int
- customer_address_entity_text
- customer_address_entity_varchar

I hope it helps someone
Lomion

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