tally-database-loader - GitLite

KO | EN

gitlite — search

#javascript #react #ai #llm #go #deep-learning #nodejs #macos #java #awesome #vue #awesome-list #ios #openai

tally-database-loader

(★ 149)

Tally to Database Server (SQL Server / MySQL / PostgreSQL / BigQuery) data transfer utility powered by Node.JS Javascript compiler. Supports CSV, JSON & Azure Data Lake export too.

파일 탐색기

최종 버전 다운로드 (.zip)

- launch.json
- settings.json
- database.mjs
- definition.mjs
- index.mjs
- logger.mjs
- server.mjs
- tally.mjs
- utility.mjs
- commandline-options.md
- data-structure.md
- faq.md
- google-bigquery.md
- incremental-sync.md
- release-history.md
- - data-load.sql
  - README.md
- - database-structure.sql
- - database-structure-incremental.sql
  - database-structure.sql
- - data-views.sql
  - database-structure-incremental.sql
  - database-structure.sql
- - list-open-companies.ps1
  - sync-multiple-company.ps1
- - account-ledger.sql
  - accounting-voucher-view.sql
  - bill-payable.sql
  - bills-receivable.sql
  - daily-cash-movement.sql
  - profit-loss.sql
  - purchase-daily.sql
  - purchase-monthly.sql
  - purchase-register.sql
  - sales-daily.sql
  - sales-monthly.sql
  - sales-register.sql
  - stock-voucher-view.sql
  - trial-balance.sql
- - account-ledger.sql
  - accounting-voucher-view.sql
  - bills-payable.sql
  - bills-receivable.sql
  - daily-cash-movement.sql
  - group-tree-children-parent.sql
  - group-tree-parent-child.sql
  - profit-loss.sql
  - purchase-daily.sql
  - purchase-monthly.sql
  - purchase-register.sql
  - sales-daily.sql
  - sales-monthly.sql
  - sales-register.sql
  - stock-summary.sql
  - stock-voucher-view.sql
  - trial-balance.sql
- database.mts
- definition.mts
- index.mts
- logger.mts
- server.mts
- tally.mts
- tsconfig.json
- utility.mts
.gitignore
config.json
database-structure-incremental.sql
database-structure.sql
gui.html
LICENSE
package-lock.json
package.json
README.md
run-gui.bat
run.bat
tally-export-config-incremental.yaml
tally-export-config.json
tally-export-config.yaml

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

# Tally to Database Server: Data Loader Utility
![logo](https://excelkida.com/image/github/tally-database-loader.png)

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](version)
* [Download](#download)
* [Requirements](#requirements)
* [Graphical User Interface](#graphical-user-interface)
* [Tally XML Server](#tally-xml-server)
* [Database Creation](#database-creation)
* [Utility Installation](#utility-installation)
* [Configuration Setup](#configuration-setup)
    * Database Connection
    * Tally Options
* [Steps](#steps)
* [Tutorial](#tutorial)
* [Understanding Database Structure](docs/data-structure.md)
* [Incremental / Full Sync](docs/incremental-sync.md)
* [Tally Export Config](#tally-export-config)
* [Commandline Options](docs/commandline-options.md)
* [Logs](#logs)
* [Reports](#reports)
* [Google BigQuery](docs/google-bigquery.md)
* [Develop Further](#develop-further)
* [License](#license)
* [Contact](#contact)
* [Credits](#credits)
* [Known Issues](#known-issues)
* [Frequently Asked Questions](docs/faq.md)
* [Release History](docs/release-history.md)

<br><br>

## Version
Latest Version: **1.0.44**<br>
Updated on: **02-Aug-2026**

Beta Feature:
* Collection based data extraction with 2x performance and 40% less RAM usage. In definition simply change file extension ~~yaml~~ to **json**

<br><br>

## 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.

[https://excelkida.com/resource/tally-database-loader-utility-1.0.44.7z](https://excelkida.com/resource/tally-database-loader-utility-1.0.44.7z)

<br><br>

## Requirements
Utility requires installation of following as a pre-requisite (along with download link)
* Windows 10
* [Tally Prime](https://tallysolutions.com/download/)
* [Node JS](https://nodejs.org/en/)
* Database Server (supports any of below)
    * [Microsoft SQL Server](https://www.microsoft.com/en-ie/sql-server/sql-server-downloads/)
    * [PostgreSQL](https://www.postgresql.org/download/)
    * [MySQL](https://dev.mysql.com/downloads/mysql/)
    * [MariaDB](https://mariadb.org/download/)
    * [Google BigQuery](https://cloud.google.com/bigquery/)

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*

<br><br>

## Graphical User Interface
Utility is available in 2 types of interfaces
1. Command-line based which can be invoked using **run.bat** file
1. Browser-based interface which can be invoked using **run-gui.bat** file

Both use the same back-end. File **run-gui.bat** uses browser-based interface which offers following features:
* Ease of editing config.json values (without requiring Notepad)
* Automatic default value setting based on dropdown to minimize invalid configuration values
* Option to send configuration to utility without requiring it to save (suggested when user do no wish to save &amp; reveal password in config.json file)

**Note:** *Please do not close the command-line window which acts as temporary webserver to interact with browser. Close it only after you close the browser tab of the utility.*

<br><br>

## 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 XML format), which is then imported into Database Server.

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

<br>

**Note: Support for Tally.ERP 9 has been removed, to keep database aligned to Tally Prime. Kindly upgrade to Tally Prime.**

<br><br>

## Database Creation
Blank database first needs to be created. Afterwards utility will automatically create tables.

<br><br>

## Configuration Setup
Utility contains a file **config.json** containing database connection and tally related settings.

<br>

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

**SQL Server**
```json
"database": {
    "technology": "mssql",
    "server": "localhost",
    "port": 1433,
    "ssl": false,
    "schema": "<database_name>",
    "username": "sa",
    "password": "<your_password>",
    "loadmethod": "file"
}
```
<br/>

**MySQL / MariaDB Server**
```json
"database": {
    "technology": "mysql",
    "server": "localhost",
    "port": 3306,
    "ssl": false,
    "schema": "<database_name>",
    "username": "root",
    "password": "<your_password>",
    "loadmethod": "insert"
}
```

**PostgreSQL Server**
```json
"database": {
    "technology": "postgres",
    "server": "localhost",
    "port": 5432,
    "ssl": false,
    "schema": "<database_name>",
    "username": "postgres",
    "password": "<your_password>",
    "loadmethod": "file"
}
```

<br/>

| Settings | Value |
| --- | --- |
| technology | **mssql**: Microsoft SQL Server<br>**mysql**: MySQL Server or MariaDB Server<br>**postgres**: PostgreSQL Server<br>**bigquery**: Google BigQuery<br>**json**: JSON file<br>**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<br>**mssql**: Default port is **1433**<br>**mysql**: Default port is **3306**<br>**postgres**: Default port is **5432** |
| ssl | **true**: Secured (to be used only if Database Server is on Cloud)<br>**false**: Unsecured [*default*] (to be used when Database Server is on same machine / within LAN / within VPN)<br>Supported for mssql / postgres only |
| schema | Database name in which to insert data |
| username | Username<br>**mssql**: Default user is **sa** <br>**mysql**: Default user is **root**<br>**postgres**: Default user is **postgres** |
| password | Password for corresponding user. It is set during installation of Database Server.<br>*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** ) <br> **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)

<br>

### 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

## Full sync (YAML)

```json
"tally": {
     "definition": "tally-export-config.yaml",
     "server": "localhost",
     "port": 9000,
     "fromdate" : "2025-04-01",
     "todate" : "2026-03-31",
     "sync": "full",
     "batchsize": 5000,
     "frequency": 0,
     "company": ""
}
```

## Full sync (JSON)

```json
"tally": {
     "definition": "tally-export-config.json",
     "server": "localhost",
     "port": 9000,
     "fromdate" : "2025-04-01",
     "todate" : "2026-03-31",
     "sync": "full",
     "batchsize": 5000,
     "frequency": 0,
     "company": ""
}
```

## Incremental sync

```json
"tally": {
     "definition": "tally-export-config-incremental.yaml",
     "server": "localhost",
     "port": 9000,
     "fromdate" : "auto",
     "todate" : "auto",
     "sync": "incremental",
     "batchsize": 5000,
     "frequency": 5,
     "company": ""
}
```

| Setting | Value |
| --- | --- |
| definition | Name of export config file in the utility folder. This setting is to be used for easy switching between incremental and full sync, as both the files contains different structure. The definition file is available in format **yaml** (Report-based) and **json** (Collection-based)  |
| 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*) <br> **false** = Skip master/transaction data |
| fromdate / todate | **YYYY-MM-DD** = Period from/to for export of transaction and opening balance <br> **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*)<br> **incremental** = Sync only that data which was added/modified/delete from last sync |
| batchsize | Number of vouchers to extract from voucher collection at in single batch (applicable only in Collection-based extraction method). Default value is 5000 which is found to be stable. Do not increase this beyond 10000 since as the export might get stuck from tally indefinitely |
| frequency | ping frequency in minutes to Tally to monitor changes in data and trigger sync (0 = off i.e. just run sync once and close it) |
| 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) |

<br><br>

## Steps
1. Create database in Database Server
1. Ensure options are properly set in **config.json**
1. Ensure Tally is running and target company from which to export data is Active
1. Run the file **run.bat**
1. Commandline window will open, attempt to import data and will get closed after import/error
1. Check for import status in **import-log.txt** file and errors (if any) in **error-log.txt** file

<br><br>

## Tutorial

YouTube tutorial video are availabe (link below)

**SQL Server**
<br>

[![YouTube tutorial SQL Server](https://img.youtube.com/vi/Am0uspXtTzM/0.jpg)](https://www.youtube.com/watch?v=Am0uspXtTzM)

<br>

**MySQL Server**
<br>

[![YouTube tutorial MySQL Server](https://img.youtube.com/vi/_bXc54bKTlI/0.jpg)](https://www.youtube.com/watch?v=_bXc54bKTlI)

<br><br>

## Tally Export Config

### YAML
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

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

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

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

### JSON
Utility also supports extraction collections and then loading them into database. In that scenarion, kindly use file **tally-export-config.json** in the definition settings.

<br><br>

## 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

<br><br>

## 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](https://cloud.google.com) and use BigQuery with free daily limits

<br><br>

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

<br><br>

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

<br><br>

## Contact
Project developed & maintained by: **Dhananjay Gokhale**

For any query email to **info@excelkida.com** or Whatsapp on **(+91) 90284-63366**

<br><br>

## Credits
Bug fixes or enhancements from various contributors

* [CA Venugopal Gella](https://github.com/gellavenugopal) - Fixing of Tally Prime 2.0.1 export issue

## Known Issues
* When multiple companies are selected in Tally &amp; 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 &amp; 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) &amp; 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.