Learn how to create QuerySurge Connections to Fixed Width Flat Files
Configuring QuerySurge Connections: Fixed Width 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 Fixed Width Flat File Connection, you will need the following information:
- The directory path where the Flat Files are located
- The file extension, if any
- The column widths in the file
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 data types of each column
- Formats of date, time or datetime columns
Launch the Add Connection Wizard
- Log into QuerySurge as an Admin user.
- To configure a Connection, select Configuration > Connection in the Administrative View tree (at the left).
- 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.
- 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.
- Once you have selected your Data Source, you can select your Connection Type. Click on the Fixed Width File Connection Type. The Wizard will tell 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.
-
File Details. Enter the path to the directory that contains the flat files. This is just a directory path (either mapped or UNC paths work); no file name should be included. Indicate the file extension, if any. Click Next.
Required fields for your Connection Type are marked by an *.
-
Header Options. If your flat file contains headers in the first row, leave Headers in File checked.
If the file contains no headers, select either Generate Headers 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.)
Column Width. Specify the fixed column widths, using a comma-delimited list of ranges. In the example, the ranges are: 1-16,17-24,25-27,35-42,43-50,51-58, indicating that positions 1-16 constitute the first column; positions 17-24 make up the second column; positions 25-27 form the third, etc., for a total of 6 columns.
Note: All column widths must be specified as a range. So, if a column is composed of a single position, enter it with the same number on both sides of the range. For example, if there were a seventh column in the example above, consisting of a single position, it would be entered as 59-59, so the whole range would be: 1-16,17-24,25-27,35-42,43-50,51-58,59-59
- When you have finished with these entries, click Next.
- After you have reviewed your connection information, Save the Connection.
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.