Import data from Excel to Tally using Sample Excel file

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.