Configuring Connections: Delimited Flat Files

Versions: 8.0+

Configuring QuerySurge Connections: Delimited Flat Files

When you create a QuerySurge Connection, the Add Connection Wizard will guide you through the process. Different types of QuerySurge connections require different types of information.

QuerySurge supports flat file querying using a JDBC-compliant Flat File driver. Using this driver, you can use a standard SQL vocabulary to query your Flat Files. 

For a Delimited Flat File Connection, you will need the following information:

• The directory path where the Flat Files are located
• The file extension, if any
• The delimiter the file uses

 Note: The directory specified should only contain delimited flat files. The presence of other file types can cause unwanted metadata issues and behavior when using the Connection in the QueryWizard.

Optionally, the following information can be used to make queries run on your Connection more effective:

• Whether the file contains column headers or not
• Column header names, if the file does not contain them (they can be imposed)
• The number of columns in the file
• The data types of each column
• Formats of date, time or datetime columns

Launch the Add Connection Wizard

1. Log into QuerySurge as an Admin user.
2. To configure a Connection, select Configuration > Connection in the Administrative View tree (at the left).
3. Click on the Add button at the bottom left of the page to launch the Add Connection Wizard. Click Next.
   
   Note: Check the Advanced Mode checkbox for access to advanced features, such as Date and Time formatting options and the default Maximum String width for String column types.
   
   
4. Provide a name for your connection. Select Flat File as the Data Source. Click Next.
   
   Note: The J-Stels Flat File JDBC driver is bundled with QuerySurge, so you can install this driver from the QuerySurge Installer.
   
   
5. Once you have selected your Data Source, you can select your Connection Type. Note that the Delimited File Connection Type is selected by default – this is what you need for a delimited Flat File Connection. The Wizard tells you what information you are likely to need in order to create your Connection. Once you have collected the information, you are ready to click Next.
   
   
6. File Details. Enter the path to the directory that contains the flat files. This is just a directory path (either a mapped or UNC paths work); no file name should be included. Indicate the file extension in the File Extension field, if any.
   
   
   
   Delimiter Options. Specify the delimiter by either selecting one of the radio buttons, or, if your delimiter is not one of the choices, select the Other option and enter the delimiter character.
   
   Note: Multi-character delimiters are supported - using the Other option, simply type in the characters that the delimiter is composed of. For more details, see this article.
   
   Note: Using the Other option, you can define delimiters using the hex value of the character. Hex values are defined using C-style "0x" notation. For example, to set a delimiter with a value of 1 (SOH character), you would enter 0x0001. For more details, see this article.
   
   When you are done, click Next.
   
   
7. Header Options. If your flat file contains headers in the first row, leave Headers in File checked, and enter the Number of Columns in the file.
   
   If the file contains no headers, select either Generate Headers (and enter the Number of Columns in the file) or Provide Header Line (and enter a comma-separated list of Headers – one for each column in the file). If you select Generate Headers, QuerySurge will impose generated headers (COLUMN1, COLUMN2, …) on the file. If you select Provide Header Line, QuerySurge will impose the headers you provide on your file data. In either case, you can write SQL using these imposed column names.
   
   Data Type Options.  You may enter a comma-separated list of data types to be imposed on your flat file data. If you impose data types, you can use related comparisons in your SQL. For example, you can perform numeric comparisons (=, >, >=, <, <=) in your SQL queries if you type columns as numeric types. If you do not indicate any types, all data types default to VARCHAR(2000). (Use the Advanced Mode to modify this default.)
   
   When you have finished with these entries, click Next.
   
   
8. After you have reviewed your connection information, Save the Connection.
   
   
9. Congratulations! You’ve created a QuerySurge Connection. You’re ready to use QuerySurge with flat files.

Note: Make sure that you have deployed the driver for this Connection to all your QuerySurge Agents. See Adding a JDBC Driver to your QuerySurge Agent for instructions.

Note: The user login under which the QuerySurge Agent is running must have read access to the directory path in which your flat files are located. This is especially important to verify if the directory is a network share directory, however it may be important even for local directories. If you need to change the user login under which the QuerySurge Agent is running, see: Setting an Agent to Run Under a Specific Login for instructions.