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

Overview

This Flow extracts data from file(s) and loads it into a relational database.

Process

In Connections

Access Connections here.

Step 1. Create a source Connection for the file storage. Test it if possible.

• 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

Step 2. Create a destination Connection for the relational database and test it.

Step 3. Create a Format for the source file. For example, use a CSV Format.

In Explorer (optional)

Acess Explorer here.

If source or destination files are nested, learn how to work with nested datasets.

In Flows

Access Flows here.

Step 4. Create a new File to database Flow.

Step 5. Add a new transformation and select FROM Connection, Format, and filename or a wildcard filename and TO Connection and table.

Step 6. Click MAPPING and optionally configure the flattening for the nested JSON or XML. Most likely, it is going to be a Source SQL.

Step 7. Test the transformation. Make sure it returns the flat data set.

Step 8. Add the per-field Mapping if needed.

Step 9. If you entered a wildcard filename in FROM, enable processing of all files which match the wildcard.

Read more about processing files matching a wildcard file name.

Step 10. Configure MERGE (UPSERT) if needed.

Step 11. Save the Flow and execute it manually.

In Schedules

Step 12. Schedule the Flow to be executed periodically.