MDA Workbench enables transfer of data from Load-Ready files to a Microsoft Access database, which is referred to as a Local System Database.
- Create an unlimited number of Local System Databases.
- Choose and use any existing Local System Database.
- Browse the data in Local System Databases.
- Export data from Local System Databases: to Excel, or to tab-delimited text files.
- Transfer Load-Ready Files to a Local System Database with few clicks.
The currently selected Local System Database is shown as a tool tip when hovering the mouse pointer over the System Database - Choose button on the External Data ribbon tab.
You'll primarily use the buttons on the External Data ribbon tab.
- Create or Choose a Local System Database.
- Open a Mapping Project that defines the Load-Ready files.
- Click the Transfer - Load-Ready button on the External Data ribbon tab.
- Click the Database Browser button to open a database browser window to view transfered data in the Local System Database.
When the Transfer - Load-Ready button on the External Data ribbon tab is clicked, a transfer process with the following steps is executed:
- User is prompted to choose a Windows folder.
- This folder is expected to contain the one or many Load-Ready files that are defined by the active MDA Workbench project.
- For example, for a project named MD_VEND, the folder would contain Load-ready files such as MD_VEND01.TXT, MD_VEND02.TXT, etc.
- Program confirms that the Load-Ready files for the active MDA Workbench project exist in the chosen folder.
- Program checks whether Staging Tables for the Load-Ready files already exist in the currently selected Local System Database (e.g. tables named MD_VEND01, MD_VEND02, etc.
- If any such named Staging Tables exist then the user is prompted to confirm that the Staging Tables will be deleted and re-created.
- Program creates Staging Tables in the currently selected Local System Database, according to the definition of the active MDA Workbench project.
Load-Ready files are then processed in order. Each file is processed as follows:
- Program scans the Load-Ready file to confirm that it meets the definition of the active MDA Workbench project.
- Are all of the fields in the project contained in the Load-Ready file (i.e. are any fields missing)?
- Are there extra fields in the Load-Ready file?
- Are the fields in the Load-Ready file in the same order defined by the active MDA Workbench project.
- Does each line of data in the Load-Ready file contain the same number of fields?
- Program confirms that the Primary Key fields exist in the -Load-Ready file.
- If the fields in the Load-Ready file are not as defined by the active MDA Workbench project (e.g. fields out of order, missing fields), then the fields are mapped so that data will still be imported.
- Program transfers data from the Load-Ready file to the Staging Table.
- Program adds Primary Key index to the Staging Table.
- Program adds a special field and index to the Staging Table to support paging records.
- When browsing data in the Local System Database tables, it's possible to browse tables that contain millions of records. The records in this case will be displayed as pages of records.
Throughout the transfer process, a status Window and progress bar inform the user of the status and progress.
At the end of the process, a Window is displayed with the process execution details. The process execution details (a grid) may be saved to an Excel workbook by right-clicking the grid.