Using the Data Import Suite(MoPro 3.1)

Introduction

The Data Import Suite is a collection of import programs available for use by customers and which utilise XML formatting to simplify the data requirements for creating the import files and improve processing time.

Additional programs may be added in the future, and will utilise the same methodology and rules as the existing programs.

Core to the operation of the Data Import Suite is the MTransform.exe tool which reads a properly laid out comma-separated (csv) file and converts the data into the required import file layout in XML format for ease of importing.

Assumptions

The following assumptions have been made in the following list of considerations:

  • The customer is licensed to use the Data Import Suite
  • The user has security access to the import programs and MTransform.exe tool
  • The MTransform.exe tool has been installed to a valid and available directory

Considerations

The following list includes some issues that need to be considered when undertaking the import of data using the Data Import Suite:

  1. Is the data load to be an update or a replacement of existing data?
  2. Have all the required fields been considered and loaded into the csv file?
  3. Have the column headings been included in the csv file (mandatory for conversion to XML)?
  4. Will the data be loaded to the Test database for initial validation and evaluation?
  5. Does the user have access to the appropriate directories on the network to access the files and MTransform.exe?
  6. Are there any additional security implications (such as access to the appropriate import program)?

Steps

The following steps need to be taken to successfully load data using the Data Import Suite:

No / Description / Program(s) / M/O
1 / Prepare the initial csv file of data to be imported / Manual / M
2 / Convert the csv file to the required XML format for import / MTransform.exe / M
3 / Import the XML data using the appropriate MomentumPro import program as follows:
  • Chart of Accounts User Defined Fields
/ GL Chart of Accounts User Fields Import / M
  • Customers
/ AR Customer Master Import / M
  • Customer User Defined Fields
/ AR Customer User Fields Import / M
  • Price Schedules
/ SP Price Schedule Import / M
  • Products
/ IM Product Master Import / M
  • Product User Defined Fields
/ IM Product User Fields Import / M
  • Product Rename
/ IM Product Rename Import / M
  • Prospects
/ MA Prospect Master Import / M
  • Prospect User Defined Fields
/ MA Prospect User Fields Import / M
  • Stock Take Counts
/ WH Stock Take Count Import / M
  • Suppliers
/ AP Supplier Master Import / M
  • Supplier Products
/ AP Supplier Product Import / M
  • Supplier User Defined Fields
/ AP Supplier User Fields Import / M

1Creating the initial CSV file

Notes

  • The CSV data file must contain two or more rows of data including a first row of headings identifying each column of data
  • Individual column headers need to be provided exactly as shown in the CSV Column Label column of the data format table. In addition the column headers must not have any spaces, and must be provided with capital and lower case letters as shown. An incorrectly specified column header will not be recognised by the MomentumPro and will therefore not be imported
  • The columns of data are not required to be positioned in the data file in any specific order as each column is identified to the transform tool by its column heading
  • It is not necessary to include data columns for data that is not required for the import. That is, it is not necessary to include all the columns in the data file, only those columns that are identified as mandatory for the specific import file format
  • The format of each column must be appropriate to the data contained in that column to ensure Excel does not apply any default formatting
  • Columns must not contain commas in the data, including in text fields
  • If the data in columns exceed the corresponding field lengths in MomentumPro, the data will be truncated to fit the database by the Import program, assuming the data is valid. This will be indicated via a warning on the validation and import reports generated by the import program, and will appear similar to the following:

Steps

  1. Open Microsoft Excel
  2. If available, import data from another source
  3. Remove any unnecessary columns
  4. Insert a line at the top to record column headers
  5. Enter headers exactly as per the file format documentation
  6. Format columns as required by the data
  7. Ensure all mandatory columns are included in the file
  8. Add or edit the data to suit the requirements of both the company and the file format documentation
  9. Save the file as a comma-separated (CSV) file to a directory accessible from the location of the MTransform.exe tool

Sample data

Below is a simple example of supplier product data ready to be saved as a CSV data file. This example shows the minimum columns required to import supplier products and prices and create new supplier product codes. The example could be simplified even further by removing the Description column if we assume that all the supplier products already exist in MomentumPro and we are simply wishing to update the buy price for each product. Note that the first row of data contains the headers for each column.

SupplierProductCode / Description / BuyPrices-BuyPrice
X1-WH / X1 Widget Handle / 120.76
C20-DD-2IN / C20 Doodad 2” / 5.51
H1000-MB / H1000 Mega Bit / 67.20

Note in the above example the Description for the second supplier product includes a “ character. For Microsoft Excel to correctly generate the CSV data file it is vital that the cell format for this column be set to Text. Similarly, the SupplierProductCode column should also be assigned a cell format of Text. This is because typically product codes can be recorded with long numeric codes, and if the default Excel format (General) is used the numeric codes will be automatically altered by Excel to an exponential notation resulting in incorrect or corrupted product codes when the file is saved in CSV data format.

When the CSV data file generated for the above example is opened using Microsoft Notepad or a similar text editor the data will display as follows:

Once the supplier product data has been exported or saved in the CSV data format it is ready to be converted to an XML import file using the MomentumPro Transform Tool.

2Converting the CSV data file to XML

The transform tool provided as part of the Data Import Suite will convert the CSV data file prepared in step 1 to the required XML format required for importing into MomentumPro.

Notes

  • If the transform tool has been used for a previous MomentumPro Enterprise V3.1 release it is necessary to ensure that the versionof MTransform.exehas been updated to the latest version in the MomentumPro 7.02.00 installation folder before attempting to convert any data files

Steps

  1. Run MTransform.exe

When MTransform.exe is executed the screen below will be displayed:

  1. Select the source file to be converted (i.e. the CSV data file from Step 1) via the Browse...button
  2. The results file path will be automatically defaulted once the source file has been selected and will be generated using the same folder and file name as the source file with an “.xml” file extension in place of the “.csv” extension.

To specify a different result file path do so via the Save as... button

  1. The transform method must then be selected from the combo-box list via the downward facing arrow beside the Use transformoption.

In this example, to transform supplier productdata from a .csv file to the required XML format select Supplier Productfrom the transform options as shown below:

  1. Once the files have been selected and specified and the transform option is selected, click the Transform button at the bottom of the screen to complete the operation and generate the XML import file to the results file path specified.

  1. If the CSV data file has been correctly prepared the transform will be completed and the following message window will be displayed:

The resulting Price Schedule XML file is now ready to be imported into MomentumPro.

XML Sample

A transform process performed using the example CSV data file shown in Step 1 will generate the following Supplier Products XML data file. The column headers recorded in the CSV data file have been interpreted by the transform tool to generate the XML file in the required format.

3Loading the XML file into MomentumPro

The MomentumPro Import programs which form part of the Data Import Suite require the XML files created by the transform tool to successfully operate.

Once the XML file for a specific format has been created, it can be imported using the appropriate Import program in MomentumPro.

Notes

  • The successful creation of the XML file does not guarantee success for the import process, as other related information may not have been set up in MomentumPro.
  • The XML file to be imported need not be in the default system Import Files folder, but must be accessible from the MomentumPro application server for the file to be found for importing
  • Files can be validated before actually importing, and will generate a report showing any errors to be resolved. The validation can be run as often as required
  • The option is provided to delete the XML file following a successful import

Steps

  1. Run MomentumPro and locate the required import program from the Data Exchange folder of the appropriate module – in this example the Supplier Product Import program from the Pricing module

The screen will be displayed as below:

  1. Run the required import program to display the wizard as below:
  1. Proceed through the wizard applying criteria as required until the following screen is displayed:
  1. If the XML file has been moved to the default system Import Files folder (which varies between organisations), the required file can be selected from the list of files provided.

If the file is located elsewhere, click the Get local file button and select the file from the appropriate location

Once the Open button isclicked toselect the file it will be copied to the Import Files directory. If a file of the same name already exists in that directory a prompt will appear to replace the existing file.

  1. Once the file is selected in the wizard, click Next to display import options as below:
  1. Specify whether to run the import as a validation only, to check for errors without trying to actually import or update data, or whether to validate and import concurrently

If the file is validated, the validation report will be saved in the following format:

Note that any errors will be identified for resolution prior to running the import again

  1. Specify whether to overwrite existing data by selecting the checkbox to overwrite (only available if import processing option chosen above)
  2. Indicate whether to delete the import XML file once the import has been successful by selecting the Delete import file after load option
  3. Enter a reference if required (this will print on the import report) and indicate when to run the import

The import report will be created as follows:

Note the import processing and number of lines created.

MomentumPro Data Import Formats

As the file format may change as the database changes, and as new import programs are added to the Data Import Suite occasionally, file formats will not be provided in this initial guide, but the latest formats should be requested from Markinson for successful operations of the Data Import Suite.