Managed Batch Import

dbFront has the built-in ability to allow users to import CSV or Excel data directly into specific tables.  The Quick Import Wizard is both simple and powerful but it creates the risk that users could accidentally mess up their own data.

The instructions below allow you to create a managed import process that is much safer and allows for additional processing so that you can properly validate, prepare and import the data.

The solution involves creating an Import Batch and Import Staging table and having your users import into the Import Staging table. Once the import is complete, they would trigger a stored procedure to validate and process the data and then migrate it to your destination tables. The two tables would be structured to fit your specific needs.

Import Batch Table

The purpose of the [Import Batch] table is to identify the separate import jobs. It would also contain specific information such as who created the batch and why, and the status of the current batch. The Import Batch can be set up as follows:

• BatchId: Key Field (AutoNumber is good),
• BatchComment: Information about the import source or purpose,
• UserHelp: Add status specific user help, See: Context Specific Help Setup
• AuditDate: Add Audit Information, See: Audit Values
• AuditUser, Add Audit Information, See: Audit Values
• BatchStatus: [New, Processing, Complete, Failed]
• "Process Import" button: An ActionButton that calls a stored procedure to process the data,
• RowSecurity: Use RowSecurity to ensure that users can't change a completed Import Batch,

Important!: Disable User import to this table. Users should not import into this table.

Import Staging Table

The Import Staging table is where your users would be importing their data. For this reason, its structure would be unique to your environment and import source. The structure should match the import file with the expectation that the Stored Procedure would be responsible for importing the data correctly into the various destination tables.

• BatchId (ReadOnly Key): The [Import Staging] table must be related to the Import Batch via the batch key,
• RowStatus (ReadOnly): An optional status indicator for the current row,
• Import Fields: All the fields you want your users to import...
• Lookup Values: The import fields can be foreign keys. dbFront will lookup the key values,
• RowSecurity: Use RowSecurity to ensure that users can't change a completed Import Batch,
• "Process Import" ActionButton: You could duplicate the same action button to the staging table.
• ChildTable: The Import Staging table should be set up as a child table.

Important!: Enable User import to this table. Users will import directly into this table.

Import Action Button

The Action Button on the [Import Batch] table would run a stored procedure to do the work.

The stored procedure would take the "BatchId" as its only input and would be responsible for processing the imported data in the child [Import Staging] table.

Once complete, the store procedure could return formatted results with further instructions to the user. ​The stored procedures can be set up to return a report, a table of data, or a simple message with optional HTML formatting.

At runtime the user would:

1. Create an Import Batch record manually.... (usually with the details of the import source, type, etc),
2. Import the data into the Import Staging table via the "Quick Import",
3. Click the Process Import button to validate and process the imported data.

Summary

Importing into a child Import Staging table as described above results in a robust and secure import process:

• Direct control of how the data is validated and applied as it is imported.
• Optionally Audit and Revert changes.
• Full rollback if any portion of the import fails to ensure data integrity.
• Higher throughput as the comparison and processing work is done at the database level.