Importing and Exporting QueryPairs
QuerySurge provides users the ability to import and export one or more QueryPairs using an appropriately-formatted Excel spreadsheet. This allows you to export a set of QueryPairs, perform bulk modifications on them, and import them back into that same instance of QuerySurge. Additionally, you can use this feature to transfer QueryPairs between different instances of QuerySurge.
Note: The QuerySurge Import/Export feature is licensed separately.
Full v. Relative Paths
When exporting QueryPairs, you will be asked to choose one of two options:
- Full Paths
- Relative Paths
If you elect to export your QueryPairs with their full paths, each QueryPair (which will be represented as a row in an Excel sheet) will include its full path. The full path will start at the root-level node (i.e., the QueryPairs folder, without a leading forward-slash) and end with the QueryPair name (e.g. a Full path is: QueryPairs/mytopfolder/somesubfolder/myQueryPair, while a Relative path is: somesubfolder/myQueryPair)
Alternatively, if you choose to export your QueryPairs with their relative paths, the path structure will start at the node from which you exported your QueryPairs. For example, if you export from a folder directly under the root-level QueryPairs folder, the relative path will not include the QueryPairs folder.
The path structure is important when importing your QueryPairs into QuerySurge (either the same or a different instance). In situations where QuerySurge is creating new QueryPairs, the folder structure specified in the path will be created as needed. Please be aware of your path selection when exporting, or your entire folder hierarchy may be inadvertently recreated when importing into a subfolder.
Supported Excel Versions
QuerySurge currently supports QueryPair imports and exports for Excel 2007 and later. When importing, QuerySurge supports both .xls and .xlsx file extensions. When exporting, all files will be generated with a .xlsx file extension.
QueryPairs and Snippets Sheets
Your Excel workbook must contain two sheets. The first sheet, named QueryPair Export, contains the data pertaining to your QueryPairs. The second sheet can be named Query Snippets or any generic name like Sheet2. It contains the data pertaining to your Reusable Query Snippets referenced by your QueryPairs or the sheet may be blank. Omitting this second sheet will cause on error on import.
Format and Layout
When importing QueryPairs, you must use our format and column names. When modifying your QueryPairs from an exported Excel sheet, this is simple. However, if you create your own Excel document from scratch, please be sure to adhere to our layout.
The QueryPair Export sheet contains the following column names:
| Column Name | Content |
|---|---|
| QueryPair Id | The ID of the QueryPair, if it exists. If QuerySurge locates the QueryPair by this ID, it will update it; otherwise, it will search by the path and name attributes. Note: If you use existing IDs in your file, the QueryPairs identified by these IDs will be overwritten with the file contents on file import.Note: If you are importing QueryPairs into a separate QuerySurge instance, you will need to manually modify these values to match the QueryPair IDs of the new instance. |
| QueryPair Name | The name of the QueryPair. This value is required. |
| Path | The path to the QueryPair, including the QueryPair name. This value is required and the name portion of the Path column must match the value specified in the QueryPair Name column. |
| Source Query | The Source query text associated with this QueryPair. Any Resuable Query Snippets should be written in encoded form (i.e., ${snippet_name};) - the underlying data is entered on the Query Snippets sheet. |
| Source Connection Id | The ID of the Source Connection for this QueryPair, if it exists.Note: If you are importing QueryPairs into a separate QuerySurge instance, you will need to manually modify these values to match the Source Connection IDs of the new instance. |
| Source Connection Name | The name of the Source Connection, if it exists. If QuerySurge cannot find your Source Connection by the specified ID, it will try to locate it by this value. |
| Target Query | The Target query text associated with this QueryPair. Any Resuable Query Snippets should be written in encoded form (i.e., ${snippet_name};) - the underlying data is entered on the Query Snippets sheet. |
| Target Connection Id | The ID of the Target Connection for this QueryPair, if it exists.Note: If you are importing QueryPairs into a separate QuerySurge instance, you will need to manually modify these values to match the Target Connection IDs of the new instance. |
| Target Connection Name | The name of the Target Connection, if it exists. If QuerySurge cannot find your Target Connection by the specified ID, it will try to locate it by this value. |
| QueryPair Descr | The Description attached to this QueryPair, which appears on the Properties tab. |
| QueryPair Mapping | The Mapping attached to this QueryPair, which appears on the Properties tab. |
| Row Count Options | The Outcome Rule for Source/Target Row Count mismatches. Valid values are:
|
| Key Column Defn | A comma-separated list of Key Columns by name, coming from either the Source or the Target. |
| Key Column S/T | The specified Key Column origin (None, Source, or Target). Valid values are:
|
| Data Type Check | The specified Data Type Checking option (on or off). Valid values are:
|
| Duplicate Check | The specified Duplicate Checking option (on or off). Valid values are:
(Note: If you are using a key column, this option must be set to F.) |
| Thresholds S/T | The specified Threshold column origin (None, Source, or Target). Valid values are:
|
| Thresholds Pct/Abs | Specifies whether the Threshold is Percentage-based or Absolute. Valid values are:
|
| Thresholds Columns | A comma-separated list of column names to which the Threshold values apply. |
| Thresholds | A comma-separated list of numeric Threshold values, in the same order as the column name list. |
The Query Snippets sheet contains the following column names:
| Column Name | Content |
|---|---|
| Snippet Id | The ID of the Snippet. If QuerySurge locates the Snippet by this ID, it will update it; otherwise, it will search by the name attribute.Note: If you are importing Snippets into a separate QuerySurge instance, you will need to manually modify these values to match the Snippet IDs of the new instance. |
| Snippet Name | The name of the Snippet. |
| Snippet | The underlying data stored in the Snippet. |
| Notes | The Description attached to this Snippet, which is stored in the Properties tab. |
Requirements
There are a few requirements you must take into consideration when importing Excel files into QuerySurge. First and foremost, the QueryPair name is a mandatory field. With only the name, we can create a QueryPair (all other fields will either be blank or set to a predefined default value). Without the name, we cannot proceed. Additionally, the value in the QueryPair name column must match the name that terminates the value in the Path column.
Secondly, there are a few columns that can accept a range of values. For these columns, your entered value must be within this range.
- Row Count Options: F (Fail), WS (Warning Source), WT (Warning Target), IS (Ignore Source), or IT(Ignore Target)
- Key Column S/T: N (None), S (Source), or T (Target)
- Data Type Check: T (On) or F (Off)
- Duplicate Check: T (On) or F (Off)
- Thresholds S/T: N (None), S (Source), or T (Target)
- Thresholds Pct/Abs: P (Percentage-based) or A (Absolute)
Restrictions
QuerySurge does not support the use of formulas in imported Excel files. And, of course, you must use QuerySurge's Excel format.
How do I Access this Feature?
You can access the QueryPair Import and Export functionality from two locations. First, you can access it from the Design Library by right-clicking a folder or QueryPair (either in the Design Library Tree or the Library Explorer). Second, you can access it from the Administration section by selecting QueryPair Import/Export from the Administration Tree.
Exporting to an Excel File
When exporting, you must choose whether to export with either Full or Relative Paths. Full paths with begin with the root-level QueryPairs folder and terminate with the QueryPair name. Relative paths will begin at the node from which you're exporting and terminate with the QueryPair name.
What is the Pre-Scan?
You are encouraged to Pre-Scan your Excel file before importing it. The Pre-Scan affords QuerySurge the opportunity to preview your file and highlight possible pitfalls and erroneous input. After pre-scanning, you will receive an output window with informational messages in black and error messages in red. You will need to fix the errors before importing.
If you import a spreadsheet that contains errors, QuerySurge will import all QueryPairs up to the point when it encounters an error. To avoid duplicate QueryPairs, you will need to delete the rows from your spreadsheet that were successfully imported, fix the errors, and then re-import the file.
Importing to a New QuerySurge Instance
When importing QueryPairs from one instance to another, the QueryPair IDs, Source Connection IDs, Target Connection IDs, and Snippet IDs may not match. You will need to modify the QueryPair and Snippet IDs to match the new instance or delete them entirely from the Excel sheet to create new QueryPairs and Snippets. You will also need to manually create the Source and Target Connections in the new instance, and update the Source and Target Connection IDs accordingly.
Importing Back to the Same QuerySurge Instance
When importing back to the same instance that you have exported from, your QueryPair IDs, Source Connection IDs, Target Connection IDs, and Snippet IDs should all exist and match. A point of note in same-instance importing and exporting is the paths you associate with the QueryPairs. The folder structure in the path will be created if necessary - make sure your paths are in line with your import destination so that your full folder structure is not unnecessarily recreated.
Note: When you import from the Design Library Tree, your QueryPairs will be imported under the tree node that you select for import unless you have Full paths in your Excel file. Remember, a Full path is any path the start with QueryPairs (no leading forward-slash).
Processing Imported Files
When processing an imported file, QuerySurge needs to know whether to update or create QueryPairs; if it needs to update a QueryPair, it needs to know how to go about locating that QueryPair. For this reason, QuerySurge uses the information in the Excel sheets to determine how to proceed.
- QueryPair IDs take precedence over Names and Paths. If QuerySurge receives a valid ID and is able to locate the QueryPair from that ID, it will update that QueryPair with the contents of the Excel sheet.
- When there is no QueryPair ID (or there is an invalid one) in the Excel sheet, QuerySurge will attempt to use the Path and QueryPair Name. If QuerySurge is able to locate the QueryPair with this information, it will update it with the contents of the Excel sheet.
- If QuerySurge cannot locate the QueryPair using the ID or the Path and QueryPair Name, it will create a new QueryPair. The location of this new QueryPair will be determined based on the parent folder selected during import, as well as the Path in the Excel sheet (i.e., parent_folder/path/in/excel/sheet/querypair_name). Note: If you do not modify existing IDs and QueryPair names, and you import your Excel file, QueryPairs identified by the IDs in the file will be overwritten.
Connections
The Import/Export functionality is designed specifically for QueryPairs. This includes the ID, Name, Path, Source and Target Queries, Properties, and Source and Target Connection IDs and Names. QuerySurge will attempt to match existing Source and Target Connection IDs and Names, but will not actually create or update these Connections.
Minimum Requirements
The minimum requirements to create a QueryPair using the import feature are a QueryPair Name and a Path. All other values will either be blank or set to a predefined default value. However, QuerySurge cannot proceed without a name and path.
Sample Import File for Uploading New QueryPairs
You can download a sample upload file from the Resources section below. The purpose of this file is to show how to import new QueryPairs from an Excel sheet. The sample file contains some sample QueryPair data. Note that ID values in the file may or may not exist on your QuerySurge instance, so it is advisable to modify the file carefully to avoid mistakes before you use it.
Resources