HomeTallyPrimeManage Your DataExtract Data Using ODBC

 

Explore Categories

 

 PDF

Extract Data Using ODBC in TallyPrime

You may need to share data of ledgers, stock items, or any other master with you 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 your data – with your clients or superiors – using Excel, you can extract data from ledgers and stock items.  Using the ODBC feature, you can extract the data.

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

Enable ODBC in TallyPrime

To enable ODBC in TallyPrime, follow the steps given below.

  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.

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.

  1. Initiate document creation.
    1. Click Mailings > Start Mail Merge > Step by Step Mail Merge Wizard.
      MS Word Mail Merge Wizard
      The Mail Merge appears on the right-hand side.
    2. Select document type: Labels.
    3. Click Next: Starting document.
      MS Word - Mail Merge Wizard on the Right-hand Side
  2. Apply formatting options, as applicable.
    1. Click Next: Select recipients.
      Select Recipients in Mail Merge in MS Word for Formatting
      The Label Options window appears.
    2. Select Product number:
      1. In MS Word 7, select Business Card and click OK.
        Product Number Business Card - Mail Merge in MS Word 7
      2. In MS Word 10, select based on your preference, for instance, European Size, and click OK.
        Product Number European Size - MS Word 2003
  3. Select the source of data.
    1. Click Next: Arrange your labels.
      Arrange Your Labels - MS Word - Mail Merge Using ODBC in TallyPrime
      The Select Data Source window appears.
    2. Click New Source.
      Click New Source in the Select Data Source Window of the Mail Merge Wizard in MS Word
      The Data Connection Wizard window appears.
    3. Select ODBC DSN and click Next.
      Data Connect Wizard Mail - Merge in MS Word (ODBC in TallyPrime)
      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.Select TallyODBC with Appropriate Port Number in Mail Merge Data Source (ODBC in TallyPrime)
      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.
      Select Data Type - Master - MS Word's Mail Merge for Label Printing (ODBC in TallyPrime)
      The Data Connection Wizard window with File Name appears as shown below.
      Data Connection Window with File Name in MS Word's Mail Merge
    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.
      Test the Connect of Mail Merge in Word File with Tally ODBC
      A test connection success message appears as shown below.
      Test Connection Success Message
    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.
      Filter and Sort Data in Mail Merge - MS Word
    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.
      Insert Merge Field Mailings Tab - MS Word
    2. The MailingName field will appear in the first line of the first label as shown below.
      Mailing Name Copied for Label Printing with Mail Merge Using ODBC in TallyPrime
    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.
      More Labels Copied for Label Printing Using ODBC in TallyPrime
    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.
      Labels Printed Using Mail Merge Wizard - MS Word
  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.
      Preview After Label Printing Using ODBC in TallyPrime
    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.

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.
      Mail Merge in MS Word 2003 Using ODBC in TallyPrime
    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.
      Select Data Source - Mail Merge in MS Word 2003
      The Data Connection Wizard appears.
    4. Select the required data source as shown below and click Next.
      Select Data Source Mail Merge - MS Word 2003 - ODBC in TallyPrime
      The Connect to ODBC Data Source appears.
    5. Under ODBC data sources, select the appropriate ODBC data source as shown below and click Next.
      ODBC Data Source Selection Mail Merge - MS Word 2003 - ODBC in TallyPrime
  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.
      Select Master - ODBC Data Connection Wizard
    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.
      Enter the Initial Catalog to Use Mail Merge in MS Word 2003
  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.
      Mail Merge Recipients - Filter Data Using Dropdown (Mail Merge Using ODBC in TallyPrime)
      The list displaying the Filters appears.
    2. Select Advanced.
      Mail Merge Recipients - Applying Advanced Filters
      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.
      Data Sorted and Filtered - Mail Merge - MS Word 2003
    7. Click OK.
      A sample Mail Merge Recipients window with the filtered and sorted data appears as shown below.
      Mail Merge Recipients Window with Data Sorted and Filtered
    8. Click OK.
  7. Select options for label printing, as applicable.
    1. Click Insert Merge Fields.
      Insert Merge Fields - Mail Merge - MS Word 2003
      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.
      Insert Merge Field in Mail Merge - MS Word 2003
      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.
      Updated Labels - Mail Merge in MS Word 2003 Using ODBC in TallyPrime
  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.
      Preview - Label Printing with Mail Merge Using ODBC in TallyPrime
    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.

Post a Comment

Is this information useful?
YesNo
Helpful?