udiMagic Professional edition supports import of data from SQL Server database to Tally ERP 9. It supports :
How do I transfer data from SQL Server database into Tally ?
Before we proceed, let's have look at the steps involved in Excel to Tally vs SQL Server to Tally.
Steps | Import from Excel to Tally | Import from SQL Server to Tally | Remarks |
---|---|---|---|
Step 1 | Start udiMagic Software | Start udiMagic Software | |
Step 2 | Select option Excel to Tally |
Select option Advanced Usage |
|
Step 3 | Select option Import data into Tally |
Select option MS-SQL Server |
[SQL Server to Tally] TODO : Specify the Configuration string to connect to the SQL Server database |
Step 4 | Select the Excel file | Select the Query file | [Excel to tally] [SQL Server to Tally] |
Step 5 | Select the XML file [Optional - XML file is automatically selected] |
Select the XML file [Mandatory] |
Each Standard Excel template provided with udiMagic has a corresponding XML file. Here's an example: Excel file: Vouchers-Sales-Purchase-Basic.xls XML file : Vouchers-Sales-Purchase-Basic-xml-tags.xml |
Step 6 | Click the Start button | Click the Start button |
As it can be seen, the above task to import from SQL Server to Tally involves two main things:-
Though use of udiMagic does not require any programming knowledge, you need to have understanding of SQL (Structured query Language). And Of-course, you must have the rights / privileges to access the SQL Server database.
Advanced Usage
option.SQL Server
Settings
button and specify the "connection string" for your SQL Server.TEST SERVER CONNECTION
button to test-check the connection.A Connection string is a string that contains information required to connect to a database or a data-file. Because there are several providers and each provider may have multiple ways to make a connection, you can write a connection-string in multiple ways.
Connecting to a SQL Server InstanceProvider=sqloledb;Data Source=myServerName\theInstanceName;Initial Catalog=myDataBase;Integrated Security=SSPI;
Standard SecurityProvider=sqloledb;Data Source=myServerAddress; Initial Catalog=myDataBase;UserId=myUsername;Password=myPassword;
Trusted ConnectionProvider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
a) You need to replace the highlighted text with actual values.
b) For more information on connection-strings, please refer this link http://www.connectionstrings.com
As database structures vary from case to case, you must write SQL statements (queries) to fetch data from SQL Server.
Sample SQL SELECT query
<SQL.LIST> <SQL>SELECT vchno as id,basevtype,vchtype,vchno,vchdate,refno,refdate,partyname,partygstin,statename,itemname,hsncode,uom,qty,rate,discount,amount,igstrate,igstamt,cgstrate,cgstamt,sgstrate,sgstamt,salepurcledger,igstledger,cgstledger,sgstledger,narration FROM gstdata</SQL> </SQL.LIST>Remarks:
a) You must write SQL SELECT to select appropriate fields from your database tables.
b) Save the SQL Query in a file with extension qry.
c) If required, please consult your system administrator to write SQL SELECT statement.
Additional notes:
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. 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.
Advanced Usage
SQL Server
Browse
button and select the "SQL Query file"Browse
button and select the "XML file"Start
buttonYou can use named parameters :FromDate and :ToDate in your SQL Query to provide input for the fields "From Date" and "To Date" in udiMagic for uploading data from SQL Server to Tally ERP.
Here's a sample SQL SELECT query that shows how to use the :FromDate and :ToDate named parameters
SELECT field1,field2,fldDate FROM table WHERE table.fldDate>=:FromDate AND table.fldDate<=:ToDate
Instead of hard-coding the period (from and to dates) in SQL-query, we have used 2 named parameters viz :FromDate and :ToDate. The values for these parameters are assigned at runtime based on the period you enter in the udiMagic screen as shown here.