How to Create Custom Excel Mapping Templates in TallyPrime
How to Create Custom Excel Mapping Templates in TallyPrime
TallyPrime Release 4.0 and above allows you to import data from custom PDF formats directly. Discover how to build, configure, and debug custom mapping templates step-by-step.
Who is this for: Step-by-Step Tutorial
Prior to TallyPrime Release 4.0, importing vouchers and masters from spreadsheets was a tedious process. Accountants had to convert their Excel data into highly rigid, proprietary XML files or rely on third-party utilities. With the introduction of the native mapping templates utility, TallyPrime allows users to map any custom Excel layout directly to Tally's database schema.
However, building a custom mapping template requires a precise understanding of spreadsheet mechanics, row index configurations, and structural field dependencies. A single misplaced column mapping or structural mismatch can prevent the entire file from importing. This guide provides a detailed, step-by-step technical walkthrough on how to build, test, and deploy custom mapping templates in TallyPrime.
Step 1: Navigate to the Template Creator
To build a mapping template, you must access the management panel inside TallyPrime. Ensure your software version is Release 4.0 or higher:
- Start at the Gateway of Tally and select the Import button from the top toolbar (or press the keyboard shortcut Alt+O).
- In the dropdown menu, select Manage > Mapping Templates.
- Choose Create to launch the template configuration wizard.
- Select the database category: Masters (for ledgers, stock items, groups) or Transactions (for sales invoices, payment vouchers, journals, receipts).
Step 2: Define File Structure and Row Properties
Before mapping individual fields, you must tell TallyPrime where to look for data and how to parse the file structure. In the template header configuration, fill in these options:
- Template Name: Give the template a descriptive name (e.g. "Amazon Sales Import" or "SBI Statement Mapper").
- File Type & Path: Choose Excel as the source format. Enter the absolute path to your folder and select the specific spreadsheet file to parse header values.
- Sheet Name: Select the exact worksheet tab containing the transaction rows.
- Excel Data has Column Headers: Select Yes if your sheet contains descriptive labels in row 1. Set this to No if you want to map using raw column letters (e.g. Column A, Column B).
- Start Row: Specify the row number where Tally should begin reading transaction data. If row 1 contains headers, set the Start Row to 2 to bypass the header title line.
Step 3: Choose the Data Layout Type
Depending on how your Excel sheet displays ledgers, choose one of two distinct layout mapping modes:
📊 Layout 1: Fixed Columns
Use this layout when the debit and credit accounts are consistently placed in the same columns on every single row. For example, a cash sales register where Column C is always the customer ledger and Column D is always the Cash ledger. This is simple to map since each column maps 1-to-1 to a fixed database property.
🔄 Layout 2: Ledger Details from Multiple Rows
Use this layout when a single transaction spans multiple rows. This is common for complex tax invoices containing several items or multiple expense lines. Under this layout, the Voucher Number acts as the grouping anchor. TallyPrime reads consecutive rows with matching Voucher Numbers and aggregates them into one multi-ledger entry.
Step 4: Map Tally Fields to Spreadsheet Headers
Next, you will map Tally's internal database fields (left column) to your spreadsheet headers (right column). Here is a standard configuration list for mapping transactions:
| Tally Database Field | Mapping Configuration | Recommended Excel Header | Formatting Restriction |
|---|---|---|---|
| Voucher Date | Map to Column | Date / Invoice Date | Short Date, DD-MM-YYYY |
| Voucher Type | Specify Default Value | -- (Sales / Purchase) | Must match active Tally Voucher Type |
| Voucher Number | Map to Column | Invoice No / Bill No | Alphanumeric, unique key |
| Ledger Name | Map to Column | Party Name / Ledger | String, spelling must match Tally exactly |
| Ledger Amount | Map to Column | Net Amount / Value | Decimal numeric, no commas |
Handling Missing Spreadsheet Data
If your source spreadsheet does not contain all the columns required by TallyPrime, you do not need to edit your Excel sheet. Use Tally's Specify Default Value option:
- Voucher Type: If your sheet only contains sales, set a default value of
Sales. - Bank Ledger: If you are importing a statement for a specific bank, set the default bank ledger to
ICICI Bank Current A/c. - Voucher Narration: Set a default text block like
Imported via Excel mapping utility.
Dry-Running and Deploying the Template
Before running a live import, it is highly recommended to test your configuration in a backup copy of your company files:
- Open your test company files in TallyPrime.
- Go to Import > Transactions.
- Select the Excel file path and your custom mapping template.
- Ensure the option Preview Import Summary is set to Yes.
- Click Import. Check the resulting summary for mismatched records, and audit the
tally.implog file if any records show as failed.
TrulyInvoice: The Zero-Mapping Automation Platform
While creating custom Excel mapping templates is functional, it remains a repetitive manual task. Every time a client changes their invoice spreadsheet columns, or a different bank statement format is introduced, your mapping template must be recreated or modified, disrupting bookkeeping workflows.
TrulyInvoice provides a template-independent sync utility. Instead of manually mapping column headers:
- Layout Agnostic: Upload any PDF invoice or spreadsheet export. Our OCR engine parses dates, amount values, items, and tax structures automatically.
- Direct Local Sync: Connects to TallyPrime over a secure Port 9000 connector, sending transactions directly to your books.
- Flat Invoicing Rate: Sync unlimited transaction records for a flat pricing of **plans starting at ₹399/month**, eliminating credit caps and manual spreadsheet formatting.
Chartered Accountant & Accounting Automation Specialist