The Data Upload template can be used to allow end users to upload Excel files directly into the database. End users can either upload data directly from an Excel file, or copy and paste it into an onscreen text area for ease of use. At run-time, users have various options including which datasheet to upload, defining whether the first row of data should be ignored (in case the dataset contains column descriptions), and selecting whether to replace or append records to the dataset. Applications created with this template will display a status window to track the progress of the upload, and these applications will display any errors in the upload process so the end user is aware of what data was not successfully submitted to the database.
Runtime Walkthrough
Notes on Creating a Data Upload Maintenance Application
Before using this template, the developer will need to create the database table that the template will upload the data (if one does not already exist). This requires that the developer know, ahead of time, the structure that the user will be submitting the data. Additionally, the user will not be able to change either the number of columns or the order of the columns once the table is created.
You can create the table using m-Power or any alternative method way you feel comfortable creating database tables. Once completed, ensure that the table has been registered to m-Power.
Tables created with m-Power can be easily modified within m-Power. For this reason, it is recommended to create your data table with m-Power.
Template Features
Tables/Fields
Here you will see/modify the table you have selected for your application. This template only allows a single table to insert data into, therefore joining to additional tables is not supported.
Field Settings
The dimension key(s) are required to be listed first in the field settings and the order of the fields listed in field settings must match the order of the fields listed in your spreadsheet file. If the order is not correct, use the re-order tool to fix it.
Here you will find the settings for all the fields in the application. You can find out more in detail about these options here.
Dimensions
When building maintenance applications, a developer’s sequencing selection is analogous with selecting a Primary Key. This selection will tell m-Power how this table is unique.
The dimension keys in this template must be the first fields listed in the application. See the Field Settings section for more information.
To learn more about Dimensions, please see here.
Filters
Filters are not supported with the Data Upload Maintenance template.
Calculations
Calculations are not supported with the Data Upload Maintenance template.
External Objects
External Objects are not supported with the Data Upload Maintenance template.
SmartLinks
SmartLinks are not supported with the Data Upload Maintenance template.
Template Properties
The Template Properties screen allows you to adjust specific settings applicable to the template. Depending on the template, some options may or may not be available.
For more on Template Properties, please see here.
Edit Properties
The Edit Properties section allows you to control the behavior of the application. Every m-Power application has properties, however the list of available properties will be different depending on the template selected.
This section below will only focus on properties that are specific to the Data Upload template. A complete list of globally shared properties between all templates can be found here.
Program Options
File Upload Max Size (xlsx) — Maximum size of upload file for .xlsx (in Mb).
Max Rows for Error — Maximum number of errors allowed before upload process stops.
Allow Null Dates — Allow blank date cells to be loaded as null values in the table.
Default File to Upload — Specify the directory and filename on the m-power server to be used when automating the data upload maintainer. More information can be found here.
URL Parameters
run — This parameter allows your Data Upload template to run automatically (such as on a schedule) using the “Default File to Upload” property.
- 0: Do not run automatically
- 2: Run automatically
topLineIsHead — This parameter controls whether the spreadsheet you are uploading has data or column headers in the first row.
- 0: Top line is data
- 1: Top line contains column headers
load_action — This parameter controls how the maintainer will load the data to the table.
- 0: Add
- 1: Clear and add
- 2: Add new and update existing rows
- 3: Add new and skip existing rows