ETL: File Storage Options

Common source and destination configuration options for file and object storage components in Integrate.io ETL.

This page is a reference for the configuration options that are shared across all file and object storage source and destination components: Amazon S3, Google Cloud Storage, Azure Blob Storage, SFTP, FTPS, and HDFS.

Provider-specific details (connection setup, which bucket or container label appears, which source actions are available for that provider) are documented on each provider’s own page. Google Drive source is also documented separately because its options differ significantly from the other providers.

---

Source Options

Source Location

Bucket / Container (S3, GCS, Azure Blob only)

The name of the cloud storage bucket or container that holds the source files. The label varies by provider:

Provider	Label
Amazon S3	Bucket
Google Cloud Storage	Bucket
Azure Blob Storage	Container
SFTP, FTPS, HDFS	Not shown

Source path

The path to the input folder, file, or pattern within the bucket or container (for object stores) or the server (for SFTP, FTPS, HDFS).

Example	Description
sales/2015/01/	All files in a folder (not supported for GCS; use a pattern instead)
sales/2015/01/log.csv	A single file
sales/2015/{01,02}/	A glob pattern matching multiple folders

You can use wildcard characters for pattern globbing. File and directory names that begin with an underscore (_) or a dot (.) are ignored.

For SFTP and FTPS, the path must begin with /. Integrate.io prepends it automatically if omitted.

Case-insensitive path (zip files only)

When the source path contains .zip, a Case-insensitive path checkbox appears. Enable it to match the zip file path without regard to letter case.

---

Source File Format

Record delimiter: what breaks the data into records:

• New line (\n, \n\r, \r): each line in the file is one record.
• End of file: each file is treated as a single record.

Record type: the format of each record:

Type	Description
Delimited values	Fields separated by a delimiter (comma, tab, or custom). See Delimited values parameters.
Excel	An .xlsx workbook. See Excel parameters.
JSON object	Each record is a JSON object enclosed in {}. See JSON parameters.
Raw	The entire record is read as a single string or binary field.
Parquet	Reads .parquet files. Columns with DATETIME/INT96 data type are not supported.

Avro file format is not supported.

Source data can be compressed (zip, gzip, or bzip2) or uncompressed. Zip files can be password-protected; enter the password in the Zip Password field. Source data must be UTF-8 encoded. For other encodings, see processing a different encoding.

To read ASC X12 EDI files such as 834 benefit enrollment files, use the dedicated EDI (X12) source instead.

---

Delimited Values Parameters

Applies when record delimiter is New line and record type is Delimited values.

1. Field delimiter: select from the dropdown (comma, tab) or type a single character or escape sequence:

   Escape	Character
   \b	Backspace
   \f	Form feed
   \n	Newline
   \r	Carriage return
   \t	Tab
   \'	Single quote
   \"	Double quote
   \	Backslash

2. String qualifier: if fields are enclosed in quotes, select ' or ". To allow line breaks inside quoted fields, select ” (newline inside) or ’ (newline inside). Use the newline-inside option with caution, as unbalanced quotes can affect job performance.

3. Escape character: available when a string qualifier is set. Specifies the character used to escape qualifier characters within field data.

4. First row contains column names: check to skip the header row in each file.

5. Detect header by name: locates the header row dynamically by matching your schema column names. Use this when the header is not always on row 1 (for example, files with a leading title or blank rows before the header). The full schema header must still appear in column order in each file. See Header Name Matching for how header cells are compared to schema field names.

---

Excel Parameters

Applies when record type is Excel.

Field	Description
Select sheet by	Name: target a sheet by its tab name. Position: target by position in the workbook (1 = first visible sheet, hidden sheets not counted). Leave both unset to combine all sheets (all sheets must share the same schema).
Sheet Name	Shown in Name mode. Enter the exact tab name.
Sheet Number	Shown in Position mode. Use position mode when the sheet name changes between files but its position stays the same.
Ingest large Excel file	Enables streaming mode for large files. Target a single sheet by name or position, or leave both unset to combine all sheets.
First row contains column names	Skip the header row.
Match columns by name	Maps workbook columns to your schema by header name instead of position. Use when column order varies between files. See Header Name Matching.
Detect header by name	Locates the header row dynamically. Combined with Match columns by name, both the row and column order can vary. Glob patterns (e.g. 2025_*.xlsx) are supported when matching by name. See Header Name Matching.

Password-Protected Workbooks

If a workbook is encrypted with a password (set via Protect Workbook in Excel), check This file is password protected and enter the password in the File Password field. Integrate.io decrypts the workbook during schema preview and job runs. Passwords containing special characters such as $ are supported.

The checkbox appears when the source path ends in .xlsx or .xlsm. Legacy .xls files are not supported for password decryption. Unchecking the box clears any saved password.

Password-protected workbooks do not use streaming mode. When a password is set, enabling Ingest large Excel file reads the workbook in standard mode.

When streaming a single sheet, date cells come through as Excel serial numbers (e.g. 45306). Declare those columns as int or decimal in your schema, or transform them downstream.

---

Header Name Matching

Detect header by name and Match columns by name compare header cells in the file against the field names in your schema. The comparison is not an exact string match. It follows the same rules schema detection uses to turn a raw header into a field name:

• Matching is case-insensitive.
• Runs of spaces and underscores are treated as a single underscore, so the header Customer Id matches the field customer_id.
• Punctuation is ignored, so the header Sales (USD) matches the field Sales_USD.
• A leading run of underscores in a file header is ignored, so the header _natural_key matches the field natural_key. Schema detection cannot keep a leading underscore in a field name, and this rule lets the detected schema match the file it was detected from. Only leading underscores are dropped: workflow_id_ does not match workflow_id, and user_id does not match userid.

For example, a file whose header row is:

_id,_name,_city,_amount

matches a schema with the fields id, name, city, amount.

Match columns by name assigns exact matches first. The leading-underscore rule applies only to schema fields with no exact match, and only to columns that no other field claimed. If a workbook contains both an id column and an _id column, the schema field id maps to the id column.

If a job with Detect header by name fails with no row matching expected columns [field1, field2, ...], the schema holds placeholder field names instead of the file’s real header names. Re-detect the source schema, then rerun the job.

---

JSON Parameters

Applies when record type is JSON object.

Base record JSONPath Expression: defines the base record and extracts nested objects or arrays.

Preset	Behavior
Object	Uses the keys of the root JSON object as input fields.
Array	Uses the keys of JSON objects within an array as input fields.
Custom expression	Example: $.data[*]/ extracts fields from objects inside an array named data.

Read more about processing JSON data.

---

Source Action

Determines what happens to source files during and after processing.

Action	Description
Process all files directly from source	Default. Files are read and processed in place.
Process only new files (Incremental load)	Reads only files that have not been processed before, tracked via a manifest file. Not supported for Excel. See incremental load.
Copy, merge and process all files	Reads all files, merges them into larger files, then processes. Use for sources with many small files. Fails if the input path points to a single file. Not available for all providers. Check your provider’s page.
Move processed files to another folder	Moves source files to a backup directory after processing.

Available source actions vary by provider. Not all actions are available for every storage type. Refer to your provider’s page for details.

Incremental Load (Process Only New Files)

Field	Description
Manifest connection	The connection used to store the manifest file. Defaults to the source connection.
Manifest path	Path to the manifest file, e.g. bucket/path/to/manifest.gz. Created on first run. Each run compares the current file listing to the manifest and processes only new files. Previous manifests are kept as backups.

Move Processed Files

Field	Description
Destination path	The folder to move processed files to.
Append string to filename	Optionally append a custom string (e.g. _processed) to each moved file’s name.

---

PGP Decryption

To decrypt source files encrypted with OpenPGP, select an active entry from the PGP Key dropdown. The entry must hold a private key. Integrate.io decrypts each matching file before parsing it.

Paths that contain a space, semicolon, or parenthesis are not supported when a PGP key is set.

See PGP Keys for how to add and manage keys.

---

Source Schema

After defining the source location and format, select the fields to include.

Format	Schema behavior
Delimited values	Fields are read by position. Define all fields in order.
JSON	Define only the fields you want to use. Specify the JSON key for each field.
Raw	Single field containing the entire record.

For each field, define:

• Alias: how to refer to the field in downstream components. Illegal characters are flagged before you close the dialog.
• Data type: the type to cast the field to.
• Key (JSON only): the key name in the source JSON.

You can also add fields from the Meta Data section. They are not read from the file: Integrate.io fills them in for every row. A preview reads only the first file the path matches, so every preview row shows that file’s path and time; a job run fills in each row’s own file. Change a meta data field’s alias to choose its column name. If the file already has a column named file_path or file_modified_at, that column is read like any other and the meta data field of the same name isn’t offered.

Field	Type	Value
file_path	string	The path of the file the row was read from.
file_modified_at	datetime	The last-modified time of the file the row was read from, in UTC. Every row from the same file gets the same value.

Use file_modified_at to tell apart deliveries of a file that keeps the same name, or to keep only the newest version of a resent file. It is the time the storage reports for the file, not the time the job ran (for that, use the $_JOB_SUBMISSION_TIMESTAMP variable):

• Amazon S3, Google Cloud Storage, Azure Blob Storage: when the object was last written, usually the upload time.
• SFTP, HDFS: the file’s modification time, to the second.
• FTPS: depends on the server. Many FTP servers report times only to the minute, and some in the server’s local time zone.
• A file inside a zip archive (a path such as archive.zip/data.csv): the archive’s time.

Some transfer tools keep the original file’s time when they copy it (for example rsync -t or scp -p). For files delivered that way, the value is when the file was created at the sender, not when it arrived.

file_modified_at is not available for Excel, Parquet, XML, EDI (X12) or whole-file raw sources, for a path ending in .zip, for Google Drive, or with the Process only new files (Incremental load) and Copy, merge and process all files source actions. In those cases the files are copied before they are read, and the copy does not keep the original time.

---

Destination Options

Target Location

Bucket / Container (S3, GCS, Azure Blob only)

The name of the target bucket or container. The label varies by provider:

Provider	Label
Amazon S3	Bucket
Google Cloud Storage	Bucket
Azure Blob Storage	Container
SFTP, FTPS, HDFS	Not shown

Target directory: the directory within the bucket or container where output files are written. One or more files are created in this directory. By default, the job fails if the directory already exists (see Destination Action).

---

Destination Format

Format	Description
Delimited values	CSV, TSV, or any delimiter-separated format. Output files are UTF-8 encoded.
Line delimited JSON	One JSON object per line. UTF-8 encoded.
Parquet	Columnar format compatible with Athena, Impala, Presto, and other tools. datetime and complex types are not supported. Compression is not available for Parquet.
XML	One well-formed XML document per output file. UTF-8 encoded. Available for Amazon S3, SFTP, and FTPS destinations only. See XML Options.
Excel (XLSX)	One .xlsx workbook per output file, with a single sheet. Available for SFTP, Amazon S3, Google Cloud Storage, and Azure Blob Storage destinations only. See Excel Options.
Fixed width	Records written as fixed-width lines with no separators. Requires configuring the Fixed Width Layout.

---

Delimited Values Options

Field	Description
Delimiter	Character separating fields. Select from the dropdown or type a single character. Default is tab.
String qualifier	If selected (' or "), fields containing the delimiter are enclosed in quotes.
Escape character	Available when a string qualifier is set. Character used to escape qualifier characters in field data.
Line ending	Unix/Linux (LF) or Windows (CRLF).
Write field names in header	Adds a header row with field names to each output file.

Header names with spaces: use triple underscores (___) in the alias or column name in a Select component to produce a space in the output header. To use a different separator character, add a package variable _FS_REPLACE_WITH_SPACE_IDENTIFIER with the character you want to replace.

---

XML Options

Applies when Destination format is XML. Each output file is a single XML document: a root element wrapping one record element per output record. Each field is written as a child element named after its field alias.

XML output is available for Amazon S3, SFTP, and FTPS destinations. The XML format button appears only when the component’s connection is one of those types. A package saved with XML on any other backend fails validation with an error naming the supported backends.

Field	Description
Root element	The name of the document’s root element. Defaults to records. Leave empty to use the default.
Record element	The name of the element written for each record. Defaults to record. Leave empty to use the default.

Both names must be valid XML element names: a letter or underscore, followed by letters, digits, underscores, hyphens, or periods. Colons are not allowed. An invalid name shows an inline error in the editor and fails when you save the package, not when the job runs.

For example, with root element customers and record element customer:

<?xml version="1.0" encoding="UTF-8"?>
<customers>
  <customer>
    <id>1</id>
    <name>Acme &amp; Co</name>
    <email/>
  </customer>
</customers>

Document structure (optional)

Use these options when the consumer of the file dictates the shape of the whole document, such as a two-section import file or a feed that expects a specific declaration. Every option is off unless you set it, so an XML destination saved without them writes the same bytes it wrote before.

Field	Description
Root attributes	Attributes written on the root element, in the order you type them. Format each as name="value". Values cannot contain spaces, quotes, backslashes, or $. Use one pair per attribute, separated by whitespace. A name may carry one namespace prefix, for example xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance". A repeated attribute name is rejected on save.
Standalone document	Adds standalone="yes" to the XML declaration.
Section element	Wraps every record in a fixed section element with this name, between the root and each record element. The name is a constant, not read from the data. Use this when the section name is the same for every record. The name must be a valid XML element name. This option does not require Merge output to single file. Each part file writes a complete document with its own section wrapper. Mutually exclusive with Section field: set one or the other, never both.
Section field	The name of a column, not the section name itself. For a fixed name, use Section element instead. Wraps records in a section element named by the value of this column. The section opens when the value first appears, closes when it changes, and the column itself is not written. A null or empty value puts the record directly under the root. Needs Merge output to single file. The ordering that keeps each section together is handled for you. Mutually exclusive with Section element.
Record element field	The name of a column, not the record element name itself. For a fixed name, use Record element instead. Names each row’s record element from the value of this column, so one document can hold several record types. A null or empty value falls back to Record element. The column itself is not written.
Omit empty fields	Leaves out a child element whose value is null, an empty string, or an empty container. Use this when rows of different shapes share one schema.
Repeat nested groups	Writes a repeated group as sibling elements sharing one name, instead of one wrapper element holding an <item> per entry. A single-field tuple inlines its value. An empty group is absent.

Both field options are checked against the destination’s field list when you save. A name that does not match a written field fails on save with a message that lists the fields it saw. A section that reappears after it closed fails the job with a message that names the section field.

With Root element set to ConfigDataImport, Record element set to PositionType, and Section element set to Position:

<?xml version="1.0" encoding="UTF-8"?>
<ConfigDataImport>
  <Position>
    <PositionType>
      <id>1</id>
      <title>Engineer</title>
    </PositionType>
    <PositionType>
      <id>2</id>
      <title>Manager</title>
    </PositionType>
  </Position>
</ConfigDataImport>

For example, with root attributes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance", Standalone document enabled, Section field set to section, and Record element field set to record_type:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<records xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Position>
    <Position>
      <code>P001</code>
      <title>Engineer</title>
    </Position>
  </Position>
  <PositionAssignment>
    <PositionAssignment>
      <employee>E100</employee>
      <position>P001</position>
    </PositionAssignment>
  </PositionAssignment>
</records>

With Repeat nested groups on, a bag column named ContactInformation holding two tuples writes as:

<Employee>
  <id>E100</id>
  <ContactInformation>
    <type>email</type>
    <value>[e-mail]</value>
  </ContactInformation>
  <ContactInformation>
    <type>phone</type>
    <value>555-0100</value>
  </ContactInformation>
</Employee>

With the option off, the same bag writes as one <ContactInformation> wrapper holding two <item> entries.

Output is always well-formed XML, regardless of the data:

• The characters &, <, and > are escaped. Control characters that XML 1.0 does not allow are dropped. Tabs and newlines are preserved. A carriage return is written as the character reference 
 so parsers read it back unchanged.
• A null value is written as an empty element (<email/>). An empty string is written as an open and close tag.
• A nested record becomes nested elements named by its own fields. A bag becomes repeated <item> elements. A map becomes one child element per key. Keys that are not valid XML element names are written as <entry key="..."> with the key kept verbatim.
• A field alias that is not a valid XML element name is adjusted to a valid one. Sibling elements that would end up with the same name get a numeric suffix to stay distinct.

XML output is always UTF-8. The document declares encoding="UTF-8", so the character encoding selector is not shown for XML destinations. Setting any other encoding through the API fails on save.

Selecting XML turns on Merge output to single file so one job writes one document in one file. You can uncheck it to write one document per part file. The suggested file name suffix is .xml.

A job that produces zero records writes a document with the declaration and an empty root element, for example <?xml version="1.0" encoding="UTF-8"?><records></records>. An empty XML file is not a valid document, and most parsers reject it. Enable Skip Empty Output to write no file at all when there are no rows.

---

Excel Options

Applies when Destination format is Excel (XLSX). Each output file is an .xlsx workbook with one sheet. The sheet holds an optional header row of field aliases, then one row per record.

Use Excel output when the consumer of the file expects a workbook rather than a CSV, such as a business user or an import tool that only accepts .xlsx.

Excel output is available for SFTP, Amazon S3, Google Cloud Storage, and Azure Blob Storage destinations. The Excel (XLSX) format button appears only when the component’s connection is one of those types. A package saved with Excel on any other connection type, such as FTPS or HDFS, fails validation with the error Excel (XLSX) output is not yet available for <provider> connections; it is available for SFTP, Amazon S3, Google Cloud Storage and Azure Blob Storage.

Field	API attribute	Description
Sheet name	sheet_name	The name of the sheet in each workbook. Defaults to Sheet1. Leading and trailing spaces are removed. Leave empty to use the default.
Write field names in first row	write_header	Writes the field aliases as the first row of the sheet. On by default.

The sheet name must follow these rules. A name that breaks one shows an inline error in the editor and fails when you save the package, not when the job runs.

• At most 31 characters. Emoji and some other symbols count as two characters.
• None of these characters: : \ / ? * [ ].
• No spaces or other whitespace. Use an underscore instead, for example Q1_Sales.
• No $, no double quote ("), and no control characters.
• Does not start or end with an apostrophe ('), and does not start with a hyphen (-).

Excel output has these restrictions:

• Each sheet holds at most 1,048,576 rows, including the header row. A job that writes more rows to one file fails rather than truncating the output.
• Output is always UTF-8. The character encoding selector is not shown for Excel, and setting another encoding through the API fails on save.
• Output compression is not available. The compression selector is not shown for Excel, because an .xlsx file is already a compressed archive.
• The suggested file name suffix is .xlsx. If you do not set a suffix, Integrate.io adds .xlsx to each output file.

To create an Excel destination through the API, set the component’s destination type as follows:

{
  "destination_type": {
    "xlsx_destination_type": {
      "sheet_name": "Customers",
      "write_header": true
    }
  }
}

---

Output Compression

Available for Delimited values, JSON, XML, and Fixed width formats. Not available for Parquet or Excel.

Option	File suffix added
None	(none)
Gzip	.gz appended to output directory name
Bzip2	.bz2 appended to output directory name

---

PGP Encryption

To encrypt output files with a trading partner’s OpenPGP public key, select an active entry from the PGP Key dropdown. The entry must hold a public key. Files are encrypted before being written to the target path.

Paths that contain a space, semicolon, or parenthesis are not supported when a PGP key is set.

See PGP Keys for how to add and manage keys.

---

Destination Action

Controls what happens when the target directory already exists.

Action	Description
Fail if target directory exists	Default. The job fails if the destination directory already exists. Use variables to generate unique directory names per run (e.g. /output/${_JOB_ID}).
Delete target directory if exists	Deletes the existing directory before writing.
Replace files in directory	Writes to the existing directory. Files with the same names are overwritten. To add files without overwriting, use a unique file prefix (e.g. include ${_JOB_ID}). Warning: the target directory is deleted on job failure.
Use intermediate storage, copy to existing directory	Writes to intermediate storage first, then copies to the existing target directory. The target directory must already exist. Supports character encoding selection.
Use intermediate storage, copy to new directory	Writes to intermediate storage first, then copies to a new target directory. Fails if the directory already exists.

---

Merge Output to Single File

Check Merge output to single file to write all output to a single file rather than multiple part files. Note that file size limits on some platforms may cause the job to fail for large outputs.

---

Character Encoding

Available only when Destination action is set to Use intermediate storage, copy to existing directory.

Option	Description
UTF-8	Default
UTF-16
Shift-JIS
CP932

XML destinations require UTF-8. The selector is not shown for XML, and setting another encoding through the API fails on save.

Excel destinations also require UTF-8. The selector is not shown for Excel, and setting another encoding through the API fails on save.

---

Target File Names

Default pattern: output files are named part-[mr]-[0-9]{5} (e.g. part-m-00000).

To use a custom pattern, select Custom pattern:

Field	Description
File name prefix	Leave empty to keep the default prefix (part-[mr]-), or enter a custom prefix. Supports package variables for dynamic names (e.g. a timestamp).
File name suffix	Integrate.io suggests a suffix based on the selected format and compression type.

---

Fixed Width Layout

When Destination format is set to Fixed width, a Fixed width layout step appears. The layout is a table with one row per incoming field, in schema order. Every incoming field is written with no separators between columns.

To write only some fields or in a different order, add a Select transformation before this destination.

Column	Description
Field	Incoming field name. Read-only.
Width	Number of characters this field occupies. Whole number, 1-1000.
Align	Left or Right. Controls which side padding is added to.
Pad	Single character used to fill unused width. Defaults to a space.
If too long	Fail the job (stops on first overflow; always applies to numeric fields) or Truncate (text fields only).
Decimals	Implied decimal places for numeric fields. 0 writes the value as-is. With 2, the value 12.34 is written as 1234. Values are rounded HALF_UP. Whole number, 0-1000.
Sign	Where to place the sign for numeric fields: None, Leading (start of field), or Trailing (end of field).

The total record width in characters is shown below the table and updates as you edit.

Preview with Data

Click Preview with data to run the component through the previewer and see the actual records the job would write. Rows whose sample value does not fit the configured width are marked with an advisory message. Blocking errors (invalid width, pad, or decimals) appear in the errors box and prevent saving.

Persisted Layout Format

The layout is stored as fixed_width_destination_type on the component. Example:

{
  "fields": [
    {
      "name": "id",
      "width": 8,
      "align": "right",
      "pad": "0",
      "on_overflow": "fail",
      "implied_decimals": 0,
      "sign": "none"
    },
    {
      "name": "amount",
      "width": 12,
      "align": "right",
      "pad": " ",
      "on_overflow": "fail",
      "implied_decimals": 2,
      "sign": "leading"
    }
  ],
  "record_delimiter": "\n",
  "char_encoding": "UTF-8"
}

record_delimiter and char_encoding round-trip through the API but are not editable in the UI.

---

Skip Empty Output

Available for Delimited values, JSON, XML, and Fixed width formats. When checked, no output files are written if the job produces zero records. Useful for downstream processes that should not receive empty files.

---

FAQ

Which source actions are available for my storage provider?

It depends on the provider. Amazon S3, GCS, and SFTP/FTPS support all four actions. Azure Blob Storage supports three. HDFS supports only “process all files directly.” Check your provider’s page for the exact list.

Can I read compressed files?

Yes. Source files can be zip, gzip, or bzip2 compressed. Password-protected zip files are also supported; enter the password in the Zip Password field.

Can I read files in a different character encoding?

Source data must be UTF-8 encoded. For other encodings, see processing a different encoding.

How does incremental load work?

Integrate.io tracks which files have been processed in a manifest file stored at a path you specify. Each run compares the current file listing to the manifest and reads only new files. See reading file storage data incrementally.

Can I write to a single output file instead of multiple part files?

Yes. Enable Merge output to single file in the destination component. Be aware that very large outputs may exceed file size limits on some platforms.

How do I include spaces in output header names?

Use triple underscores (___) in the alias in a Select component. They become spaces in the output header. To use a different replacement character, set the package variable _FS_REPLACE_WITH_SPACE_IDENTIFIER.

---

Related

Reading File Storage Data Incrementally

Selecting File Input Format

Pattern Matching in Source Paths

PGP Keys

Using Variables in Packages

EDI (X12) Source