Introduction
The User Define Table (UDT) module in DotNetNuke (DNN) is a powerful and flexible foundation to construct a solution for many data management needs. A limitation to its adoption and use is the ease of getting table definitions and data migrated from other database environments into UDTs. The UDT Module team has provided an “Import Content” feature that receives a properly formatted XML file. The process can be visualized as follows:
Source: Access, Excel, MySQL, SharePoint, etc.
Obstacles
The construction of the XML files is not trivial. Problems arise like: bitmap images and other binary data are not supported in UDT, the XML file requires hidden field definitions, its layout needs to conform to a UDT schema similar to a UDT export, the UDT XML export includes many more fields than are needed to import, etc.. A utility created by Stefan Cullman named CSV2UDT has been created to simplify the process. However, because CSV files can be so variable it is rare to accomplish the conversion without manually editing either the input or output files.
Actions
The key to a proper XML content file is its format. I thank Stefan Cullman and his CSV2UDT application which provided the format used for the output from this application.
Microsoft Access has been a good environment to accomplish data conversion and cleaning in the past so it was used for the development of this solution, which was accomplished with about 650 lines of VBA code.
Access2UDT Features
- Requires ZERO editing or post-processing of CSV or XML files.
- Supports all common UDT field types (Integer, Text, Currency, etc.).
- Ignores non-importable field types (OLE Object/Binary)
- Supports importing of additional UDT field attributes (Default, Required, etc.).
- Does not alter the source database tables.
- Exports an entire source database in a single pass.