In [ ]:
import pandas as pd
import numpy as np
Load information we save from part 1 and drop unused column.
In [ ]:
#Load a raw information
raw_information = pd.read_csv('sql_prestashop/raw_information.csv', index_col='id_customer')
raw_information = raw_information.drop(['Unnamed: 0'], axis=1)
Important thing to note here. We must download wp_users and wp_usermeta to our computer first and use them to collect data. If you attemp to import wp_users without backup, the admin username and password will change. You can't login into Woocommerce again.
When we work on our Woocommerce store, there is only admin. We download the wp_usermeta and change its name to 'admin_detail'. The follow customer (begin at ID = 2) will be store in form.
In [ ]:
#When installing woocommerce admin username and password maybe change.
#We separate usermeta into 2 files
#1) admin_detail: the information of admin in new website only ID 1
#2) form: the customer detail begin at ID 2
#Load admin_detail
admin_detail = pd.read_csv('sql_prestashop/admin_detail.csv', header=None, index_col=0)
admin_detail = admin_detail.rename(columns = {1:'id_customer'})
Load usermeta form. We create this form by create one dummy customer. Fill the important information. Then download the new wp_usermeta. We delete the information of admin, using only customer data. This form will use to fill all customer detail.
In [ ]:
#Load usermeta form
form = pd.read_csv('sql_prestashop/usermeta_form_short.csv', header=None, index_col=0)
form = form.rename(columns = {1:'id_customer'})
#reset all value to nan
form[3] = np.nan
We run the for loop to fill in our usermeta form. The wp_usermeta is the detail of each user collect in row. We must load usermeta form we create, run current user id and then fill each row of the form with our information from information we have gathered in part 1.
The important information here is:
1) firstname
2) lastname
3) address1
4) city
5) postcode
6) country
7) email
8) phone_mobile
9) customer role (the 3 groups 18+, vendors and customers we created in part 1)
3) - 8) are filling in billing address. Woocommerce has a function to select shipping address to be the same as billing address, so we choose to fill only one. That is billing address.
Then in the end of each for loop, we append our edited wp_usermeta form to admin_detail.
In [5]:
#Create new usermeta using "information" to fill in
for x in raw_information.index[1:]:
#Fill lastest id in form
form['id_customer'] = x
#Start to fill in
#form.iloc[0,2] = np.nan
form.iloc[0,2] = raw_information.loc[x,'firstname']
form.iloc[1,2] = raw_information.loc[x,'lastname']
#form.iloc[10,2] = a:1:{s:13:"administrator";b:1;}
form.iloc[3,2] = 0
form.iloc[4,2] = raw_information.loc[x,'firstname']
form.iloc[5,2] = raw_information.loc[x,'lastname']
form.iloc[6,2] = raw_information.loc[x,'address1']
form.iloc[7,2] = raw_information.loc[x,'city']
form.iloc[9,2] = raw_information.loc[x,'postcode']
form.iloc[10,2] = raw_information.loc[x,'country']
form.iloc[11,2] = raw_information.loc[x,'email']
form.iloc[12,2] = raw_information.loc[x,'phone_mobile']
#Create customer role
if raw_information.loc[x,'id_group'] == 6:
form.iloc[2,2] = 'a:1:{s:6:"vendor";b:1;}'
if raw_information.loc[x,'id_group'] == 4:
form.iloc[2,2] = 'a:1:{s:10:"subscriber";b:1;}'
if raw_information.loc[x,'id_group'] == 3:
form.iloc[2,2] = 'a:1:{s:8:"customer";b:1;}'
admin_detail = admin_detail.append(form)
The index numbers are not continuous. We arrange them.
In [6]:
#The index of result array is not continuous
admin_detail = admin_detail.set_index(np.arange(1,admin_detail.shape[0]+1))
Save the new wp_meta to .csv file. Ready to import.
In [ ]:
#save new wp_usermeta to .csv
admin_detail.to_csv('customer_import_to_woo/raw_wp_usermeta.csv', header=False, encoding='utf-8')
Next we create the wp_users file. We get into the database and download wp_users which has only admin data. Rename as "wp_users_form".
In [7]:
admin_from_wp_users = pd.read_csv('sql_prestashop/wp_users_form.csv', header=None)
admin_from_wp_users = admin_from_wp_users.rename(columns = {0:'ID', 1:'user_login', 4:'user_email', 9:'display_name'})
Create empty dataframe which has index equal to "raw_information" and 10 columns. Set all element to nan value. Then rename the coloumn to match the wp_users file of Woocommerce.
In [8]:
#Create dataframe for arrange "information" into wp_users format
information_to_wp_users = pd.DataFrame(np.empty((raw_information.shape[0],10)))
information_to_wp_users[:] = np.NAN
information_to_wp_users = information_to_wp_users.rename(columns = {0:'ID', 1:'user_login', 4:'user_email', 9:'display_name'})
The raw information index is already a "id_customer". We set "raw_information" index to be index of our new create dataframe.
In [9]:
#Set index equal to the index from "raw_information". Protect miss matching
#the data with wrong ID
information_to_wp_users.index = raw_information.index
Fill the important data of user. Then set "ID" column equal to the index (because both are the "id_customer").
In [10]:
#Filling the data
information_to_wp_users['user_login'] = raw_information['email']
information_to_wp_users['user_email'] = raw_information['email']
information_to_wp_users['display_name'] = raw_information['firstname']
#Extract 'ID' to be column
information_to_wp_users['ID'] = raw_information.index.values
Fill the first index by the data of admin in wp_users.
In [11]:
#Adding the admin data
information_to_wp_users.loc[0] = admin_from_wp_users.loc[0]
Save "information_to_wp_users" as .csv file.
In [ ]:
#save new wp_users to .csv
information_to_wp_users.to_csv('customer_import_to_woo/raw_wp_users.csv', header=False, index=False, encoding='utf-8')
Finally, we have 2 new files "raw_wp_usermeta.csv" and "raw_wp_users.csv". We import "raw_wp_users.csv" replace "wp_users" on database. This will cause no problem. Then we import "raw_wp_usermeta.csv" replace "wp_usermeta", you may found an alert that the upload has timeout because this file is too big. Upload again with the same file 3-4 times may solve this problem.