Some of the commonly asked questions about data extraction using ODBC are answered here.
Tally OBDC helps you to extract the Data from Tally and design the reports in Excel. This can be done by following the steps listed below:
1. Start Tally.ERP 9 . It should be open till the process is complete.
2. Ensure that the words ODBC Server are displayed in the Configuration block of Information Panel (at the bottom) of Tally.ERP 9 screen:
Next, Open MS Excel and follow the steps to proceed:
3. Start > Programs > MS Excel
4. On the Data Menu > Click Import External Data > Select From Other Source > New Microsoft query. In the Choose Data Source dialog box, select TallyODBC_9999*.
Tally connects to data source and displays Query Wizard Choose Column screen > select the columns you would want to include in the query. For example, Select Ledger and Click " >" button to the right of the following fields: $Name , $Address , $State , $PinCode (move all the other address fields as well):
5. Set the filter conditions in the Filter Data dialog box to limit the data to those that match your criteria.
6. You can also sort the data in ascending or descending order in the Sort Order dialog box. Click on the Finish button to complete the Query Wizard process:
7. Once the Query Wizard process is complete, the dialogue box entitled Returning External Data to Microsoft Excel/Import Data appears:
8. Click on the OK button in the screen mentioned above to return to the Excel sheet which displays all the columns selected, as shown below:
Tally OBDC helps you to extract the Data from Tally and design the reports in Excel.
To extract data
● Start Tally.ERP 9. It should be open till the process is complete.
● Ensure that the words ODBC Server are displayed in the Configuration block of Information Panel (at the bottom) of Tally.ERP 9 screen.
Next, Open MS Excel
1. Start > Programs > MS Excel
2. On the Data Menu > Click Import External Data > Select New Database Query
3. In the Choose Data Source dialog box, select ‘TallyODBC’
4. Tally connects to data source and displays Query Wizard screen > select the columns you would want to include in the query. Select Ledger and Click " >" button to the right of the following fields: $Name, $Address (move all the other address fields as well.)
5. Set the filter conditions in the Filter Data dialog box to limit the data to those that match your criteria.
6. You can also sort the data in ascending or descending order in the Sort Order dialog box.
7. Click Finish to complete the Query Wizard process.
Once the Query Wizard process is complete, the dialogue box entitled Returning External Data to Microsoft Excel / Import Data appears.
8. Click OK in the screen mentioned above to return to the Excel sheet which displays all the columns selected, as shown below:
The procedure for accessing Tally data in MSExcel has been given below:
1. Execute Tally
2. Select the Company which you wish to access the data in Tally ODBC
3. Go to MSExcel.
4. Open the Menu Data > Get External Data > New data base query.
5. Click Tally ODBC > Select the required field (for e.g., Ledgers) > Choose the parent, name and the closing balances because these details are required for creating the Balance Sheet. The data can be brought to the first MSExcel sheet
6. In the second sheet, define the format of the Balance Sheet as per the requirements
7. Now define the formula for each field (for ex. Capital Account, Sundry Debtors etc.,) in the second sheet. Defining the formulae is an MSOffice tool.
1. Select the report in Tally > Click Alt+E (Export) > Specify the format as ASCII or SDF (select the ASCII format) > Specify the file name > Activate the required options and export.
2. Open MS Excel > File > Open > Select the Tally directory > Select the exported file > Text import wizard screen > If you have exported in ASCII format, select Delimited > Click Next > Deselect Tab and select comma > Click Next and Finish.
Tally ODBC helps you to extract the Data from Tally.ERP 9 and design the reports in MS Excel 2007.
1. Start Tally.ERP 9. It should be open till the process is complete.
2. Ensure that the ODBC Server is running. You can confirm this when the message Running as ODBC Server is displayed in the Configuration block of Information Panel (at the bottom) of Tally.ERP 9 screen, as shown:
In case, the ODBC Server is not running, enable the ODBC Server
● From Gateway of Tally or Company Info menu, press F12 Configure > Advanced Configuration.
● In the Client/Server Configuration screen, set Enable ODBC Server to Yes.
1. Start Microsoft Office 2007.
2. Click Data.
The sub options of Data menu appears as shown:
3. Click From Other sources .
4. Select From Microsoft Query .
Choose Data Source dialog box appears.
5. Select TallyODBC.
Tally.ERP 9 connects to data source and displays Query Wizard – Choose Columns dialog box.
6. Select the columns you would want to include in the query. Select Ledger and Click > button to the right of the following fields: (For example, $Parent)
7. Click Next .
The Query Wizard - Filter Data dialog box appears.
8. Set the filter conditions in the Filter Data dialog box to limit the data to those that match your criteria.
9. Click Next .
The Query Wizard - Sort Order dialog box appears.
10. Sort the data in ascending or descending order as per the requirement.
11. Click Next.
The Query Wizard - Finish dialog box appears.
The option Return Data to Microsoft Office Excel will be selected, by default.
12. Click Finish.
Once the Query Wizard process is complete, the dialogue box entitled Import Data appears.
13. Click OK .
The excel sheet will display the report as shown below:
This error occurs when, tallywin.dat file found in the application folder is absent or corrupted. Further, this can also occur when the bitness of tallywin.dat file does not match with the Operating System. This may happen when tallywin.dat file is copied manually.
Ensure that the tallywin.dat file is available along with exe and dll files necessary for ODBC data exchange. In case the file is corrupted or is not compatible with the bitness of the Operating system, it is recommended to change the tallywin.dat file.
Let us suppose a customer want the details of the purchase and sales and also wants to see the stock item through ODBC.
Ensure that ODBC is displayed in the calculator pane of Tally.ERP 9 when the particular company is opened.
To import data into excel from Tally.ERP 9
1. Open MS Excel > Data > Import External Data > New Database Query .
2. Just as in the Tally.INI file the server port mentioned is 10000 and displayed as such 10000, in the calculator pane, Tally ODBC_10000 has to be selected. If nothing is mentioned in the Tally.INI file it will take as default port 9000 and the same has to be selected.
3. Select the data source in the Choose Data Source screen as shown below:
4. Double-click Tally ODBC_10000* . The Query Wizard - Choose Columns screen is opened as shown below:
5. Double-click on the company vouchers and select the particular field shown in the right side.
6. Click Next .
7. Click Next .
8. Click Next .
9. Select View data or Edit query in Microsoft Query , if you want to add details of another table. For example: Stock Item.
10. Click Finish .
11. Click Add Table tool to add another table.
12. Select stock item and click Add .
13. Click Close .
The screen displayed will be as shown below:
14. Double-click the field Name , Inward quantity , Inward Value , Outward quantity , Outward Value .
15. Click File and the click Return Data to Microsoft Excel .
The data will be returned to Excel as shown below:
By default, Tally.ERP 9 installs an ODBC driver of the same bitness on startup and uninstalls the same, on quitting. This driver can be used by an ODBC client (like the Microsoft Office - Excel) of the same bitness. There is no manual intervention required in this case.
A user may install the ODBC driver (tallywin32.dat or tallywin64.dat) manually to enable export of data via ODBC, when the bitness of Tally.ERP 9 and the Microsoft Office (in a 64-bit Operating System) is not the same.
For information on Installation of ODBC drivers for effective export of data via ODBC, click here .
Note : ODBC drivers can be installed either in the default port (fetched from the tally.ini file) or using a different port.
If the user has installed the ODBC driver using the default port (mentioned in the tally.ini file), follow the given steps.
● Go to Start
● Select Run
● Type cmd and press Enter to open the Command Prompt
● Type the following command to unregister the ODBC drivers,
o If 32-bit ODBC driver was installed - <Tally.ERP 9 64-bit application folder path>regodbc32.exe /UNREGISTER
Or
o If 64-bit ODBC driver was installed - <Tally.ERP 9 32-bit application folder path>regodbc64.exe /UNREGISTER
● Press Enter .
The ODBC driver will now be unregistered.
For example: If the Tally.ERP 9 application folder path is C:tally and 32-bit ODBC driver was installed with 64-bit Tally.ERP 9, then the command to be entered in the Command prompt would be:
C:tallyregodbc32.exe /UNREGISTER
On pressing Enter , the following message will be displayed.
Server at port: 9000 Unregistered
If you have installed the ODBC driver using a different port, then to unregister, enter the following command in the command prompt.
●
If
32-bit ODBC driver was installed (with 64-bit Tally.ERP 9 application):
<Tally.ERP 9 64-bit application folder path>regodbc32.exe /UNREGISTER
PORT
Or
●
If
64-bit ODBC driver was installed (with 32-bit Tally):
<Tally.ERP 9 64-bit application folder path>regodbc64.exe /UNREGISTER
PORT
For example: In the Tally.ERP 9 application folder - C:tally, if a 64-bit ODBC driver was installed with 32-bit Tally.ERP 9 and the port used to install the ODBC driver is 10000, then the command to be entered in the Command prompt would be
C:tallyregodbc64.exe /UNREGISTER 10000
On pressing Enter, the following message will be displayed
Server at port: 10000 Unregistered
While fetching Ledger information through ODBC in MS Excel an error message displayed as Error in TDL. ‘Collection: ABCCompany1-Apr-2014)’.TallyUser.Ledger’ Could not find description!
As there is no opening bracket mentioned in the company name, closing bracket should be removed. Check the company name and set it right by removing ) = Close bracket.
1. Take Data backup.
2. Go to Company alteration screen.
3. Remove ) = Close bracket.
4. Accept the company details.
Then you will be able to proceed with fetching data through ODBC in MS Excel.
To resolve this issue, install/activate MS Query.
Select the company and minimize Tally before opening ODBC in Excel. This error message will continue to be displayed till you select a company.
A brief exercise will explain the process to use ODBC through MS-Word.
Exercise 1: ABC Company wishes to send greeting cards to all its customers. The customer database in Tally already exists. You want to use the names and addresses that you stored there to print out labels for the Greeting Cards.
1. Start Tally.ERP 9. It should be open till the Mail Merge process is complete.
2. Ensure ODBC Server is visible in the calculator pane of Tally screen.
3. Open Ms Word and follow the steps to proceed:
● Start > Programs > MS Word
● On the Tools Menu > Click Mail Merge
● Under Main document, click Create > click Mailing Labels .
● Click Active Window .
1. Click on Get Data > Click on Create Data Source
2. Click on MS Query
3. In Microsoft Query screen > Select TallyODBC
4. The Query Wizard brings the Tally fields that may be selected. Select Ledger and Click Next.
● You can also give filter conditions in Filter Data dialogue box to limit the data brought in to only those that match your criteria.
● You can also sort the data in ascending or descending order in the Sort Order dialogue box.
5. Click Finish to complete the Query Wizard process.
6. Click Set Up Main Document .
7. In the Create Label dialogue box:
● The process of Mail Merge is intuitive and is the domain of MS Word. You can set up the main document and bring in the Create Labels dialogue box. Select the printer information and product number.
● Click on Insert Merge Fields to insert merge fields into sample label box. The labels will be created in the following format. Click OK .
8. Click Merge in the Mail Merge Helper dialogue box.
9. Clicking on Merge in the Merge dialogue box will set the query options as shown below:
The Mailing Labels will be created to a new word document as shown below:
The above error messages are displayed due to either of the following reasons:
● Microsoft Office is not installed
● The components of MS-Office used for 'Tally ODBC is corrupted'
Steps to resolve
● Install MS-Office [including MS Query]
● If MS-Office is not installed and the user does not wish to install it, download MDAC 2.8 SP1.
o Click here to download MDAC 2.8 - http://msdn2.microsoft.com/en-us/data/aa937730.aspx .
o Click on MDAC 2.8 SP1
o Download and install it.
o Restart the system and start Tally.
Mail Merge using MS Word 2003 through ODBC
Can we have the list of Customers in a label format from the Tally 9 Database using MS Word 2003?
We have been using Tally’s ODBC facility in MS Word 2000 apart from using the Mail Merge facility. Can the same facility be used in Tally 9 with MS Word 2003?
Our main aim is to get the address of the customers from Tally 9 in a label format.
In the procedure mentioned below, Tally data is first imported using MS Excel 2003 . This is because you are allowed to create a Query to either import only the Sundry Debtors or Sundry Creditors or both. This involves eliminating all the other groups of Tally and then importing the query created above (using MS Excel 2003) to MS Word 2003.
Note: You can also use only MS Word 2003 to import the data. In this case, you would be required to manually select the required groups of Tally 9 (Example Sundry Debtors/Creditors) during the process by editing the Mail Merge Recipient list which unlike MS Excel is very tedious. This may be done, by following the steps listed below:
1. Open Tally
2. Ensure that the words ODBC Server is visible in the calculator pane of Tally screen
3. Open the Excel sheet
4. Go to Data > Import External Data > New Database Query
5. Choose the Tally Source (Example: TallyODBC 9111*)
6. Click OK
7. The Query Wizard window opens
8. Select Ledger
9. Expand the list under the Ledger. Select the required fields and move them to the right side
10. Click Next
11. Map the Parent as Sundry Debtors (if only Sundry Debtors are required) or Sundry Creditor (if only Sundry Creditors are required) or select both one by one, based on the requirement.
12. Click Next
13. Select sorting if required
14. Click Next
15. Click on Save Query
16. Save the Query in a particular path (Eg. C:Documents and SettingsUserApplication DataMicrosoftQueriesQuery from TallyODBC_9000.dqy)
17. Click on Finish
18. The customer data can also be seen in the Excel Sheet .
19. Click OK in the Import Data window to import the data to Excel
1. Open MS Word
2. Click on Open Data Source button (Check if View > Toolbars > Mail Merge is enabled)
3. Then browse for the Query file that was saved from Excel and click ‘Open’ (i.e., from C:Documents and SettingsUserApplication DataMicrosoftQueriesQuery from TallyODBC_9000.dqy)
4. This process will take a little time to proceed.
5. There will be a message that pops up as shown below.
6. Select No
7. After the process is complete, the page will still appear blank. Click on Tools > Letters and Mailings > Mail Merge
8. Click on the Previous option twice (to come to Step 1 of 6)
9. On clicking the option twice, the heading Select document type appears with the options as shown below. Choose Label and click Next : Starting document (Step 1 of 6)
10. Click on Label options and set the settings as required. Click OK
11. The document appears as shown below
12. Click on the Insert Merge fields button
13. Select the fields one by one and click Insert from the ‘ Insert Merge Field ’ window
14. Click on Close
15. After closing the ‘ Insert Merge field ’ window click on ‘Next: Select recipient’ (Step 2 of 6)
Then
click on ‘Next: Arrange your labels’ (Step 3 of 6)
16. Arrange the fields as required
17. Click the button ‘ Update all labels ’ to obtain the labels as shown below:
18. Click on ‘ Next: Preview your label’ (Step 4 of 6)
19. The customer details will appear on the labels as shown below:
20. Then click ‘Next : Complete the Merge ' (Step 5 of 6) and Save the document.
This would ensure your labels are made and merged to send to various recipients.
Tally.ERP 9 has an in-built SQL processor that processes SQL Select statements on collections. Collections which are exposed to ODBC are available as tables to external applications. By default, the methods at the first level of the collection are available for selection. Use the Calculator Area for processing SQL Queries.
SELECT [<Method Name/s> / <*>] FROM <Collection/Table> WHERE <Condition> ORDER BY <Method Name/s>
Note: SQL is not case sensitive. SELECT is the same as select.
SELECT $Name FROM Ledger
Here, $Name is the Method Name and Ledger is the Collection.
A Collection is where all the stored information is available. The collection Ledger will have all the information about all the ledgers in the company.
A method is where the particular information of a collection is stored. The method $Name is where the names of all the Ledgers are stored.
Note: Method names always start with a “$”.
The result of the above SQL Query is the list of all ledgers in the company and will be listed.
Note: In a SQL Query the WHERE clause and ORDER BY keyword are optional. The WHERE clause is used to filter the search and the ORDER BY keyword is used to view the results in the desired order.
1. To retrieve list of all the Collections which are exposed to ODBC
SELECT $Name FROM ODBCTables
2. To retrieve one method from a collection; say $Name method from Ledger collection
SELECT $Name FROM Ledger
3. To retrieve more than one method from a collection, separate the method names with a comma. For example, to retrieve $Name and $ClosingBalance of Ledger collection
SELECT $Name, $ClosingBalance FROM Ledger
4. To display all methods of a collection; for example, retrieve all the methods of Ledger collection. This SQL Query can be used to discover all the Methods available for a Collection.
SELECT * FROM Ledger
Note: “*” will retrieve all the methods of a collection.
The WHERE clause is used to filter queries based on conditions using functions, mathematical/logical expressions, and patterns/strings.
1. Retrieve the names of the ledgers with only debit closing balance using Functions
SELECT $Name FROM Ledger WHERE $$IsDr: $ClosingBalance
Note: To get Credit Closing Balance use the function “NOT $$IsDr”.
2. Retrieve the names of the ledgers whose closing balance is between 10000 and 11000
SELECT $Name FROM Ledger WHERE $ClosingBalance BETWEEN 10000 AND 11000
3. Retrieve the names of the ledgers which have the word “sales” in their name
SELECT $Name FROM ledger WHERE $name LIKE '%sales%'
Note: The "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.
With the WHERE clause, the following mathematical operators can also be used:
|
Operator |
Description |
|
= |
Equal to |
|
< > or != |
Not equal to |
|
> |
Greater than |
|
< |
Less than |
|
>= |
greater than or equal to |
|
<= |
Less than or equal to |
To sort the result of the query, the ORDER BY keyword is used. The ORDER BY keyword sorts the result of the query in ascending order by default. To sort the results in descending order the DESC keyword is used.
For example, to display the names and debit closing balances of all Ledger objects sorted in descending order of closing balances
SELECT $Name, $ClosingBalance FROM Ledger WHERE $$IsDr: $ClosingBalance ORDER BY $ClosingBalance DESC
'Data Extraction Using ODBC - FAQ' has been shared with
