Add to Favourites

Elements of Importing Excel Data 

Manually entering data into TallyPrime can be time-consuming, especially when large volumes of transactions or masters details are stored in Excel files. To simplify this process, TallyPrime provides a seamless Import feature that allows you to bring in data from Excel files with just a few clicks. 

Sample Excel File vs User-Defined Excel File | Pre-defined vs User-Defined

• Sample Excel File: A ready-to-use template provided by TallyPrime, structured with predefined columns that align perfectly with TallyPrime’s data format. Since the structure is already set, no additional mapping is required—simply enter your data and import it directly.

• User-Defined Excel File: Import data from any Excel file. You can create a Mapping Template to map the columns in your Excel file with the corresponding fields in TallyPrime, making it adaptable to different data structures.

Choosing the right approach depends on your business needs—use the Sample Excel File for a quick, hassle-free import or the create a Mapping Template when working with your own Excel formats.

What are Sample Excel Files? | Pre-defined Excel Files

The Sample Excel File enables you to keep record of your data in the way similar to how you would enter data in TallyPrime. Using Sample Excel Files helps in easily importing data into TallyPrime without the need to do any manual entry.

TallyPrime follows a defined structure when importing data from Excel. The Sample Excel File provides a template for entering data correctly before importing it into the system.

The Sample Excel File consists of the following key sheets:

Data Entry Sheet

The Data Entry Sheet is the first main worksheet for entering master or transaction details. It includes predefined columns, sample entries, and validation rules to ensure that all information adheres to the required format.

• Enter relevant details for example, for Ledgers enter details like Name, Group, Mailing Details, GSTIN/UIN, as needed.
  Column headers with bold font indicate that those are mandatory for importing the data.
• Do not edit any column headers, as they are mapped internally for ease of importing data. 
• Add or remove columns as needed for data entry. Refer to the Read Me worksheet for adding new column names as mentioned.

ReadMe Sheet

This sheet provides detailed instructions on how to use the Sample Excel File, including data format requirements, mandatory fields, and best practices for successful data import.

• Do not make any data entry in this worksheet.
• Use the different columns as reference for easy of data entry in the main worksheet.
  • List of Fields: Contains all the fields in TallyPrime, related to the ledger master.
  • Applicable Countries: Indicates the countries that the ledger fields are applicable for. 
  • Applicable Values: Contains values that are applicable for the ledger fields in TallyPrime.

User-Defined Excel Files and Mapping Templates

TallyPrime allows you to import data from any Excel file, making it adaptable to different data structures. Using a Mapping Template, you can map the columns in your Excel file with the corresponding fields in TallyPrime, ensuring seamless and accurate data import.

Understanding Data in Multiple Rows and Multiple Columns

Some data structures require multiple rows or columns for a single transaction or master. Handling these formats correctly ensures accurate data import.

• Multiple Rows: Your data might be spread across multiple rows, where details like party ledger, sales/purchase ledger, and tax ledgers appear in a single column but across different rows.
  A sample image of Excel data entered in multiple rows:

  
  
  

• Multiple/Fixed Columns: Your data might be spread across multiple columns, with details like party name and taxes (CGST, SGST, IGST) recorded in separate columns.
  A sample image of Excel data entered in multiple columns:

  
  
  

Components of a Mapping Template

A mapping template in TallyPrime helps define how data should be imported from an Excel file. It includes various fields that allow precise mapping of information.
When you click Create > Mapping Template > Master/Transactions, this sub-form appears before the Mapping Template Creation screen.

• File Path: Specify the location of the Excel file containing master details.
• File to Import: Select the desired Excel file from the available list.
• Worksheet Name: Choose the worksheet from which data needs to be imported.

Once the Mapping Template Creation screen appears, you can configure the template by providing the necessary details.

Saving the Mapping Template:

• All Companies: Saves the template in the TallyPrime application folder, making it accessible across all companies loaded in the same application.
• This Company: Saves the template at the company level, allowing access across different computers. Ensure security by restricting editing or deletion.

Additional Configurations:

• Name: Assign a name to the mapping template.
• Type of Master or Type of Voucher: Select the appropriate master or voucher category based on the Excel data.
• Excel Data Structure:
  • Excel Data has column headers: If the data includes column headers, set this to Yes.
    If your Excel data does not have column headers, set this option to No. 
  • Row no. for column headers: Enter which row of the Excel has the column headers. This helps the template to identify the header row while importing data. 
  • Import Data from row to (blank for end): Enter the starting row number and the ending row number that should be considered for importing data.
    • blank for end represents the last row until which the data is available in the worksheet.
  • Import Data from column to: Enter the starting column and ending column details that should be considered for importing data.
    • Last Column represents the column until which the data has been entered in the Excel file. TallyPrime considers importing data from Excel up to 702 columns.
  • Map Field (TallyPrime) with Column Header (Excel) or Column (Excel). Depending on the way you have maintained data, you can start mapping the field names with the data in the Excel file. 

When mapping ledger details using fixed columns, users can choose to map with either the Top Ledger or Bottom Ledgers.

• Top Ledger: Typically includes ledgers such as Party/Supplier, Bank, and Cash.
• Bottom Ledger: Primarily mapped with ledgers like Expenses, Sales, Purchases, and taxes such as SGST, CGST, and IGST.

The mapping template is saved in the config > excelmaps folder within the TallyPrime installation directory. It can be reused for future imports, ensuring a seamless data migration process.

Prerequisites for Importing Data into TallyPrime

Before you begin importing data into TallyPrime, ensure that you have met the following prerequisites for a seamless experience.

1. Import Configurations

TallyPrime provides flexible import configurations that allow you to map data fields in Excel to relevant fields in TallyPrime. Before importing, you must:

• Enable and set up the import configurations.
• Define the data format and structure.
• Ensure that Dependent masters exist before importing vouchers. For example,

  • Multi-Currency Transactions: Ensure the currency is already created in your company data.

  • Godown Transactions: Check that the godowns exist, and allocation details are included, if enabled.

  • Stock Items with Opening Balance: Ensure units of measurement exist.

  • Payroll Data Import: Create the required Pay Heads in the company data.

• Enable the required features under Configurations as some features need to be enabled before you can import related data. For example, 

  • Bill-wise Details for Ledgers
    • Go to F11 (Features) and set Enable Bill-wise entry to Yes.
    • Open the party ledger and set Maintain balance bill-by-bill to Yes.

To know more about Import Configurations, click here. 

2. Mandatory & Dependent Fields

For successful data import, ensure that all required fields are correctly mapped.

Mandatory Fields in Excel

These fields must be present in your Excel file for a successful import. Some common mandatory fields include:

• For Ledgers: Ledger Name, Under Group
• For Vouchers: Date, Voucher Type, Debit/Credit Amount

Dependent Fields in TallyPrime

Certain fields in TallyPrime depend on other data being available in your system. For example:

For Masters

Masters include ledgers, stock items, cost centers, etc. Instead of creating these manually, you can import them directly, saving time and minimising errors.

• Ledgers: The ledger’s Parent Group must already exist in the company data before import.

• Stock Items: The Stock Group under which the item is to be created must exist. If not specified, the stock item is imported under the Primary Group (only for stock items).

• Units of Measurement: Ensure the Unit of Measurement exists before importing stock items.

• Compound Units: Both the first and second units must be created before importing compound units.

For Vouchers

You can import transactions such as sales, purchases, receipts, and payments to quickly update your books. This is particularly useful while migrating data from an external accounting software and reconciling records for accuracy and completeness.

• The required stock items are already created.
• The necessary units of measurement exist before importing vouchers.
• While importing salary details, ensure that the required Pay Heads are already created in the company data before proceeding.

Below are the key mandatory and dependent fields for different data types:

By ensuring these prerequisites, you can prevent import errors and optimize your data entry process.

Configure TallyPrime for Importing Data

You can configure the import feature in TallyPrime based on your requirements. This includes selecting the right import format, setting up error handling to ensure data is correctly placed.

Under Import Configuration (Alt+O > Import > Configuration), you can set up the following configurations:

• Location of Import/Export Files: Define the folder path where imported or exported files should be stored.
• Behaviour of Import when exceptions exist: If the data contains issues like missing masters or invalid values, you can decide how TallyPrime should process it. 
  
  
  • Stop Import at First Exception: The import process stops if any error is detected.
  • Record Exceptions and Import: The import continues while recording exceptions in the Exceptions Report for review.
  • Ignore Exceptions and Import: Data is imported without considering any exceptions.
• Overwriting Vouchers (XML format): If a voucher with the same GUID exists, enabling this option ensures that it is overwritten instead of creating a duplicate. If importing into a different company, vouchers are overwritten regardless.
• Removing Invalid Characters (Excel format): Eliminates extra spaces, tabs, and special characters from the Excel file to prevent duplication or errors. (Available from Release 3.0.2 onward.)
• Import Batch Size: Set the number of records to be processed in each batch during import.
• Enable Detailed Log: Capture a detailed event log to identify and rectify import errors efficiently.

By configuring these settings, you can ensure a smooth transition of data from Excel files into TallyPrime, aligning with your business processes.