Chat now with support
Chat with Support

Toad Data Point 4.2 - User Guide

Compare Differences in Data

 

You can compare result sets from the current editor or a different editor in the Database Explorer's Data tab or the editor's Result Sets without having to save one of the sets to a file.

Notes:

  • If you want to synchronize data, use Data Compare instead of the Data Diff Viewer.  
  • You cannot modify files in the Diff Viewer at this time.

To compare differences in data

  1. Right-click data in the Editor's result set or Database Explorer's Data tab and select Compare To | Set_number.

    You can also save a result set and later use it as a historical comparison.

  2. Review the differences.

    Tips:

    • To switch the contents of the left pane to the right pane, and the contents of the right pane to the left, click .
    • You can also open files to compare by clicking beside the drop-down list at the top of each pane.
    •  You can show or hide tables that contain equal records, different records, etc., at any time using the toolbar.

Export Data with One Click Export

Use One Click Export to quickly export data to an Excel report. You can also export data and include a linked query in the file. A linked query lets you create a "live" Excel report that can be refreshed dynamically. Because a linked query contains the underlying SQL for a report, each time you refresh the report the query executes and retrieves the latest data. The dynamic nature of linked query reports makes them ideal for distribution to users who need to see changes to data on demand without waiting for new static reports to be distributed.

Click here to view a video of this feature.

Notes:

  • Before you can export to Excel, the Primary Interop Assemblies (PIAs) for your version of Excel must be installed. Typically, the PIAs are installed automatically when you install Microsoft Office.

  • Use the Export wizard if you need to specify column delimiters and advanced field options when exporting data.

To export data in one click

  1. Select the range of data to export or click any row in a data grid to export all data.

  2. Right-click the selected range or data grid and select Quick Export.
  3. Select an export option. Review the following for additional information:

    Icon

    Option

    Description

    Excel File

    Export data to an Excel file. The file is automatically saved under a default name in the default export folder.

    CSV File

    Export data to a CSV file (comma separated value file). The file is automatically saved under a default name in the default export folder.

    HTML File

    Export data to an HTML file. The file is automatically saved under a default name in the default export folder.

    Excel Instance

    Export data to an Excel instance. Data is copied to a clipboard, Excel is launched, and a worksheet is populated with data. You can save the worksheet under a name and folder you specify or save it in the default export folder.

    Excel Instance at active cell

    Export data to an active cell in Excel. First open Excel and select a cell. Then select the Excel Instance at active cell option.  

    Excel Pivot Table

    Export data to an Excel pivot grid report. See the Excel documentation for information on creating a Pivot Table report with external data.

    Excel Pivot Table at active cell

    Export data to an active cell for an Excel Pivot Table report. First open Excel and select a cell. Then select the Excel Pivot Table at active cell option.  

    Excel Linked Query

    Export the underlying query for data to Excel.

    Excel Linked Query at active cell

    Export the underlying query for data to an active cell in Excel. First open Excel and select a cell. Then select the Excel Linked Query at active cell option.  

    Excel Linked Query with pivot table

    Export the underlying query for data to an Excel Pivot Table report. See the Excel documentation for information on creating a pivot grid report with external data.

    Excel Linked Query at active cell with pivot table

    Export the underlying query for data to an active cell for an Excel Pivot Table report. First open Excel and select a cell. Then select the Excel Linked Query at active cell with pivot table option.  

    Excel Column Chart Export data to an Excel chart instance. Data is copied to the clipboard, Excel is launched, and a chart is created with the data. You can then modify the chart's design and save the Excel file.

    Tip: To refresh data in a linked query report, click in Excel's External Data toolbar. This executes the underlying query for the report. See Share Excel Linked Queries for more information about creating and using linked queries.

Note: If the window containing data does not have the focus when you select a One Click Export option, the following message displays: "Export did not find any results sets to process."

Tips:

  • To specify a default export folder, go to Tools | Options | Environment | Export.
  • To always open a Save File dialog when exporting using One Click Export, go to Tools | Options | Environment | Export and select the option. This allows you to always customize the file name.

 

Share Excel Linked Queries

You can use linked queries to create "live" Excel reports that can be refreshed dynamically. Because a linked query contains the underlying SQL for a report, each time you refresh the report the query is executed and the latest data is retrieved. The dynamic nature of linked query reports makes them ideal for distribution to users who need to see changes to data without waiting for scheduled updates or for new reports to be distributed.  

Notes:  

  • Any user who opens an Excel linked query must have access to a user name and password for the queried database to refresh the data. If a user does not have access to the database, they can only see the original data in the spreadsheet.

  • If the Excel linked query was created from an ODBC connection that uses a Data Source Name (DSN), the DSN must also exist on the computer where the Excel file is opened to refresh the data.

Scenario

You need to create an Excel linked query so that the Regional Sales Manager, can track whether the Sales Representatives in his region meet their monthly and quarterly goals. By providing a linked query, you only have to create the query once, and then the Regional Sales Manager can refresh the data at any time to view the latest sales information.

To create an Excel linked query

  1. Connect to the database.
  2. Create a query in the Query Builder or Editor.
  3. Execute the query to display data results.
  4. Select the result set and click one of the following options from the toolbar:

  5. Save the Excel spreadsheet.
  6. Email or upload the spreadsheet to a shared network folder to share it with colleagues.

To refresh data in an Excel linked query

  1. Open the spreadsheet with the linked query in Excel. The original data from the query displays.
  2. In Excel, select Data | Refresh to update the original data with the current data.
  3. If prompted, enter a user name and password to connect to the database and refresh the data.

    or

    If the following error displays: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified", you must specify the DSN used by the query on your computer. Complete the following steps to resolve this issue:

Report

Related Documents