If you want to work fast and want to create proper MIS reports and need to import data from Tally into Excel follow this Best Tally to Excel Import Trick. With this we can create proper reports and tables in Excel. Tally is a financial accounting software that is widely used in India. While it is possible to export data from Tally to Excel, the process is not always straightforward.
The good news is that there is a way to import data from Tally to Excel using power query and Tally TDL file. Power query is a feature in Excel that allows you to easily connect to and import data from a variety of sources.
To use the power query Tally to Excel import trick, you will first need Microsoft Excel. And in this post you will learn how to import Tally data into Excel by using a TDL and Excel power BI.
Required files to connect live data of Tally to Excel:
| Software Required: | Tally Prime / Tally ERP9 and Microsoft Excel |
| Required Code for Tally | Tally TDL file with Report option in Code |
| Required Code for Excel | Excel Power BI code with ODBC option |
| Required Setting: | Enable ODBC port in Tally |
| Things to do | Keep open Tally when collect data into excel from Tally |
Tally to Excel Import Trick : With TDL and Excel Power Query :
In this method we will import our Tally ERP9 or Tally Prime Data reports, that generated through TDL file. We import our Tally reports directly in Excel sheet with Tally Live connection through Tally ODBC. Follow the below Tally to Excel Import Trick to get live data from Tally into Excel :
Load TDL file into Tally Prime or in Tally ERP9.
First of all if we want to fetch Tally Data reports of a TDL , then we need to a TDL file. You can download free TDL files from Learnwell store, according to your need.
- TDL code should be have reports option
- After download TDL code copy the TDL file path including Name and extension (as – C:\Users\HP\Desktop\rec\StockReport.txt)
- Open Tally Prime Software
- Click on Help and click on TDL & Addon (for Tally Prime)
- Press F4 (to open a Box)
- Set Load TDL file on Startup – Yes
- Paste The path in blank space
- Press enter and save
- Now your Bill additional details TDL is ready to use.
Paste Power BI code in Excel power query:
Copy below code and paste in Excel Power query. How to use power query ? Follow Excel Power Query Post in this site
Code with TDL report name:
let
url = “http://localhost:9000”,
_body = “<ENVELOPE>
<HEADER>
<TALLYREQUEST>Export Data</TALLYREQUEST>
</HEADER>
<BODY>
<EXPORTDATA>
<REQUESTDESC>
<STATICVARIABLES>
<SVEXPORTFORMAT>ASCII (Comma Delimited)</SVEXPORTFORMAT>
<SVFROMDATE>DATE1</SVFROMDATE>
<SVTODATE>DATE2</SVTODATE>
</STATICVARIABLES>
<REPORTNAME>TDLReportName</REPORTNAME>
</REQUESTDESC>
</EXPORTDATA>
</BODY>
</ENVELOPE>”,
FromDate = Date.ToText(#date(2022, 04, 01)),
ToDate = Date.ToText(#date(2022, 04, 02)),
body = Text.Replace(Text.Replace(_body, “DATE1”, FromDate), “DATE2”, ToDate),
Source = Csv.Document(Text.Replace(Text.FromBinary(Web.Contents(url, [Content=Text.ToBinary(body, TextEncoding.Utf16), Headers=[#”Content-Type”=”text/xml;charset=utf-16″]]), TextEncoding.Utf16), “,#(cr,lf)”, “#(cr,lf)”), { “Particulars”, “GSTIN”, “Invoice Number”,”Date”, “Document Type”, “Invoice Value”,”Place of Supply”, “Note Number”, “Note Date”,”Status”,”Taxable Value”,”IGST Amt”,”IGST Eligible Amt”,”CGST Amt”,”CGST Eligible Amt”,”SGST Amt”,”SGST Eligible Amt”,”Cess Amt”,”Cess Eligible Amt”})
in
Source
How to use Excel Power BI with TDL file Video:
FAQ:
What is the Tally to Excel import trick through power query and Tally TDL?
This is a method of importing data from Tally into Excel using power query and Tally TDL.
How does this method work?
This method works by first load a reporting type TDL file in Tally. Then we can import its reports directly into Excel using power query. Data will processed using Tally ODBC and Power BI code and finally imported into Excel.
What are the benefits of this method?
There are several benefits of this method. Firstly, it is a very quick way to import data from Tally into Excel. Secondly, it is a very accurate method and get best MIS as the data is processed using Tally TDL. Finally, this method can be used to import data from multiple Tally instances into a single Excel workbook.
What data can I import from Tally using power query?
You can import any data from Tally that you can access through the Tally TDL or Tally direct reports. This includes data from ledgers, accounts, transactions, and more.
This is a great way to get your data from Tally into Excel. The process is simple and straightforward, and it only takes a few minutes to get everything set up. This is a great way to save time and energy, and it will make your life a lot easier.
Require a TDL code to create odbc table for outstanding of sundry debtors to be used in any dashboard using odbc connectivity eith tally erp9