Custom Encodings for Flat File Connections – Customer Support

Changing the Encoding for Flat File Connections

By default, QuerySurge uses the default character encoding from your Operating System when connecting to and reading from your files. However, you may want to use non-default-encoded Flat Files with QuerySurge. In order to do this, you'll need to explicitly modify the Connection encoding for your Flat File. This is done by specifying the encoding that you want to use when you set up your files. You can do this on either the QuerySurge Connection level or on the QuerySurge Agent level. If you set up a file encoding on the Connection level, then the encoding applies to any file accessed using that Connection regardless of the Agent it runs on. If you set up a file encoding on the Agent level, then it applies to any file that the Agent queries, regardless of the flat file Connection running. These options are mutually exclusive - choose which one fits your needs.

A Note About Encoding Names

The file encodings available for Flat File Connections use the encodings supported by Java or OpenJDK. You can see encoding listings, as follows:

QuerySurge 6.4 -10.0 (Java 8)
QuerySurge 10.1+ (OpenJDK 11)

A Connection-based Approach

To customize the encoding for a flat file Connection, you'll need to use QuerySurge's Connection Extensibility feature. This approach allows you to take advantage of the power of the underlying connection URL to explicitly set the character encoding, which will modify the file processing regardless of which Agent the file is queried by.

When you set up your Connection with Extensibility, the first piece of information you need is the Driver Class for the flat file driver. For the flat file driver, use jstels.jdbc.csv.CsvDriver2 for the Driver Class.

The next step is to provide a JDBC URL. A typical JDBC connection URL for a flat file connection looks like this: jdbc:jstels:csv:C:\path\to\files...

To specify a non-default encoding, simply append the following string to the end of the URL: &charset=<your_encoding_name> so that the URL looks like:

jdbc:jstels:csv:C:\path\to\files...&charset=<your_encoding_name>

To implement, you'll need the full Flat File URL. A sample full default URL is:

jdbc:jstels:csv:C:\path\to\files?dbInMemory=false&commentLine=--&separator=,&suppressHeaders=false&fileExtension=.csv&colCount=2&schema=@schemaPath@&ColWidth=500&dateFormat=yyyy-MM-dd HH:mm:ss.SSS | yyyy-MM-dd HH:mm:ss | yyyy-MM-dd | HH:mm:ss.SSS | HH:mm:ss&columnTypes=Integer,String

Note that the URL specifies important Connection details. The file for which this URL works has a column count of 2 columns (colCount=2), and data types for the two columns are "Integer,String" types (columnTypes=Integer,String). In addition, the file is comma delimited (separator=,) with a standard ".csv" file extension (fileExtension=.csv).

In order to specify the file encoding on this URL, we'll make an addition to the URL as described above. This example uses the UTF-16BE encoding (charset=UTF-16BE):

Note: When setting up a flat file Connection with the Connection Extensibility feature, it is best to provide a custom schema.xml file for the Connection. If you decide not to provide a custom schema, a default schema will be used, which may or may not be appropriate for your flat file. Details on how to create a custom schema.xml are available here.

The full Connection details can be seen below:

An Agent-based Approach

If you'd like to manage your file encodings on a per-Agent basis, you can apply the encoding at the Agent-level. This is done by setting a connection property for the Flat File driver in the Agent configuration.

To implement this, navigate to the /<QuerySurge Install Dir>/QuerySurge/agent/config/ directory and open the agentconfig.xml file. Scroll down to the <connectionProps> tag. Here, insert the following <driverProp> subtag:

Using the sample encoding from above, this property would be:

You'll need to save the file and restart your Agent(s) before these changes will take effect.

A Worked Example (Connection-based Approach)

Suppose we have two files, each with the same data but a different encoding. The files contain column headers, and have 2 columns, the first an Integer type, and the second a String type. One file uses a UTF-8 encoding (see the Resources section below for file: utf8_char_data.csv, while the other uses UTF-16-BE encoding (see the Resources section below for file: utf16_BE_char_data.csv). Both files contain Unicode characters. Sample file content looks like the following:

"Id","utf8_addr"
100,"Россия, 105066, г.Москва ул. Старая Басманная д.16 стр. 1а"
200,"경기 용인군 기흥구 삼성2로 95"
300,"中国上海诸光路9号3楼,5室张小明先生"
400,"123 Main St., Anytown, USA"
500,症例が終了しました。

For the UTF-8 encoded file, we set up the file in the QuerySurge Connection Wizard using the standard Flat File option ("Flat File [Driver Bundled]"), specifying "Headers in File", and the column types. We create a single QueryPair for both files using "SELECT * " queries for both the Source and Target files.

Initially, we run both files on this Connection - even though one of them is not a UTF-8 encoded file. The UTF-8 file query runs normally, as expected. However, the query on the UTF-16BE encoded file causes the run to error, and gives the following message:

The char '0x0' after 'SQL_EXCEPTION (Error: 0; SQL STATE: null) [StelsCSV JDBC driver] Can\'t load the file \'utf16_BE_char_data.csv\' to H2 database...

This error makes sense, because the file is being read with an incorrect encoding, so the processing isn't handled properly. If we open this file up in an ASCII editor, we get a visual sense of why the file can't be processed with the wrong encoding - the byte-character relationship is wrong:

" I d " , " u t f 8 _ a d d r "
1 0 0 , " >AA8O , 1 0 5 0 6 6 , 3 .>A:20 C; . !B0@0O 0A<0==0O 4 . 1 6 AB@ . 10 "
2 0 0 , "¬½®0 Æ©Çxp ®0×el À¼Á1 2¸\ 9 5 "
3 0 0 , "N-VýN
mw‹øQIï 9S÷ 3i| , 5[¤_ \fQHu "
4 0 0 , " 1 2 3 M a i n S t . , A n y t o w n , U S A "
5 0 0 ,uÇO‹0L}BN†0W0~0W0_0

To set up the UTF-16BE file correctly in QuerySurge, we use the Connection Extensibility option (which appears in the Data Source dropdown as: "* All Other JDBC Connections (Connection Extensibility)") in the Connection Wizard drop-down. For the Driver Class of this Connection, use:

jstels.jdbc.csv.CsvDriver2

For the URL, we use the sample URL from above, specifying the non-default UTF-16BE charset:

Note: When you run this example, you'll need to modify the "C:\path\to\files" part of the template URL above to match your local file path.

Once we have created this Connection in the Connection Wizard, we can assign it to the query for the UTF-16BE encoded file.

With this set up, the UTF-16BE encoded file is processed properly, and the QueryPair runs normally. The sample files used in this example can be downloaded from the Resources section below.

Resources

utf8_char_data.csv
292 Bytes Download
utf16_BE_char_data.csv
370 Bytes Download

Articles in this section

A Note About Encoding Names

A Connection-based Approach

An Agent-based Approach

A Worked Example (Connection-based Approach)

Related articles