Working with Custom Templates in udiMagic

Published · Updated

udiMagic comes with a number of pre-designed Excel templates for importing data into Tally Prime. This guide covers both Standard and Custom templates, and how to map your own Excel sheet columns to a Custom template using simple Excel-style formulas.

In this article:

Standard templates

udiMagic software comes with a number of pre-designed Excel formats for uploading data into Tally Prime. These formats are available in the c:\udimagic folder. In addition, a mapping file is provided for each Excel format. The mapping file has the same name as that of the Excel file.

Note: A template is a special format meant to be used as a starting point for creating new formats.

A standard udiMagic template comprises two files:

Import data into Tally Prime [Standard templates]

Follow these steps to upload data from a standard format into Tally Prime:

  1. Start the udiMagic software
  2. Select option Excel to Tally
  3. Select option Import data into Tally
  4. Select Excel File <Any-Standard-udiMagic-template>
  5. Next, click the Start button to import data into Tally

On completion, view the data in Tally using: Gateway of Tally >> Display >> Daybook

What is a Custom template?

A Custom template is a user-defined template. Common uses include creating custom templates for Flipkart, Amazon, or Snapdeal sales so that you can directly import data from the Excel or CSV file downloaded from the e-commerce website into Tally Prime.

How to create a Custom template?

Custom templates are created based on udiMagic Standard templates. You don't have to alter your existing Excel sheet, just map your Excel sheet columns with one of the udiMagic Standard templates. Mapping is done using Excel formulas.

  1. Start the udiMagic software
  2. Select option Excel to Tally
  3. Select option Create Custom Template
  4. Select files:
    • 1st File: <Standard-udiMagic-template> (Example: Vouchers-V7-Sales-Purchase-Advanced.xls)
    • 2nd File: <Your-Excel-File>
  5. Map using Excel formulas
  6. Click the Save button and name your Custom template file

On completion, a mapping file is generated.

Note: A mapping file contains code / instructions to upload data from Excel into Tally. It is also called an XML file.
Import data into Tally Prime [Custom templates]

Follow these steps to upload data from a Custom Excel format into Tally Prime:

  1. Start the udiMagic software
  2. Select option Excel to Tally
  3. Select option Import data into Tally
  4. Select files:
    • Excel File: <Your-Excel-file>
    • Mapping File: <Custom-Mapping-file>
  5. Next, click the Start button to import data into Tally
Important Note: While uploading data using a Custom Excel template, you must enable the option 'Use separate file for XML tags' and select the relevant mapping/XML file.
How to map Excel sheet

The mapping process requires some preliminary work to be done. For mapping your sales format, you must:

  1. List all important and mandatory fields required for a sales entry in Tally Prime (Voucher-type, Invoice-Date, Invoice-Number, Party-Name, Item-Name, Qty, Rate, Item-Amount, Tax-type, Tax-Rate, Tax-Amount, etc.)
  2. Note down your Excel file column names against each field

Snapdeal sales format mapping example:

udiMagic template column Map with (Your Excel sheet) Remarks
UniqueID C Specify the mapping as given for InvoiceNo.
InvoiceNo C In the Snapdeal Excel file, the Invoice Number appears in Column C. Hence, C is specified in the mapping column.
InvoiceDate left(L#,10) In the Snapdeal Excel file, the Invoice Date appears in Column L. However, as the date is in long format (dd/mm/yyyy HH:MM:SS), the Excel function left() is used to fetch the first 10 characters.
Voucher-type "SD-Sales" Voucher-type is not present in the Snapdeal Excel file. Hence, a fixed value of "SD-Sales" is used.
Customer or Party-Name "Jasper" In case of e-tailers, instead of maintaining separate ledgers for each buyer, it is generally preferred to have a single Party ledger A/c and then use the bill-by-bill feature to track receipts. Hence, a fixed value of "Jasper" is used.
StockItem-Name D In the Snapdeal Excel file, the StockItem-Name appears in Column D. Hence, D is specified in the mapping column.
Units "Pcs" Unit of measurement is not present in the Snapdeal Excel file. Hence, a fixed value of "Pcs" is used.
Qty 1 Item-Qty is not present in the Snapdeal Excel file. Hence, a fixed value of 1 is used.
Rate Round(AP#*100/(100+AR#),2) In the Snapdeal Excel file, the rate appears in Column AP, but is inclusive of tax, and the tax rate is given in Column AR. The rate excluding tax must be reverse-calculated as ItemRate * 100 / (100+TaxRate), i.e. AP# * 100 / (100+AR#), rounded to 2 decimals.
Amount Round(AP#*100/(100+AR#),2) As the Qty is always 1, the same formula as used for Rate applies.
TaxType AQ# & " " & AR# & "%" Tax-Rate is not specified in the Snapdeal Excel sheet. Two columns, AQ and AR, are added at the end to hold Tax-type and Tax-Rate. Example: VAT 5% or VAT 15%.
Tax-Amount Round(AP#*AR#/(100+AR#),2) Similar to the Item-Rate formula, the Tax-amount is computed as ItemRate * TaxRate / (100+TaxRate), i.e. AP# * AR# / (100+AR#), rounded to 2 decimals.
Note: The mapped Snapdeal Sales format is provided with udiMagic software. Use these steps to view/modify it:
  1. Start the udiMagic software
  2. Select option Excel to Tally
  3. Select option Modify Custom template
  4. Select file Snapdeal-Sales
  5. Click the Next button to view/modify the mapping
  6. Click the Save button to save changes or the Exit button to exit
Excel formulas used for mapping
Description Excel formula udiMagic mapping formula
Formula with single column =A2*5/100 A#*5/100
Formula with multiple columns =(A2+B2+C2)*5/100 (A#+B#+C#)*5/100
Static text with cell references ="Order Id: " & B2 "Order Id: " & B#
Rounding off to 2 decimals =Round(A2,2) Round(A#,2)
Offset formula to get cell-value from next row =Offset(A2,0,1) Offset(A#,0,1)
Sum value for a specific column across multiple rows =Sum(A2:A5) {Round(A#,2)}
Fixed value, e.g. VoucherType ="Local Sales" "Local Sales"
Absolute and relative cell references =A2+B$1 A#+B$1
Note: Use '#' for relative row numbers like A#, B# etc.

Ready to import your Excel data into Tally?

Download udiMagic and start a 14-day free trial — no credit card required.

Download udiMagic View Pricing