Import Masters from Excel into Tally
Importing data from Excel into Tally, involves three main tasks. These tasks are:-
1) Create an Excel sheet
2) Write XML tags
3) Run UDI-Magic and import data into Tally
Step 1 - Create an Excel sheet
Herein, we shall take up the sample Excel-sheet "Masters - StockItemMaster.xls"
which is provided with udi-Magic Demo version. The Excel-sheet contents are given
underneath for your reference.
Excel-Sheet: "Masters - StockItemMaster.xls"
|
|
A |
B |
C |
D |
E |
F |
|
1 |
NAME
|
Parent |
BaseUnits |
AdditionalName |
Standard-
CostListRate |
Standard-
PriceListRate |
|
2 |
Bush |
Spares |
Nos |
B1 |
15 |
18 |
|
3 |
Piston |
Spares |
Nos |
P1 |
75 |
95 |
Remarks:-
a) The Cell A1 highlighted in Yellow color contains Comments (i.e. XML tags).
b) Alternatively, you may store the XML tags in a separate TEXT file.
Step 2 - Write XML tags
Once your Excel sheet is ready, the next thing is to write XML tags. These tags
are used to tell UDI-Magic "What data you have in your Excel sheet and where is
it stored ?". For those who are not familiar with XML (tags), here's a brief introduction
to XML.
"XML tags are basically holders that hold your data. XML tags names are generally
descriptive and the user can easily make out
what it refers to. XML stands for Extensible Markup Language. "
Explanation of XML tags used in the sample Excel-sheet
The XML tags specified in cell A1 (File:- "Masters- StockItemMaster.xls") are given
herewith:-
|
XML Tags |
Remarks |
<MASTER TYPE="UNIT">
<NAME COLUMNREFERENCE="C"/>
<ISSIMPLEUNIT>Yes</ISSIMPLEUNIT>
</MASTER>
|
Create UNIT masters
Explanation:-
- NAME is to be taken from Column C
Remarks:-
C# means C2, C3,C4 and so on. In simple words, the # sign is replaced by row-numbers
at run-time. udi-Magic stops processing when a blank row or hidden row is found.
- ISSIMPLEUNIT tag always set to YES.
Summary
udi-Magic process all rows in the Excel sheet, contructs a valid XML string based
on the above specifications and sends it to Tally. |
<MASTER TYPE="STOCKGROUP">
<NAME.LIST>
<NAME COLUMNREFERENCE="B"/>
</NAME.LIST>
</MASTER>
|
Create STOCKGROUP masters
Explanation:-
- NAME is to be taken from Column B |
<MASTER TYPE="STOCKITEM">
<NAME.LIST>
<NAME COLUMNREFERENCE="A"/>
<NAME COLUMNREFERENCE="D"/>
</NAME.LIST>
<PARENT COLUMNREFERENCE="B"/>
<BASEUNITS COLUMNREFERENCE="C"/>
<ADDITIONALNAME.LIST>
<ADDITIONALNAME COLUMNREFERENCE="E"/>
</ADDITIONALNAME.LIST>
<STANDARDCOSTLIST.LIST>
<DATE>20070401</DATE>
<RATE COLUMNREFERENCE="F"/>
</STANDARDCOSTLIST.LIST>
<STANDARDPRICELIST.LIST>
<DATE>20070401</DATE>
<RATE COLUMNREFERENCE="G"/>
</STANDARDPRICELIST.LIST>
<OPENINGBALANCE COLUMNREFERENCE="H"/>
<OPENINGRATE COLUMNREFERENCE="I"/>
<OPENINGVALUE COLUMNREFERENCE="J"/>
</MASTER>
|
Create STOCKITEM masters
Explanation:-
- NAME is to be taken from Column A
- The 2nd NAME refers to the Alias and is taken from Column D
- PARENT is to be taken from Column B
- BASEUNITS is to be taken from Column C
- ADDITIONALNAME is to be taken from Column E
- STANDARDCOSTLIST tags comprises of two sub-tags. Herein DATE is static and RATE
is to be taken from Column F
- STANDARDPRICELIST tags comprises of two sub-tags. Herein DATE is static and RATE
is to be taken from Column G
-OPENINGBALANCE is to be taken from Column H
- OPENINGRATE is to be taken from Column I
- OPENINGVALUE is to be taken from Column J |
Remarks:
a) As seen above, the Excel file "Masters - StockItemMaster" contains additional
tags to create Units; StockGroup as these are required for creating the StockItem
Master.
b) Also, note that these Masters (Unit, StockGroup) are to processed before
StockItem Masters are created and hence they are specified before the StockItem
Master tags.
Step 3 : Run UDI-Magic and Import data into Tally
With your Excel sheet and the XML tags ready, you can now Import data into Tally
Software.
Steps
a) Start Tally Software.
b) Create/open the Company in which you want to Import the data.
c) Minimize Tally.
d) Start the UDI-Magic software and select the Excel-sheet from which you wish to
Import Data.
e) Follow wizard-screen instructions to Import data into Tally.
That's all.
You can check the data in Tally, using the following option:-
"Gateway of Tally >> Inventory Info >> Stock Items >> Alter"
Tags: Excel to Tally |
