Tally to Database Server: Data Loader Utility

Commandline utility to load data into Database Server from Tally software, intended for further use by

MS Excel / Google Sheet (for tabular reports)
Power BI / Tableau / Google Data Studio (for dashboards)

Index

Version
Upcoming Features
Requirements
Download
Tally XML Server
Database Creation
Utility Installation
Configuration Setup
- Database Connection
- Tally Options
Steps
Tutorial
Understanding Database Structure
Incremental / Full Sync
Tally Export Config
Commandline Options
Logs
Reports
Google BigQuery
Develop Further
License
Contact
Credits
Known Issues
Frequently Asked Questions
Release History

Version

Latest Version: 1.0.17
Updated on: 24-Jul-2022

*Note: I keep on fixing utility and adding fields into database. So you are requested to re-create existing databases and re-download utility folder *

Upcoming Features

Stagnant water is hazardous. Same applies to human brain. Improvement / Exploration is a one of key aspect of this project.

Upcoming features:

Graphical Interface for the utility (Electron JS powered App)
Password encryption in config.json to prevent password reveal while editing it
Web-page to configure tally-export-config.yaml and database-structure.sql for easy expansion of Tally system defined and/or TDL fields

Tentative Features:

Export to Excel (cannot promise, is a bit difficult to implement in Node.JS)

Requirements

Utility requires installation of following as a pre-requisite (along with download link)

Windows 10
Tally.ERP 9 / Tally Prime
Node JS
Database Server (supports any of below)

Free version of all the above Database Servers are available for download. Also all of them are available on popular cloud like Microsoft Azure / Google Cloud Platform / Amazon Web Services

Preferred versions:

SQL Server - version 2019
MySQL - version 8.x
PostgreSQL - 11.x or above

Note: Utility and SQL Queries for reports are deviced considering latest version of the above Database Server. Running it in lower version might hamper few of the functionalities, as some SQL syntax were introduced in latest version

Download

Database Loader Utility is portable, and does not have a setup wizard like we find for software installation. Zip archive of utility can be downloaded from below link. Kindly use open-source & free software 7-zip file archiver to un-compress utility archive.

Download Database Loader Utility

Also, it is a commandline utility having no window interface (to keep it minimal and faster)

Tally XML Server

Tally has in-built XML Server capability, which can import/export data in/out of Tally. This utility sends export command to Tally along with report specification written in TDL (Tally Developer Language) in XML format. In response, Tally returns back the requested data (in CSV format), which is then imported into Database Server. This utility works for both Tally.ERP 9 and Tally Prime both. Kindly ensure that XML Server of Tally is enabled (one-time). Avoid running Tally Prime & ERP.9 both at same time, as by default, both of them would try to block port numbert 9000 for XML Server. If you still need to run both simulteneously, change the port number of Tally Prime, or you can also disable XML Server of any of the Tally instance.

Tally.ERP 9

Gateway of Tally > Configure (F12) > Advanced Configuration
Set Tally.ERP 9 is acting as Both

Tally Prime

Help (F1) > Settings > Connectivity
Client/Server configuration
Set TallyPrime is acting as Both

Database Creation

Database first needs to be created and then Tables needs to be created in which data from Tally will be loaded, before running utility. File database-structure.sql contains SQL for creating tables of database. Just ensure to create database using any of GUI Database Manager. That database name should be updated in schema property of config.json. Open-source database editor available freely are

Note: Database structure creation SQL script for PostgreSQL is avilable inside platform/postgresql folder of project. In future, database technology-wise separate SQL Script will be available for individual technologies.

Utility support import into database server installed and hosted on

Same PC where Tally is
On any machine on LAN
Virtual Private Server
Cloud Database [ Microsoft Azure / Amazon Web Services (AWS) / Google Cloud Platform / Oracle Cloud ]

Configuration Setup

Utility contains a file config.json containing database connection and tally related settings.

Database Connection

Database Connection credentials needs to be set in the file in database section of config.json. A sample configuration file

SQL Server

"database": {
    "technology": "mssql",
    "server": "localhost",
    "port": 1433,
    "ssl": false,
    "schema": "<database_name>",
    "username": "sa",
    "password": "<your_password>",
    "loadmethod": "insert"
}

MySQL / MariaDB Server

"database": {
    "technology": "mysql",
    "server": "localhost",
    "port": 3306,
    "ssl": false,
    "schema": "<database_name>",
    "username": "root",
    "password": "<your_password>",
    "loadmethod": "insert"
}

PostgreSQL Server

"database": {
    "technology": "postgres",
    "server": "localhost",
    "port": 5432,
    "ssl": false,
    "schema": "<database_name>",
    "username": "postgres",
    "password": "<your_password>",
    "loadmethod": "insert"
}

Settings	Value
technology	mssql: Microsoft SQL Server mysql: MySQL Server or MariaDB Server postgres: PostgreSQL Server bigquery: Google BigQuery csv: Generate CSV dump for further import (below parameters of database connection are dummy when CSV setting is applied)
server	IP Address of PC on which Database Server is hosted (localhost = same machine)
port	Port number on which Database Server is listening mssql: Default port is 1433 mysql: Default port is 3306 postgres: Default port is 5432
ssl	true: Secured (to be used only if Database Server is on Cloud) false: Unsecured [default] (to be used when Database Server is on same machine / within LAN / within VPN) Supported for mssql / postgres only
schema	Database name in which to insert data
username	Username mssql: Default user is sa mysql: Default user is root postgres: Default user is postgres
password	Password for corresponding user. It is set during installation of Database Server. Note: Trusted Login (password-less) of SQL Server not supported by this utility
loadmethod	insert: loads rows in database tables using SQL query with multiple rows. This is most compatible method which works everywhere (Compatibility: High / Performance: Slow ) file: loads rows in database table using file based loading method. This method works only when database server and utility is running on same machine. So this method is not compatible with Cloud databases (Compatibility: Low / Performance: Fast )

Kindly override configurations, as per respective Database Server setup

Note: Utility supports SQL Server connection via TCP/IP port only. This option is disabled by default, which needs to be enabled. Kindly refer FAQ where it has been elaborated in detail along with screenshots (applicable for Microsoft SQL Server only)

Tally Options

Few of the options of Tally may need modification, if default settings of Tally are specifically over-ridden (due to port clashes). A sample configuration of tally is demonstrated as below

"tally": {
     "server": "localhost",
     "port": 9000,
     "fromdate" : "20190401",
     "todate" : "20200331",
     "sync": "full",
     "company": ""
}

Setting	Value
server	IP Address or Computer Name on which Tally XML Server is running (localhost is default value equivalent of IP Address 127.0.0.1). Change this if you need to capture data from a Tally running on different PC on your LAN
port	By default Tally runs XML Server on port number 9000. Modify this if you have assigned different port number in Tally XML Server settings (typically done when you want run Tally.ERP 9 and Tally Prime both at a same time parallely, where you will be changing this port number)
master / transaction	true = Export master/transaction data from Tally (default) false = Skip master/transaction data
fromdate / todate	YYYYMMDD = Period from/to for export of transaction and opening balance (in 8 digit format) auto = This will export complete transactions (irrespective of selected Financial Year) from Tally by auto-detection of First & Last date of transaction
sync	full = Sync complete data from Tally to Database Server (default) incremental = Sync only that data which was added/modified/delete from last sync
company	Name of the company from which to export data or leave it blank to export from Active company of Tally (this parameter is intended for use when user needs to export data from specific company irrespective of it is active or not. Setup a powershell script to run a loop when multiple companies needs to be targeted one-by-one)

Steps

Create database in Database Server along with tables inside it (use database-structure.sql to create tables) [ignore if already created]
Ensure options are properly set in config.json
Ensure Tally is running and target company from which to export data is Active
Run the file run.bat
Commandline window will open, attempt to import data and will get closed after import/error
Check for import status in import-log.txt file and errors (if any) in error-log.txt file

Tutorial

YouTube tutorial video are availabe (link below)

SQL Server

MySQL Server

Tally Export Config

Certain times we may require to add or remove any of the fields from export (to add user defined fields created by TDL Developer in Tally customisations). So this export specification is defined in tally-export-config.yaml file in YAML format. This file is divided into Master and Transaction, containing multiple tables in it. To understand structure and nomenclature, an example of this is given below

master:
    - name: mst_group
      collection: Group
      fields:
        - name: guid
          field: Guid
          type: text

name: mst_group (Database Table name)
collection: Group (Tally Collection name)
name: guid (Database Column name)
field: Guid (Tally field name)
type: text / logical / date / number / amount / quantity / rate / custom

amount: Credit = positive / Debit = negative
quantity: In Quantity = positive / Out Quantity = negative
rate: Rate type of data (is always positive)
custom: Any custom expression in TDL format

Logs

Utility creates log of import specifying how many rows in each tables were loaded. This log can be found in import-log.txt file. If any error occurs, then details of error(s) are logged in error-log.txt file

Reports

Project hosts library of SQL Queries to generate some popularly used reports, required for preparing Dashboards in Microsoft Power BI and Google Data Studio. Due to minor difference in SQL syntax & functions of SQL Server and MySQL, SQL for same report is provided for both of these Server platforms.

Author actively supports Google BigQuery (fully cloud-based solution of Google), and even shared equivalent SQL query for BiQuery. BigQuery acts as a input for Google Data Studio Dashboards and also supports easy export of tabular output to Google Sheets. Interested users can sign-up for a free Google Cloud account and use BigQuery with free daily limits

Develop Further

If you intend to develop and modify this utility further to next level for your use-case, then you can clone this project from Git and run the project as below

Clone the project repository
Install Visual Studio and open the project repository folder
Install required npm packages by following command npm install
Install global instance of typescript compiler available on Node Package Manager by following command npm install typescript -g
Run the project in Visual Studio code (launch.json file already provided in .vscode folder to run it with required settings)

License

This project is under MIT license. You are free to use this utility for commercial & educational purpose.

Contact

Project developed & maintained by: Dhananjay Gokhale

For any query email to dhananjay1405@gmail.com or Whatsapp on (+91) 90284-63366

Credits

Bug fixes or enhancements from various contributors

CA Venugopal Gella - Fixing of Tally Prime 2.0.1 export issue

Known Issues

When multiple companies are selected in Tally & specific company name is specified in config.json, it has been observed that in a rare case (especially on Windows Server), Tally fails to fetch data from that target company & internally produces an error that specified company is not loaded.
It has been observed that sometimes when Tally remain running for several days on PC then in a rare case Tally fails to return back updated / latest data (especially on Windows Server) & you may have to restart Tally.
If you have configured automatic sync of data via Windows Task Schedular, then make sure you don't log-off, but just disconnect as Tally is graphical based software.

wesdx/tally-database-loader