Thursday, May 8, 2008

XML tags with Formula's

UDIMagic || Excel to Tally

This post illustrates how to write XML tags that include formula's. For those who are new, here's a brief introduction to UDIMagic.

"UDIMagic is a software utility that allows you to Import Excel data into Tally Software. It can process any type of data (Masters or Vouchers) irrespective of the format in which it is arranged in the Excel sheet. All you need to do is to specify what data you have in the Excel sheet and where is it stored. This is done using XML tags."

In simple words, UDIMagic uses the XML tags to read the Excel sheet data and then Import it into the Tally Software.

Now, let's us try to understand how to write the XML tags. For illustrative purpose, we have taken up a single tag i.e. the NARRATION tag which is used to specify the Narration in Voucher-entries.

Format 1
<NARRATION> This is a Static Narration </NARRATION>

Remarks:-
The above XML tag instruct UDIMagic that NARRATION tag has a static / fixed value.

Format 2
<NARRATION COLUMNREFERENCE="B"/>

Remarks:-
In the above XML tag, we have used an attribute named COLUMNREFERENCE which instructs UDIMagic to fetch the value of NARRATION tag from Column B of the Excel sheet.

Format 3
<NARRATION FORMULA="=+B# &amp; &quot; &quot; &amp; C#"/>

Remarks:-
In the above XML tag, we have used an attribute named FORMULA which instructs UDIMagic that NARRATION tag value is to be taken from TWO columns viz Column B and Column C.

Writing FORMULA's

Before you write any formula in XML, it is advised that you test the same in MS-Excel. Here are the steps for writing / testing the Formula that we require :-

1) Start Excel and open a new workbook
2) Enter "Shweta" in Cell B2
3) Enter "Computers" in Cell C2
4) Enter a formula in Cell D2 as shown below:-
=B2 & " " & C2

If you correctly enter the aforesaid formula, then you will get "Shweta Computers" in Cell D2. After you get the correct result, you can then write the formula in your XML tags with some modifications.

The aforesaid formula in XML format is as follows:-

Formula in XML
=B# &amp; &quot; &quot; &amp; C#

Formula in Excel
=B2 & " " & C2

Remarks:-
1) The ampersand sign (&) and quote (") are special characters and must be written differently in XML. The ampersand sign (&) is written as &amp; and the quote (") is written as &quot; in XML.

2) For more details on XML Escape sequences, please refer http://www,rtslink.com/introductionxmlsoap.html



No comments: