Flat Files and Non-Default Locales

Versions: 8.0+

Data formatted according to international locales is becoming increasingly common in data verification and testing. By default, QuerySurge handles all parsing of columns within the default locale of the Operating System on which it is deployed. This may pose a problem for users handling flat files with data formatted for non-default locales.

Areas dependent on locale include (but are not limited to): number formatting, date-time abbreviations and formatting, and currency formatting. This article illustrates handling non-default locales using the example of a flat file containing a column of dates; other locale-specific data can be handled in a similar way.

QuerySurge’s built-in flat file JDBC driver can be set up to support non-default locales at either the Agent level or the column level. Both of these options are illustrated below.

Note: This tutorial uses a Spanish locale to parse Spanish dates to show the setup; the approaches shown here can be adapted for any locale and data affected by locale.

Sample File Data

Suppose we have a flat file with the following sample data, with the dates formatted for a Spanish locale (see Resources at the bottom of the article):

spanish-data.csv

id,fechadecompra,precio
A3943B,23-Abr-2015,4567.23
C5734H,01-Dic-1999,2357.35
G9043M,15-Jun-2006,68267.23

Agent-Level Locale

Applying a different locale to a specific Agent requires a few initialization options to be given to the Agent JVM.

For Agents deployed on Windows, start by launching the Agent service wrapper located at:

<QuerySurge Install Dir>\QuerySurge\agent\QuerySurgeAgentw.exe

Once the QuerySurgeAgent Properties window has opened, stop the Agent service under the General tab then navigate to Java Options under the Java tab.

The, add the following list of options to the preexisting list:

-Duser.country=ES
-Duser.language=es
-Duser.variant=Traditional_WIN

Afterwards, start the Agent service under the General tab

The Connection

In your flat file Connection (see this article, step 7, for details), under Data Type Options, specify the column type for the column with the locale-specific date information as Date. For our sample file using an Agent-level locale, the data types for all three columns are: Varchar,Date,Decimal.

The QueryPair

Now in QuerySurge we can write our queries as we can in any other case and the newly-configured Agent to read the incoming Spanish dates:

SELECT id, fechadecompra, precio FROM `spanish-data.csv`

Results:

 

Column-Level Locale

To impose a different locale to individual columns in your queries, we can write a custom function that applies a specified locale to a date-formatted column.

Following our article on creating custom flat file functions, we’ll use the following example Java function for this example (see Resources at the bottom of the article):

Note that the first argument is the locale-specific date, the second argument is the format of the incoming date data, and the third and fourth arguments specify the Locale using two-letter ISO codes for language and country.

The Connection

In your flat file Connection (see this article, step 7, for details), under Data Type Options, specify the column type for the column with the locale-specific date information as Varchar, so that QuerySurge reads it without applying any date-related typing. This allows our function to parse the incoming string representation of the dates and apply the desired Locale. For our sample file using an Column-level locale, the data types for all three columns are: Varchar,Varchar,Decimal.

The QueryPair

After deploying the function by following our Knowledge Base article, we can invoke our custom function within our SQL:

SELECT id, parseDateLocale(fechadecompra, 'dd-MMM-yyyy', 'es', 'ES') AS fechadecompra, precio FROM `spanish-data.csv`

Results:

Resources