Getting started with files

Overview

In Etlworks Integrator, it is possible to read and write data in numerous data exchange formats, such as CSV, JSON, XML, Excel, etc. The actual files may be in file storage, cloud storage, key-value storage, a NoSQL database or on an email server.

Connecting to data storage, NoSQL database or email server

• Amazon S3 
• Google Cloud Storage 
• Microsoft Azure Storage 
• Server Storage 
• FTP 
• FTPS 
• SFTP 
• Box
• Dropbox
• Google Drive
• OneDrive for Business
• SharePoint
• WebDAV 
• Inbound email 
• Outbound email 
• Redis 
• MongoDB 

Testing a connection

Click the Test Connection button on the connection screen to test the connection. Test Connection is only available for Cloud and File storage. In addition to actually connecting to the storage, Integrator attempts to read file names using a configured folder and file name. Integrator supports wildcard file names.

Formats

When working with files it is required that you describe the data exchange format. Etlworks Integrator supports the most commonly used formats:

• CSV
• Fixed Length Text
• JSON
• JSON dataset
• XML
• XML dataset
• Excel
• HL7 2.x
• HL7 FHIR
• X12 and EDIFACT Formats
• Key=Value format
• Avro
• Parquet
• CLOB Format
• PDF
• HTML

Browsing files and viewing data

Use the Explorer to browse files and view data.

Step 1. Create a file connection.

Step 2. Choose the format.

Step 3. Open Explorer, select the connection created in Step 1 and link it to the format chosen in Step 2. 

Step 4. Explore the metadata (files and fields), view data in a grid, query data and discover dependencies using SQL.

File operations

• Copy, move, rename, delete, zip, and unzip files
• Transforming XML files using XSLT
• Read how to split files.
• Read about merging files.

Extracting, transforming and creating files

• A transformation where a file is a source
• A transformation where a file is a destination

Expose file as an API endpoint

• Read how to expose a dataset as an API endpoint.

Tips and tricks when working with files

• Working with local files
• Using server storage as a staging area
• Processing files in a folder by a wildcard filename
• Process the file and then delete it
• Reading and writing files from/to specific folder using the same connection
• Reading from and writing into different Excel worksheets using the same Excel format
• Reading from and writing into different worksheets using the same Google Sheets connection
• Filtering rows in a flat file
• Disable automatic setting of the field data type when working with files
• Working with very large files
• Merging files
• Reading non-standard CSV files
• Split a CSV file when creating it
• Adding a custom header to a CSV file
• Disabling encoding CLOB fields in CSV files
• Configuring XML parser
• Reading an Excel worksheet with a header not in the first line
• Using an Excel worksheet index when reading Excel workbooks
• Working with region-specific S3 buckets
• Archive files before copying them to a cloud storage
• Adding metadata when creating files in S3 and Google Storage