Working with files

Files never went away.

Twenty-one formats, every one of them readable and writable. Match files by wildcard, map flat or nested structures by dragging, copy and move and zip them without a transformation at all, and look inside any of them before you build anything.

Start free trial Talk to us

Three things you do with a file

  1. Read 21 formats, any storage By name or by wildcard, compressed or not
  2. Transform Flat or nested mapping Flatten a hierarchy, assemble one, or convert format to format
  3. Manage Copy, move, zip, delete File operations run as their own flows, with no mapping at all

Most of what a real integration does with a file is not the transformation. It is finding the right file, unpacking it, putting it somewhere else afterwards, and not processing it twice.

Capabilities

What’s built in.

Twenty-one formats, both directions

Every format in the table below is a source and a destination. Any of them can be converted to any other in one transformation, which is how a fixed-length mainframe extract becomes Parquet, or an X12 document becomes rows in a table.

Wildcards, not filenames

Point a flow at *.csv and it processes whatever arrived. Wildcards are set on the connection, on the transformation, or both, and the override rule between them is explicit rather than a surprise. Matching is case-insensitive on local files.

Nested mapping by dragging

JSON, XML, Avro, Parquet, and the EDI and HL7 formats all carry hierarchy. Flatten a nested document into tables, assemble a nested one from flat rows, or reshape it with XSLT. The mapping is a drag between two trees, not a script you maintain.

File operations as flows

Copy, move, rename, delete, zip, unzip, create folders, split a large file into chunks, merge many into one, or run an XSLT over it. These are their own flow type, so moving files on a schedule needs no source-to-destination mapping.

Folders, not just files

Create folder1/folder2/folder3 in a single step. Missing destination folders can be created automatically as a flow writes. Unzipping either reproduces the archive’s folder structure or flattens it, depending on which you ask for.

Compression handled in the connection

Set Expected Compression and ZIP or GZIP input is decompressed on read. Set Archive output files and everything written is compressed on the way out. The two are independent, so converting ZIP to GZIP while copying is a setting rather than a script.

Explorer, before you build anything

Etlworks Explorer, the built-in data browser, expands a storage connection into its files and folders. Preview the parsed grid or the raw bytes, run SQL against a file that has never seen a database, and upload, download, or delete without leaving the screen.

Not processing it twice

Delete source files once they load, and optionally delete them when the load fails instead. Skip files already processed. Filter which ones qualify with a JavaScript expression, control the order they run in, and process them in parallel when order does not matter.

Fourteen places to put them

Amazon S3, Google Cloud Storage, Azure Storage, server storage, FTP, FTPS, SFTP, Box, Dropbox, Google Drive, OneDrive for Business, SharePoint, WebDAV, and SMB shares. The same flow types work across all of them, so the storage is a connection choice.

Specifications

Every format, read and written.

Twenty-one formats. There is no read-only column here, because there is no read-only format — each one can be the source of a transformation or its destination, and any format can be converted to any other.

Tabular and text
CSV
Delimited text, any separator, with configurable headers and encoding
Fixed Length Text
Column positions and widths — the format mainframe extracts arrive in
Excel
XLS and XLSX, by sheet
Key=Value
Property-style pairs, one per line
CLOB
The whole document into one text column, unparsed, for when you only need to change a little of it
Byte Array
No interpretation at all — moves a payload byte for byte
Structured and nested
JSON
Nested objects and arrays, flattened or assembled by mapping
XML
Deeply nested elements and attributes, with XSLT available
Avro
Schema-carrying binary, including files written by other producers
Parquet
Columnar, for lake storage and warehouse bulk loads
JSON dataset
JSON carrying column names and types alongside the rows
XML dataset
The same idea in XML, used when building APIs in Etlworks
EDI and healthcare
X12
The North American EDI standard, segment and loop aware
EDIFACT
The international EDI standard
NCPDP
Pharmacy claims and billing
VDA
German automotive EDI
HL7 2.x
Pipe-delimited clinical messaging
HL7 FHIR
The modern resource-based clinical standard
Documents and analytics
PDF
Table extraction on read, with a choice of algorithm; a structured table on write
HTML
Parses tables and merges identically structured ones; writes a real table element
QVD
Qlik’s native data file

FAQ

Common questions.

The filename changes every day. Can I still read it?
Use a wildcard. *.csv on the connection processes whatever landed in the directory, and the transformation can narrow it further with its own pattern such as *invoice*.csv. Which one wins is controlled by an explicit setting rather than left to chance, and local file matching ignores case, so *.JSON still finds data.json.
Do I have to unzip before I can read it?
No. Set Expected Compression to ZIP or GZIP on the connection and files are decompressed as they are read, in ETL flows, copy and move flows, and compressed API responses alike. One caveat worth knowing: the setting is applied unconditionally rather than by looking at the extension, so a connection cannot mix compressed and uncompressed sources, and each archive must hold exactly one file.
Can I convert one format to another without writing code?
Any of the twenty-one to any other. Pick a source format, pick a destination format, and map the fields. Fixed-length to Parquet, X12 to JSON, Excel to a database table, PDF tables to CSV. Where the shapes differ, the nested mapping does the flattening or the assembly.
I just need to move files. Do I need a transformation?
No. Copy, move, rename, delete, zip, unzip, create folder, split, and merge are their own flow type with no mapping involved, and one flow can chain several of them. They run on a schedule, through the API, or from the CLI like any other flow, with the same history and permissions.
How do I stop the same file being processed twice?
Several ways, depending on what you control. Delete loaded source files after a successful load, and optionally on failure instead. Turn on skipping of files already processed. Or filter candidates with a JavaScript expression when the rule is yours rather than ours.
Can I look inside a file before I build the flow?
Open Etlworks Explorer, our built-in data browser. Expand a storage connection down to the file, then read it as a parsed grid or as raw bytes, run SQL against it even though it is not a database, and export the result. You can also upload, download, and delete files there, with single uploads capped at 10 MB.

Start your trial

14 days. No card. Real workloads.

Drop one of your awkward files into Explorer and see what comes back.

Start free trial Talk to us