Import Excel data (including UDFs) into Tally Software
UDIMagic is a software utility that allows you to import data from MS-Excel into
Tally Software. It supports all Masters and Vouchers-types.
This article illustrates how to Import data including UDFs from
Excel into Tally Software. For reference, sample Excel sheets and Tally Data backup
have been provided at the end. To begin with, lets have a look at "What are
UDFs ?"
UDF stands for User-defined-field. Tally Software supports UDFs.
(i.e. it allows you to add new fields in any Voucher-Entry screen or Master-entry
screen).
UDFs in Tally are created using the Tally Defintion Language (TDL) which is a proprietory
product of Tally Solutions, Bangalore. You can get more information about Tally
Definition Language at our blogsite
tdlplayground or "How
to Customize Tally using TEXT files ?" . Herein, we shall focus on how
to import Excel data including UDFs into Tally Software using the
UDIMagic utility.
Tally Software supports INDIAN VAT, and comes with several reports / forms as required
by Customers in different Indian States. However, as the taxation provisions vary
from one country to another, the default VAT reporting formats in Tally software
may not be useful to Customers using Tally in other Countries. As a result, Tally
Solutions-Bangalore has provided a TCP (Tally-compiled-file) which contains new
reports and fields to meet the requirements of Customers in Singapore.
To use the TCP file provided by Tally Solutions, follow these steps:-
Steps
a) Download and copy the GST72.TCP
file into the "Tally" folder.
(Please note that this TCP is for Tally 7.2)
b) Make changes in the Tally.ini to include the TCP file.
User TDL =Yes
TDL=c:\tally\gst72.tcp
c) If the changes in the Tally.ini are done correctly, then "GST version
2.06" will be displayed in the Gateway of Tally. Please note that you
need to re-start Tally for the TCP file to take effect.
d) Next, select option "Gateway of Tally -> F12 -> General -> Use Accounting
Terminology" and set it as Singapore.
e) Next, select option "Gateway of Tally -> F11 -> Company Operations ->
Set modify other Company features" and set it as Yes
f) Next, enable the "Prepare Singapore Style GST return" option
by setting it as Yes.
g) After this, when you do a Purchase of Sale entry, notice that a new field
"GST Type" appears below the Voucher-Type field.
h) Next, input one Sales Voucher and one Purchase Voucher for test-purpose.
UDIMagic supports all Tally Masters and Voucher-types. If you are using any TCP
file that adds new UDF's in Tally, UDIMagic works with that too. To be concise,
whatever data you can manually enter (any Voucher or Master) in Tally, the same
can be processed by UDIMagic too.
All you need to do is tell UDIMagic what data you have in your Excel sheet and where
is it stored. This is done by using XML tags.
As mentioned earlier, XML tags are used to specify "What data we have in our Excel
sheet and where is it stored ?". Though writing XML tags is not very difficult task,
it may take some time. For those who are not familiar with XML, you can get some
good tutorials for understanding the basics of XML at www.w3schools.com
To get-going with XML, refer these links :-
a)
UDIMagic Tutorials
b)
Understanding XML tags
And here are the steps that we follow while writing XML tags for Excel-sheets:-
STEP 1
Before you begin writing XML tags, make sure you have two things :-
a) Excel sheet with sample data
b) Tally data (i.e. effect of those sample entries given in Excel sheet)
STEP 2
Next, export the Tally data using options :-
a) Display -> Daybook -> Alt+E
(This exports Voucher data and generates XML tags in Daybook.xml)
b) Display-> List of Accounts-> Alt+E
(This exports Master data and generates XML tags in Masters.xml)
The above will generate the XML tags for Vouchers and Masters (including UDFs).
STEP 3
Modify the XML tags generated by Tally Software by specifying the Excel-sheet COLUMNREFERENCE's
Remarks:-
In Step 2, we had exported the Tally data which generated XML tags in Daybook.xml
(and Master.xml for Masters) . These XML tags contain static values and must
be modified to include the Excel-sheet COLUMNREFERENCE where the Actual data resides.
In other words, we can use the same XML tags that are generated by Tally Software;
modify the same a little-bit to specify the Excel-sheet COLUMNREFERENCE's from where
data is to be taken.
The easiest way to learn writing XML tags is to refer our blog-site
www.udimagic.blogspot.com
where we provide Excel-sheets and their XML tags on regular basis. Few Excel-sheets
(with XML tags) have been provided at our site www.rtslink.com/downloads.html
also.
Download the sample
Excel sheets and the Tally data backup for Singpore GST.
ZIP file contents:-
a) Excel Sheets
sales-with-tax.xls
sales-without-tax.xls
sales-credit-notes.xls
purchase-debit-notes.xls
purchase-with-tax.xls
b) XML tags for Excel-sheets
sales-with-tax-xml-tags.xml
sales-without-tax-xml-tags.xml
sales-credit-notes-xml-tags.xml
purchase-debit-notes-xml-tags.xml
purchase-with-tax-xml-tags.xml
c) Tally 7.2 Data backup (for sample Excel sheet entries)
d) GST72.TCP (TCP file provided by Tally Solutions,Bangalore)
|
