Part 25 of 45 — TallyPrime Deep-Dive Masterclass
JSON & ODBC Integration: The Analytics Layer of TallyPrime
Connect Power BI, Excel, Python, and Tableau to TallyPrime. Master JSON over HTTP for modern REST-style APIs and ODBC for real-time analytics. Complete setup guide, SQL query patterns, DSN configuration, Tally as an ODBC client, and production analytics architecture.
1
JSON & ODBC — The Modern Integration Layer
Why TallyPrime offers three interfaces and when to use each
TallyPrime does not just integrate through XML. It also offers JSON over HTTP for modern applications and ODBC for analytics. Together, these three interfaces cover every integration need — from transactional writes to real-time dashboards.
🏗️ The Three Interface Family:
• XML over HTTP — Read + Write · Transactional integration
• JSON over HTTP — Read + Write · Modern REST-style apps
• ODBC — Read Only · Analytics, dashboards, data science
• XML over HTTP — Read + Write · Transactional integration
• JSON over HTTP — Read + Write · Modern REST-style apps
• ODBC — Read Only · Analytics, dashboards, data science
The Analytics Architecture
TallyPrime (Source of Truth)
ODBC Server · Port 9000
▼
ODBC Driver / JSON Client
SQL Query · JSON Request
▼
Power BI · Excel · Python · Tableau
Transform · Visualize · Analyze
▼
Dashboards · Reports · Insights
When to Use Which Interface
📡 XML
Transactional Integration
- Creating vouchers (Sales, Purchase, Receipt, Payment)
- Creating masters (Ledgers, Stock Items, Groups)
- Complex invoices with inventory + tax
- Traditional ERP integration
📋 JSON
Modern Web Apps
- REST-style APIs from web/mobile apps
- Easier parsing in JavaScript, Python, Go
- Compact payloads
- Microservices integration
🔌 ODBC
Analytics & BI
- Power BI dashboards
- Excel-based reporting
- Python data analysis
- Data warehouse ETL
9000
Shared Port (XML/JSON/ODBC)
Read
ODBC Access Mode
Read+Write
JSON Access Mode
0
Cost (built into TallyPrime)
Real-World Impact: A Bangladeshi manufacturing group runs a live Power BI dashboard connected directly to TallyPrime via ODBC. Sales managers see today's orders, outstanding balances, and stock positions in real-time — without anyone manually exporting a single report.
2
JSON vs XML — Which One Should You Use?
Structural differences, use cases, and the decision framework
Both XML and JSON are supported by TallyPrime for HTTP integration. They are functionally similar but differ in syntax, readability, and ecosystem support. Choosing the right one depends on your tech stack and integration complexity.
Side-by-Side Comparison
| Dimension | XML | JSON |
|---|---|---|
| Syntax | Verbose tags <NAME>value</NAME> |
Compact key-value "name":"value" |
| Payload Size | Larger (~2-3x) | Smaller |
| Parsing Speed | Slower (SAX/DOM) | Faster (native in JS/Python) |
| Readability | Verbose but explicit | Clean and concise |
| Maturity in Tally | 20+ years | Growing |
| Documentation | Extensive | Limited |
| Complex Vouchers | Excellent support | Good support |
| Best For | Traditional ERPs, complex schemas | Modern web apps, REST APIs, mobile |
Syntax Comparison — Same Voucher in Both Formats
;; XML Version — Compact but verbose <VOUCHER VCHTYPE="Receipt"> <DATE>20260918</DATE> <NARRATION>Payment received</NARRATION> <ALLLEDGERENTRIES.LIST> <LEDGERNAME>SBI Bank</LEDGERNAME> <AMOUNT>57500</AMOUNT> </ALLLEDGERENTRIES.LIST> </VOUCHER> ;; JSON Version — Compact and clean { "voucher": { "vchtype": "Receipt", "date": "20260918", "narration": "Payment received", "ledgerentries": [ { "ledgername": "SBI Bank", "amount": 57500 } ] } }
Decision Framework
- Choose XML when: You are creating complex vouchers with inventory + tax + bill allocations. XML has more documentation and examples for these cases.
- Choose JSON when: Your tech stack is JavaScript, TypeScript, or Python. JSON parses natively without extra libraries. Ideal for web and mobile apps.
- Choose XML when: You need the widest compatibility. XML has been supported since Tally 6.3. Some older add-ons and middleware only speak XML.
- Choose JSON when: You are building a REST-style microservice. JSON is the natural choice for modern APIs.
💻 Developer Recommendation: For any production integration, start with XML. It has more battle-tested patterns, more Stack Overflow answers, and more third-party middleware support. Use JSON when your team has strong JSON preference or you need JSON-native parse performance.
3
JSON Integration Basics
How JSON requests to TallyPrime differ from XML — and how to structure them
TallyPrime's JSON interface uses HTTP POST to the same port as XML (9000) but with a JSON payload instead of an XML envelope. The request structure is more compact but functionally equivalent.
Basic JSON Request Structure
{
"tallyrequest": "Import Data",
"reportname": "Vouchers",
"staticvariables": {
"svcurrentcompany": "ABC Ltd."
},
"requestdata": {
"tallymessage": [
{
"voucher": { /* voucher details */ }
}
]
}
}
JSON Attributes Explained
| JSON Key | XML Equivalent | Purpose |
|---|---|---|
tallyrequest | <TALLYREQUEST> | Import Data / Export Data / Execute |
reportname | <REPORTNAME> | Vouchers / Ledgers / StockItems |
staticvariables | <STATICVARIABLES> | Company, dates, filters |
requestdata | <REQUESTDATA> | Payload for imports |
tallymessage | <TALLYMESSAGE> | Array of objects to import |
voucher | <VOUCHER> | Voucher object |
Testing JSON with curl
# Linux / macOS — Send JSON to Tally curl -X POST http://localhost:9000 \ -H "Content-Type: application/json" \ -d '{ "tallyrequest": "Export Data", "reportname": "List of Companies" }'
⚠️ Reality Check: TallyPrime's JSON support is less mature than XML. Some complex features (deeply nested collections, certain voucher types) are better documented and more reliable in XML. If you hit unexpected JSON parsing errors, fall back to XML.
💡 Compatibility Strategy: Design your integration service so the payload format is configurable. If you start with JSON and later need XML for a specific case, you can switch without rewriting the entire service.
4
Writing Data via JSON — Vouchers & Masters
Creating vouchers, ledgers, and stock items using JSON payloads
JSON writes work the same way as XML writes — the payload shape is different, but Tally processes them identically. Here is how to construct common JSON write requests.
Creating a Ledger via JSON
{
"tallyrequest": "Import Data",
"reportname": "All Masters",
"staticvariables": {
"svcurrentcompany": "ABC Ltd."
},
"requestdata": {
"tallymessage": [
{
"ledger": {
"name": "Rahim Traders",
"action": "Create",
"parent": "Sundry Debtors",
"isbillwiseon": "Yes",
"creditlimit": 500000,
"creditdays": 30,
"ledgerphone": "+8801700000000",
"mailingname": "Rahim Traders",
"address": "123 Motijheel, Dhaka",
"countryname": "Bangladesh",
"ledgstregistrationnumber": "123456789012"
}
}
]
}
}
Creating a Sales Voucher via JSON
{
"tallyrequest": "Import Data",
"reportname": "Vouchers",
"staticvariables": {
"svcurrentcompany": "ABC Ltd."
},
"requestdata": {
"tallymessage": [
{
"voucher": {
"vchtype": "Sales",
"action": "Create",
"date": "20260918",
"vouchernumber": "SI-2026-0042",
"reference": "ERP-INV-98765",
"partyledgername": "Rahim Traders",
"narration": "Sales as per PO",
"ledgerentries": [
{
"ledgername": "Rahim Traders",
"isdeemedpositive": "Yes",
"amount": 414000,
"billallocations": [
{
"name": "SI-2026-0042",
"billtype": "New Ref",
"amount": 414000
}
]
},
{
"ledgername": "Sales Account",
"isdeemedpositive": "No",
"amount": -360000
},
{
"ledgername": "Output GST 15%",
"isdeemedpositive": "No",
"amount": -54000
}
]
}
}
]
}
}
Key JSON Rules
✓ JSON Best Practices
- Use consistent key casing (lowercase)
- Amounts as numbers (not strings)
- Debits positive, credits negative
- Include REFERENCE for idempotency
- Validate JSON before sending
- Test with Postman first
✗ Common JSON Mistakes
- Trailing commas (invalid JSON)
- Using uppercase keys
- Amounts as strings
- Missing company name
- Forgetting bill allocations
- Wrong debit/credit signs
💻 Encoding: If you are using UTF-16LE encoding for Tally Gateway Service, remember JSON also requires the same encoding. Most modern JSON libraries output UTF-8 by default — you may need to convert.
5
Reading Data via JSON — Export Requests
Fetching ledgers, vouchers, and reports as JSON from TallyPrime
Reading data via JSON follows the same pattern as XML — send an Export Data request and receive a JSON response with the requested data.
Export List of Ledgers
{
"tallyrequest": "Export Data",
"reportname": "List of Ledgers",
"staticvariables": {
"svcurrentcompany": "ABC Ltd."
}
}
Sample JSON Response
{
"ledgers": [
{
"name": "Rahim Traders",
"parent": "Sundry Debtors",
"closingbalance": -125000,
"openingbalance": 0
},
{
"name": "SBI Bank Current A/c",
"parent": "Bank Accounts",
"closingbalance": 850000,
"openingbalance": 500000
}
]
}
Export Vouchers with Date Range
{
"tallyrequest": "Export Data",
"reportname": "Voucher Register",
"staticvariables": {
"svcurrentcompany": "ABC Ltd.",
"svfromdate": "20260901",
"svtodate": "20260930",
"vouchertypefilter": "Sales"
}
}
💻 Parsing Tip: Tally's JSON responses can be large for wide date ranges. If you are fetching more than 1,000 vouchers, split the request into weekly or monthly batches. This avoids timeouts and keeps response sizes manageable.
6
Enabling ODBC in TallyPrime
Complete setup: Tally ODBC server, Windows DSN, and connection testing
ODBC (Open Database Connectivity) is the standard way to connect any analytics tool to TallyPrime. Once enabled, tools like Power BI, Excel, Tableau, and Python see Tally as a database they can query with SQL.
Step 1 — Enable ODBC Server in TallyPrime
- Open TallyPrime with a company loaded
- Press F1 → Settings → Connectivity
- Under Tally.NET Server, set:
- Tally.NET Server: Both (Client and Server)
- Port: 9000
- Set Enable ODBC Server to Yes
- Press Ctrl+A to save and restart Tally
💡 Same Port, Both Services: ODBC and XML share the same port (9000). Enabling ODBC does not interfere with XML integration. Both can run simultaneously.
Step 2 — Install Tally ODBC Driver
The Tally ODBC driver is bundled with TallyPrime installation. If it is not present, install it from the Tally installation folder:
📦 Driver Location
- 32-bit:
C:\Program Files (x86)\TallyPrime\ODBC\ - 64-bit:
C:\Program Files\TallyPrime\ODBC\
Step 3 — Create a Windows DSN
A DSN (Data Source Name) is a Windows-registered connection profile that tells applications how to reach the Tally ODBC server.
- Open Windows ODBC Data Source Administrator (search "ODBC" in Start menu)
- Choose System DSN tab (so all users can access it)
- Click Add
- Select Tally ODBC Driver (or "TallyODBC64")
- Name the DSN (e.g.,
TallyPrime_Prod) - Enter connection details:
- Server: localhost or IP address of the Tally machine
- Port: 9000
- Click Test Connection
- Save the DSN
Step 4 — Test the Connection
# PowerShell — Test ODBC connection $conn = New-Object System.Data.Odbc.OdbcConnection $conn.ConnectionString = "DSN=TallyPrime_Prod" $conn.Open() $cmd = $conn.CreateCommand() $cmd.CommandText = "SELECT * FROM Company" $reader = $cmd.ExecuteReader() while ($reader.Read()) { Write-Host $reader["Name"] } $conn.Close()
Common ODBC Issues & Fixes
| Issue | Cause | Fix |
|---|---|---|
| Driver not found | Tally ODBC driver not installed | Install driver from TallyPrime folder |
| Connection refused | ODBC server not enabled in Tally | Enable ODBC Server in Connectivity settings |
| Architecture mismatch | 32-bit vs 64-bit mismatch between driver and app | Use 64-bit DSN for 64-bit tools like Power BI |
| Timeout on large queries | Query returns too many rows | Add WHERE and date filters · Reduce date range |
| Company not loaded | Tally does not have the company open | Load the company in Tally UI |
⚠️ Architecture Critical: If you install a 32-bit ODBC driver but use a 64-bit application (like Power BI Desktop 64-bit), the connection will fail. Always check your application's architecture and install the matching driver.
7
ODBC SQL Query Patterns
SQL queries against Tally ODBC tables and how to write efficient analytics
Once connected via ODBC, TallyPrime exposes a set of SQL tables that you can query. The table structure mirrors Tally's object model — one table per object type, with sub-tables for collections.
Core Tables Exposed via ODBC
| Table | Contains | Common Columns |
|---|---|---|
Company | Company details | Name, Address, Period |
Ledger | Chart of accounts | Name, Parent, ClosingBalance |
Group | Account groups | Name, Parent, IsRevenue |
StockItem | Products | Name, Parent, BaseUnits, ClosingBalance |
StockGroup | Product categories | Name, Parent |
Voucher | Transactions | Date, VoucherType, Number, PartyLedgerName |
VoucherLedger | Ledger entries within vouchers | VoucherId, LedgerName, Amount |
VoucherInventory | Inventory entries | VoucherId, StockItemName, Qty, Rate |
CostCentre | Cost centres | Name, Parent |
GroupCompany | Multi-company details | Name, Parent |
Basic Queries
-- All ledgers with their parent group SELECT Name, Parent, ClosingBalance FROM Ledger WHERE Parent = 'Sundry Debtors' ORDER BY ClosingBalance DESC -- Sales vouchers for the current month SELECT Date, VoucherNumber, PartyLedgerName, Amount FROM Voucher WHERE VoucherTypeName = 'Sales' AND Date >= '2026-09-01' AND Date <= '2026-09-30' -- Top 10 customers by outstanding SELECT Name, ClosingBalance FROM Ledger WHERE Parent = 'Sundry Debtors' AND ClosingBalance > 0 ORDER BY ClosingBalance DESC LIMIT 10
Advanced Query — Sales by Customer
-- Aggregate sales by customer for the quarter SELECT V.PartyLedgerName AS Customer, SUM(V.Amount) AS TotalSales, COUNT(*) AS InvoiceCount, AVG(V.Amount) AS AvgInvoiceValue FROM Voucher V WHERE V.VoucherTypeName = 'Sales' AND V.Date >= '2026-07-01' AND V.Date <= '2026-09-30' GROUP BY V.PartyLedgerName ORDER BY TotalSales DESC
Advanced Query — Stock Movement
-- Stock item movement (in vs out) SELECT VI.StockItemName, SUM(CASE WHEN V.VoucherTypeName IN ('Purchase', 'Receipt Note') THEN VI.ActualQty ELSE 0 END) AS TotalIn, SUM(CASE WHEN V.VoucherTypeName IN ('Sales', 'Delivery Note') THEN VI.ActualQty ELSE 0 END) AS TotalOut FROM VoucherInventory VI JOIN Voucher V ON VI.VoucherId = V.Id WHERE V.Date >= '2026-04-01' GROUP BY VI.StockItemName ORDER BY TotalOut DESC
💻 ODBC Limitations:
• Read-only: You cannot INSERT, UPDATE, or DELETE via ODBC
• Limited SQL: Not all SQL functions are supported. Test complex queries first
• No transactions: No BEGIN TRAN / COMMIT
• Performance: Very fast for aggregates, slower for large row-level scans
• Read-only: You cannot INSERT, UPDATE, or DELETE via ODBC
• Limited SQL: Not all SQL functions are supported. Test complex queries first
• No transactions: No BEGIN TRAN / COMMIT
• Performance: Very fast for aggregates, slower for large row-level scans
💡 Query Optimization: Always use WHERE clauses to filter by date range and voucher type. Querying all vouchers without a filter can pull hundreds of thousands of rows and take minutes. Filtered queries return in seconds.
8
Connecting Tally to Power BI
Build real-time CFO dashboards by connecting Power BI directly to TallyPrime
Power BI is the most popular analytics tool for Tally integration. By connecting Power BI to Tally via ODBC, you can build dashboards that refresh on-demand with the latest data.
Setup Steps
- Ensure TallyPrime ODBC Server is enabled
- Create a System DSN for Tally (see Part 6 of this article)
- Open Power BI Desktop
- Click Get Data → ODBC
- Select the DSN you created (
TallyPrime_Prod) - Browse to the table you want (e.g.,
Ledger) - Click Load or Transform Data
- Build your visualizations
Recommended Tables for a CFO Dashboard
| Dashboard Tile | Table | Key Columns |
|---|---|---|
| Total Receivables | Ledger | ClosingBalance (Parent = Sundry Debtors) |
| Total Payables | Ledger | ClosingBalance (Parent = Sundry Creditors) |
| Bank Balance | Ledger | ClosingBalance (Parent = Bank Accounts) |
| Monthly Sales | Voucher | Date, Amount (VoucherType = Sales) |
| Top Customers | Voucher | PartyLedgerName, Amount |
| Stock Summary | StockItem | Name, ClosingBalance, ClosingValue |
| Expense Breakdown | Ledger | Name, ClosingBalance (Parent = Indirect Expenses) |
Power Query M Code — Custom Filter
// Power Query — Filter ledgers to Sundry Debtors only let Source = Odbc.DataSource("dsn=TallyPrime_Prod", [HierarchicalNavigation=true]), Ledger_Table = Source{[Name="Ledger",Kind="Table"]}[Data], FilteredDebtors = Table.SelectRows(Ledger_Table, each [Parent] = "Sundry Debtors") in FilteredDebtors
Refresh & Gateway
- Manual Refresh: Click Refresh in Power BI Desktop — pulls latest data from Tally
- Scheduled Refresh: Publish to Power BI Service, install On-Premises Data Gateway on the Tally machine, schedule refresh (e.g., every 30 minutes)
- DirectQuery: Not supported for Tally ODBC — Tally must be imported, not queried live
⚠️ Refresh Performance: Full refresh on large companies can take several minutes. Use incremental refresh in Power BI for voucher tables — only fetch new/changed rows by date. This reduces refresh time from minutes to seconds.
💰 CFO Dashboard Delivered: With this setup, a CFO can see — in one Power BI screen — cash position, receivables ageing, top overdue customers, monthly revenue trend, expense breakdown, and stock summary. No manual data exports. No Excel juggling. Just live numbers.
9
Connecting Tally to Excel
Use Excel as a live reporting tool connected to TallyPrime via ODBC
Excel remains the most widely used analytics tool in finance. With ODBC, you can build live Excel dashboards that pull directly from TallyPrime — no more exporting CSVs or manual copying.
Connecting Excel to Tally
- Open Excel
- Go to Data tab → Get Data → From Other Sources → From ODBC
- Select your Tally DSN
- Choose the table you want (e.g.,
Ledger) - Click Load or Load To (choose a worksheet or data model)
- Data appears as a Query Table that can be refreshed on demand
Refresh Data
- Manual refresh: Data → Refresh All (or press Ctrl+Alt+F5)
- Auto-refresh: Query Properties → Refresh every N minutes
- On file open: Enable "Refresh data when opening the file"
Common Excel Reports Built from Tally ODBC
| Report | Tables Used | Use Case |
|---|---|---|
| Ageing Analysis | Ledger + Voucher | Track overdue invoices by customer |
| Sales Register | Voucher (filtered by Sales) | Day-wise/monthly sales reporting |
| Customer Ledger | Voucher + VoucherLedger | Detailed statement for each customer |
| Stock Summary | StockItem | Current stock position with values |
| Bank Reconciliation Helper | Ledger + Voucher (Bank ledgers) | Match bank statement with Tally |
Excel Power Query — Custom SQL
// Power Query — Fetch sales vouchers with custom SQL let Source = Odbc.Query("dsn=TallyPrime_Prod", "SELECT Date, VoucherNumber, PartyLedgerName, Amount " & "FROM Voucher " & "WHERE VoucherTypeName = 'Sales' " & "AND Date >= '2026-09-01' " & "ORDER BY Date DESC") in Source
💡 Excel Best Practice: Use a dedicated Data sheet with only the ODBC query tables. Build your Analysis sheets using PivotTables and formulas that reference the Data sheet. This separation keeps reports fast and clean.
10
Python & Pandas Analytics
Use Python for advanced data science, forecasting, and automated Tally reporting
Python is the go-to language for data science and automation. With pyodbc and pandas, you can pull Tally data into Python for advanced analysis, forecasting, and automated reporting.
Setup — Install Required Packages
# Install packages pip install pyodbc pandas matplotlib # Verify ODBC driver is installed python -c "import pyodbc; print(pyodbc.drivers())"
Connect to Tally and Fetch Data
import pyodbc import pandas as pd # Connect to Tally via ODBC conn = pyodbc.connect('DSN=TallyPrime_Prod') # Fetch all Sundry Debtors query = """ SELECT Name, Parent, ClosingBalance FROM Ledger WHERE Parent = 'Sundry Debtors' ORDER BY ClosingBalance DESC """ df = pd.read_sql(query, conn) print(df.head(10)) conn.close()
Sales Trend Analysis
import pandas as pd import matplotlib.pyplot as plt conn = pyodbc.connect('DSN=TallyPrime_Prod') # Fetch sales vouchers for the year query = """ SELECT Date, Amount FROM Voucher WHERE VoucherTypeName = 'Sales' AND Date >= '2026-04-01' AND Date <= '2026-09-30' """ df = pd.read_sql(query, conn) df['Date'] = pd.to_datetime(df['Date']) df['Month'] = df['Date'].dt.to_period('M') # Group by month monthly = df.groupby('Month')['Amount'].sum() # Plot monthly.plot(kind='bar', title='Monthly Sales Trend') plt.tight_layout() plt.savefig('sales_trend.png') conn.close()
Automated Ageing Report
from datetime import datetime, timedelta import pyodbc import pandas as pd conn = pyodbc.connect('DSN=TallyPrime_Prod') # Fetch all customer vouchers query = """ SELECT V.PartyLedgerName, V.Date, V.Amount FROM Voucher V WHERE V.VoucherTypeName = 'Sales' AND V.Date >= DATEADD(month, -6, GETDATE()) """ df = pd.read_sql(query, conn) df['Date'] = pd.to_datetime(df['Date']) today = pd.Timestamp(datetime.now().date()) df['AgeDays'] = (today - df['Date']).dt.days # Bucket by ageing def bucket(days): if days <= 30: return '0-30' elif days <= 60: return '31-60' elif days <= 90: return '61-90' else: return '90+' df['Bucket'] = df['AgeDays'].apply(bucket) # Pivot ageing = df.pivot_table( index='PartyLedgerName', columns='Bucket', values='Amount', aggfunc='sum', fill_value=0 ) print(ageing) ageing.to_excel('ageing_report.xlsx') conn.close()
💻 Advanced Python Patterns:
• Forecasting: Use Prophet or ARIMA on Tally sales data for next-month predictions
• Anomaly detection: Use Isolation Forest or z-scores to spot unusual transactions
• Automation: Schedule scripts with cron/Task Scheduler to email reports daily
• Data warehouse: Push Tally data to PostgreSQL/Snowflake for long-term analytics
• Forecasting: Use Prophet or ARIMA on Tally sales data for next-month predictions
• Anomaly detection: Use Isolation Forest or z-scores to spot unusual transactions
• Automation: Schedule scripts with cron/Task Scheduler to email reports daily
• Data warehouse: Push Tally data to PostgreSQL/Snowflake for long-term analytics
Real-World Impact: A Bangladeshi conglomerate uses Python scripts connected to Tally ODBC to run daily automated ageing reports that email each sales manager their overdue customer list — every morning at 7 AM, without a single human touch.
11
Tally as an ODBC Client
Fetching data FROM external databases INTO Tally using TDL collections
TallyPrime is not just an ODBC server — it can also act as an ODBC client. This means TDL can fetch data from external databases (SQL Server, MySQL, PostgreSQL, Oracle) using SQL queries. This is a powerful feature for creating custom reports that blend Tally data with external sources.
What Is an ODBC Client?
🔗 Reverse Direction
Normally, Tally is the source and external tools are the consumers. When Tally acts as an ODBC client, the roles reverse — Tally queries external databases and pulls data into its own reports and processing.
Common ODBC Client Use Cases
- CRM Integration: Pull customer contact history from a CRM database into a Tally custom report
- Warehouse Sync: Fetch stock levels from a warehouse management system into Tally's stock reports
- HR Data: Pull employee details from an HRMS into payroll processing
- Project Data: Fetch project details from a project management tool
- External Reference: Look up product catalog, pricing, or exchange rates from external sources
Defining an ODBC Client Collection in TDL
;; TDL — ODBC Client Collection [Collection: ExternalCustomerColl] Type : ODBC ODBC : DSNName : MyCRM_DSN ODBC : SQLQuery : "SELECT CustomerCode, Email, Phone FROM Customers" Fetch : CustomerCode, Email, Phone ;; Use the collection in a report [Report: CustomerEnrichedReport] Form : CustomerEnrichedForm Variable : SVFromDate, SVToDate
Configuring the ODBC Client DSN
- Create a System DSN in Windows ODBC Administrator pointing to your external database
- Use the appropriate driver (SQL Server, MySQL, PostgreSQL)
- Test the connection to ensure it works
- Reference the DSN name in your TDL collection
- Load the TDL file in TallyPrime
- Navigate to the custom report to see live external data merged with Tally data
💻 ODBC Client Advantages:
• Blend data sources: Combine Tally + external data in a single report
• Real-time lookups: Fetch fresh data every time the report opens
• No middleware needed: TDL handles the connection directly
• Custom dashboards: Build reports that blend internal and external KPIs
• Blend data sources: Combine Tally + external data in a single report
• Real-time lookups: Fetch fresh data every time the report opens
• No middleware needed: TDL handles the connection directly
• Custom dashboards: Build reports that blend internal and external KPIs
⚠️ ODBC Client Limitations:
• Read-only — cannot write to external databases
• Performance depends on external database speed
• No transaction support
• Limited SQL dialect support (not all database-specific functions work)
• Read-only — cannot write to external databases
• Performance depends on external database speed
• No transaction support
• Limited SQL dialect support (not all database-specific functions work)
12
Performance Optimization
Keeping queries fast, avoiding timeouts, and scaling analytics
Tally's ODBC and JSON interfaces perform well when used correctly but can become slow if you query too much data at once. Here are the patterns that keep analytics fast at scale.
Performance Benchmarks
Simple Query
<1s
Ledger list
Filtered Vouchers
2-5s
1 month range
Full Year Vouchers
10-30s
Large dataset
Unfiltered Scan
1-5min
Avoid this
The 8 Performance Rules
- Always filter by date. Never query all vouchers without a WHERE clause on Date.
- Use small date windows. Weekly or monthly — never fetch a full year in one query.
- Filter by voucher type.
WHERE VoucherTypeName = 'Sales'dramatically reduces result size. - Fetch only needed columns. Avoid
SELECT *— name only the columns you use. - Use aggregates at the source. Let Tally compute totals with GROUP BY instead of pulling rows and summing in your tool.
- Cache results. If data does not change often, store it in a local data warehouse and refresh nightly.
- Use incremental refresh. Fetch only new data since last pull, not the full history.
- Run analytics off-peak. Large exports during business hours slow down Tally for other users.
Building a Data Warehouse for Analytics
For serious analytics, do not query Tally directly. Instead, build a data warehouse:
TallyPrime (Source)
Nightly ODBC Extraction
▼
ETL Job (Python / SSIS / Airflow)
Transform · Clean · Enrich
▼
Data Warehouse (SQL Server / PostgreSQL / Snowflake)
Fast SQL Queries
▼
Power BI · Excel · Python · ML Models
Why Data Warehouse Architecture Wins
- No load on Tally: Analytics never touches the live Tally server
- Historic data: Keep years of history without slowing Tally
- Blended data: Combine Tally with CRM, HRMS, warehouse, and external data
- Fast queries: Optimized schema and indexes make dashboards instant
- Full SQL: No Tally ODBC limitations — use full SQL capabilities
- Data quality: Clean, transform, and validate data before consumption
💰 The 3-Tier Analytics Architecture (Recommended for Enterprises):
Tier 1 — TallyPrime: Source of truth, live transaction processing
Tier 2 — Data Warehouse: Historical data, cleaned and optimized for analytics
Tier 3 — BI Tools: Dashboards, reports, and ML models on the warehouse
This pattern scales to billions of rows and supports hundreds of concurrent users — while Tally continues to run at full speed for daily operations.
Tier 1 — TallyPrime: Source of truth, live transaction processing
Tier 2 — Data Warehouse: Historical data, cleaned and optimized for analytics
Tier 3 — BI Tools: Dashboards, reports, and ML models on the warehouse
This pattern scales to billions of rows and supports hundreds of concurrent users — while Tally continues to run at full speed for daily operations.
13
Security for ODBC & JSON
Protecting your financial data when exposed to analytics tools
ODBC and JSON interfaces have no built-in authentication. Anyone who can reach port 9000 can read your financial data. Every organization must implement network-level security.
The 7 Security Layers
- Network isolation: Keep Tally on a private network — never expose port 9000 to the internet
- Firewall rules: Restrict access to specific IP addresses (the analytics server only)
- VPN for remote access: If remote users need access, use VPN — not port forwarding
- Reverse proxy: Put a proxy with authentication and rate limiting in front of Tally
- Windows authentication: Run Tally under a restricted Windows account
- Audit logging: Log every ODBC connection and query at the proxy or DSN level
- Data minimisation: Only expose the data that is actually needed for analytics
Recommended Network Architecture
Analytics Server (Power BI / Python)
Private Network · Allowed IP Only
▼
TallyPrime · Port 9000 · Localhost + Analytics Server IP
Firewall Configuration
# PowerShell — Restrict Tally port 9000 to specific IP New-NetFirewallRule -DisplayName "Tally ODBC - Analytics Server" ` -Direction Inbound ` -Protocol TCP ` -LocalPort 9000 ` -RemoteAddress "192.168.1.100" ` -Action Allow ` -Profile Domain,Private # Block all other IPs New-NetFirewallRule -DisplayName "Block Tally port 9000 - External" ` -Direction Inbound ` -Protocol TCP ` -LocalPort 9000 ` -Action Block ` -Profile Public
What NOT to Do
✗ Never Do This
- Expose port 9000 to the internet
- Allow access from 0.0.0.0/0 (any IP)
- Use Public profile in Windows Firewall for Tally rules
- Share DSN credentials across users
- Embed database passwords in plain text in scripts
- Run analytics tools on the same machine as Tally (contention)
✓ Always Do This
- Keep Tally on the private network
- Restrict to specific IPs
- Use Domain/Private firewall profiles
- Use individual service accounts for analytics
- Store secrets in a secrets manager or environment variables
- Run analytics on a separate server
🚨 Real Security Risk: A single misconfigured firewall rule can expose your entire financial data to the internet. Attackers actively scan for open port 9000. Never, ever expose Tally directly to the internet. Always use VPN, reverse proxy, or run behind a firewall.
14
Frequently Asked Questions
Answers to common JSON & ODBC integration questions
Can I write data via ODBC?
▾
No. ODBC in TallyPrime is strictly read-only. You cannot INSERT, UPDATE, or DELETE records via ODBC. For writes, use XML or JSON over HTTP. Use ODBC exclusively for analytics and reporting.
Does Tally ODBC work with Power BI Service (cloud)?
▾
Yes, but only via On-Premises Data Gateway. Power BI Service in the cloud cannot reach your Tally server directly. You must install the Power BI On-Premises Data Gateway on the same network as Tally, register the Tally DSN, and use that gateway when setting up scheduled refresh.
Can I use JDBC instead of ODBC?
▾
Tally does not provide a JDBC driver. For Java applications, use the JDBC-ODBC Bridge (not recommended for production due to performance) or XML/JSON over HTTP via standard Java HTTP clients. Java applications typically use HTTP-based integration rather than JDBC.
How often should I refresh a Power BI dashboard?
▾
It depends on the use case:
- Real-time sales tracker: Every 15-30 minutes
- Daily CFO dashboard: Once a day (overnight)
- Weekly MIS reports: Once a week
- Monthly financial reports: Once a month, after month-end close
Frequent refreshes increase load on Tally. Balance freshness against performance.
Why does Power BI say "The evaluation period has expired" for ODBC?
▾
This is a known Power BI behavior. The workaround is to use ODBC.Query() in Power Query with a custom SQL statement instead of browsing the ODBC driver's table list directly. This bypasses the driver evaluation prompt.
Can I use ODBC from a cloud application?
▾
Only with a gateway or VPN. Cloud applications (hosted on AWS, Azure, GCP) cannot reach a Tally server on your office network. You need either:
- On-Premises Data Gateway (for Power BI Service, Power Apps, Logic Apps)
- VPN tunnel between cloud and office
- Reverse proxy with HTTPS exposing the Tally HTTP endpoint securely
- Middleware service running on-premise that exposes a REST API to the cloud
What are the table names for Tally ODBC?
▾
Core Tally ODBC tables: Company, Ledger, Group, StockItem, StockGroup, Unit, Voucher, VoucherLedger, VoucherInventory, VoucherBill, VoucherCostCentre, CostCentre, CostCategory, Budget, GroupCompany. Browse them using Excel or Power BI's table picker to see all available tables and columns.
Can I filter by company in ODBC queries?
▾
Yes. There are two ways:
- DSN per company: Create a separate DSN for each company, pointing to the same Tally instance
- Query column filter: Some tables include a Company column you can filter on
For multi-company analytics, create one DSN per company and union the results in your BI tool.
Does ODBC work with TallyPrime Server?
▾
Yes. TallyPrime Server supports ODBC on the same port 9000. In fact, TallyPrime Server handles concurrent ODBC queries better than TallyPrime Gold because it manages data access centrally.
Learning Path for JSON & ODBC
- Day 1-2: Understand the differences between XML, JSON, and ODBC
- Day 3-5: Test JSON requests with Postman and curl
- Day 6-8: Enable ODBC server and create a DSN
- Day 9-12: Connect Power BI and build your first dashboard
- Day 13-15: Connect Excel and build live reports
- Day 16-20: Connect Python and run analytics scripts
- Day 21-25: Explore Tally as an ODBC client using TDL
- Day 26-30: Build a data warehouse architecture for enterprise analytics
💡 Final Advice: Start small. Get one Power BI dashboard working end-to-end with a single table. Then expand. ODBC integration is more about understanding your data model than about complex code. Once you know which tables hold which data, everything else is straightforward SQL.
Series Roadmap — 45 Parts to CFO-Level Mastery
You have completed 24 parts of the TallyPrime Deep-Dive Masterclass. Here is the complete roadmap.
01 Foundation
02 History
03 vs ERP
04 Company
05 COA
06 Double Entry
07 Vouchers
08 Sales
09 Purchase
10 Inventory
11 Inv+Acct
12 AR/AP
13 Banking
14 Tax
15 Cost Centre
16 Payroll
17 Manufacturing
18 Financial Stmt
19 MIS
20 CFO
21 Security
22 Architecture
23 TDL
24 XML
25 JSON/ODBC 📊
26 Custom ERP
27 Tally Engine
28 Enterprise
29 Tally+AI+BI
30 Case Study
31-45 Advanced
🎯 What's Next: Part 26 — Custom ERP + Tally Architecture brings everything together. You will learn how to architect a custom ERP that uses Tally as its accounting engine, handle master data synchronisation, voucher posting in real-time, error reconciliation, and the complete integration blueprint. Then Parts 27-30 cover the Tally engine, enterprise architecture, AI, and the grand case study.
0 Comments
thanks for your comments!