How to Import Service Jobs and Past Payments from a Spreadsheet

Years of jobs and payments live in a spreadsheet

A services firm that decides to stop using spreadsheets still has years of jobs and payments in them. Retyping them is not realistic, and starting from zero loses what clients still owe.

Importing payments without linking them to jobs makes outstanding wrong from day one.

Running an import twice by mistake can double the history.

Why this happens

• There is no import target for jobs and payments.
• Clients are typed differently in every sheet.
• Payments are imported with no link to the job they paid.
• Imports are not safe to re-run.

Biznsbook addresses this through Data Import of service jobs, Data Import of historical payments, duplicate-safe payment import when Service Billing & Collections is licensed. Clients are the parties you already keep, and collectors record what they received without seeing the fee or the outstanding.

Services firms lose money in the gap between the work done and the payment recorded. Keeping the fee on the job, the payment against the job, and the collector away from the numbers closes that gap without turning the firm into an accounting office.

Step-by-step: Import Service Jobs and Past Payments from a Spreadsheet

Built for owners of services firms and the staff who collect client payments in Biznsbook Collections.

1. Import clients first. Collections uses the parties you already keep. Load clients through Data Import → Parties. Rows for an unknown client are refused in the next steps.
2. Prepare the jobs sheet. One row per job: client name, service reference, service date, fee, service type, assignee, and whether it is an opening balance. Leave the fee blank when it is not decided.
3. Import Collections service jobs. In Data Import, choose Collections service jobs, map the columns, and run it. A blank fee shows Fee not set.
4. Prepare the payments sheet. One row per payment: client name, date, amount, mode, UTR, and the service reference it paid.
5. Import Collections historical payments. Choose Collections historical payments. A payment that names a service reference goes on that job; otherwise it is kept as an advance.
6. Check outstanding. Open the dashboard and a few client statements. Outstanding is computed from the imported fees and payments.
7. Re-run safely. A payment with the same client, reference, and amount as one already saved is refused, so re-running a file with UTRs does not double them.

Review results after the first full weekly cycle. Adjust roles, mappings, or approvals where the same exception repeats.

Screen names in this guide match Collections in the product: Record a payment, My entries, Dashboard, Jobs, Payments, Payment links, Corrections, Service types, Settings, and Guide. Every screen has a ? help button.

Common mistakes to avoid

• Mistake 1: There is no import target for jobs and payments. Repeating this each month usually shows up first in billed, collected, or outstanding for the period.
• Mistake 2: Clients are typed differently in every sheet. Repeating this each month usually shows up first in billed, collected, or outstanding for the period.
• Mistake 3: Payments are imported with no link to the job they paid. Repeating this each month usually shows up first in billed, collected, or outstanding for the period.
• Mistake 4: Imports are not safe to re-run. Repeating this each month usually shows up first in billed, collected, or outstanding for the period.

Note each recurring gap in the weekly review; each should map to a step in this guide.

Best practices that hold up as you scale

• Import clients first — Collections uses the parties you already keep.
• Prepare the jobs sheet — One row per job: client name, service reference, service date, fee, service type, assignee, and whether it is an opening balance.
• Import Collections service jobs — In Data Import, choose Collections service jobs, map the columns, and run it.
• Prepare the payments sheet — One row per payment: client name, date, amount, mode, UTR, and the service reference it paid.
• Import Collections historical payments — Choose Collections historical payments.

Firms that keep the fee on the job, let Collections compute outstanding, and correct by reversal instead of editing a row keep their period totals and their trail intact.

How Biznsbook supports this workflow

Data Import of service jobs is part of Biznsbook Service Billing & Collections. Each client is a party you already keep, each job holds its fee, and each payment keeps who entered it, when, and how the client paid.

Data Import of historical payments is part of Biznsbook Service Billing & Collections. Each client is a party you already keep, each job holds its fee, and each payment keeps who entered it, when, and how the client paid.

duplicate-safe payment import is part of Biznsbook Service Billing & Collections. Each client is a party you already keep, each job holds its fee, and each payment keeps who entered it, when, and how the client paid.

Ledger posting is off until Collections settings turn it on, and only when Accounting or Finance is licensed. Outstanding is the fee plus any tax, minus the payments on the job. Collectors do not see fees, outstanding, other people’s entries, the dashboard, statements, the correction queue, or a journal.

Suggested implementation timeline

1. Week 1: Add your service types, check that clients exist as parties, and open jobs with fees.
2. Week 2: Start Data Import of service jobs with one collector and a handful of clients.
3. Week 3: Add the rest of the collectors and clear the dashboard attention items daily.
4. Week 4: Review billed, collected, and outstanding by age with the client statements.
5. Ongoing: Weekly review using clients, then jobs, then payments and the dashboard.

Related guides

Start with track a service job and fee and record a client payment without showing the fee. Then bill a monthly retainer, split a fee into installments, and read billed, collected, and outstanding.

Owner routines: review link payments, confirm cash hand-over, and the daily summary email.

All guides: service billing & collections. Decision page: service billing software vs spreadsheets. Product page: Service Billing & Collections.

Opening balances from before you started

Work billed before go-live that is still unpaid can be imported as an opening-balance job. It stays in outstanding but is left out of the period’s billed total, so the first months on the dashboard are not inflated.

Money a client paid in advance before you started can be recorded as an opening credit. It is kept as an advance and left out of the period’s collected total.

Cleaning the sheet before import

Use the same client name in the sheet as on the party. Use one service reference per job and repeat it exactly in the payments sheet. Give non-cash payments their UTR; it is what stops a re-run from doubling them.

Modes such as cash, UPI, bank transfer, cheque, card, and other are understood; common bank wording such as NEFT is read as bank transfer. A row with an unknown mode is refused with a clear message while the other rows import.

After the import

Open the dashboard and the statements of a few large clients. Outstanding should match what your spreadsheet said each client owed. If one is off, the usual cause is a payment whose service reference did not match the job exactly, so it was kept as an advance; allocate it to the right job from Payments.

From then on, collectors record new payments on Record a payment, and the spreadsheet can be retired.

Who should run the import

Run the import as the owner or an administrator who can see Collections amounts, because the files carry fees and payments.

The import checks that Service Billing & Collections is active for the company before it saves anything, and it never posts imported payments to the ledger, so the books are not changed by history.

Metrics to track monthly

• Billed for the date range
• Collected for the date range
• Outstanding, and outstanding older than 90 days
• Collections by payment mode
• Payments not yet reconciled

Start with three metrics; trend direction matters more than a single point-in-time snapshot.

Spreadsheets and notebooks vs Biznsbook Collections

Compare running this on spreadsheets with Biznsbook Data Import of service jobs and related capabilities.

Clients, then jobs, then payments

The order matters. Clients are the parties you already keep, so they come first. Jobs carry the fees, so they come second. Payments come last so each one can land on its job. Done in that order, outstanding is right the first time you open the dashboard.

Write this into your weekly owner routine and revisit it when you add collectors or new services.

Frequently asked questions

How this differs by industry