Magento 1.9 – How to Update Bulk Customer Address Information

customer-addressmagento-1.9MySQL

I have one requirement to update the customer first name and last name in address information section. I tried through csv file import/export method but it only update in account information section not in address information such as billing and shipping address in admin panel(customer->manage customer section).

Currently few records in customer information section are present with lastname combine in firstname section (e.g firstname:- testtest1 and lastname:- blank instead of firstname:-test and lastname: test1)

and I want to split the firstname and lastname separately in address information section too.

So how should I proceed to solve this query to my bulk of customer.

Best Answer

Go admin side System -> Import/Export -> Export and Export all customer with all data

After your exported csv file updates bellow header fields data

_address_firstname

_address_lastname

Go admin side System -> Import/Export -> Import and upload your updated csv file

Make bellow settings :

Entity Type * : Customer
Import Behavior *: Replace Existing Complex Data
Select File to Import *: upload updated file

After click Check Data button if no error then click import button

Note : first backup your database

Related Solutions

SQL Statement for Customer Email and Address in Magento

Here is your SQL (the first part loads users with addresses, second without addresses):

select customer_entity.email, first_name.value as first_name, last_name.value as last_name,

  CONCAT_WS(', ',
       NULLIF(address_first_name.value, ''),
       NULLIF(address_last_name.value, ''),
       NULLIF(address_street.value, ''),
       NULLIF(address_city.value, ''),
       NULLIF(address_postcode.value, ''),
       NULLIF(address_region.value, '')
  ) AS addresses,

  password_hash.value as password_hash,
  IFNULL(newsletter_subscriber.subscriber_status, 0) as subscription_flag

from customer_entity

join customer_entity_varchar as first_name on customer_entity.entity_id = first_name.entity_id
join customer_entity_varchar as last_name on customer_entity.entity_id = last_name.entity_id
join customer_entity_varchar as password_hash on customer_entity.entity_id = password_hash.entity_id

left join newsletter_subscriber on customer_entity.entity_id = newsletter_subscriber.customer_id

join customer_address_entity as address_address on customer_entity.entity_id = address_address.parent_id
join customer_address_entity_varchar as address_first_name on address_address.entity_id = address_first_name.entity_id
join customer_address_entity_varchar as address_last_name on address_address.entity_id = address_last_name.entity_id
join customer_address_entity_text as address_street on address_address.entity_id = address_street.entity_id
join customer_address_entity_varchar as address_city on address_address.entity_id = address_city.entity_id
join customer_address_entity_varchar as address_postcode on address_address.entity_id = address_postcode.entity_id
join customer_address_entity_varchar as address_region on address_address.entity_id = address_region.entity_id

where first_name.entity_type_id = 1 AND first_name.attribute_id = 5
AND last_name.entity_type_id = 1 AND last_name.attribute_id = 7
AND password_hash.entity_type_id = 1 AND password_hash.attribute_id = 12

AND address_first_name.entity_type_id = 2 AND address_first_name.attribute_id = 20
AND address_last_name.entity_type_id = 2 AND address_last_name.attribute_id = 22
AND address_street.entity_type_id = 2 AND address_street.attribute_id = 25
AND address_city.entity_type_id = 2 AND address_city.attribute_id = 26
AND address_postcode.entity_type_id = 2 AND address_postcode.attribute_id = 30
AND address_region.entity_type_id = 2 AND address_region.attribute_id = 28


UNION


select customer_entity.email, first_name.value as first_name, last_name.value as last_name, '' AS addresses,
password_hash.value as password_hash,
IFNULL(newsletter_subscriber.subscriber_status, 0) as subscription_flag

from customer_entity

join customer_entity_varchar as first_name on customer_entity.entity_id = first_name.entity_id
join customer_entity_varchar as last_name on customer_entity.entity_id = last_name.entity_id
join customer_entity_varchar as password_hash on customer_entity.entity_id = password_hash.entity_id

left join newsletter_subscriber on customer_entity.entity_id = newsletter_subscriber.customer_id

left join customer_address_entity as address_address on customer_entity.entity_id = address_address.parent_id

where first_name.entity_type_id = 1 AND first_name.attribute_id = 5
AND last_name.entity_type_id = 1 AND last_name.attribute_id = 7
AND password_hash.entity_type_id = 1 AND password_hash.attribute_id = 12

AND address_address.parent_id IS NULL

Magento 1.9 – Can’t Pass Billing Step in Checkout Page

i think this error does have its origin in some eav attribute validation in the saveBillingAction like here: https://github.com/bragento/magento-core/blob/1264aee821b451b3c88e55659cd1ee79d3b324af/app/code/core/Mage/Checkout/Model/Type/Onepage.php#L284

I'm not 100% sure if this error comes from this, but you could check the eav_attribute table.

The customer should have the eav_entity_type id 1 (at least for me) and the custoemr address should have 2. (you can also look them up in the eav_entity_type table).

In the eav attribute table, theres a column 'is_required'. Although, its weird, that the attribute is empty in the message, you might find an attribute here, that is required (and then validated) in the table but not filled in in your checkout process.

It might also be a cache issue. If I'm recognizing corretly, there is a nasty cache for the eav attributes that could actually be responsible for such behaviour (given you changed or maybe deleted a customer or address attribute recently)

Best Answer

Related Solutions

SQL Statement for Customer Email and Address in Magento

Magento 1.9 – Can’t Pass Billing Step in Checkout Page

Related Topic