Add to Favourites

Extract Data Using ODBC in TallyPrime

You may need to share data of ledgers, stock items, or any other master with your customers or internal stakeholders to keep the record of those masters. You can share the data in MS Excel or MS Word format by extracting from TallyPrime. All you need to do is to enable ODBC in TallyPrime and follow the steps in MS Excel or MS Word to share data using ODBC. While in MS Excel, you can extract data with the From Microsoft Query option, in MS Word, you can do so by label printing with Mail Merge Wizard.

Extract Data to MS Excel Using ODBC in TallyPrime

When you have to share data with your clients or superiors using Excel, you can extract data from ledgers and stock items using the ODBC feature. 

In this section

• Master Data using ODBC

• Extracting Stock Item Data to Microsoft Excel using ODBC

Master Data using ODBC

Using ODBC, you can extract ledger data to Microsoft Excel and generate the required reports.

Illustration

National Enterprises wants to extract information from the ledger master, as shown below:

To extract ledger data to Microsoft Excel

1. Open Microsoft Excel on your computer.
2. Click Data > Get External Data > From Other Sources > From Microsoft Query, as shown below:
   
3. Select TallyODBC64_9000*, as ODBC is enabled on the server 9000.
4. Click OK. The Query Wizard – Choose Columns screen appears as shown below:
   
5. Click Ledger and expand the list to select the required fields.
6. Click Next. The Query Wizard – Filter Data screen with the column names appears as shown below:
   
7. Click Next. The Query Wizard – Sort Order screen appears as shown below: 
   
8. Select Ascending or Descending for each drop down list under Sort by, as required.
9. Click Next. The Query Wizard – Finish screen appears as shown below:
   
10. Select the option Return Data to Microsoft Office Excel.
11. Click Finish. The Import Data screen appears as shown below:
    
12. Click OK. The Microsoft Excel sheet with the imported data appears as shown below:
    

Similarly, any data can be extracted from TallyPrime by using the Microsoft Excel Query Wizard.

Extracting Stock Item Data to Microsoft Excel using ODBC

Using ODBC, you can extract stock item data to Microsoft Excel and generate the required reports.

Illustration

National Enterprises wants to extract information from the stock item master:

To extract stock item data to Microsoft Excel

1. Open Microsoft Excel on your computer.
2. Click Data > Get External Data > From Other Sources > From Microsoft Query, as shown below:
   
   The Choose Data Source screen appears as shown below:
   

3. Select TallyODBC64_9000*, as ODBC is enabled on the server 9000.
4. Click OK. The Query Wizard – Choose Columns screen appears as shown below:
   
5. Click Stock Item and expand the list to select the required fields.
6. Click Next. The Query Wizard – Filter Data screen with the column names appears as shown below:
   
7. Click Next. The Query Wizard – Sort Order screen appears as shown below:  
   
8. Select Ascending or Descending for each drop down list under Sort by, as required.
9. Click Next. The Query Wizard – Finish screen appears as shown below:
   
10. Select Return Data to Microsoft Excel.
11. Click Finish. The Import Data screen appears as shown below:
    
12. Click OK. The Microsoft Excel sheet with the imported data appears as shown below:
    

Similarly, any data can be extracted from TallyPrime by using the Microsoft Excel Query Wizard.

Extract Data to MS Word Using ODBC by Label Printing with Mail Merge Wizard

You may need to share TallyPrime data in an MS Word document so that your customers or stakeholders can view. Based on the version of MS Word installed in your system, the process of extracting data to MS Word using ODBC varies. In both the cases, you will first need to enable ODBC in TallyPrime.

In this section

• Enable ODBC in TallyPrime

• Extract Data to MS Word 2007/2010 Using ODBC

• Extract Data to MS Word 2003 Using ODBC

Enable ODBC in TallyPrime

1. Start TallyPrime.
2. Press Alt+Z (Exchange) > Data Synchronisation and press Enter.
   The Data Synchronisation Configuration screen appears.
3. Under List of Configurations, select Client/Server configuration and press Enter.
   The Client/Server configuration screen appears.
4. Select options under the labels as follows:
   1. TallyPrime acts as: None.
   2. Enable ODBC: Yes.
   3. Select the Port as applicable to your license server.
      

Seamlessly integrate your TallyPrime data with other applications and databases by utilising this feature and enhance your workflow efficiency. 

Extract Data to MS Word 2007/2010 Using ODBC

After enabling ODBC in TallyPrime, follow the steps given below in MS Word 2007 or 2010 to extract the required data. 

1. Initiate document creation.
   1. Click Mailings > Start Mail Merge > Step by Step Mail Merge Wizard.
      
      The Mail Merge appears on the right-hand side.
   2. Select document type: Labels.
   3. Click Next: Starting document.
      
2. Apply formatting options, as applicable.
   1. Click Next: Select recipients.
      
      The Label Options window appears.
   2. Select Product number:
      1. In MS Word 7, select Business Card and click OK.
         
      2. In MS Word 10, select based on your preference, for instance, European Size, and click OK.
         
3. Select the source of data.
   1. Click Next: Arrange your labels.
      
      The Select Data Source window appears.
   2. Click New Source.
      
      The Data Connection Wizard window appears.
   3. Select ODBC DSN and click Next.
      
      The Data Connection Wizard window starts displaying the list of ODBC data sources as shown below.
   4. Select the Tally ODBC data source with the appropriate port number.
      

      Note: If multiple data sources of Tally are listed, then you can check the appropriate port number in TallyPrime. 
      
      In TallyPrime, press F1 (Help) > Settings > License > Manage License. 
      
      The License Information screen appears, and you can identify the port number mentioned under the label Tally Gateway Server.

4. Select data that needs to be extracted.
   1. Select the master, for instance, Ledger, StockItem, CostCategory, as applicable and click Next.
      
      The Data Connection Wizard window with File Name appears as shown below.
      
   2. Click Finish.
      The Data Link Properties window appears.
5. Test the connection.
   1. Click Connection.
   2. Specify the source of data: Use data source name.
   3. From the list, select TallyODBC with the appropriate port number.
   4. Click Test Connection.
      
      A test connection success message appears as shown below.
      
   5. Click OK on the Test connection succeeded message sub-screen.
   6. Click OK to save the Data Link Properties
      The Mail Merge Recipients Window appears.
6. Filter and sort the data, as required.
   1. Click Sort.
      The Filter and Sort window appears.
   2. Click Filter Records.
   3. Field: $Parent.
   4. Comparison: Equal to.
   5. Compare to: type Sundry Debtors.
   6. In the next row, select the Or option to add more filters.
   7. Select appropriate options under Field and Comparison and under Compare to: type Sundry Creditors.
      
   8. Click OK and the filters will be applied.
7. Select options for label printing, as applicable.
   1. In the Mailings tab of the MS Word file, click Insert Merge Field and select MailingName from the dropdown list.
      
   2. The MailingName field will appear in the first line of the first label as shown below.
      
   3. Press Enter to bring the cursor to the next line.
   4. Select other fields such as M_Address1, M_Address2, M_Address3, M_Address4, PinCode, and StateName, as applicable.
      
   5. Click Next: Arrange your labels.
   6. In the Mail Merge Wizard, under Replicate labels, click Update all labels.
      The updated screen after label printing appears as shown below.
      
8. Save the document after label printing with mail merger.
   1. Click Next: Preview your labels.
      The preview of the labels appears as shown below.
      
   2. Click Next: Complete the Merge
   3. Click Previous: Preview your labels, only if you want to preview the labels again.
   4. Press Ctrl+S to save the document.

Follow the above-mentioned steps to extract the required data with ease and use the data effectively for various purposes such as mailing lists, address labels, or business correspondence.

Extract Data to MS Word 2003 Using ODBC

After enabling ODBC in TallyPrime, follow the steps given below in MS Word 2003.

1. Initiate document creation.
   1. Click Tools > Letters and Mailings > Mail Merge.
      The Mail Merge task pane appears as shown below.
      
   2. Select document type: Labels.
   3. Click Next: Starting document.
2. Apply formatting options, as applicable.
   1. Under Change document layout, click Label options.
      The Label Options screen appears.
   2. Under Product number, select the product number with the text Label, as required and click OK.
      Subsequently, Use the current document gets selected.
3. Select the source of data.
   1. Click Next: Select Recipients.
      Use an existing list is selected by default. Retain the selection.
   2. Click Browse.
      The Select Data Source window appears.
   3. Click New Source.
      
      The Data Connection Wizard appears.
   4. Select the required data source as shown below and click Next.
      
      The Connect to ODBC Data Source appears.
   5. Under ODBC data sources, select the appropriate ODBC data source as shown below and click Next.
      
4. Select data that needs to be extracted.
   1. Select the required master – Ledger, StockGroup, StockCategory, or any other, as per your requirement and click Next.
      
   2. Click Finish.
      The Select Data Source window appears.
   3. Select the required data source from the list of data sources displayed.
   4. Click the Connection tab.
   5. Click the Use data source name checkbox.
   6. Click the dropdown list and select the required data source.
      The name of the catalog is displayed in Enter the initial catalog to use as shown below.
      
5. Test the connection.
   1. Click Test Connection.
      A test connection success message appears.
   2. Click OK on the success message.
   3. Click OK in Data Link Connection.
      The Mail Merge Recipients appears.
6. Filter and sort data, as required.
   1. Click the dropdown list box.
      
      The list displaying the Filters appears.
   2. Select Advanced.
      
      The Filter and Sort screen appears.
   3. Click the Filter Records tab.
   4. Click the Field drop-down list and select $Parent.
   5. Click Comparison and select Equal To.
   6. In Compare To, enter Sundry Creditors or Sundry Debtors, as required.
      
   7. Click OK.
      A sample Mail Merge Recipients window with the filtered and sorted data appears as shown below.
      
   8. Click OK.
7. Select options for label printing, as applicable.
   1. Click Insert Merge Fields.
      
      The Insert Merge Fields window with the Database Fields checked by default appears.
   2. From the list of Fields displayed, select the required field.
   3. Select other fields from the list, as per your requirement.
      
      The address fields will start appearing as labels.
   4. Arrange the address fields, as per your requirement.
   5. Click Next: Arrange your labels.
   6. Under the section Replicate labels, click Update all labels.
      A sample image with the updated labels will appear as shown below.
      
8. Save the document after label printing with mail merger.
   1. Click Preview your labels.
      The preview of the labels with The Company Name along with the Address in each slot appears.
      
   2. Click >> to move to the next set of records or click << to move to the previous set of records.
   3. Click Next: Complete the Merge.
   4. Click Print.
      The Merge to Printer dialog box appears.

By default, you can print all the available records or provide the record number if you want to print the label of a selected party in the Current Record field. You may also print a subset of the available records by providing the starting record number in the From field and the ending record number in the To field.