How to Import Data into TallyPrime

Published on: 22-12-2023

 

If you manually enter any data into TallyPrime by referring to transaction details available any external software, you can directly import the data from such software into TallyPrime. TallyPrime allows you to import data from both Excel and XML files in a few simple steps. Importing data from Excel is based on mapping of data from Excel to the fields in TallyPrime, using the templates for mapping. At the same time, if your data is available in XML files, through import, TallyPrime reads the XML data and migrates it into relevant fields in TallyPrime.      

Import Data to update Books of Account in TallyPrime: Let’s consider some business scenarios where data is maintained in external software:

• Using external software such as Marg, QuickBooks, or Sage to maintain your accounts or inventory data? When you send your books of accounts in TallyPrime for audits, you will certainly need to move all the data from the external software to TallyPrime.
• You might be running an online retail business. Maintaining details such as daily sales transactions, payment details, contact details of the parties and so on in Excel sheets is quite common.
• As a chartered accountant, you would help your clients in validating their data for filing returns regularly. You may have received the data, such as bill books, in either Excel or XML formats.

Entering such data into TallyPrime manually could be very time-consuming and could lead to unnecessary errors. Use the Import feature to move all such data into TallyPrime and keep your books of accounts updated and compliant. Worried that the data to be imported might be incomplete or incorrect? You can record the exceptions while importing data and resolve the errors in the data with necessary correction, from a single report.

Import Data for Reconciliation: If your business involves payments through bank, you will need to reconcile your bank statements with your payments recorded in TallyPrime. The Import feature also allows you to import bank statements for bank reconciliation purpose. Similarly, before filing your returns, you will certainly want to reconcile the GST portal data with your books of account. Import the returns from the portal before filing, reconcile the data with the books of account, make necessary changes to your data, and proceed with filing the returns.

The import feature in TallyPrime helps in making data entry faster and ensuring that your data in books is accurate as per the bank and portal records.   

Configure TallyPrime for Importing Data

We understand that your data is critical to your business. TallyPrime is designed to ensure that the Import process is easy and takes necessary steps to move your data accurately. However, you might want to configure the Import process in TallyPrime depending on your data or business practices. 

1. Press Alt+O (Import)> Configuration. 
2. Press Show More to see all the available options for configuring the Import process. 
   
3. From the List of Configurations, set the options based on how you want to import the Data. 
   1. Location of Import/Export Files: Set the folder path to save the imported or exported file.
   2. Behaviour of Import when exceptions exist: The data that you want to import might have some exceptions, such as invalid values, missing masters and so on. By default, TallyPrime is configured to record all such exceptions and complete the import process. However, you can update the behavior of import, as needed.  
      
      1. Stop Import at First Exception: While importing, if any exception is spotted, you cannot proceed further with the import process.  
      2. Record Exceptions and Import: Selecting this will record exceptions and the import process will continue. 
      3. Ignore Exceptions and Import: Data will be imported without taking the exceptions into consideration. 
   3. Overwrite vouchers when a voucher with same GUID exists (for XML format):  If a voucher is exported and imported back to the same company, a duplicate voucher gets created when this option is enabled. Set it to Yes to overwrite the duplicate vouchers. If the vouchers are exported from one company and imported in a different company, then the vouchers will be overridden irrespective of the option being enabled or disabled.
   4. Remove invalid characters such as tabs, extra spaces, & so on (for Excel format): If you are on Release 3.0.1 or earlier, this option is not available. The data in the Excel file might have certain characters like spaces, carriage returns (/r), tab (/t), new line character (/n) – known as noise characters – appended to the values. Enable this option to remove such noise characters to avoid any duplication of values or other exceptions during import.    
   5. Import batch size: You can set the batch size for the number of files to be imported, with this option. 
   6. Enable detailed log: Data log errors are common while transferring data. You can view a detailed event log report by enabling this option and rectify the errors. 

Setting up TallyPrime for importing your data ensures your data is moved from Excel or XML files into TallyPrime according to your business requirements.

Import Data from Excel File Provided by TallyPrime | Sample Excel File

This option is applicable from TallyPrime Release 4.0. Maintaining data in different Excel files, and consolidating them to update your books of accounts, audit, or reconcile can be quite tedious. TallyPrime provides you with Sample Excel Files that are in-line with the TallyPrime fields for data entry.  Simply export the required Sample Excel File in TallyPrime, record all the required data in the file, and save the file with a name as you want. You are all set to import the data now. 

If you are on Release 3.0.1 or earlier, importing data from Excel files is not applicable.

 

In this section

• Export Sample Excel Files
• Import Data from Sample Excel File 

Export Sample Excel File

In TallyPrime 4.0, you can export Sample Excel File for masters or transactions depending on your needs. Each sample file includes a set of worksheets, where each worksheet is structured to enter information in-line with the corresponding field names in TallyPrime. 

Say you need the Sample Excel File for importing masters.

1. Press Alt+O (Import) > Manage > Sample Excel File > Masters. 
   
   By default, All Accounting Masters is selected for exporting the sample file. You can choose any specific master type for which you want to export the sample file. 
2. Press C (Configure) before exporting the Sample Excel File.
   
   1. Export sample file for: Specify for which master is the sample file being exported. 
   2. Export Settings: Configure to ensure a smooth export process.  
      1. File Format: Ensure that the File Format is selected as Excel (Spreadsheet). 
      2. Export to: Specify the location for storing the exported file in your computer. 
      3. Folder Path: Enter the path for storing the exported file. 
      4. File Name: Specify the File Name to be exported. 
      5. Open file after Export: Enabling this will open the Sample Excel file after it is exported.
3. Press E (Export) and export the Sample Excel File.

Similarly, you can export the Sample Excel File for other masters or transactions depending on your needs. 

Use Sample Excel File to record data

Before recording data in the Sample Excel Files, let’s understand how to use the files.

Each Sample Excel File contains a pair of worksheets:

• The first main worksheet for entering master or transaction details.
  • Enter relevant ledger details such as 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.
• The Read Me worksheet for referring to the guidelines. 
  • 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. 

Once you are familiar with the Sample Excel File, record the data as per the required column headers in the file. In case of the ledger:

1. Enter the Name and Group Name in the respective columns, as these are mandatory fields.
2. Enter data under other columns as needed for the ledger master and save the Sample Excel File.

Once you have entered all necessary details for ledgers in the Sample Excel File, you can use the same next to import the ledger data into TallyPrime.

Import Data from Sample Excel File into TallyPrime

The sample files enable you to keep record of your data in the way similar to how you would enter data in TallyPrime. Importing data from Sample Excel Files is very simple. Let’s consider that you need to import the ledger-related data from the Sample Excel File > Worksheet (Ledger).

1. Press Alt+O (Import) > select Masters or Transactions.
   The Import Masters or Import Transactions screen appears.
2. Provide details of the Excel file from which the data needs to be imported.
   1. File Format: Select Excel (Spreadsheet).
   2. File Path: Is prefilled with the location configured under Import > Configuration > Location of Import/Export files. Update the location, if you have the Excel file in a different location.
   3. File to Import: Select the Sample Excel File that you had exported for recording your business data – Ledger, in this case. 
   4. Mapping Template: Select Default Template.
      TallyPrime provides the template by default for the Sample Excel File.
      The Import Data screen appears, as shown below:
      
3. Configure for the impact of import on existing masters using the option Behaviour of Import if Masters already exists. Your existing company data may have the same set of masters as in the Excel file. In this situation, you can choose to:
   1. Combine Opening Balance: The Opening Balance of the existing master and the imported master will be combined. 
   2. Ignore Duplicates: Any duplicate of the existing master in Excel format will be ignored. 
   3. Modify with New Data:  The imported master will replace the existing master. 
4. Preview Import Summary: You can see the preview of what will get imported based on the selected file. 
5. It is always recommended to take a backup of your company data, before importing any external data. 
   1. Backup Company Data before Import: Enabling this option will safeguard your Company Data from getting corrupted or lost.  
   2. Backup Destination Path: Specify the location to store the backup of your Company Data.
6. Press I (Import) to proceed with importing your masters into TallyPrime. 

Depending on the Import configurations you have set, TallyPrime imports the data successfully. The use of Sample Excel Files helps in easily importing data into TallyPrime without any manual entry. 

T0 refer to some sample data, click here to download.

Import Data from Any Excel File

This is applicable from TallyPrime Release 4.0. Your business practices might require you to record transaction details in different ways. Say you maintain data such as your party details, tax details, or expense details in Excel worksheets. Similarly, you might have recorded all the sales, purchase, payments and so on in Excel worksheets with all the necessary information. It is quite normal that you would maintain these worksheets the way it is convenient for your business. If you use this data in TallyPrime to update your Books of Account and ensure that your data is compliant for returns filing or reconciliation purposes, you can use the Import feature in TallyPrime to import the data, by creating simple-to-use mapping templates. These mapping templates help you to map your data in Excel files with the TallyPrime fields.  

 

In this section

• Prepare/set up data based on import scenarios
• Use Mapping Templates for Importing Data from any Excel File
• Download Mapping Templates with corresponding Excel files in ZIP format

Prepare/set up data based on import scenario

Before importing data from Excel, you might need to ensure that your company data or the Excel file has the related or dependent information, for a smooth import experience. In certain cases, you need to verify the configuration in the company. In the absence of required supporting details, the imported data may get into exceptions or the import process might not be successful.    

Ensure availability of dependent masters before importing vouchers
For example:

• Importing transactions involving Multi Currency: Ensure that Currency is already created in the company data. 
• Importing transactions involving godowns:
  • Ensure that the corresponding godowns are create in the company data. 
  • In case Godowns/Locations are enabled in your company, ensure that you have provided Godown/Location allocation details in the transactions or deleted the unwanted Godowns and then import the data.
• Importing Stock Items with opening balance: Ensure that units are created in the company data. 
• Importing salary details or payroll vouchers: Ensure that the required Pay Heads are created in the company data. 
• Importing Quantity for stock items in your voucher: Ensure that you have provided Item Rate.

Ensure availability of dependent configurations in the company
For example:

• Bill-wise details: To import the bill-wise details for ledgers, enable the following options: 
  • F11 (Features) > set Enable Bill-wise entry to Yes.
  • Open the corresponding party ledger > set Maintain balance bill-by-bill to Yes. 
  • To import Bill-wise details in case of transactions, ensure that you have mentioned Bill Type Reference.

Update Excel data with other related and required information
For example:

While importing vouchers in case you need to import details, such as buyer/supplier details, ensure that such details are:

• Added in the Excel worksheet appropriately
• Mapped with the correct fields in TallyPrime.

Update Excel data with field-level mandatory details
For example:

While importing Standard Rate and Standard Selling Price details, ensure that you have provided the Applicable From field in the Excel file and mapped for the relevant field in TallyPrime.

 

Create Mapping Templates for importing data from any Excel file 

Create and save a template by mapping the data from your Excel sheet with the fields in TallyPrime. You can use this template while importing data from the corresponding Excel file so that the data gets imported based on the mapping you have created. 

Create Mapping Templates for Masters

Say you maintain all the stock item details of your company for the financial year or quarter-wise in a worksheet named Sheet1 in the Excel file named Item List.xlsx. Let’s consider the following sample data for ease of understanding:

Now, let’s create a mapping template for the sample data shown in the image: 

1. Press Alt+O (Import) > Manage > Mapping Templates > MASTERS > Create. 
2. Provide the Excel file details for which you need to create a template, and press Enter. 
   1. File Path: Enter the location of the Excel file that contains the master details. 
   2. File to Import: From the List of Files, select the Excel file that you want to import.
   3. Worksheet Name: Lists all the worksheets available in the Excel file. Select the worksheet from the List of Worksheets for which you want to create a template.
      
      The Mapping Template Creation screen for masters appears, as shown below: 
      
3. Provide details required for saving the mapping template.
   1. Save Mapping Template for:
      1. All Companies: Opt for this option, when you want to save the mapping template in the current TallyPrime application folder. Saving a mapping template for all companies by default allows users to access these templates from other companies that are loaded in the same application. For example, if you have saved a mapping template in National Enterprises in your computer, when you load another company – say GaneshJi Enterprises – you will be able to access the mapping templates saved in National Enterprises.
      2. This Company: You can choose this option, when you want to save a mapping template at the company level so that users across different computers can also access the template. Saving a mapping template at the company level provides the flexibility to easily share data with multiple users, when needed. If you are thinking of the security of such mapping templates, nothing to worry! You can restrict other users from editing or deleting the mapping templates that you have saved. That means, other users will only be able to open the mapping template and refer to the details in it but cannot change the settings.
   2. Name: Enter a name for the Mapping Template. 
   3. Type of Master: Depending on the details of your Excel data, select the applicable set of masters. 
4. Provide row and column details of the Excel worksheet. 
   1. Excel Data has column headers: If your Excel data has column headers for the columns, set this to Yes.
   2. 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. 
   3. 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.
   4. 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.
5. 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. 
   Name and Group Name are mandatory fields for creating a mapping template for Accounting Masters. 
   In this case, you can create the following mapping:
   
   Before you save the mapping template, it is recommended to verify the mapping to ensure that the import does not fail. 

6. Save the mapping template. As always, press Ctrl+A to save.
   The template gets saved under the config > excelmaps folder available under the folder where your TallyPrime application is installed. You can use this template next time you import data from the Sheet1 worksheet.

You are now ready to import the data anytime from that Excel worksheet, using the mapping template created for master. When you import the master data, TallyPrime by default creates masters based on unique ledger names from the Excel worksheet.

Need to map data, reuse the saved templates, or even configure the mapping templates further based on your preferences? No worries! TallyPrime provides a host of flexible options to configure and use mapping the way it suits your data needs. To know more, refer to the Mapping Options Based on Types of Data in Excel section. 

Create Mapping Templates for Transactions

Say you maintain all your sales-related details of your company for the financial year or quarter-wise in a worksheet named Sales Bills in the Excel file named Sales Transactions Q1.xlsx. Let’s consider the following sample data for ease of understanding:

Now let’s create a mapping template for the sample data shown in the image: 

1. Press Alt+O (Import) > Manage > Mapping Templates > TRANSACTIONS > Create. 
2. Provide the Excel file details for which you need to create a template, and press Enter. 
   1. File Path: Enter the location of the Excel file that contains the transaction details. 
   2. File to Import: From the List of Files, select the Excel file that you want to import.
   3. Worksheet Name: Lists all the worksheets available in the Excel file. Select the worksheet from the List of Worksheets for which you want to create a template.
      
      The Mapping Template Creation screen for transactions appears, as shown below: 
      
3. Provide details required for saving the mapping template.
   1. Save Mapping Template for:
      1. All Companies: Opt for this option, when you want to save the mapping template in the current TallyPrime application folder. Saving a mapping template for all companies by default allows users to access these templates from other companies that are loaded in the same application. For example, if you have saved a mapping template in National Enterprises in your computer, when you load another company – say GaneshJi Enterprises – you will be able to access the mapping templates saved in National Enterprises.
      2. This Company: You can choose this option, when you want to save a mapping template at the company level so that users across different computers can also access the template. Saving a mapping template at the company level provides the flexibility to easily share data with multiple users, when needed. If you are thinking of the security of such mapping templates, nothing to worry! You can restrict other users from editing or deleting the mapping templates that you have saved. That means, other users will only be able to open the mapping template and refer to the details in it but cannot change the settings.
   2. Name: Enter the name of the Mapping Template.
   3. Type of Voucher: Depending on the details of your Excel data, you can select the applicable set of vouchers from the Types of Vouchers.
4. Provide row and column details of the Excel worksheet. This is similar to how you would do in case of masters. 
   1. Excel Data has column headers: If your Excel data has column headers for the columns, set this to Yes.
   2. 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. 
   3. 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.
   4. Import Data from column to Last Column: Enter the starting column and ending column details that should be considered for importing data.
      • TallyPrime considers importing data from Excel up to 702 columns.
5. Map fields in TallyPrime with the columns in your Excel file. Depending on the way you have maintained data, you can start mapping the field names with the data in the Excel file. 
   Voucher Type Name, Voucher Date, Voucher Number, Ledger Name, and Ledger Amount are mandatory fields for creating a mapping template for Accounting Vouchers. 
   In this case you can create the following mapping: 
   
   Before you save the mapping template, it is recommended to verify the mapping to ensure that the import does not fail. 
6. Save the mapping template. As always, press Ctrl+A to save.
   The template gets saved under the excelmaps folder available under the folder where your TallyPrime application is installed. You can use this template next time you import data from the Sales worksheet.

You are now ready to import the data anytime from that Excel worksheet, using the mapping template created for transactions. By default, the mapping template considers creating vouchers based on Voucher Type Name, Voucher Date and Voucher Number found in the Excel worksheet. In case you need to configure the mapping template to set the way the vouchers should be created, TallyPrime provides you the additional flexibilities. 

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

Mapping of data using Ledger Entry Details from Multiple Rows

You may have the voucher details of different parties entered in multiple rows in your Excel file. 

You can use the Ledger Details from Multiple Rows option in the mapping template to easily import this data into TallyPrime. 

In the Mapping Template Creation screen,

1. Map Voucher Type Name, Voucher Date and Voucher Number to the Voucher Type, Date and Invoice Number column headers available in the Excel file. 
2. Under Field (TallyPrime), from the List of Fields, select Ledger Details from Multiple Rows.
3. Map the fields in TallyPrime with the corresponding column headers in your Excel worksheet.
   The Ledger Name and Ledger Amount are mandatory fields for mapping under this option. 
4. Map for the other fields in the voucher.
   The Mapping Template appears, as shown below:
   
5. Once you customise the mapping template, accept the screen to save the template.

Mapping of top and bottom entries in vouchers using Ledger Entries from Fixed Columns

You may have entered the details of the sales vouchers in multiple columns in your Excel file. You may have entered the top ledger and bottom ledger details of the sales vouchers in different columns in your Excel sheet. 

You can use the Ledger Entries from Fixed Columns option in the mapping template to easily group and import this data into TallyPrime. 

In the Mapping Template Creation screen,

1. Map Voucher Type Name, Voucher Date and Voucher Number to the Voucher Type, Date and Invoice Number column headers available in the Excel file.
2. Under Field (TallyPrime), from the List of Fields, select Ledger Details from Fixed Columns.
   You can then select which Column Headers in your Excel file contain details of the top ledgers in the sales voucher, say Party Name. 
3. Under Field (TallyPrime), select Top Ledger (Party, Cash, Bank, & so on). 
   The Ledger Name and Ledger Amount are mandatory fields for mapping under this option. 
4. Map the fields in TallyPrime with the corresponding column headers that contain details of the top ledgers in your Excel worksheet.
5. Similarly, Under Field (TallyPrime), select Bottom Ledger (Tax, Discount, & so on) for mapping other ledger details like Sales, Taxes, Discount, Expenses and so on. 
6. Map the fields in TallyPrime with the corresponding column headers that contain details of the bottom ledgers in your Excel worksheet.
   The Mapping Template appears, as shown below:
   
7. Once you customise the Mapping Template, accept the screen to save the template.

After saving the template, you can use the same to import your transactions into TallyPrime. View the Day Book to view the imported transactions in TallyPrime. 

Download Mapping Templates for data entries applicable in e-commerce businesses 

You also have the option to download Excel worksheet with prefilled column headers and templates already mapped to the worksheet.

The following table lists the sample files that can be used for recording data, especially if you are into e-commerce or online retail industries.

Serial No.	E-commerce/ Other Software	Zip Files
1.	Amazon	Amazon B2B
2.	Busy	Busy
3.	Marg	Marg
4.	Odoo	Odoo
5.	QuickBooks Desktop	QuickBooks Desktop
6.	QuickBooks Online	QuickBooks Online
7.	Sage	Sage
8.	Shopify	Shopify

1. Click the required sample file to download.
   • You can download more than one zip files, if needed.
   • You can also reach this table by selecting the Other Templates/Sample Files option available from the Import Masters or Import Transactions screen.
2. Unzip the file in the downloaded folder.
3. Move the mapping template (.tsf file) to the excelmaps folder under the TallyPrime application folder.
   
   This is needed to list the mapping templates under the List of Mapping Templates, while you select the template for importing data from Excel.
   It is also recommended that you keep a backup of these templates in a separate folder. 

Just fill in the worksheet with relevant data based on your day-to-day transactions or master details, and import using the mapped templates. Isn’t that simple? Moreover, you can use the Excel files as is or make any necessary modifications to record data and alter the corresponding mapping files as needed. 

If you are downloading the TSF files for Other Templates/Sample Excel Files for the first time, you must manually create the config > excelmaps folder under the TallyPrime installation folder.

Ensure that you check the Templates and Sample Excel Files and alter them according to the data that you want to import into TallyPrime.

 

Import Data by using Mapping Templates 

You can create and customise your own template as per your business requirements and start the import process. You can manage the mapping template that you have created in TallyPrime. Similarly, if you have downloaded the Other Templates/Sample Excel File for mapping your data, you can manage those templates as well. 

Let’s consider that you need to import the sales transactions data from the worksheet named Sales Bills from the Excel file Sales Transactions Q1.xlsx.

1. Press Alt+O (Import) > select Transactions.
   The Import Data screen appears.
2. Provide details of the Excel file from which the data needs to be imported.
   1. File Format: Select the file format as Excel (Spreadsheet).
   2. File Path: Prefilled with the location configured under Import > Configuration > Location of Import/Export files. Update the location, if you have the Excel file in a different location.
   3. File to Import: Select Sales Transactions Q1.xlsx. 
   4. Mapping Template: Select the template you have saved for the selected Excel worksheet Sales Bills.
      The Import Data screen appears, as shown below:
      
3. Configure for the impact of import on existing masters using Behaviour of Import is Masters already exists. It is possible that your existing company data has the same set of masters as in the Excel file. In this situation, you can choose to:
   1. Combine Opening Balance: The Opening Balance of the existing master and the imported master will be combined. 
   2. Ignore Duplicates: Any duplicate of the existing master in Excel format will be ignored.  
   3. Modify with New Data:  The imported master will replace the existing master. 
4. Preview Import Summary: You can see the preview of what will get imported based on the selected file. 
5. Configure for backup, if needed.
    It is always recommended to take a backup of your company data, before importing any external data. 
   1. Backup Company Data before import: Enabling this option will safeguard your Company Data from getting corrupted or lost.  
   2. Backup Destination Path: Specify the location to store the backup of your Company Data. 
6. Press I (Import) to proceed with importing your masters into TallyPrime. 

Depending on the Import configurations you have set, TallyPrime imports the data successfully. 

The data that has been successfully imported is displayed in different reports in TallyPrime. 

• To view the Masters that got imported, press Alt+G (Go To) > Chart of Accounts > Ledgers. 
• To view the Transactions that got imported, press Alt+G (Go To) > Day Book.

Data Exceptions in Import

If the data in the Excel has any exceptions or errors, TallyPrime completes the Import process, but with a few exceptions/errors. TallyPrime imports the data, and displays the following message:

You also have the choice to either stop import of the data in the Import Configuration screen, if there is any exception or import data without recording any exception to avoid any risk of corrupting your existing data. 

By default, TallyPrime will record the exceptions and proceed with the Import process. 

While importing data, if any masters or transactions have any errors, the same is recorded either in the Exceptions Report or Event Log, depending on the type of error. 

1. Exceptions that require creation of new masters or transactions are logged under the Import Exceptions report. To resolve the Import exceptions,
   • Press Alt+O (Import) > select Exceptions.
     For more information, refer to the Data Exceptions & Resolutions topic. 
2. Exceptions that require alteration of the existing masters or transactions are logged under the Event Log report. You will need to alter the specific masters or vouchers to resolve the errors listed in Event Log.
   • Press F1 (Help) > Troubleshooting > Event Log. 

Mapping Options Based on Types of Data Entry in Excel | Configure Mapping Templates

TallyPrime Release 4.0 helps you to create mapping templates for ease of importing data from Excel worksheets. Depending on the way you maintain the data in Excel file, you will need to create the mapping accordingly. Say the Excel worksheet does not have column headers, or you need to combine values from multiple cells, or calculate the nett amount for your transactions. Use the host of flexibilities with the mapping templates to ensure that such specific requirements can be handled easily.

In this section

• Mapping data from Excel File- with or without Column Headers
• Mapping data by Computing Values from Excel
• Mapping data that is common/fixed in multiple Rows in the Excel File
• Additional options as Unique Values for creating Masters or Transactions
• Exclude Rows in Excel for Importing Data

Mapping data from Excel file with or without Column Headers

Excel data with Column Headers

Consider that you have column headers for each column of data in the Excel file, as shown in the image below:
 

Note: To create a mapping template for ledgers, Name and Group of the ledgers are mandatory details.

The mapping template appears, as shown below:

Excel data without Column Headers 

If any column in the Excel does not have a column header, you can specify the column while mapping. For instance, the Excel file shown below does not have any column header. 

In the Mapping Template for Masters, 

• Set the option Excel Data has column headers as No. 
• Map the fields in TallyPrime with the corresponding columns in your Excel worksheet.

The mapping template appears, as shown below: 

Mapping data by computing values from Excel 

You might have Opening Balance stored in multiple columns, with or without column headers in the Excel file. Similarly, you might want to compute values from different columns in the Excel worksheet. In such cases, you can use the Compute Value mapping options for importing such data. 

Compute Value where field value will be Nett Amount 

Opening Balance in different columns for debit & credit | Compute Value using Cr/Dr 

Let’s say you maintain an Excel sheet for entering the opening balances of your party accounts. You have separate columns for entering the debit and credit amount for each party and you want to import this list into TallyPrime. 

In the Mapping Template Creation screen,

1. Under Field (TallyPrime), select Ledger – Opening Balance to map with the column in your Excel file. 
2. Under Column Header (Excel), select Compute Value from the List of Column Headers.
   
   The Compute Value for: Ledger – Opening Balance screen appears.
   • Map the Column Headers and specify if the value is Debit or Credit and proceed for import. 
     
     After importing the data, you can verify the change in credit and debit amounts from Alt+G (Go To) > Trial Balance. 
     

Opening Balance with debit & credit as cell value | Compute Value as per Cell Value 

In the Excel file, you may have mentioned whether the value is Dr or Cr just beside the Opening Balances.

In this case, simply selecting the As per Cell Value option will import the data as mentioned in the cell in the Excel file. 

Opening Balance with specific column headers for debit & credit | Compute Value as per Column Headers 

Similarly, if you have put the data under specific headers, selecting the As per Column Headers option will prompt the system to consider the values as they are specified under the Column Headers and do the mapping. 

Selecting the Column Headers Debit and Credit will import all the values under them. 

Combine values where field value will be a combination of multiple cell values 

Let’s say that you have maintained stock item details in Excel sheet as follows:  

In case you want to use the details from the Column Headers Model and Size as the stock item name, you can map using the Compute Value option in the Import process. 

In the Mapping Template Creation screen,

1. Under Field (TallyPrime), select Name.
2. Under Column Header (Excel), select Compute Value. 
   • Under Column Header (Excel), select the field values that you want to combine, for example, Model and Size.
     
3. Map other relevant fields for the stock items and save the mapping template.
4. Import the data using the saved template. 
   The Model and Size of the stock items appear as Name in the Chart of Accounts in TallyPrime. 
   

Compute values where field value will be set to Yes

There are certain fields in TallyPrime that accept only Yes/No as the input, like Maintain Balances bill-by-bill, Cost centres are applicable and more. 

In you Excel file, you may have entered values other than Yes/No which you want to map with the fields in TallyPrime. 

Let’s say your Excel file has data as follows and you want to map the value for Maintain balances bill-by-bill:

In the Compute Value for: screen,

1. Under Column Header (Excel), select Maintain Balances bill-by-bill. 
2. From the List of Conditions, select containing. 
3. Select the Cell Value, for example, Billwise in this case. 
   
4. Map other relevant fields for the ledgers and save the mapping template. 

By using this option, you can easily map your Excel data to TallyPrime fields that accept logical values like Yes or No.

This capability simplifies the process of importing data and ensures that your data is correct and consistent within TallyPrime.

Mapping data that is common/fixed in multiple rows in the Excel file | Fixed Field Value 

Let’s say you have entered details of multiple parties in an Excel file, without specifying their Group, as they all belong to the same group – Sundry Debtors. 

However, as you know that Group Name is mandatory during ledger entry, while importing you will need to specify the Group for the ledgers. You can use the Specify Fixed Field Value option while mapping the data to import such masters easily.   

In the Mapping Template for Masters, 

1. Map the fields in TallyPrime with the corresponding column headers in your Excel worksheet.
2. Under Field (TallyPrime) select Group Name and under Column Header (Excel), select Specify Fixed Field Value. 
   A List of Groups is displayed on the screen to provide you a seamless experience for mapping.
3. From the List of Groups, select the Group Name that you want to consider as the fixed value while mapping the ledgers with TallyPrime, say Sundry Debtors.
   

As a result, all the parties in your Excel file will be systematically assigned to the specified group in TallyPrime, simplifying the data import process and ensuring that your ledger details are accurately organized for the import process. 

Additional options as Unique Values for creating Masters or Transactions

Based on the parameters you select for creating the uniqueness, the masters or transactions will be created. When you import masters from an Excel file, TallyPrime by default creates masters based on each unique master Name. 

Similarly, when you import vouchers from an Excel file, TallyPrime by default creates vouchers based on each unique Voucher Type Name, Voucher Date and Voucher Number. 

However, you can choose the unique values based on which the masters or transactions should be created when importing data. Once you have mapped the fields with the Excel file, select the required option under Unique values for creating masters > Additional Options.   

In case you do not see this option in the mapping template, press F12 (Configure) > Show Additional Options for creating Masters > Yes. 

1. Every Blank Row: Say you have entered the details of four parties in your Excel file and you have left a row blank before the details of the next party.
   
   

   On selecting this option and importing data, TallyPrime will use the data until each blank row to create a master. 

2. Every Nth Row:  Say you have entered the transaction details with parties in multiple rows and each row represents one single voucher. You can specify the set of rows in the Excel file based on which TallyPrime should create the vouchers while importing. 
   
   Let’s suppose you have maintained an Excel worksheet in the following manner: 
   
   In the Mapping Template Creation screen,
   • Select the option Every Nth Row as the Unique values for creating vouchers.
   • Specify the Value for N as 1. 
     Every row will be considered as one voucher while importing. 
3. Specify Columns: You can specify the list of columns that will determine the uniqueness while creating the master.  
4. Specify Column Headers: Similarly, you can specify the list of column headers that will determine the uniqueness while creating the master. 

The availability of additional options for creating masters or transactions adds flexibility and customization to the import process. These options enable you to tailor the uniqueness criteria to your specific needs, making it easier to import and manage data effectively. 

Exclude rows in Excel for importing Data

You can specify the conditions based on which data from one or more rows should be excluded while importing the data. Let’s say you have entered transaction details with parties in an Excel worksheet in the following manner:

You want to exclude rows that contain the entries for Sunday. 

In the Mapping Template Creation screen,

1. Set the option Exclude Rows in Excel to Yes.
   In case you do not see this option, press F12 (Configure) > set the option Exclude rows in Excel while importing Data as Yes. 
2. Select the Column Header where the data in the row is to be excluded. 
3. Select the Condition from the List of Conditions.
4. Enter the value based on which you want to exclude the row while importing.
   The screen for setting the Conditions for Excluding Rows appears, as shown below: 
   
5. Accept the screen and proceed with the importing your transactions into TallyPrime. 

You can select more than one Column Header or more conditions for the same Column Header for excluding rows in the Excel worksheet. 

By using this option, you can easily set conditions to filter out unwanted data during the import process. Whether you need to exclude rows based on dates, specific values, or other criteria, TallyPrime empowers you to tailor your data for importing. 

Alter Mapping Templates

After saving a mapping template, you can alter the same in the Mapping Template Alteration screen. For example, you may have created a mapping template for the list of Stock items in Excel and now you want to alter the row number from which you want to import the data from. 

1. Press Alt+O (Import) > Manage > Mapping Templates > MASTERS > Alter.
2. Provide details for the mapping template that needs to be altered. 
   1. Select the Mapping Template that you want to alter.
   2. Specify the File Path.
   3. Select the File to Import.
   4. Select the Worksheet Name. 
      
      The Mapping Template Alteration screen appears, as shown below: 
      
3. Proceed with altering the field in the mapping template. 
4. Accept the screen and save the mapping template. 

In conclusion, this feature provides a convenient and flexible way to modify your mapping templates as needed. Once the alterations have been made, you can easily save the updated mapping template, ensuring that your data import processes remain accurate and efficient.

Delete Mapping Templates

You can delete a specific mapping template in case you do not require it any further, in just a few simple steps.  

1. Press Alt+O (Import) > Manage > Mapping Templates > Alter. 
2. Press Alt+D (Delete) to delete the saved mapping template. 

However, TallyPrime provides you the flexibility of deleting the mapping templates saved for either All Companies or This Company in your TallyPrime. 

In this section

• Delete Mapping Templates saved for All Companies 
• Delete Mapping Templates saved for This Company

Delete Mapping Templates saved for All Companies

You can delete the mapping templates saved for All Companies in your TallyPrime.   

1. Press F1 (Help) > Troubleshooting > Delete Mapping Templates Saved for All Companies. 
2. From the List of Mapping Templates, select the mapping template that you want to delete, say Groups in this case.  
   
   The following message appears:
   
3. Select Yes and confirm the deletion. 

The selected mapping template will be deleted for all companies.

Similarly, you can also delete all the mapping templates saved for all companies at one go.

1. From the List of Mapping Templates, select Delete All. 
   
   The following message appears: 
   
    
2. Select Yes and confirm the deletion. 

Whether you need to delete a single mapping template or clear out all templates associated with all companies in your TallyPrime, the process is quick and easy to execute. 

Delete Mapping Templates saved for This Company

You can delete a mapping template saved only for the company that is currently loaded in your TallyPrime. 

1. Press F1 (Help) > Troubleshooting > Delete Mapping Templates Saved for This Company. 
2. From the List of Companies, select the Company for which you want to delete the mapping template.
   
3. From the List of Mapping Templates, select the mapping template that you wish to delete, say Ledger in this case.  
   
   The following message appears:
   
    
4. Select Yes and confirm the deletion. 

The selected mapping template will be deleted for the company that you have selected.

You can also delete all the mapping templates saved for the company at one go.

1. From the List of Mapping Templates, select Delete All. 
   
   The following message appears: 
   
2. Select Yes and confirm the deletion.
   Whether you need to delete a single mapping template or clear out all templates associated with a particular company in your TallyPrime, the process is quick and easy to execute. 

TallyPrime offers an efficient and flexible way to manage your mapping templates, giving you the ability to delete templates as needed. This feature allows you to maintain a well-organized and clutter-free workspace within TallyPrime, ensuring that your data import processes remain streamlined and efficient.

Import Data from XML

You may have recorded your masters and transactions in an external software or have a company created in an older version of TallyPrime. Now, you might want to move the data into the newest version of TallyPrime. You can utilise the TallyPrime’s Import feature and get your job done in a few simple steps. All that you have to do is, export the required masters and transactions into an XML file and then import it back into TallyPrime. When you export any data from other company or software, and want to import the same data into TallyPrime, ensure to save it in XML file format.

Note: The company features that were enabled while exporting the data should be enabled in the company in which the data is imported.

Let us consider that you want to import masters created in National Enterprises (Bangalore) to a new company – National Enterprises (Chennai). The options Maintain stock categories and Maintain batch-wise details must have been enabled in National Enterprises (Bangalore) before exporting the masters. To import masters into National Enterprises (Chennai), you need to ensure that both the options Maintain stock categories and Maintain batch-wise details are enabled in National Enterprises (Chennai) before importing.

In this section

• Import data from XML file
• Import data from Marg Software

Import data from XML file

1. Press Alt+O (Import) > select Masters or Transactions.
2. In the Import Masters screen, fill in the details for importing your data. 
   1. File Format: From the List of File Formats, select XML (Data Interchange) as the file format.

   2. File Path: Enter the location where your Data is stored in XML file format.
      By default, the export location is the TallyPrime installation folder, which is also the default import location. Therefore, it is not required to specify the file path during import.

   3. File to Import: From the List of Files, select the XML file that you want to import. 
   4. Behaviour of Import if master already exists: (Not applicable if you are importing transactions) It is possible that your existing company data has the same set of masters as in the XML file. In this situation, you can choose to:
      1. Combine Opening Balance: The Opening Balance of the existing master and the imported master will be combined. 
      2. Ignore Duplicates: Any duplicate of the existing master in XML format will be ignored.  
      3. Modify with New Data:  The imported master will replace the existing master. 
         
3. Press Enter to proceed with the import process.

Depending on the Import configurations you have set, TallyPrime imports the data. 

If you are using TallyPrime Release 3.0.1 or earlier, click here.

Export Data from Marg Software

In MARG, you have an option to export the masters and transactions in MARG to TallyPrime.

• Select Final Reports > Utilities > Data IMPORT/EXPORT > Tally/Busy Erp9.

Data from masters and transactions from Marg will be exported in XML format. You can import the same XML file to TallyPrime.

• Press Alt+O (Import) > Masters.

You can check the statistics after importing masters and transactions. 

In conclusion, the ability to import data into TallyPrime from external sources like Excel and XML files significantly simplifies the process of maintaining accurate and up-to-date business records. This import feature not only saves your valuable time but also reduces the risk of errors that can occur during manual data entry. So, whether you are a business owner, an accountant, or a financial professional, utilise the power of TallyPrime’s Import feature to ensure the seamless flow of data and enhance your overall business efficiency.

Source URL: https://help.tallysolutions.com/tally-prime/data-exchange-tally-prime/import-data-in-tally/

---