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

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

In Connections

Step 1. Create 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. Test it.

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

In Explorer (optionally)

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

In Flows

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 7. Click the MAPPING button and optionally configure the "flattening" for the nested JSON or XML. Most likely it is going to be a Source SQL.

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

Step 9. Add the per-field mapping if needed.

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

Step 11. Configure MERGE (UPSERT) if needed.

Step 12. Save the flow and execute it manually.

In Schedules

Step 13. Schedule the flow to be executed periodically.