Add to Favourites

Extract Data into MS Excel Using ODBC in TallyPrime

To share master data like ledgers or stock items from TallyPrime, you can extract the data to MS Excel using ODBC. By enabling ODBC in TallyPrime and using the From Microsoft Query option in Excel, you can easily extract and share structured data with your stakeholders.

Enable ODBC in TallyPrime

1. Press Alt+Z (Exchange) > Configure.
2. Under Data Synchronisation, select Client/Server configuration and press Enter.
   The Client/Server configuration screen appears.
3. Enable ODBC: Yes.
   Ensure that the Port is as applicable to your license server.
   

You can now use the ODBC feature to extract data seamlessly. 

Extract Ledger Data to Excel using ODBC

For illustration purposes, let’s see how to extract ledger or stock item data from TallyPrime using ODBC to generate the required reports.

Microsoft Excel Version 2410 has been used to record the below procedure.

1. Ensure that you have enabled ODBC in TallyPrime. 
2. Open Microsoft Excel.
3. Click Data > Get Data > From Other Sources > From ODBC.
   
4. Select TallyODBC64_9000* from the list of Data Source name, and click OK.
5. Click the company name loaded in TallyPrime, and type or select the data you want to extract from the list.
   You can select Ledger or Stock item, as needed. You can also select multiple items by clicking the Select multiple items checkbox.
   
6. Click Ledger or Stock Item > Transform Data.
   The Power Query Editor screen with the column names appears:
   
7. Press Ctrl and select the rows from which you want to extract the data.
8. Right-click on the heading of any of the selected rows and click Remove other columns.
   All the unrequired columns which were not selected will be removed.
9. Click Close & Load > Close & Load To and select Existing Worksheet on the Import Data form.
   The Microsoft Excel sheet with the imported data appears as shown below:
   

By using ODBC for data extraction, you can seamlessly integrate the data between TallyPrime and Microsoft Excel. This simplifies data extraction and also ensures real-time synchronization. Any updates made in TallyPrime are instantly reflected in Excel. With this dynamic capability, you can design customized reports and keep them up-to-date with the latest data from Tally.

FAQs

• How do I get online data from TallyPrime to MS Excel?
  

  It is possible to update Excel with the changes made to TallyPrime in real-time. This is possible only when ODBC connectivity is available.

  The online update can be done automatically by setting the duration of auto-refresh rate in Excel by following the steps shown:

  1. Click Data.
  2. Click Refresh All.
  3. Select Connection Properties.
  4. By default, Refresh every is checked.
  5. Enter the duration in minutes in Refresh every field. TallyPrime will automatically send the data to excel at the specified duration.
     
  6. Click OK.
• How to export TallyPrime data to Microsoft Excel, when the bitness of the two applications are not the same?
  

  With Open Database Connectivity (ODBC), you can exchange data between TallyPrime and any other application.

  The bitness of the following three important elements determines Export of data via ODBC.

  1. TallyPrime
  2. Operating System
  3. Microsoft Office

  For seamless functioning of this ODBC feature, it is essential that these three elements are of the same bitness.

  Consider the scenario where the Operating System is 64-bit, TallyPrime is 64-bit and MS Office is 32-bit. Then, by default, Microsoft Excel will not be able to communicate to TallyPrime as it is of a different bitness.

Errors & Resolutions

1. Client without ODBC
   This error might be displayed even after configuring the port number for ODBC, in the About page of TallyPrime.
   

   Cause
   The port number specified may already be in use.

   Solution

   1. Press Alt+F1 (Help) > About > select Client/Server and ODBC Services and press Enter.
   2. Enter a different Port number.
   3. Press Enter to restart TallyPrime.
   
   
2. Data Source Name not found
   

   For computer in which the operating system installed is Windows XP or a higher version:

   1. Right-click the TallyPrime icon, and select Run as administrator.
      
   2. The Select Company screen appears. Select the required company from which you want to extract the data. The About page displays the Client/Server with ODBC Services. If the port number is not configured, you can configure it by following the steps given below:
      • Press Alt+F1 (Help) > About > select Client/Server and ODBC Services and press Enter.
      • Enter the Port number. By default, the port is set to 9000, as shown below:
        
        

        In case the port number mentioned is occupied by another application, you need to provide another port number.