rtslink.com
Home / UDI-Magic / Professional / MySQL to Tally

MySQL to Tally


Looking for a software utility to import data from MySQL into Tally software. Try out UDIMagic Professional.


CONTENTS


Introduction

MySQL is a popular open-source Database Management System (DBMS) which is used as a backend in many ERP packages. Untill now, integrating an ERP package with Tally was a tedious task as it required modifications in the existing ERP package. Moreover, this could be done only by programmers.

UDIMagic professionl edition allows to fetch data from your MySQL database and send it directly to Tally. And all this happens by merely writing few SQL-SELECT statements and XML-tags.

- Works with MySQL 4.x and higher versions
- Supports Tally 6.3 and higher versions
- MySQL-to-Tally feature is available in the UDIMagic professional-edition only (UDIMagic v3.0 onwards)


Getting Started

How do I transfer data from MySQL database  into Tally ?

The above task involves three main things:-

1) MySQL Server information
    (Specify the IP address; database name; user-name; password)

2) SQL-SELECT statements 
    (Writing SQL-SELECT statements to fetch data from the MySQL database)

3) XML tags 
    (Writing XML tags required by UDIMagic to process the data and send it to Tally)

Though use of UDIMagic does not require any programming knowledge, you need to have basic understanding of SQL (Structured query Language). And Of-course, you must have the rights / privileges to access the MySQL database.

MySQL Server Information

The first thing you need to do is to specify MySQL Server Infomation.

Follow the steps given underneath:-
a) Start UDIMagic.
b) Next, select "Advanced Usage" option.
d) Next, select the Database-type as "MySQL"
d) Next, click on the "Settings" button and specify the IP address and other details of your MySQL Server.
e) Next, click on the "TEST SERVER CONNECTION" button to test-check the connection.

Remarks:-
a) The UDIMagic Demo version comes with default values for MySQL Server. These values (IP address; database-name etc) refer to Shweta Computer's MySQL 4.x database named "scdatabase"
b) Access to the MySQL "scdatabase" is provided for Demo purpose only.
c) Users can specify their MySQL Server details and test-check it.

For MySQL 5.x users

Those using MySQL 5.x or higher versions may get error 'Client does not support authenticated protocol' message. To resolve this issue, you need change the password to pre-4.1 style. This can be done using the SET PASSWORD statement and the OLD_PASSWORD() function:-

SET PASSWORD FOR  'some_user'@'some_host' = OLD_PASSWORD('newpwd');

For more information on this issue, refer this link http://dev.mysql.com/doc/refman/5.0/en/old-client.html


Writing SQL-SELECT statements to fetch data from MySQL database

After you have test-checked the connection to your MySQL database, you need to write SQL statements (queries) to fetch data from MySQL.

This process involves :-
a) Plan-out which DATA is to be transferred from MySQL to Tally
b) Identify the tables (Masters and Vouchers) that contain the required data
c) Write SQL-queries to fetch data

SQL-QUERIES
SQL-queries are generally written in a Text file (having .qry extension). You can use any TEXT-editor (like Notepad) to view/edit the SQL-query files.

UDIMagic allows you to write multiple SQL-SELECT statements in a Query (qry) file.

Sample file :-

MySQL Query1.qry


<SQL.LIST>
<SQL>.........your SQL-SELECT statement1..........</SQL>

<SQL>.........your SQL-SELECT statement2..........</SQL>
........
</SQL.LIST>

As you can see, each SQL-SELECT statement is to be enclosed in <SQL>....</SQL> tag. This allows you to have multiple queries in a single Query (txt) file.

UDIMagic processes these queries one-by-one in the order in which they appear in the query-file. It contructs a result data-set which can be visualized as Columnar format comprising of Rows and Columns. The first field in the result data-set can be accessed using COLUMNREFERNCE A, the second field in the result data-set can be accessed using COLUMNREFERENCE B and so on. This has been discussed in the next section.

Writing XML Tags required by UDIMagic to identify data retrieved from MySQL

In the previous section, we had seen how to fetch data from the MySQL Server. In this section, we shall focus on how to write XML tags to identify the data and send it to Tally.

What are these XML tags and why are they required ?

To import data into Tally, you need  to tell UDIMagic what data you have and where is it stored.  This is done using XML tags.
Let's take an example to understand it.

EXAMPLE:-

Step 1:
Write a SQL-query to fetch data from Ledger table  (of "scdatabase") as given below:-

SQL-query
(Refer file "MySQL Query2.qry" provided with UDIMagic Demo)

<SQL.LIST>
<SQL>
SELECT l.name AS name,g.name AS parent FROM ledger l,groupmaster g WHERE l.groupid=g.id </SQL>
</SQL.LIST>


The result of the above SQL-query can be visualised as follows:-

Result data-set

Name Parent
Cash Cash-in-hand
Profit & Loss A/c Primary
Shweta Softwares Sundry Debtors
Sale of Goods Sales Accounts
VAT Output 4% Duties & taxes

Step 2: Write XML tags to identify and send the data to Tally

XML tags
(Refer file "MySQL XMLTags2.xml" provided with UDIMagic Demo)

<XML.LIST>
  <XML>
    <MASTER TYPE="LEDGER">
      <NAME.LIST>
        <NAME COLUMNREFERENCE="A"/>
      </NAME.LIST>
      <PARENT COLUMNREFERENCE ="B"/>
    </MASTER>
  </XML>
</XML.LIST>


SUMMARY

- Fetch data from MySQL database using SQL-SELECT statements
- Write XML tags required by UDIMagic to process the data and send it to Tally.

Sample MySQL Database

The sample MySQLdatabase named 'scdatabase' contains the following tables:-

TableName Remarks
Unit Unit master
StockGroup StockGroup Master
StockItem StockItem Master
GroupMaster Group Master
Ledger Ledger Master
Mvouch Contains Voucher details - main table
tvouch1 Contains Voucher details - child table containing items
tvouch2 Contains Voucher details - child table containing ledgers

Downloads
UDI-Magic Demo version


Links
UDIMagic Home Page 
UDIMagic Articles and sample Excel templates



Tags:-
UDIMagic,  MySQL to Tally,  Excel to Tally
Tally is a registered trademark of Tally Solutions FZ LLC.

Valid HTML 4.01 Transitional