Add to Favourites

Map Data from Multiple Rows & Columns in Excel

When preparing data in Excel for import, you might have entered details in different formats, either spread across multiple rows (for different parties or vouchers) or across multiple columns (for various items or components within a voucher).

TallyPrime allows you to import such data easily by creating a Mapping Template that maps the fields in your Excel file with the corresponding fields in TallyPrime.

Map Data from Multiple Rows

Let’s say you have entered the sales voucher details across multiple rows in Excel, as shown below.

In the Mapping Template Creation screen:

1. Save Mapping Template for: Select All Companies.
2. Name: Enter a name for the mapping template, for example, Sales.
3. Type of Voucher: Select Accounting Vouchers.
4. Set Excel Data has column headers to Yes and Row no. for column headers to 1.
5. Import Data from row: Enter 2.
6. Under Field (TallyPrime), map the following fields with their corresponding column headers in Excel:
   1. Voucher Type Name with Voucher Type.
   2. Voucher Date with Date.
   3. Voucher Number with Invoice Number.
   4. Ledger Name with Ledger Name.
   5. Ledger Amount with Ledger Amt.
   6. Ledger Amount Dr/Cr with DrCr.
   7. Item Name with Item Name.
   8. Billed Quantity with Quantity.
   9. Item Rate with Rate.
   10. Item Amount with Value.
       
7. Press Ctrl+A to save the mapping template and import the data using the saved template.

Map Data from Multiple Columns

Let’s say you have entered the sales voucher details in multiple columns in Excel, as shown below.

In the Mapping Template Creation screen:

1. Save Mapping Template for: Select All Companies.
2. Name: Enter a name for the mapping template, for example, Sales Voucher Without Inventory.
3. Type of Voucher: Select Accounting Vouchers.
4. Set Excel Data has column headers to Yes and Row no. for column headers to 1.
5. Import Data from row: Enter 2.
6. Under Field (TallyPrime), map the following fields with their corresponding column headers in Excel:
   1. Voucher Number with Invoice Number.
   2. Change Mode: Select Specify Fixed Field Value and enter Accounting Invoice under Fixed Value.
   3. Under Top Ledgers (Party, Cash, Bank, & so on),
      1. Map Ledger Name with Party Name
      2. Ledger Amount with Invoice Value.
      3. For Ledger Amount Dr/Cr, select Specify Fixed Field Value and enter Dr under Fixed Value.
   4. Under Bottom Ledgers (Tax, Discount, & so on),
      1. Map the Sales ledger by selecting Specify Fixed Field Value for Ledger Name and entering Sales under Fixed Value.
      2. Map Ledger Amount with Sales Amount and set Ledger Amount Dr/Cr to Cr using Specify Fixed Field Value.
   5. To map the CGST ledger,
      1. Select Specify Fixed Field Value for Ledger Name and enter CGST under Fixed Value.
      2. Map Ledger Amount with CGST and set Ledger Amount Dr/Cr to Cr using Specify Fixed Field Value.
   6. Similarly, to map the SGST ledger,
      1. Select Specify Fixed Field Value for Ledger Name and enter SGST under Fixed Value.
      2. Map Ledger Amount with SGST and set Ledger Amount Dr/Cr to Cr using Specify Fixed Field Value.
         
   7. Press Ctrl+A to save the mapping template and import the data using the saved template.

To refer to some sample data and corresponding Mapping Templates, click here.