XBASE (DBF) to Tally

•    Introduction
•    Getting Started 
  
•    XBase (DBFs) Information 
•    Writing SQL-queries (to retrieve data from XBase - DBFs)
•    Writing XML tags (required by UDIMagic to identify/send data to Tally)
•    Sample DBF
•    Downloads
•    Links

XBase (DBF) files have been in use since eighties. These are light-weight databases which are quite popular with programmers developing Applications for small and medium-sized enterprises. Some of the popular DBF formats are:-
a) Dbase
b) Foxpro 2.x
c) Visual Foxpro tables (DBFs) and Database (DBC)

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

- Works with Visual Foxpro; Foxpro 2.x; DBase and other Xbase DBF files 
- Supports Tally 6.3 or higher versions
- DBF-to-Tally feature is available in the UDIMagic professional-edition only (UDIMagic v3.0 onwards)

The above task involves three main things:-

1) Xbase (DBF) information
    (Specify the folder which contains the DBF files OR  Connection-string for DBC files)

2) SQL-SELECT statements 
    (Writing SQL-SELECT statements to fetch data from the DBF's)

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

The first thing you need to do is to specify XBase (DBF) Infomation.

Follow the steps given underneath:-
a) Start UDIMagic.
b) Next, select "Advanced Usage" option (available in professional version).
c) Next, select the Database-type as "Visual Foxpro/Fox 2.x/XBase"
d) Next, click on the button "SETTINGS"" and specify the "FOLDER" which contains the DBF files.

Remarks:-
a) The UDIMagic Demo version comes with a sample DBF named "Ledger.dbf" (created in Fox 2.x)

For DBC files

Those using Visual Foxpro Database (DBC) files need to specify the Connection string instead of the folder path. Plus, you need to download and install VFPOLEDB from the Microsoft site.

Sample Connection-string for using Visual Foxpro (DBC) files

Provider=vfpoledb.1;Data Source=C:\MyDbFolder\MyDbContainer.dbc;Collating Sequence=machine;

This process involves :-
a) Plan-out which DATA is to be transferred from DBF's 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 :-

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

In the previous section, we had seen how to fetch data from the XBase (DBFs). 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  (Ledger.dbf created in Fox 2.6) as given below:-

SQL-query

<SQL.LIST>
<SQL>
SELECT name, groupname AS parent,opbal*-1 AS openingbalance FROM ledger WHERE optype='Dr' UNION
SELECT name, groupname AS parent,opbal*1  AS openingbalance FROM ledger WHERE optype='Cr'
</SQL>
</SQL.LIST>

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

Result data-set

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

XML tags

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


SUMMARY

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


Sample Xbase (DBFs)