Configuring Connections: Zipped Flat Files (Delimited)

Configuring QuerySurge Connections for Delimited Flat Files in a Zip Archive

QuerySurge's JDBC-compliant Flat File driver can connect to zip archives in addition to connecting directly to files. The setup in the Connection Wizard uses the Connection Extensibility option, so you'll need both a hand-written schema.xml file as well as well as a properly constructed JDBC URL.

The Sample Delimited Flat File

The file has the following characteristics. The file name has a ".csv" extension. Headers are not found in this file; it contains only data rows.  The file contains 6 columns, and uses a comma delimiter.

A row of data looks like:

1A36,3060,Mason,"LED HDTV - 42",Y,1 

The Schema File

The schema file for this delimited file is:

<schema>
  <table name ="*.csv" separator = ",">
    <column name = "RowId" pos = "1" type = "Varchar" size="32"/>
    <column name = "Purch_ID" pos = "2" type = "Double"/>
    <column name = "Last_Name" pos = "3" type = "Varchar" size="32"/>
    <column name = "Item_Name" pos = "4" type = "Varchar" size="32"/>
    <column name = "S" pos = "5" type = "Varchar" size="32"/>
    <column name = "Qu" pos = "6" type = "Integer"/>
  </table>
</schema>

In addition to specifying each of the column widths and their names and data types, the schema file also supplies a wildcarded filename: "*.csv".

The JDBC URL

The flat file driver URL protocol for files in Zip archives must contain a special "zip" token signifying that the target file is contained in a Zip archive. In addition, it must specify the Zip file's full path, and the full path to the schema.xml file in the schema property. Another important property is the fileExtension property, which specifies the extension of the data file. Other driver properties are also included, such as suppressHeaders, which is set to true because there are no headers in our sample file. In addition, we specify commentLine and escapeEOLInQuotes properties.

jdbc:jstels:csv:zip://C:/Documents/Sample_Flat_Files/Shipped_Electronics.zip?dbInMemory=false&commentLine=--&fileExtension=.csv&suppressHeaders=true&schema=C:/Documents/Sample_Flat_Files/Shipped_Electronics_Delim_schema.xml&escapeEOLInQuotes=true

Note: Even for Windows-type paths, the forward slash is used. 

Setup and Execution

In the Connection Wizard, select the Connection Extensibility option, after you have input a name for the Connection.

For the Driver Class, use:
jstels.jdbc.csv.CsvDriver2

For the JDBC URL, copy in your URL:

Save the connection. You are now ready to write QueryPairs with this Connection.

Resources