Reebelo is all about upcycling. We were the middlemen. We brought refurbished devices from different stores to the customers. However, in order to do this, we had to fulfil each and every order from the multiple stores that we hosted on our platform. Since Janio was our chosen solution to deliver these products, the shipping label had to be generated for every order and sent to the right contact from each 3rd party company we worked with. This would be managed through WhatsApp. All the vendors will indicate the orders that are ready to ship to customers. Initially, this fulfilment had to be done one by one. However, when they were a lot of volume of orders being fulfilled, a better way had to be found.
The solution used Python & TagUI (helps you rapidly automate your repetitive or time-critical tasks – use cases include process automation, data acquisition and testing of web apps. The goal of user interface (UI) automation is to reproduce cognitive interactions that you have with websites or on your desktop.)
MasterCreatorAutomation.cmd was a shortcut program that forced a TagUI script to run. The script will automatically download all unfulfilled orders from Shopify and save them into a folder. After, the python script ‘MasterCreator.py’ would create a master list of all the orders and format it into data frames to assist with the last step.
import os, os.path
import tkinter as tk
import pandas as pd
import shutil
from tkinter import simpledialog
from tkinter import messagebox
from tkinter import filedialog as fd
#os.rename('C:/tagui/flows/FirstFlow/orders_export_1.csv', 'C:/Users/Verghese/Desktop/orders_export_1.csv')
#shutil.move('C:/tagui/flows/FirstFlow/orders_export_1.csv', 'C:/Users/Verghese/Desktop/orders_export_1.csv')
#Pop up on error
def ErrorMessage():
messagebox.showinfo("Error", "Please check if the address is valid!")
#User selects file to extract name
#fullPath = fd.askopenfilename()
#Path for Shopify File
filedirectory = "C:/Users/Verghese/Desktop/BulkUploadJanio/orders_export_1.csv"
#Path for GW Janio File
filedirectoryJanio = "C:/Users/Verghese/Desktop/BulkUploadJanio/GWTemplate.csv"
#Create DataFrame with the CSV File
df = pd.read_csv(filedirectory,encoding='utf-8')
new_df=pd.read_csv(filedirectoryJanio,encoding='utf-8')
#Extracting required columns and rearranging them
df = df[['Name','Lineitem name','Lineitem quantity','Lineitem sku','Lineitem price','Shipping Name','Shipping Phone','Shipping Street','Shipping Zip','Email']]
#df.rename({'Name':'shipper_order_id','Lineitem name':'item_desc (description)','Lineitem quantity':'item_quantity','Lineitem sku':'item_sku','Lineitem price':'item_price_value (max 2 decimal places)','Shipping Name':'consignee_name ','Shipping Phone':'consignee_number','Shipping Street':'consignee_address','Shipping Zip':'consignee_postal','Email':'consignee_email (optional - unless consignee wants to receive email notifications)'})
#Shopify_list_title = [df.columns.values.tolist()]+ df.values.tolist()
#Shopify_list_title = [df.columns.values.tolist()]
#New Dataframe with Shopify and Janio Template
Alldf=new_df.append(df)
#Formatting Janio Waybill Template
Alldf['shipper_order_id (optional)'] = Alldf['Name']
Alldf['item_desc (description)'] = Alldf['Lineitem name']
Alldf['item_quantity'] = Alldf['Lineitem quantity']
Alldf['item_sku (optional)'] = Alldf['Lineitem sku']
Alldf['item_price_value (max 2 decimal places)'] = Alldf['Lineitem price']
Alldf['consignee_name'] = Alldf['Shipping Name']
Alldf['consignee_number'] = Alldf['Shipping Phone']
Alldf['consignee_address'] = Alldf['Shipping Street']
Alldf['consignee_postal'] = Alldf['Shipping Zip']
Alldf['consignee_email (optional - unless consignee wants to receive email notifications)'] = Alldf['Email']
Alldf["item_price_currency ('SGD' 'USD' etc)"] = 'SGD'
Alldf["pickup_country (must be in full spelling such as 'Singapore')"] = 'Singapore'
Alldf["consignee_country (must be in full spelling such as 'Singapore')"] = 'Singapore'
Alldf["consignee_state"] = 'Singapore'
Alldf["order_width (cm - max 2 decimal places)"] = '10'
Alldf["order_length (cm - max 2 decimal places)"] = '10'
Alldf["order_height (cm - max 2 decimal places)"] = '10'
Alldf["order_weight (kg - max 2 decimal places)"] = '1'
Alldf["payment_type (one of these: 'prepaid' 'cod')"] = 'prepaid'
Alldf["item_category (one of these: 'Fashion Apparel' 'Electronics' 'Others' 'Printed Matters' 'Lifestyle Products')"] = 'Electronics'
#Clean up the data
Alldf['consignee_postal'] = Alldf['consignee_postal'].str[1:]
Alldf['shipper_order_id (optional)'] = Alldf['shipper_order_id (optional)'].str[1:]
#Delete Unnecessary Columns
Alldf.drop(Alldf.columns[[27,28,29,30,31,32,33,34,35,36]], axis = 1, inplace = True)
#Write into CSV
Alldf.to_csv('Desktop/BulkUploadJanio/masterz.csv', encoding='utf-8', index=False)
The last step would be to run ‘WritingFromMaster.py’. This will create a popup where you can export the required orders into a CSV file ready to be uploaded into Janio. Janio with the CSV file will create respective shipping labels that will be sent out to the 3rd party companies to prepare the products for shipment.
import os, os.path
import csv
import tkinter as tk
import pandas as pd
from tkinter import simpledialog
import re
from tkinter import messagebox
#Creating Popup Window for User
application_window = tk.Tk()
application_window.withdraw()
#Get User's Multiple Waybill Inputs (max 10)
x = [int(x) for x in simpledialog.askstring("Input", "Please enter Waybill numbers",parent=application_window).split(",")]
number = len(x)
if number > 10:
messagebox.showerror("Error!", "Please enter less than 10 entries!")
x = [int(x) for x in simpledialog.askstring("Input", "Please enter Waybill numbers",parent=application_window).split(",")]
number = len(x)
#Debugging
print("Number of list is: ", x)
#Regex Check on Address
pattern = re.sub(\\d{2}[\\s-#/_]+\\d)
result = re.match(pattern, x)
if result:
messagebox.showwarning("Warning","Please check unit number before uploading!")
else:
messagebox.showinfo("Information","Unit number seem to be valid")
#Create DataFrame with the CSV File
filedirectory = "C:/Users/Verghese/Desktop/BulkUploadJanio/masterz.csv"
df = pd.read_csv(filedirectory,encoding='utf-8')
output = df.loc[df['shipper_order_id (optional)'].isin(x)]
print (output)
#Write CSV File
output.to_csv('Desktop/BulkUploadJanio/GWReady.csv', encoding='utf-8', index=False)
This was the first version of the solution that was made. It would serve as a foundation for future iterations/modifications. With this automation, errors such as processing and fulfilling incorrect orders had reduced significantly. Furthermore, this automation reduced the risk of bottlenecks. During peak periods (11-1pm/3-5pm), multiple orders from numerous merchants would be ready to ship and everyone would expect a shipping label quickly. This would cause stress and sometimes lead to errors. With the automation in place, all these problems were solved and performed accurately.
The limitation that this solution had was with TagUI’s capability. TagUI used the position of specific buttons or their HTML position to recognise and click. This would mean that sometimes, if the screen resolution changed or the website layout changed, the TagUI script would break. So the same machine had to be used in order for the different scripts to work unanimously.
This limitation of TagUI comes from the lack of experience and exposure. I only created a solution that would work in that environment and did not look into the possibility of scaling it to other machines or similar purposes. If I was to look at this situation again, I would work on creating a solution such that all Janio users could use this automation. This would be regardless of the machine or if they had used Shopify. I think there would be better solutions or approaches to this particular problem of bulk upload.