Key=Value Format

When to use this Format

In a Key=Value Format, the keys and values are encoded in key-value tuples separated by &, with a = between the key and the value. Example:

first=Jow&last=Doe&age=18

The Format is typically used to read and create a payload encoded as application/x-www-form-urlencoded.

Process

To create a new key=value Format, go to Connections, select the Formats tab, click Add Format, and type in key=value in the Search field. 

The following parameters are available when configuring the Format:

• Delimiter: a character used as a delimiter between key-value tuples. The default is amp: &.
• Line Separator: a character used as a separator between lines.
• Default Extension: the default extension is used when the file name doesn't have an extension. If not entered, txt is the default extension.
• Ignore Byte order mark character (BOM): uncheck this option if you do not want the parser to ignore the byte order mark character (BOM). The default is ignore.
• Template: a template in the Key=Value Format. If this field is not empty. Etlworks will use it to populate column names and data types. Template is an optional field. Example: first_name=string&last_name=string.
• Column names compatible with SQL: this converts column names to SQL compatible column names by removing all characters except alphanumeric and spaces.
• Skip not Properly Formatted Rows: if this option is enabled, the system skips rows that do not conform to the key=value format specification.
• Skip Empty Rows: sometimes files contain completely empty rows with no values and delimiters. The Format can be configured to skip these rows. Otherwise, it will generate an exception when reading such a file.
• Start row: if this value is not empty, the system will start reading the file from the specified 1-based row and will ignore previous rows.
• End row: if this value is not empty, the system will stop reading the file after the specified 1-based row.
• Transformation type: the default is filter, which allows you to write JavaScript code to filter out some of the rows in a file, but you can also select preprocessor. Read more about using the preprocessor to modify the contents of the source document.
• Filter or Preprocessor: this is code written in JavaScript, which is used to filter out lines, change lines, or the entire document. Read more about filters. 
• All fields are strings: If this option is enabled, (it is disabled by default) the system will create all fields with a string data type.
• Date and Time Format: a Format for timestamps (date & time).
• Date Format: a Format for date (date only, no time).
• Time Format: a Format for time (time only, no date).
• Parse Dates: if this option is enabled, and the date or time value is not recognized as one of the Formats defined above, Etlworks will try to parse it using one of the well-known date & time Formats.
• Trim Strings: if this option is enabled, Etlworks will trim leading and trailing white spaces from the value.
• Treat 'null' as null: if this option is enabled, Etlworks will treat string values equal to 'null' as actual nulls (no value).
• Encode CLOB fields using Base64: if this option is enabled (default), Etlworks will encode fields with the CLOB data type using the Base64 algorithm.
• Maximum number of rows in file: the maximum number of rows in the file when creating new files. Use it to split a large document while creating it — it is extremely fast and efficient, compared to splitting the existing document. 
• Encoding: character encoding when reading and writing a file. No encoding means there will be no additional encoding.