How to extract data from any file and load into any database

This tutorial walks through extracting data from one or more files in a file storage and loading it into a relational database.

How do I set this up?

Set up the connections

See Creating and managing connections for background.

1. Create a source connection for the file storage and test it. Supported storage connectors include:
   • 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
2. Create the destination database connection and test it.

   

3. Create a format for the source file — for example, CSV.

   

Optional: test the source in Explorer

You can preview the source data in the Etlworks Explorer. For nested source files, see how to work with nested datasets.

Create the flow

4. Create a new File to database flow.

   

5. Add a transformation. Set FROM to the source connection, format, and filename (or a wildcard filename), and TO to the destination connection and table.

   

6. Click MAPPING and, if the source is nested JSON or XML, configure flattening — usually a Source SQL against the nested document.
7. Test the transformation. The output should be a flat dataset.

   

8. Add per-field mapping if needed.

   

9. If FROM is a wildcard filename, enable processing of all files that match the wildcard.

   

10. Configure MERGE (UPSERT) if you want to update existing records instead of just inserting.

    

11. Save the flow and execute it manually.

    

12. Schedule the flow to run periodically.