Python script to convert an Etsy sales export CSV into a Xero import CSV

2020-01-11 (Saturday) | 300 words (~2 minutes reading)

We handle the accounting for our pop out cards shop using Xero. There are convenient pre-built integrations that handle Stripe and PayPal data in Xero, but nothing for Etsy, so I had to do a little Python script to generate a Xero import CSV file from the Etsy orders export CSV.

Here’s the script for future reference:

#!/usr/bin/python3

# https://www.etsy.com/uk/your/shops/YourShopName/download
# Sold Orders
# e.g. EtsySoldOrders2020.csv

import argparse
import csv
from datetime import datetime
from io import TextIOWrapper
from typing import Dict

SALES_ACCOUNT_CODE = "200"
NOW = datetime.now()
XERO_FIELDS = [
    "*ContactName",
    "EmailAddress",
    "POAddressLine1",
    "POAddressLine2",
    "POAddressLine3",
    "POAddressLine4",
    "POCity",
    "PORegion",
    "POPostalCode",
    "POCountry",
    "*InvoiceNumber",
    "Reference",
    "*InvoiceDate",
    "*DueDate",
    "Total",
    "InventoryItemCode",
    "*Description",
    "*Quantity",
    "*UnitAmount",
    "Discount",
    "*AccountCode",
    "*TaxType",
    "TaxAmount",
    "TrackingName1",
    "TrackingOption1",
    "TrackingName2",
    "TrackingOption2",
    "Currency",
    "BrandingTheme",
]


def convert_etsy_to_xero(etsy_file: TextIOWrapper):
    output_file = f"etsy_xero_orders_{etsy_file.name}"
    with open(output_file, "w", newline="") as output_file:
        etsy_reader = csv.DictReader(etsy_file, delimiter=",", quotechar='"')
        xero_writer = csv.DictWriter(output_file, XERO_FIELDS,
                                     delimiter=",", quotechar='"')
        xero_writer.writeheader()
        for etsy_order in etsy_reader:
            xero_writer.writerow(
                etsy_order_to_xero_row(etsy_order)
            )


def etsy_order_to_xero_row(etsy_order: Dict[str, str]) -> Dict[str, str]:
    return {
        "*ContactName": etsy_order.get("Full Name"),
        "EmailAddress": "",
        "POAddressLine1": etsy_order.get("Street 1"),
        "POAddressLine2": etsy_order.get("Street 2"),
        "POAddressLine3": etsy_order.get(""),
        "POAddressLine4": etsy_order.get(""),
        "POCity": etsy_order.get("Delivery City"),
        "PORegion": etsy_order.get("Delivery State"),
        "POPostalCode": etsy_order.get("Delivery Zipcode"),
        "POCountry": etsy_order.get("Delivery Country"),
        "*InvoiceNumber": etsy_order.get("Order ID"),
        "Reference": etsy_order.get(""),
        "*InvoiceDate": etsy_date_to_xero_date(etsy_order.get("Sale Date")),
        "*DueDate": etsy_date_to_xero_date(etsy_order.get("Sale Date")),
        "Total": etsy_order.get("Order total"),
        "InventoryItemCode": "",
        "*Description": f"Etsy Order {etsy_order.get('Order ID')}",
        "*Quantity": "1",
        "*UnitAmount": etsy_order.get("Order total"),
        "Discount": "",
        "*AccountCode": SALES_ACCOUNT_CODE,
        "*TaxType": etsy_order.get("Sales"),
        "TaxAmount": "",
        "TrackingName1": "",
        "TrackingOption1": "",
        "TrackingName2": "",
        "TrackingOption2": "",
        "Currency": etsy_order.get("Currency"),
        "BrandingTheme": "",
    }


def etsy_date_to_xero_date(etsy_date: str) -> str:
    return datetime.strptime(etsy_date, "%m/%d/%y").strftime("%d %b %Y")


if __name__ == "__main__":
    parser = argparse.ArgumentParser(
        description="Convert an Etsy sales export CSV into a xero invoices CSV")
    parser.add_argument("etsy_file", default=f"EtsySoldOrders{NOW.year}.csv",
                        type=argparse.FileType("r", encoding="UTF-8"),
                        help="Path to Etsy sales export CSV file")
    args = parser.parse_args()

    convert_etsy_to_xero(args.etsy_file)
    args.etsy_file.close()

Tech mentioned