How to import client subscriptions from a vendor spreadsheet
The subscription import tool reads a vendor spreadsheet (Excel or CSV), matches the rows to your OnboardMe clients and subscription products, and creates or updates subscriptions with a linked billing schedule. This article explains every step of the import process, how reconciliation works, and what happens to existing subscriptions.
Before you start
Ensure Client Subscriptions is enabled on your account (ask support if needed).
Clients should already exist in OnboardMe. The import matches by Organisation Name or Client Code.
Subscription products (like Xero Ignite, MYOB Essentials, etc.) should exist in Templates - Subscriptions. Add missing products before importing (requires Owner or Admin role).
Set your vendor defaults under Settings so new subscriptions get the right partner discount and markup.
If you sync to Xero Ledger or QuickBooks, connect the integration first and know which account or sale item to use.
What the vendor file needs
Your spreadsheet (Excel .xlsx or CSV) must have a header row with these columns:
Organisation Name (or Client Name, or Client Code) - the client's name or unique code. Import will try to match this to your OnboardMe client list.
Price Plan (or Subscription, or Product) - the vendor's product name, like "Xero Ignite" or "QuickBooks Essentials". Import will try to match this to your Templates - Subscriptions catalogue.
Total (or Cost, or Amount) - the vendor's cost for that line. This becomes the Cost in OnboardMe.
Optional columns (if present, they are imported):
Client Code - if you use codes, this takes priority over Organisation Name when matching clients.
Details (or Narration, or Description) - text that appears on the bill line.
Billing Start Date / Billing End Date - dates from the vendor invoice. These are informational only; they do not control your OnboardMe billing dates. You set billing dates in the import template.
Discount Category - if the vendor file includes a discount tier, it is stored for reference but does not override your cost or charge settings.
Charge Type - stored for reference.
Empty rows and rows with a zero or blank Total are skipped. The file can have up to 10,000 rows and 10 MB.
Step 1 - Upload the file and set the period
Open Clients - Subscriptions.
Click Import.
Select the Vendor (Xero, MYOB, QuickBooks, or type a custom vendor name). This is required before you can upload a file.
Set the Period for this import (month and year). This is the billing period the vendor file represents. Import history is grouped by period and vendor.
If you previously imported the same vendor and period, you will see the run number and file name. You can download that earlier file from the history panel.
Drag and drop (or click to browse) the vendor spreadsheet. OnboardMe parses the file and shows a preview.
If the file is missing required columns (Organisation Name or Client Code, Price Plan, Total), you will see an error. Add the missing columns to the spreadsheet and try again.
Step 2 - Review the preview table and match rows
Once the file is parsed, you see a preview table with every row from the file plus any existing subscriptions that are missing from the file. Each row has a Status badge and shows the matched client, matched product, cost from the file, and the amount you will charge the client.
Row statuses
Create (green) - A new subscription will be created. The client and product are matched, and the row is ready to import.
Update cost (blue) - An existing subscription is matched, and the vendor cost will be updated. The amount charged to the client stays the same unless you choose to recalculate in the "Charge clients" bulk action.
Unchanged (grey) - The row matches an existing subscription, and the cost is the same. No changes will be made.
Needs client (orange) - The organisation name or client code does not match any OnboardMe client. You must map it manually (see below).
Needs product (orange) - The price plan does not match any subscription product for this vendor. You must map it manually.
Missing from file (red) - This is an existing active subscription for the vendor, but it does not appear in the imported file. You can keep it or cancel it (see Reconciliation below).
How matching works
Client matching:
If Client Code is present in the file, OnboardMe looks for an exact match in your client list. If there is exactly one match, the row is linked to that client.
If Client Code is not present or does not match, OnboardMe tries Organisation Name. It normalizes the name (lowercase, removes extra spaces) and looks for an exact match. If there is exactly one match, the row is linked.
If a previous import mapped this organisation name to a client for the same vendor, OnboardMe remembers that mapping and reuses it (unless the mapping is now ambiguous because multiple clients match).
If multiple clients match, or no client matches, the row shows Needs client.
Product matching:
OnboardMe normalizes the price plan name (lowercase, removes extra spaces and trailing "Price Plan" text) and looks for an exact match in your Templates - Subscriptions catalogue, filtered to the selected vendor.
If there is exactly one match, the row is linked to that subscription type.
If the product name partially matches a catalogue name (for example, "Ultimate 10 Price Plan" matches "Ultimate 10"), and there is exactly one partial match, OnboardMe uses it.
If a previous import mapped this product name to a subscription type for the same vendor, OnboardMe remembers that mapping and reuses it.
If multiple products match, or no product matches, the row shows Needs product.
Manually map a row
If a row shows Needs client or Needs product:
Click the row in the preview table.
Use the Client or Subscription type dropdown to search and select the correct match.
The status badge updates to Create or Update cost once both are mapped.
The mapping is saved for this vendor. The next time you import a file with the same organisation or product name, OnboardMe will remember the match.
Bulk actions on the preview table
Filter (top right of the table): show all rows, only rows needing attention, only new subscriptions, only updates, only unchanged, or only missing from file.
Charge clients (top left of the table): set how new subscriptions will charge the client. Choose Charge cost price (pass through the vendor cost with no markup), Charge catalogue price (use the full list price from Templates - Subscriptions), or Charge catalogue price with discount (apply a discount % off the catalogue price). This only affects new subscriptions. Existing subscriptions keep their current charge unless you update them separately after import.
Step 3 - Set the billing template for new subscriptions
If the import will create any new subscriptions (status Create), you must fill in the Billing template section. This template applies to all new subscriptions created in this import. Existing subscriptions keep their current billing settings.
Frequency - how often the bill is generated (Monthly, Quarterly, Half-yearly, Yearly, or Custom).
First bill date - the date OnboardMe should create the first bill. Default is the start of next month.
Bill details (required) - the narration that appears on the bill. Use
[SubscriptionName]to insert the product name. Example:Monthly subscription for [SubscriptionName].Tax - select the tax rate (GST, no GST, or a custom rate). This is the tax line on the bill.
Payment due - number of days after the bill date that payment is due.
Xero account or QuickBooks item (if connected) - the revenue account or sale item the bill line will use when synced.
Xero branding theme (if Xero is connected) - the invoice branding.
Send invoice from Xero or Send invoice from QuickBooks (toggle) - if on, the invoice is emailed to the client by Xero or QuickBooks after it is created.
The billing template is saved with the import run. If you import the same vendor again, OnboardMe prefills the template from the last run.
Step 4 - Reconcile missing subscriptions
At the bottom of the preview table, you may see a Missing subscriptions alert (red or orange badge). These are existing active subscriptions for the vendor that do not appear in the imported file.
Why does this happen? Common reasons:
The vendor file is incomplete (the client cancelled mid-period, or the vendor report filtered out inactive plans).
The client or product name changed in the vendor file, so OnboardMe did not match it to the existing subscription.
The subscription was set up manually in OnboardMe and never came from a vendor import.
You have two options:
Keep all - The missing subscriptions remain active. OnboardMe will continue to bill them. Use this if the file is incomplete or the missing subscriptions are still valid.
Cancel all missing - OnboardMe will cancel the subscriptions that are missing from the file. The linked recurring billing schedule is terminated, and no further bills will be generated. Use this if the vendor file is complete and the missing subscriptions should end.
If you choose to cancel, OnboardMe will ask you to confirm before proceeding.
Step 5 - Review the summary and import
Before you click Import, check the summary at the top of the preview:
Ready to import - how many rows will create or update subscriptions.
Will be cancelled - how many missing subscriptions will be cancelled.
Needs attention - how many rows still need client or product mapping.
If there are rows that need attention, you cannot import yet. Map the clients and products first.
When ready:
Review the billing template (if creating new subscriptions).
Review the missing subscriptions action (keep or cancel).
Click Import (or Import and cancel if cancelling missing subscriptions).
Small imports (under 200 rows) complete immediately. Larger imports run in the background, and you will receive an email when they finish. The maximum file size is 10,000 rows and 10 MB.
What happens during import
Create rows: OnboardMe creates a new client subscription and a linked recurring billing schedule. The first bill is created on the date you set in the billing template.
Update cost rows: OnboardMe updates the vendor cost on the existing subscription. The amount charged to the client stays the same (unless you used a bulk update or recalculated charges). The subscription history records the cost change and the import run number.
Unchanged rows: No changes are made. The subscription history records that the import run matched the row but did not need to update anything.
Missing from file rows (if you chose to cancel): OnboardMe cancels the subscription and terminates the linked recurring billing schedule. The subscription history records the cancellation and the import run number.
The original spreadsheet file is stored with the import run. You can download it later from Import history.
After the import
New and updated subscriptions appear on Clients - Subscriptions.
Generated bills appear on Bills & Payments - Bills.
Linked recurring schedules appear on Bills & Payments - Recurring Billing with a Subscription badge. You cannot edit these schedules directly; open the subscription instead.
View the import run in Import history. Each run shows the period, vendor, run number, file name, how many subscriptions were created or updated, and how many failed. You can download the original file.
Import history and run numbers
Every import is logged with a run number (per vendor per period). If you import the same vendor and period again, the run number increments (Run 1, Run 2, etc.).
To view history:
Open Clients - Subscriptions.
Click Import history.
Filter by period (month picker) or view all imports.
The table shows period, vendor, run number, file name, created, cost updated, failed, who ran the import, and when.
Click the download icon to retrieve the original file.
Use history to audit past imports, re-download vendor files, or check which run introduced a specific subscription or cost change.
Tips and best practices
Set vendor defaults first - Go to Settings and configure partner discount and markup for each vendor before your first import. New subscriptions will start with the right cost and charge.
Import regularly - Import the vendor file each month (or each billing period) to refresh costs. If the vendor changes a plan's price, the import updates the cost automatically.
Reconcile missing subscriptions carefully - If a subscription is missing from the file, check why before cancelling. It might be a vendor report issue, not an actual cancellation.
Map once, reuse forever - When you manually map an organisation name or product name, OnboardMe remembers it for that vendor. Future imports will match automatically.
Use bulk update after import - Import updates vendor cost. If you also want to update the amount charged to clients, select the rows on Clients - Subscriptions and use Options - Bulk update to recalculate markup or apply new pricing.
Check the preview before importing - Always review the preview table and the summary counts. Make sure clients and products are mapped correctly, and review the missing subscriptions action.
Download the file from history - If you need to prove what was imported or debug a discrepancy, download the original file from Import history.
Troubleshooting
The file won't upload - Check that it is Excel (.xlsx) or CSV, under 10 MB, and has a header row with at least Organisation Name (or Client Code), Price Plan, and Total.
All rows show "Needs client" - The organisation names in the file do not match your OnboardMe client list. Check for typos, extra spaces, or abbreviations. Map them manually, or update your client names in OnboardMe to match the vendor file.
All rows show "Needs product" - The price plan names do not match your Templates - Subscriptions catalogue. Check that the products are set up with the correct vendor. Add missing products (requires Owner or Admin), or map the rows manually.
Import says "Xero mapping required" - If you are creating new subscriptions and Xero Ledger is connected, you must select a Xero account in the billing template. Go back and fill in the Xero account dropdown.
Cost updates but charge does not - Import updates vendor cost only. To also update the amount charged to clients, use Charge clients - Charge catalogue price in the preview, or run a bulk update after import.
A subscription was cancelled but should not have been - If you chose "Cancel all missing" and a subscription was cancelled by mistake, you will need to recreate it manually or include it in the next import.
Related
Still need help?
Contact us