Import Customer and Address ps1.7

[flyerkhong]

Hello everybody,

I'm having 3 million of customers to import, but if import in back-office will take a lot of time, so I'm trying to import Customer and Address through sql database directly.

may i know which table i should import in sql database?

for your information, i have tried to import only ps_customer table but it is having error during editing in back office.

 

Thank you and appreciate

[EvaF]

something like that (customize fields, id_lang and output file)

 

SELECT "id_customer", "id_gender", "id_lang", "company", "firstname", "lastname", "email", "active", "is_guest",
"id_country","country", "acompany", "alastname", "afirstname" ,
"address1", "address2", "postcode", "city", "phone", "phone_mobile", "vat_number", "aactive", "adeleted" 
union all

select * from(
   
  SELECT c.`id_customer`, c.`id_gender`, c.`id_lang`, if(c.`company` is null,"",c.`company`) , c.`firstname`, c.lastname, c.`email`, c.`active`, c.`is_guest`,
a.`id_country`,cl.name, if(a.`company` is null,"",a.`company`), a.`lastname` as alastname, a.`firstname` as afirstname,
  `address1`, if(a.`address2` is null,"",a.`address2`) , `postcode`, `city`, `phone`, `phone_mobile`, `vat_number`, a.`active` as aactive, a.`deleted` as adeleted 

  FROM `ps_customer` c 

  join `ps_address` a on c.id_customer = a.id_customer
  join ps_country_lang cl on cl.id_country = a.id_country and cl.id_lang= 1
  WHERE a.deleted=0 and a.active=1 and c.deleted = 0 and c.active=1
  order by cl.name, c.lastname,c.firstname
) as cust

INTO OUTFILE '/tmp/customers.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

Edited March 24, 2020 by EvaF (see edit history)

[flyerkhong]

19 minutes ago, EvaF said:

something like that (customize fields, id_lang and output file)

 

SELECT "id_customer", "id_gender", "id_lang", "company", "firstname", "lastname", "email", "active", "is_guest",
"id_country","country", "acompany", "alastname", "afirstname" ,
"address1", "address2", "postcode", "city", "phone", "phone_mobile", "vat_number", "aactive", "adeleted" 
union all

select * from(
   
  SELECT c.`id_customer`, c.`id_gender`, c.`id_lang`, if(c.`company` is null,"",c.`company`) , c.`firstname`, c.lastname, c.`email`, c.`active`, c.`is_guest`,
a.`id_country`,cl.name, if(a.`company` is null,"",a.`company`), a.`lastname` as alastname, a.`firstname` as afirstname,
  `address1`, if(a.`address2` is null,"",a.`address2`) , `postcode`, `city`, `phone`, `phone_mobile`, `vat_number`, a.`active` as aactive, a.`deleted` as adeleted 

  FROM `ps_customer` c 

  join `ps_address` a on c.id_customer = a.id_customer
  join ps_country_lang cl on cl.id_country = a.id_country and cl.id_lang= 1
  WHERE a.deleted=0 and a.active=1 and c.deleted = 0 and c.active=1
  order by cl.name, c.lastname,c.firstname
) as cust

INTO OUTFILE '/tmp/customers.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

Hi EvaF,

Appreciate for the guide.

Do you able to attach a “customer.csv" and "address.csv" sample file for me?

i should put the csv file in which folder?

Thank you and appreciate

 

 

[EvaF]

t depends where you are working with sql (my example stores both ones(customers+their address) into one file csv at linux  )
if you work f.e. in phpmyadmin you can ommit the part INTO OUTFILE
paste  the query into SQL tabsheet

SELECT "id_customer", "id_gender", "id_lang", "company", "firstname", "lastname", "email", "active", "is_guest",
"id_country","country", "acompany", "alastname", "afirstname" ,
"address1", "address2", "postcode", "city", "phone", "phone_mobile", "vat_number", "aactive", "adeleted" 
union all

select * from(
   
  SELECT c.`id_customer`, c.`id_gender`, c.`id_lang`, if(c.`company` is null,"",c.`company`) , c.`firstname`, c.lastname, c.`email`, c.`active`, c.`is_guest`,
a.`id_country`,cl.name, if(a.`company` is null,"",a.`company`), a.`lastname` as alastname, a.`firstname` as afirstname,
  `address1`, if(a.`address2` is null,"",a.`address2`) , `postcode`, `city`, `phone`, `phone_mobile`, `vat_number`, a.`active` as aactive, a.`deleted` as adeleted 

  FROM `ps_customer` c 

  join `ps_address` a on c.id_customer = a.id_customer
  join ps_country_lang cl on cl.id_country = a.id_country and cl.id_lang= 1
  WHERE a.deleted=0 and a.active=1 and c.deleted = 0 and c.active=1
  order by cl.name, c.lastname,c.firstname,c.id_customer
) as cust

process query and  after that select "Export" bellow of lines. In Export  select the format you need

[flyerkhong]

24 minutes ago, EvaF said:

t depends where you are working with sql (my example stores both ones(customers+their address) into one file csv at linux  )
if you work f.e. in phpmyadmin you can ommit the part INTO OUTFILE
paste  the query into SQL tabsheet

SELECT "id_customer", "id_gender", "id_lang", "company", "firstname", "lastname", "email", "active", "is_guest",
"id_country","country", "acompany", "alastname", "afirstname" ,
"address1", "address2", "postcode", "city", "phone", "phone_mobile", "vat_number", "aactive", "adeleted" 
union all

select * from(
   
  SELECT c.`id_customer`, c.`id_gender`, c.`id_lang`, if(c.`company` is null,"",c.`company`) , c.`firstname`, c.lastname, c.`email`, c.`active`, c.`is_guest`,
a.`id_country`,cl.name, if(a.`company` is null,"",a.`company`), a.`lastname` as alastname, a.`firstname` as afirstname,
  `address1`, if(a.`address2` is null,"",a.`address2`) , `postcode`, `city`, `phone`, `phone_mobile`, `vat_number`, a.`active` as aactive, a.`deleted` as adeleted 

  FROM `ps_customer` c 

  join `ps_address` a on c.id_customer = a.id_customer
  join ps_country_lang cl on cl.id_country = a.id_country and cl.id_lang= 1
  WHERE a.deleted=0 and a.active=1 and c.deleted = 0 and c.active=1
  order by cl.name, c.lastname,c.firstname,c.id_customer
) as cust

process query and  after that select "Export" bellow of lines. In Export  select the format you need

Hi,

Thank you and appreciate, i try it now.

If my customer content have "dni“ info to import, i should add "dni" column in which part of the import query and export query?

 

Thank you and appreciate

 

[EvaF]

SELECT "id_customer", "id_gender", "id_lang", "company", "firstname", "lastname", "email", "active", "is_guest",
"id_country","country", "acompany", "alastname", "afirstname" ,"dni"
"address1", "address2", "postcode", "city", "phone", "phone_mobile", "vat_number", "aactive", "adeleted" 
union all

select * from(
   
  SELECT c.`id_customer`, c.`id_gender`, c.`id_lang`, if(c.`company` is null,"",c.`company`) , c.`firstname`, c.lastname, c.`email`, c.`active`, c.`is_guest`,
a.`id_country`,cl.name, if(a.`company` is null,"",a.`company`), a.`lastname` as alastname, a.`firstname` as afirstname, if(a.`dni` is null,"",a.`dni`),
  `address1`, if(a.`address2` is null,"",a.`address2`) , `postcode`, `city`, `phone`, `phone_mobile`, `vat_number`, a.`active` as aactive, a.`deleted` as adeleted 

  FROM `ps_customer` c 

  join `ps_address` a on c.id_customer = a.id_customer
  join ps_country_lang cl on cl.id_country = a.id_country and cl.id_lang= 1
  WHERE a.deleted=0 and a.active=1 and c.deleted = 0 and c.active=1
  order by cl.name, c.lastname,c.firstname,c.id_customer
) as cust

 

[flyerkhong]

1 hour ago, EvaF said:

SELECT "id_customer", "id_gender", "id_lang", "company", "firstname", "lastname", "email", "active", "is_guest",
"id_country","country", "acompany", "alastname", "afirstname" ,"dni"
"address1", "address2", "postcode", "city", "phone", "phone_mobile", "vat_number", "aactive", "adeleted" 
union all

select * from(
   
  SELECT c.`id_customer`, c.`id_gender`, c.`id_lang`, if(c.`company` is null,"",c.`company`) , c.`firstname`, c.lastname, c.`email`, c.`active`, c.`is_guest`,
a.`id_country`,cl.name, if(a.`company` is null,"",a.`company`), a.`lastname` as alastname, a.`firstname` as afirstname, if(a.`dni` is null,"",a.`dni`),
  `address1`, if(a.`address2` is null,"",a.`address2`) , `postcode`, `city`, `phone`, `phone_mobile`, `vat_number`, a.`active` as aactive, a.`deleted` as adeleted 

  FROM `ps_customer` c 

  join `ps_address` a on c.id_customer = a.id_customer
  join ps_country_lang cl on cl.id_country = a.id_country and cl.id_lang= 1
  WHERE a.deleted=0 and a.active=1 and c.deleted = 0 and c.active=1
  order by cl.name, c.lastname,c.firstname,c.id_customer
) as cust

 

Hi,

thank you for the export query code. i have successfully export the csv file and completed to fill-up the csv file with new customer content.

i'm trying to import the csv file back to phpmyadmin, may i know what should i do to import the csv file?

 

Thank you and appreciate

[EvaF]

you can step using Configure->Advanced Parameter ->Import

(do not forget that exported csv is created from two tables ps_address and ps_customer)

but there is not problem to split it into two files ( keep only customers columns and save as customers.csv and keep address columns and save as address.csv)

 

[flyerkhong]

4 minutes ago, EvaF said:

you can step using Configure->Advanced Parameter ->Import

(do not forget that exported csv is created from two tables ps_address and ps_customer)

but there is not problem to split it into two files ( keep only customers columns and save as customers.csv and keep address columns and save as address.csv)

 

do it able to import directly through phpmyadmin? because i tried to import by using "Configure->Advanced Parameter ->Import" but it will error and stop around row 2000 - 3000

maybe because i have 3 million row of customers to import, it take too much time to import. if import directly through phpmyadmin will be faster.

may i know how to import "customer and address" through phpmyadmin? i can update for all related table.

 

Thank you and appreciate

[EvaF]

of course you can - google "mysql import csv" - there are many tutorials how to step

[flyerkhong]

Just now, EvaF said:

of course you can - google "mysql import csv" - there are many tutorials how to step

Hi,

if we import directly through phpmyadmin, may i know what related table we should import?

because i tried to import customer table only but it show error if edit customer in back-office.

 

Thank you and appreciate

[EvaF]

I don't know what you did with csv data - but answer is easy - if you changed data, you have to update data in ps_... tables ( and in this case there is missing id_address key in csv file) - thus  you can only update customers data
if you added data, then you can insert data ( but in this case I would recommended to generate id_customer  - in order to you don't troubles with addresses
id_customer can increase linearly (min generated id_customer should be equal)
 

SELECT Max(id_customer) + 1 FROM ps_customer

but I am stritcly recomending to create copy of tables befoore you try to  change

[flyerkhong]

3 hours ago, EvaF said:

I don't know what you did with csv data - but answer is easy - if you changed data, you have to update data in ps_... tables ( and in this case there is missing id_address key in csv file) - thus  you can only update customers data
if you added data, then you can insert data ( but in this case I would recommended to generate id_customer  - in order to you don't troubles with addresses
id_customer can increase linearly (min generated id_customer should be equal)
 

SELECT Max(id_customer) + 1 FROM ps_customer

but I am stritcly recomending to create copy of tables befoore you try to  change

Hi,

is great, already successfully import.

thank you and appreciate for help

 

thank you