Extracting Day Books / Transaction Data from Tally using ODBC

Question

I am able to successfully connect Excel to Tally ODBC Server using Microsoft SQL Query. I now want to extract all transaction data for entire history into excel (including debit and credit ledgers) like in a Day Book. However, if I extract using CompanyVouchers table, I only get the data for current date in Tally, and only for single ledger.

STEPS FOLLOWED:

1. Open Microsoft Excel on your computer.
2. Click Data > Get External Data > From Other Sources > From Microsoft Query
3. Select TallyODBC64_9000*
4. Select CompanyVouchers from the Query Wizard

Answer 1

Using Tally ODBC to Export Data into Excel

Tally ERP supports SQL (Structured Query Language). You can use SQL SELECT queries to export Tally data using Tally ODBC or the Calculator panel in Tally Software itself. However, there are certain limitations. You can retrieve only the first level fields from Tally ERP. For example, you can fetch Date, Voucher Type, Voucher Number, Party Ledger etc, but you cannot retrieve all the ledgers for each voucher entry using the Tally ODBC interface.

Here are some SQL SELECT statements to retrieve data from Tally using ODBC.

Export Sales data from Tally ERP

Select $Date,$Reference,$VouchertypeName,$PartyLedgerName,$$CollectionField:$Amount:1:LedgerEntries from RTSAllVouchers where $$IsSales:$VoucherTypeName

Export Payment data from Tally ERP

Select $Date,$Reference,$VouchertypeName,$PartyLedgerName,$$CollectionField:$Amount:1:LedgerEntries from RTSAllVouchers where $$IsPayment:$VoucherTypeName

Export Receipt entries from Tally ERP

Select $Date,$Reference,$VouchertypeName,$PartyLedgerName,$$CollectionField:$Amount:1:LedgerEntries from RTSAllVouchers where $$IsReceipt:$VoucherTypeName

PS: The CompanyVouchers collection in Tally exports data only for the current date. Hence, we have used a user defined collection named RTSAllVouchers. The TDL code for this user defined collection is given underneath :-

[Collection: RTSAllVouchers]  
Type: Voucher  
IsODBCTable: Yes  
Fetch : *, AllLedgerEntries.*, LedgerEntries.*

Using Tally ODBC to Import Data from Excel

As of now, you cannot import data into Tally ERP using ODBC. Though, the Tally ODBC driver supports SQL SELECT queries, it currently does NOT support UPDATE or DELETE queries. To import data into Tally, you must send HTTP post request to Tally on Port 9000. You can know more about Tally XML at Import data into Tally ERP