shopify-routific-conversion

.gitignore

orders_export.csv
routific-destinations-*.csv

# Logs
logs
*.log
npm-debug.log*
yarn-debug.log*
yarn-error.log*

# Runtime data
pids
*.pid
*.seed
*.pid.lock

# Directory for instrumented libs generated by jscoverage/JSCover
lib-cov

# Coverage directory used by tools like istanbul
coverage

# nyc test coverage
.nyc_output

# Grunt intermediate storage (http://gruntjs.com/creating-plugins#storing-task-files)
.grunt

# Bower dependency directory (https://bower.io/)
bower_components

# node-waf configuration
.lock-wscript

# Compiled binary addons (https://nodejs.org/api/addons.html)
build/Release

# Dependency directories
node_modules/
jspm_packages/

# TypeScript v1 declaration files
typings/

# Optional npm cache directory
.npm

# Optional eslint cache
.eslintcache

# Optional REPL history
.node_repl_history

# Output of 'npm pack'
*.tgz

# Yarn Integrity file
.yarn-integrity

# dotenv environment variables file
.env

# next.js build output
.next

.DS_Store

readme.md

shopify-routific-conversion

Converts from a Shopify orders CSV export to a CSV file which can be uploaded to Routific to provide delivery locations.

Setting up

Clone the gist and install dependencies

git clone git@gist.github.com:8e358345fd49042c3283b2fecaf15cc3.git shopify-routific-conversion
cd shopify-routific-conversion
npm install

Conversion

1. Go to your Shopify orders - http://shopify.com/admin/orders

2. Filter the orders as required (e.g. only "Open" orders)

3. Export the current page as "Plain CSV file"

4. Copy the file to the shopify-routific-conversion directory

   • Make sure it's called orders_export.csv

   cp ~/Downloads/orders_export.csv ./shopify-routific-conversion
   

5. Run the conversion

   npm start
   

This will create a CSV file in shopify-routific-conversion with a file name in the format routific-destinations-<date>.csv

Use

When setting up a new project in Routific, use the "Upload a List of Stops" functionality and select the generated routific-destinations CSV file. The correct columns should be automatically selected based on the column titles.

index.js

const util = require("util");
const fs = require("fs");
const neatCsv = require("neat-csv");
const createCsvWriter = require("csv-writer").createObjectCsvWriter;

const readFile = util.promisify(fs.readFile);
const capitalize = (text) =>
  text.charAt(0).toUpperCase() + text.slice(1).toLowerCase();
const titleize = (text) => text.split(/\s+/).map(capitalize).join(" ");
const padNumber = (value) => `0${value}`.slice(-2);
const formatDate = (date = new Date()) =>
  `${date.getFullYear()}-${padNumber(date.getMonth() + 1)}-${padNumber(
    date.getDate()
  )}`;

(async () => {
  const shopfiyOrdersCsv = await readFile("./orders_export.csv");
  const shopfiyOrders = await neatCsv(shopfiyOrdersCsv);

  const consolidatedOrders = shopfiyOrders.reduce((orders, order) => {
    const id = order.Name;
    orders[id] = (orders[id] || []).concat(order);
    return orders;
  }, {});

  const items = {};
  const orders = Object.keys(consolidatedOrders)
    .sort()
    .map((id) => {
      const orderItems = consolidatedOrders[id];
      const itemCount = orderItems.reduce(
        (count, orderItem) =>
          count + parseInt(orderItem["Lineitem quantity"], 10),
        0
      );
      orderItems.forEach((orderItem) => {
        const name = orderItem["Lineitem name"];
        items[name] =
          (items[name] || 0) + parseInt(orderItem["Lineitem quantity"], 10);
      });

      const mainItem = orderItems[0];
      const shipping = (key) => mainItem[`Shipping ${key}`].trim();

      const name = titleize(shipping("Name"));
      const address = titleize(shipping("Street")).replace(" , ", ", ");
      const city = titleize(shipping("City"));
      const state = titleize(shipping("Province"));
      const zipCode = shipping("Zip");
      const phone = shipping("Phone")
        .replace("'", "")
        .replace(/\s+/g, "")
        .replace(/^44/, "+44")
        .replace(/^07/, "+447");
      const email = mainItem.Email.trim();
      const billingName = mainItem["Billing Name"].trim();

      const isApartment =
        address.toLowerCase().includes("apartment") ||
        address.toLowerCase().includes("flat ") ||
        address.toLowerCase().includes("building");
      const isPresent = name.toLowerCase() !== billingName.toLowerCase();

      if (isApartment) {
        console.warn(`Check address: ${id} - ${name}, "${address}"`);
      }

      const notes = `(${id}) Paid £${mainItem.Total}, ${itemCount} item${
        itemCount === 1 ? "" : "s"
      } ordered -- ${orderItems
        .map(
          (orderItem) =>
            `${orderItem["Lineitem quantity"]} x ${orderItem["Lineitem name"]}`
        )
        .join(", ")} -- Link: https://shopify.com/admin/orders/${mainItem.Id}`;

      const notes2 = [
        isApartment
          ? "WARNING: This address has been flagged as being an apartment"
          : "",
        isPresent
          ? `Note: This may be a present - the shipping name does not match billing name (${billingName})`
          : "",
      ]
        .filter((value) => !!value)
        .join(" -- ");

      return {
        id: id.replace("#", "Order "),
        name,
        address,
        city,
        state,
        zipCode,
        phone,
        email,
        notes,
        notes2,
      };
    });

  const csvWriter = createCsvWriter({
    path: `./routific-destinations-${formatDate()}.csv`,
    header: [
      { id: "id", title: "ID" },
      { id: "name", title: "Name" },
      { id: "address", title: "Address" },
      { id: "city", title: "City" },
      { id: "state", title: "State" },
      { id: "zipCode", title: "Zip Code" },
      { id: "phone", title: "Phone" },
      { id: "email", title: "Email" },
      { id: "notes", title: "Notes" },
      { id: "notes2", title: "Notes 2" },
    ],
  });

  await csvWriter.writeRecords(orders);

  console.log(`\nOutputted to ./routific-destinations-${formatDate()}.csv`);
  console.log(` - Delivering to ${orders.length} locations`);

  const largestSize = Math.max(...Object.values(items)).toString().length;
  const pad = (value) =>
    `${value}`.padStart(largestSize, " ").slice(-largestSize);

  console.log(
    ` - Sold ${Object.values(items).reduce(
      (count, item) => count + item,
      0
    )} products`
  );
  Object.keys(items).forEach((itemName) => {
    console.log(` -- ${pad(items[itemName])} x "${itemName}"`);
  });
})();

package-lock.json

{
  "name": "shopify-routific-conversion",
  "version": "1.0.0",
  "lockfileVersion": 1,
  "requires": true,
  "dependencies": {
    "@hapi/address": {
      "version": "2.1.4",
      "resolved": "https://registry.npmjs.org/@hapi/address/-/address-2.1.4.tgz",
      "integrity": "sha512-QD1PhQk+s31P1ixsX0H0Suoupp3VMXzIVMSwobR3F3MSUO2YCV0B7xqLcUw/Bh8yuvd3LhpyqLQWTNcRmp6IdQ=="
    },
    "@hapi/formula": {
      "version": "1.2.0",
      "resolved": "https://registry.npmjs.org/@hapi/formula/-/formula-1.2.0.tgz",
      "integrity": "sha512-UFbtbGPjstz0eWHb+ga/GM3Z9EzqKXFWIbSOFURU0A/Gku0Bky4bCk9/h//K2Xr3IrCfjFNhMm4jyZ5dbCewGA=="
    },
    "@hapi/hoek": {
      "version": "8.5.1",
      "resolved": "https://registry.npmjs.org/@hapi/hoek/-/hoek-8.5.1.tgz",
      "integrity": "sha512-yN7kbciD87WzLGc5539Tn0sApjyiGHAJgKvG9W8C7O+6c7qmoQMfVs0W4bX17eqz6C78QJqqFrtgdK5EWf6Qow=="
    },
    "@hapi/joi": {
      "version": "16.1.8",
      "resolved": "https://registry.npmjs.org/@hapi/joi/-/joi-16.1.8.tgz",
      "integrity": "sha512-wAsVvTPe+FwSrsAurNt5vkg3zo+TblvC5Bb1zMVK6SJzZqw9UrJnexxR+76cpePmtUZKHAPxcQ2Bf7oVHyahhg==",
      "requires": {
        "@hapi/address": "^2.1.2",
        "@hapi/formula": "^1.2.0",
        "@hapi/hoek": "^8.2.4",
        "@hapi/pinpoint": "^1.0.2",
        "@hapi/topo": "^3.1.3"
      }
    },
    "@hapi/pinpoint": {
      "version": "1.0.2",
      "resolved": "https://registry.npmjs.org/@hapi/pinpoint/-/pinpoint-1.0.2.tgz",
      "integrity": "sha512-dtXC/WkZBfC5vxscazuiJ6iq4j9oNx1SHknmIr8hofarpKUZKmlUVYVIhNVzIEgK5Wrc4GMHL5lZtt1uS2flmQ=="
    },
    "@hapi/topo": {
      "version": "3.1.6",
      "resolved": "https://registry.npmjs.org/@hapi/topo/-/topo-3.1.6.tgz",
      "integrity": "sha512-tAag0jEcjwH+P2quUfipd7liWCNX2F8NvYjQp2wtInsZxnMlypdw0FtAOLxtvvkO+GSRRbmNi8m/5y42PQJYCQ==",
      "requires": {
        "@hapi/hoek": "^8.3.0"
      }
    },
    "buffer-alloc": {
      "version": "1.2.0",
      "resolved": "https://registry.npmjs.org/buffer-alloc/-/buffer-alloc-1.2.0.tgz",
      "integrity": "sha512-CFsHQgjtW1UChdXgbyJGtnm+O/uLQeZdtbDo8mfUgYXCHSM1wgrVxXm6bSyrUuErEb+4sYVGCzASBRot7zyrow==",
      "requires": {
        "buffer-alloc-unsafe": "^1.1.0",
        "buffer-fill": "^1.0.0"
      }
    },
    "buffer-alloc-unsafe": {
      "version": "1.1.0",
      "resolved": "https://registry.npmjs.org/buffer-alloc-unsafe/-/buffer-alloc-unsafe-1.1.0.tgz",
      "integrity": "sha512-TEM2iMIEQdJ2yjPJoSIsldnleVaAk1oW3DBVUykyOLsEsFmEc9kn+SFFPz+gl54KQNxlDnAwCXosOS9Okx2xAg=="
    },
    "buffer-fill": {
      "version": "1.0.0",
      "resolved": "https://registry.npmjs.org/buffer-fill/-/buffer-fill-1.0.0.tgz",
      "integrity": "sha1-+PeLdniYiO858gXNY39o5wISKyw="
    },
    "buffer-from": {
      "version": "1.1.1",
      "resolved": "https://registry.npmjs.org/buffer-from/-/buffer-from-1.1.1.tgz",
      "integrity": "sha512-MQcXEUbCKtEo7bhqEs6560Hyd4XaovZlO/k9V3hjVUF/zwW7KBVdSK4gIt/bzwS9MbR5qob+F5jusZsb0YQK2A=="
    },
    "core-util-is": {
      "version": "1.0.2",
      "resolved": "https://registry.npmjs.org/core-util-is/-/core-util-is-1.0.2.tgz",
      "integrity": "sha1-tf1UIgqivFq1eqtxQMlAdUUDwac="
    },
    "csv-parser": {
      "version": "2.3.2",
      "resolved": "https://registry.npmjs.org/csv-parser/-/csv-parser-2.3.2.tgz",
      "integrity": "sha512-ggurTYuhhoUJyrPXTAkiO1x6QXkxAoimV+YSz2eSAXF+jQ/Xve/030T34tVStKFmX56pPtY5PiZl3bR4HkZK+Q==",
      "requires": {
        "@hapi/joi": "^16.1.4",
        "buffer-alloc": "^1.1.0",
        "buffer-from": "^1.0.0",
        "generate-object-property": "^1.0.0",
        "minimist": "^1.2.0",
        "ndjson": "^1.4.0"
      }
    },
    "csv-writer": {
      "version": "1.6.0",
      "resolved": "https://registry.npmjs.org/csv-writer/-/csv-writer-1.6.0.tgz",
      "integrity": "sha512-NOx7YDFWEsM/fTRAJjRpPp8t+MKRVvniAg9wQlUKx20MFrPs73WLJhFf5iteqrxNYnsy924K3Iroh3yNHeYd2g=="
    },
    "end-of-stream": {
      "version": "1.4.4",
      "resolved": "https://registry.npmjs.org/end-of-stream/-/end-of-stream-1.4.4.tgz",
      "integrity": "sha512-+uw1inIHVPQoaVuHzRyXd21icM+cnt4CzD5rW+NC1wjOUSTOs+Te7FOv7AhN7vS9x/oIyhLP5PR1H+phQAHu5Q==",
      "requires": {
        "once": "^1.4.0"
      }
    },
    "generate-object-property": {
      "version": "1.2.0",
      "resolved": "https://registry.npmjs.org/generate-object-property/-/generate-object-property-1.2.0.tgz",
      "integrity": "sha1-nA4cQDCM6AT0eDYYuTf6iPmdUNA=",
      "requires": {
        "is-property": "^1.0.0"
      }
    },
    "get-stream": {
      "version": "5.1.0",
      "resolved": "https://registry.npmjs.org/get-stream/-/get-stream-5.1.0.tgz",
      "integrity": "sha512-EXr1FOzrzTfGeL0gQdeFEvOMm2mzMOglyiOXSTpPC+iAjAKftbr3jpCMWynogwYnM+eSj9sHGc6wjIcDvYiygw==",
      "requires": {
        "pump": "^3.0.0"
      }
    },
    "inherits": {
      "version": "2.0.4",
      "resolved": "https://registry.npmjs.org/inherits/-/inherits-2.0.4.tgz",
      "integrity": "sha512-k/vGaX4/Yla3WzyMCvTQOXYeIHvqOKtnqBduzTHpzpQZzAskKMhZ2K+EnBiSM9zGSoIFeMpXKxa4dYeZIQqewQ=="
    },
    "is-property": {
      "version": "1.0.2",
      "resolved": "https://registry.npmjs.org/is-property/-/is-property-1.0.2.tgz",
      "integrity": "sha1-V/4cTkhHTt1lsJkR8msc1Ald2oQ="
    },
    "isarray": {
      "version": "1.0.0",
      "resolved": "https://registry.npmjs.org/isarray/-/isarray-1.0.0.tgz",
      "integrity": "sha1-u5NdSFgsuhaMBoNJV6VKPgcSTxE="
    },
    "json-stringify-safe": {
      "version": "5.0.1",
      "resolved": "https://registry.npmjs.org/json-stringify-safe/-/json-stringify-safe-5.0.1.tgz",
      "integrity": "sha1-Epai1Y/UXxmg9s4B1lcB4sc1tus="
    },
    "minimist": {
      "version": "1.2.5",
      "resolved": "https://registry.npmjs.org/minimist/-/minimist-1.2.5.tgz",
      "integrity": "sha512-FM9nNUYrRBAELZQT3xeZQ7fmMOBg6nWNmJKTcgsJeaLstP/UODVpGsr5OhXhhXg6f+qtJ8uiZ+PUxkDWcgIXLw=="
    },
    "ndjson": {
      "version": "1.5.0",
      "resolved": "https://registry.npmjs.org/ndjson/-/ndjson-1.5.0.tgz",
      "integrity": "sha1-rmA7NrE0vOw0e0UkIrC/mNWDLsg=",
      "requires": {
        "json-stringify-safe": "^5.0.1",
        "minimist": "^1.2.0",
        "split2": "^2.1.0",
        "through2": "^2.0.3"
      }
    },
    "neat-csv": {
      "version": "5.2.0",
      "resolved": "https://registry.npmjs.org/neat-csv/-/neat-csv-5.2.0.tgz",
      "integrity": "sha512-JMbn4RPKo1heWhDihK9kbffxkDdd2uFSk30t3YOz1u3gsbIGhGUNlmGGa5H9yySLie3qU4lsLwrxQyirExD71A==",
      "requires": {
        "csv-parser": "^2.3.2",
        "get-stream": "^5.1.0",
        "to-readable-stream": "^2.1.0"
      }
    },
    "once": {
      "version": "1.4.0",
      "resolved": "https://registry.npmjs.org/once/-/once-1.4.0.tgz",
      "integrity": "sha1-WDsap3WWHUsROsF9nFC6753Xa9E=",
      "requires": {
        "wrappy": "1"
      }
    },
    "process-nextick-args": {
      "version": "2.0.1",
      "resolved": "https://registry.npmjs.org/process-nextick-args/-/process-nextick-args-2.0.1.tgz",
      "integrity": "sha512-3ouUOpQhtgrbOa17J7+uxOTpITYWaGP7/AhoR3+A+/1e9skrzelGi/dXzEYyvbxubEF6Wn2ypscTKiKJFFn1ag=="
    },
    "pump": {
      "version": "3.0.0",
      "resolved": "https://registry.npmjs.org/pump/-/pump-3.0.0.tgz",
      "integrity": "sha512-LwZy+p3SFs1Pytd/jYct4wpv49HiYCqd9Rlc5ZVdk0V+8Yzv6jR5Blk3TRmPL1ft69TxP0IMZGJ+WPFU2BFhww==",
      "requires": {
        "end-of-stream": "^1.1.0",
        "once": "^1.3.1"
      }
    },
    "readable-stream": {
      "version": "2.3.7",
      "resolved": "https://registry.npmjs.org/readable-stream/-/readable-stream-2.3.7.tgz",
      "integrity": "sha512-Ebho8K4jIbHAxnuxi7o42OrZgF/ZTNcsZj6nRKyUmkhLFq8CHItp/fy6hQZuZmP/n3yZ9VBUbp4zz/mX8hmYPw==",
      "requires": {
        "core-util-is": "~1.0.0",
        "inherits": "~2.0.3",
        "isarray": "~1.0.0",
        "process-nextick-args": "~2.0.0",
        "safe-buffer": "~5.1.1",
        "string_decoder": "~1.1.1",
        "util-deprecate": "~1.0.1"
      }
    },
    "safe-buffer": {
      "version": "5.1.2",
      "resolved": "https://registry.npmjs.org/safe-buffer/-/safe-buffer-5.1.2.tgz",
      "integrity": "sha512-Gd2UZBJDkXlY7GbJxfsE8/nvKkUEU1G38c1siN6QP6a9PT9MmHB8GnpscSmMJSoF8LOIrt8ud/wPtojys4G6+g=="
    },
    "split2": {
      "version": "2.2.0",
      "resolved": "https://registry.npmjs.org/split2/-/split2-2.2.0.tgz",
      "integrity": "sha512-RAb22TG39LhI31MbreBgIuKiIKhVsawfTgEGqKHTK87aG+ul/PB8Sqoi3I7kVdRWiCfrKxK3uo4/YUkpNvhPbw==",
      "requires": {
        "through2": "^2.0.2"
      }
    },
    "string_decoder": {
      "version": "1.1.1",
      "resolved": "https://registry.npmjs.org/string_decoder/-/string_decoder-1.1.1.tgz",
      "integrity": "sha512-n/ShnvDi6FHbbVfviro+WojiFzv+s8MPMHBczVePfUpDJLwoLT0ht1l4YwBCbi8pJAveEEdnkHyPyTP/mzRfwg==",
      "requires": {
        "safe-buffer": "~5.1.0"
      }
    },
    "through2": {
      "version": "2.0.5",
      "resolved": "https://registry.npmjs.org/through2/-/through2-2.0.5.tgz",
      "integrity": "sha512-/mrRod8xqpA+IHSLyGCQ2s8SPHiCDEeQJSep1jqLYeEUClOFG2Qsh+4FU6G9VeqpZnGW/Su8LQGc4YKni5rYSQ==",
      "requires": {
        "readable-stream": "~2.3.6",
        "xtend": "~4.0.1"
      }
    },
    "to-readable-stream": {
      "version": "2.1.0",
      "resolved": "https://registry.npmjs.org/to-readable-stream/-/to-readable-stream-2.1.0.tgz",
      "integrity": "sha512-o3Qa6DGg1CEXshSdvWNX2sN4QHqg03SPq7U6jPXRahlQdl5dK8oXjkU/2/sGrnOZKeGV1zLSO8qPwyKklPPE7w=="
    },
    "util-deprecate": {
      "version": "1.0.2",
      "resolved": "https://registry.npmjs.org/util-deprecate/-/util-deprecate-1.0.2.tgz",
      "integrity": "sha1-RQ1Nyfpw3nMnYvvS1KKJgUGaDM8="
    },
    "wrappy": {
      "version": "1.0.2",
      "resolved": "https://registry.npmjs.org/wrappy/-/wrappy-1.0.2.tgz",
      "integrity": "sha1-tSQ9jz7BqjXxNkYFvA0QNuMKtp8="
    },
    "xtend": {
      "version": "4.0.2",
      "resolved": "https://registry.npmjs.org/xtend/-/xtend-4.0.2.tgz",
      "integrity": "sha512-LKYU1iAXJXUgAXn9URjiu+MWhyUXHsvfp7mcuYm9dSUKK0/CjtrUwFAxD82/mCWbtLsGjFIad0wIsod4zrTAEQ=="
    }
  }
}

package.json

{
  "name": "shopify-routific-conversion",
  "version": "1.1.0",
  "description": "Script to generate Routific location CSV file from Shopify orders export",
  "main": "index.js",
  "scripts": {
    "start": "node index.js"
  },
  "keywords": [],
  "author": "Alistair Brown <github@alistairjcbrown.com>",
  "license": "MIT",
  "dependencies": {
    "csv-writer": "^1.6.0",
    "neat-csv": "^5.2.0"
  }
}