How to Import & Export Data from Excel to Tally Without Using Software?

Tally ERP.9 is the most widely used small and medium business enterprise resource planning (ERP) system in India and Southeast Asia. Tally users have been rising day by day among businesses today. Tally's popularity has grown due to the software's interoperability and flexibility. That's why it is important to know how to export as well as import data from Excel to Tally in a few steps. 

Did you know?

Tally is used in over 100 countries!

Why use Tally?

• Every business relies on data to function. Data that is accurate, that is, data free of mistakes, is extremely important to a business. And IT automation is the key to having error-free data.  
• Tally is software that helps organisations run more efficiently by automating their processes. It raises the total productivity of the company by increasing efficiency. 
• Businesses use a variety of software to meet their specific company needs. Preserving the integrity of data remains critical in such scenarios. Multiple software must have proper data to ensure data integrity. 
• Because Tally does not allow you to import data from other software by default, you may use the Excel to Tally Import application to import data from another program (other than Tally) into Tally via Excel. Because this is an automated process with minimum human intervention, the possibility of human mistakes is removed. 

Also read: Tally ERP 9: What Is It and How Can I Use It?

Import data from excel to Tally ERP9 or Tally prime

Step 1: Create the relevant ledgers and an example Tally entry. 

Step 2: Follow the instructions to export the sample entry in Tally. Display - Day Book. Also, hold down ALT plus E. In Tally software, the daybook will be exported in XML format. It is useful when only a single voucher has to be exported. 

Step 3: Keep the data and the spreadsheet file handy. Open the Tally XML file that was exported. You may also use a notebook in this situation. 

Step 4: Copy and paste the XML file into Microsoft Word. 

Step 5: It's time to get rid of the identifiers. The tags <VOUCHER> and GUID> should have their values removed. It's crucial to delete only the data, not the tags. 

Step 6: Take excel as the data source and use the mail merge feature in MS Word. Begin by swapping out the fields with their matching fields. Finally, separate it into separate papers. 

Step 7: Copy and paste the content from the separate papers into the XML file to replace the values. The importance of understanding XML tags cannot be overstated. Make no changes to the XML tags. 

Step 8: In Tally, go into Data - Vouchers Imports. Then, provide the name of the XML file and its path. Vouchers are accessible in the daybook once it is completed. 

As a result, moving data from Excel to Tally becomes straightforward when the user follows the procedures mentioned above. 

Why would you want to import Excel vouchers into Tally?

Data conversion from Excel to Tally allows users to transfer any data to Tally quickly. Tally ERP 9 also allows the user to transfer data from other programmes. Tally ERP 9 serves as an intermediary stage in this procedure. With a single click, the user may import many transactions into Tally software. Additionally, numerous templates are produced, which aid in mapping Excel columns on their own. 

Advantages of Using Tally's Voucher Import Option

Using a Tally voucher import add-on in Tally has several advantages: 

1. Aids in the preparation of year-end entries, which include depreciation.  
2. Helps in the bulk entry of sales, purchases, and other transactions. 
3. Aids in the detection of duplicate entries in a certain frequency.  
4. Importing bank statements is made easier using this tool. 
5. Importing transactions from another software is made easier using this tool. 

Import masters from Tally

The company feature settings in Tally must be activated when data is exported. For instance, you wish to import masters developed in ACD Firm to a new company called WYZ Enterprise. Before exporting the masters, the parameters- Maintain stock categories and Maintain batch-wise details must always be activated in the ACD firm. Before importing masters into WYZ Company, make sure both the Maintain stock categories and Maintain batch-wise information settings are active in WYZ Company. 

1. Navigate to Tally Gateway > Import Data > Masters.  
2. If the file is stored in the Tally program folder, enter the name of the.xml file to be imported. 

Choose an action to describe how the company's existing masters will be handled. 

• Combine Opening Balances: This feature allows you to import data from many streams and aggregate the opening balances. So when opening balances of existing masters are zero and therefore need to be overridden by the balances in the imported records, you can use this option. 
• Ignore Duplicates: Choose this alternative to import new masters with existing masters while disregarding duplicates in Tally. 
• Replace the data with new information: Select this option to overrule the other alternatives in the list of potential behaviours and replace current balances with those found in the imported data. The Import Masters screen looks like this: 

3. To import, press Enter. 

To see all the masters that have been uploaded, go to Accounts Info or Inventory Info.

Also read: What is Bank Reconciliation in Tally.ERP9

Export Tally Data in Excel using ODBC Connection

1. On your PC, launch Microsoft Excel.  
2. Go to Data > Obtain External Information > From Other Places > Microsoft Query As shown in the screenshot below: 

3. Because Open Database Connectivity (ODBC) is supported on server 9000, use TallyODBC64 9000*.  
4. Click the OK button. As seen below, the Query Wizard – Select Columns screen is displayed: 

5. To choose the needed fields, click Ledger and expand the list.  
6. Select next. The column names displayed on the Query Wizard – Filter Data page, as seen below: 

7. Select next. The Query Wizard – Sort Order screen looks like this: 

8. As needed, select Ascending or Descending for every Sort by drop-down list.  
9. Select next. The Query Wizard – Finish screen looks like this: 

10. Click the option - Transfer Data to Microsoft Office Excel.  
11. Finish by clicking the Finish button. The following is a screenshot of the Import Data screen:  
12. Click the OK button. The imported data is displayed on a Microsoft Excel sheet as seen below: 

Similarly, the Microsoft Excel Query Wizard may retrieve any data from TallyPrime. 

Export masters from Tally

In a firm, you may export masters and transactions in various forms. You may also select the masters and transactions you want to export. The Import tool can be used to import XML-formatted masters and transactions into TallyPrime. 

1. Ctrl E > Masters/Transactions.  
2. Press C (Configure) to make changes to the choices. 

A. To export Masters, choose the appropriate settings.

• Choose the sort of masters you want to export. 
• You have the option of selecting only one sort of master (for example, ledger), all accounting/inventory masters, and so on. Select All Masters if you wish to export all of the masters in the database.  
• Choose the parameter Include dependent masters to Yes if you are including masters that are connected to the Type of Masters you wish to export. 
• Select Export closing balance as an opening balance to Yes if you wish to consider the closing balances of all ledgers as beginning balances for the following financial year. Otherwise, it should be kept as No. 

Also read: What is Bills Receivable Report in Tally?

B. Set the parameters as needed to export transactions.

• Choose the sort of voucher entries to export from the drop-down menu. 
• You have the option of selecting only accounting vouchers, inventory vouchers, or all vouchers. 
• Choose the checkbox- Include reliant masters to Yes. Select this option if you wish to include the masters used throughout the Type of Voucher entries opted for export. 
• Set Exclude order vouchers to No if you wish to export the order vouchers associated with the specified voucher entries. 
• Choose the time frame for which you want to export the vouchers.  

C. For masters and transactions, set the Export Settings.

• Choose a file format. 
• If you wish to transfer this data into another program, choose XML. 
• If you wish to export it as a comma-separated text document, commonly used for delivering data over e-mail, choose ASCII. 
• Choose the Folder Path option. 
• The File Name displays on its own. If necessary, you can alter it. 
• To come back to the Export screen, press Esc. 
• For export, press E (Send). 

Export formats

Tally allows you to export data or reports in any usual format. Before actually transferring the data, you may choose the needed format and then set the resolution, page size, and formatting depending on that format. 

You may also save the information in non-editable formats like JPEG and PDF. The JPEG format offers a dual capability, allowing the user to save the file in printed or picture format. 

ASCII

You may export all Tally's reports or data using the ASCII (Comma Delimited) format. The data is exported in plain text format, separated by commas, and saved in a document with the extension .txt. This is a common file type for transmitting data over email. 

Microsoft Excel

By picking the Excel (Spreadsheet) style, you may export data and reports created in Tally to Excel. The data is organised into columns, and the document is stored as a .xls file. You may also export data with the same layout and colour scheme as Tally. You can choose the formatting and backdrop colour on the Export Report page. The Excel file may be attached and used to create graphs for a successful presentation. 

XML (data interchange)

XML (Extensible Markup Language) is an HTML extension. Data may be uploaded into other databases utilising Tally's XML files. These files have an XML enclosure and are prepared using XML tags that allow them to be imported into other systems. Before incorporating data, the recipient program needs re-formatting of XSLT (XML Style Sheets) depending on the intrinsic style employed in Tally. When two systems are operating multiple variations of Tally, XML data may be imported without re-formatting through the import menu option. 

JPEG (Image)

The requested information is produced as an image file with the extension .jpg and saved in the chosen folder when this format is selected. The picture file created is non-editable, platform-agnostic, and supports the greatest compaction level possible. The sheet dimensions and orientation must be specified. You must enter the document width and height in millimetres if you want to save the analysis on a User specified or customised size. This picture file may be emailed to a client as an attachment, viewed with a browser, and printed. 

Also read: How to Print a Self-Cheque in the Tally?

Conclusion

Tally's popularity has grown due to the software's interoperability and flexibility. Tally ERP 9 also allows the user to transfer data from other programmes. As Tally does not allow you to import data from other software by default, you may use the Excel to Tally Import application to import data from another programme (other than Tally) into Tally via Excel. Data conversion from Excel to Tally allows users to transfer any type of data to Tally quickly. 

Tally users can also use the Biz Analyst application to manage the business more effectively. You can manage the accounts, make ledgers, and even do data entry to keep the business on the right track.