How to get a spreadsheet of vouchers into Tally
Lay the spreadsheet out as one row per voucher or one row per ledger line, create the ledgers before the vouchers, convert to Tally import XML, and import into a test company first — and the thing that trips everybody up is the sign convention, because in that XML a debit is a negative amount, a credit is positive, and every voucher must sum to zero.
13 min read · 7 steps · 7 ways it goes wrong
What you need before you start
- The vouchers in a spreadsheet — sales, purchases, receipts, payments, journals or contras — with a date, a voucher type, a number, a party and an amount on every row.
- The exact ledger names as they exist in the Tally company: the party ledgers, the sales and purchase ledgers, the tax ledgers, the bank and cash ledgers. Exact, including capitals and the spaces.
- A test company in Tally with the same masters as the live one. Not optional. An import that goes wrong in a live company is undone one voucher at a time.
- A backup of the live company, taken before the real import.
Choose one of the two layouts
One row per voucher is the simpler and covers most of what a small business imports: date, voucher type, voucher number, party, amount, and the tax columns you use. The debits and credits for a sales, purchase, receipt, payment, journal or contra voucher follow from the type, and they can be worked out for you.
One row per ledger line suits an export from another package: a voucher number that groups the lines, a date, a ledger and separate debit and credit columns. It is more work to prepare and it can express anything, including a journal with six lines.
Do not mix them in one file. A sheet that is mostly one row per voucher with three multi-line journals in the middle is the hardest kind of file to import, and splitting it into two sheets takes a minute.
The tool for this step: Excel / CSV to Tally Converter — turns either layout into Tally import XML, builds the debits and credits from the voucher type, checks every voucher balances, and reads Tally’s own exported XML back into a spreadsheet.
Create the ledgers before you import a single voucher
Tally will not create a ledger because a voucher mentions one. Every ledger named in a voucher has to exist in the company already, spelled exactly as the voucher spells it, or the import stops.
So the order is: masters first, vouchers second. Generate a ledger masters file for the parties that do not exist yet, import that, check the names in Tally, and only then import the vouchers.
Pay particular attention to the ledgers that are not parties — the sales and purchase accounts, the tax ledgers, the round-off ledger, the bank. Those are typed once in a default box and then used on every row, so one wrong spelling is a whole batch.
Get the dates into a form that means one thing
A date is the second most common reason an import produces plausible nonsense. A spreadsheet stores a date as a serial number. Indian sheets are typed day-month-year. Exports from other systems write year-month-day. Tally wants eight digits with no separators.
All of those can be read, but a date typed as two numbers and a year is genuinely ambiguous whenever both of those numbers are twelve or less, and no amount of cleverness will resolve it — the same characters are the third of April to one person and the fourth of March to another. Check the preview of what each row was taken to mean, and look specifically at rows whose day number is twelve or less, because those are the only ones that can be wrong.
Understand the sign convention — this is the one
In Tally’s import XML a debit is a negative amount and a credit is positive. Each ledger line also carries a flag which says whether the amount is deemed positive, and a debit carries that flag as yes while the amount itself is negative. It reads backwards the first time and it is entirely consistent once you accept it.
debit → negative amount credit → positive amount every voucher sums to zero
A sales invoice debits the party — the customer owes you — and credits the sales account and each tax ledger. A purchase does the reverse: the supplier is credited and the purchase account and taxes are debited. A receipt debits the bank and credits the party. A payment credits the bank and debits the party.
Made-up figures for one sales voucher, to show the signs rather than any real invoice.
| Ledger | Dr or Cr | Amount in the XML |
|---|---|---|
| Acme Traders (the party) | Debit | −118,000 |
| Sales — Goods | Credit | +100,000 |
| Output CGST | Credit | +9,000 |
| Output SGST | Credit | +9,000 |
| Total | 0 |
The check is that the lines add to zero. Anything that does not balance should refuse to be written, naming the row — an import file that contains one unbalanced voucher is worse than one that was never produced, because the other four hundred will go in first.
Convert, and read the issues list properly
What comes out is an XML envelope holding one message per voucher. What matters more at this stage is the list of rows that did not convert.
- A row with no party ledger.
- An amount that is not a number — usually a currency symbol, a stray space, or a value typed as text.
- A tax column that is not a number, which is the same problem one column over.
- No sales, purchase or bank ledger named, and no default set.
- A voucher whose lines do not balance, in the one-row-per-line layout: a missing line, a typo in a voucher number that split one voucher into two, or a rounding difference that needs a round-off line.
Fix these in the spreadsheet rather than in the XML. The spreadsheet is the source; an XML file edited by hand will be regenerated next month and the fix will be gone.
Import into a test company and count things
Import the masters, then the vouchers, into a copy of the company. Then check, in this order:
- The count. Day Book for the period should hold exactly as many vouchers as the sheet had. One short means one was rejected and the import log will say which.
- The totals. The sales account for the period should equal the sum of the amount column. If it does not, look at the tax columns first.
- One voucher, opened. Check the party, the ledgers, the narration and the sign — is the customer a debtor or a creditor?
- The party balances. A handful of parties against your own statement, which is what would have found a reversed sign before anybody else saw it.
- The import log itself. Tally reports what it created, altered and ignored, and "ignored" is a word that deserves to be read.
Only when all five are right does the same file go into the live company, on top of a backup.
Bringing data back out of Tally
The reverse trip is often the more useful one: the day book or a ledger exported as XML from Tally, read back into a spreadsheet, one row per voucher or one per ledger line. That is how you get a purchase register out for a tax reconciliation, or a bank ledger out to reconcile against a statement.
From Tally: Display, then Day Book (or the ledger you want), then export, with the format set to XML. Drop the file into the converter and take it as a workbook.
The tool for this step: QuickBooks / Zoho Books / Tally Converter — moves journals and invoices between QuickBooks, Zoho Books and Tally in both directions, checking every voucher balances on the way.
Where this usually goes wrong
7 things that actually happen, rather than a note asking you to be careful.
- The sign convention, flipped. This is the one the guide is named for. Debit is negative in Tally XML, credit is positive, and it is the opposite of what almost every other system does and of what most people assume. Get it backwards on a sales voucher and the customer is credited instead of debited: the import succeeds, nothing complains, and the customer appears as a creditor. Every total in the profit and loss looks plausible, the voucher still sums to zero, and the error is found weeks later when somebody wonders why a customer has a credit balance. Open one imported voucher and look at it before importing four hundred.
- A ledger name that is one character different. Tally matches ledgers by name. "Sundry Debtors" is not "Sundry debtors", "ABC Traders Pvt Ltd" is not "ABC Traders Pvt. Ltd.", and a trailing space typed into a spreadsheet cell is invisible in every view of that spreadsheet. The import will either stop on the unknown ledger, which is the good outcome, or — if the create-missing option is on — create a second, nearly identical ledger and post to that, which is the bad one. Export the ledger list out of Tally first and match against it rather than typing names from memory.
- The third of April and the fourth of March. A date typed as two numbers and a year is ambiguous whenever both of those numbers are twelve or less, and any converter has to pick a convention. Pick the wrong one and a good share of the vouchers in a batch land in the wrong month, which puts them in the wrong tax period, the wrong return and the wrong reconciliation, all of which are found much later. The only rows that can be wrong are those where both the day and the month are twelve or less, so that is where to look. Better still, format the date column as an unambiguous text with the year first before exporting from wherever the sheet came from.
- A voucher that balances to the paisa but not to the invoice. Base plus the tax lines, each rounded at its own line, frequently comes to a paisa or two away from the invoice total. In a one-row-per-line layout that voucher will not balance and will be rejected, which is correct. The fix is a round-off ledger line carrying the difference, not widening the tolerance — a tolerance that swallows a paisa will swallow a rupee, and eventually swallows a missing line in a six-line journal, which is the error it was supposed to catch.
- Invoice numbers that the spreadsheet turned into numbers. An invoice number like 0042 is a string, and a spreadsheet stores it as forty-two. A number like 2026041501 becomes scientific notation. Either way the voucher number that reaches Tally is not the number on the document, and it will not match anything afterwards — not the customer’s statement, not the tax reconciliation, not a search. Format the column as text before anything is typed or pasted into it, and check a couple of values after every export.
- Importing into the live company first. There is no undo. Rolling back a bad import means deleting vouchers one at a time, or restoring a backup and losing everything entered since it was taken. The test company exists precisely because the first import always fails for a reason nobody predicted — a ledger under the wrong group, a voucher type that is named differently in this company, a GST classification the vouchers do not carry. Import into the copy, fix the spreadsheet, and regenerate. The whole loop is a few minutes and it is the difference between a mistake and an incident.
- Assuming the tax details came across with the amounts. Tax amounts posted to tax ledgers are not the same thing as the GST details a return needs. A voucher can carry the right rupees on the right ledgers and still be incomplete for the statutory reports, because those want classifications and stock item details the spreadsheet never had. The trial balance will be right and a return generated from Tally may not be. Check one imported voucher against what your returns actually need before you rely on the data downstream.
How long this should take
An hour for a first batch, nearly all of it spent on ledger names and on the test import that fails for one reason you then fix everywhere. Ten minutes for each batch after that. If you are doing this monthly it is worth saving the column mapping, because the sheet will have the same shape next month and remapping it is the only part that is boring.
Frequently asked questions
Why does a voucher fail the balance check?
In Tally every voucher’s debits equal its credits. In the one-row-per-voucher layout that is worked out from the amount and the taxes. In the one-row-per-line layout the debit and credit columns are added up per voucher number and a difference stops it — usually a missing line, a typo in a voucher number that split one voucher in two, or a rounding difference that needs a round-off line.
Tally says a ledger does not exist. What now?
Every ledger named in a voucher must already exist, spelled exactly as the voucher spells it. Generate a masters file for the parties, import that first, and check the sales, purchase, tax and bank ledger names against the company’s own ledger list rather than against memory.
Does it calculate GST?
No. Tax columns you map are carried onto the tax ledgers exactly as you typed them. It does not work out a rate, and it does not write the full GST classification details that Tally’s statutory reports use, so check those in Tally after the import.
Can I get data back out of Tally the same way?
Yes. Export the day book or a ledger from Tally as XML and read it back as a workbook, one row per voucher or one per ledger line. That is the usual way to get a purchase register or a bank ledger out for a reconciliation.
The tools this uses
Each one described in its own words, read from its own page. Everything here runs in your browser unless it says otherwise.