Updated: May 4, 2020 7:20am

Export/Import Tool

Disclaimer: The Export Import Tool, introduced with Prism 1.14.6.1215 and described in this topic, should only be used under the direction of Retail Pro Technical Support or a Retail Pro Business Partner. Otherwise, use standard Prism Initialization and Day-to-Day replication.
The Prism Export Import Tool helps retailers overcome special challenges in both Initialization and Day-to-Day replication. The tool can work with both Oracle and MySQL databases.

  • RIL/V9-to-Prism Initialization: When initializing a Prism server, certain  resources contain significantly more records than others. They are: Inventory, Customers and Receipts (Documents). Using the Export Import Tool, these resources (and some other supporting resources) are replicated separately in .csv format. Remaining resources must be initialized using standard DRS and Prism Initialization.
  • Prism-to-Prism Replication: During day-to-day replication, certain resources can become bottlenecks. They are: Inventory, Promotions and Coupon Sets. Using the tool, you can replicate these resources separately in JSON format when needed (e.g. when new items, promotions or coupon sets have been added or changed). By exporting/importing these resources separately only when needed, normal Prism day-to-day replication can proceed more smoothly.

Limitations and Requirements
Clean Database of Carriage Returns, Line Feeds and Double-Quotes
Before exporting data using the tool, clean the database (e.g. via a custom SQL script) to remove the following characters:
carriage returns (character code 13)
line feeds (character code 10)
double-quotes ( " )
Database connection requirements
For RIL/V9-to-Prism Initialization, the tool needs a connection with both the source and target databases. The target database for the initialization must be a fresh, clean install of Prism.
For Prism-to-Prism data replication, the tool does not need a connection to both databases. You can simply run the tool on the source or target machine as needed based on whether the operation is an export or import and copy/move the files as needed between machines.
Requirement for Oracle-to-MySql Initialization with tool on MySQL machine
For Oracle-to-MySQL initialization, an RIL or V9 CLIENT INSTALL needs to be installed on the MySQL machine that you intend to import the data to. The client install is used to resolve the location of and connect to the Oracle DB from which data will be exported. If you are running the tool on the Oracle machine and pointing to the MySQL, no CLIENT ONLY install is needed.
For Day-to-Day Prism replication, no CLIENT INSTALL is required for the Day to Day tool. Simply use the tool to export from the sending server. and use it to import at the receiving server after the datafiles are manually brought over to the receiving server.

Launch the Export/Import Resources Tool

1.    Launch the ExportImportResources.exe program. Windows 10 users should use the right-click "Run as Administrator" option to launch the program.
2.    The main menu for the tool is displayed.

export import tool menu

Button Description
Prism to Prism Replication - JSON tool Click the top button (Prism-to-Prism replication JSON tool) to use the tool for Prism to Prism day-to-day replication. This tool can be used to export bulk resources from one Prism database to another (Prism Oracle or Prism MySQL).
RIL to Prism replication - CSV tool Click the second button (RIL to Prism replication CSV tool) to use the tool for RIL/V9 to Prism Initialization. This option can be used to initialize a Prism Oracle or MySQL database
OPTIONS

Click the Options to define default settings for the export and import databases.


RIL to Prism Turbo Initialization

Use the Export Import Tool to initialize a fresh, clean install of a Prism server. The tool facilitates initialization of inventory, customers, document history as well as supporting resources. After initializing the fresh, clean Prism server, some resources will be remaining and still require initialization. You will have to go into Prism Admin Console > Connection Manager and create a profile that includes the remaining resources not initialized by the tool. Using the profile, do a second initialization and then the Prism server will be fully initialized.
Requirements for RIL to Prism initialization:
The import database into which data is imported must be a fresh, clean database.
The export database must be cleaned of bad characters.
If you intend to run the tool on a MySQL machine, first install a CLIENT ONLY install of RIL on the MySQL machine into which data will be imported.
The databases used for the export and import must have an established connection.
Export Data
1.    Make sure the database has been cleaned of bad characters.
2.    Launch ExportImportResources.exe.
3.    Click the second button - RIL to Prism replication CSV tool.
4. The Turbo Initialization screen is displayed:
Turbo init screen
 5.    Click the Ellipses button and navigate to the folder where you want the data exported (create the folder, if necessary).
6.    Click the Export button.
7.    You will see the data being exported. When the process is finished, click OK.
8.    Navigate to the export folder. In the folder you will see a set of .csv files and some python (.py) files listed. You will also see a compressed file. By default, a .7z compressed file is created; if you have WinZip installed, look for a .zip file.
9.    Copy the compressed file to a location where it can be accessed during the data import process.

Import Data into Oracle or MySQL database
Copy the exported .csv files or compressed file to a location (e.g. the local drive) that can be accessed by the machine that will do the import. The exported .csv files can be imported into an Oracle DB or MySQL DB. If the file being imported is a zip file, the user must select the Zip checkbox.
Launch the Export Import Tool and select the "RIL to Prism replication CSV tool" button. In the Import File area of the tool, click the ellipses. Navigate to the location of the compressed (Zip) file created by the export process. Select the file and click Open. You will see the file listed in the Import File field. Click the Zip checkbox.
Click Import.

Post Initialization
Create Profile for remaining Resources and Initialize Again
During RIL/V9 to Prism Initialization, various supporting tables are sent in addition to primary Inventory, Customer and Document resources. The "Exported Tables" section lists these tables. The remaining resources must be initialized using Prism Connection Manager. After initialization of the "bulk" resources using the tool, go into Admin Console > Connection Manager and create a profile that includes the remaining resources (tables) and then initialize Prism using that profile.
Prism-to-Prism Replication
The Prism-to-Prism replication part of the tool facilitates the timely replication of the following resources: Inventory, Promotions, Coupon Sets. This mode is designed for installs that are already initialized and replicating day to day resources. The tool can work with both Oracle and MySQL databases. In this mode, the databases used for the export and import do not need to have an established connection.

Prism-to-Prism Replication - Export Resources
1.    Navigate to the ExportImport folder and launch the ExportImportResources.exe program.
2.    Click the top button "Prism to Prism replication". The Export Import Resources screen is displayed.
3.    Set the Starting and Ending date and time ranges.
4.    Select the individual data types you want to export.

  • Coupon Set: (future release) This option will export all existing coupon sets. Each coupon set could consist of many coupons, so this is resource benefits from using the bulk export capabilities of the Export/Import Tool.
  • Promotion: Selecting this option will export all the promotions from the installation where the tool is being run.
  • Inventory: Selecting this option will export inventory items and styles. Each inventory item has many database fields, so exporting inventory can be quite intensive.

5.    Click Export
6.    Click OK when finished.
export success message

Export Data Settings

Setting Description
Database type Oracle or MySQL
Database name Oracle = rpsods
MySQL = hostname of the MySQL server
Delete json, .csv files after export. The json and .csv files created by the process will be deleted after the export is finished.
Delete 7z The compressed file (in 7z format) will be deleted after the export is finished.
Export File path/Import File path Enter or browse to the path.
Start Date/End Date Click the Calendar to adjust the Start and End Dates as needed.
Exporting On Promotions, Inventory and Coupon Sets can be exported.

Prism-to-Prism Replication: Import Resources
Note: The tool assumes that any required child records are replicated already (e.g. via normal day-to-day Prism-to-Prism replication) to allow the import.

  1. Launch ExportImportResources.exe program on the machine where the data will be imported.
  2. Click the top button "Prism to Prism replication JSON tool."
  3. In the Import area, click the ellipses button and navigate to the folder that has the compressed file of exported resources.
  4. Click Import. In the text box, you will see the data being imported.
  5. When the process is finished, click OK.

import resources

"Created By" Field on Items added to Inventory by Export/Import Tool
If an item exists in the Export database but not in the Import database, the item will be added to the Import database. The Created By field for the item will be set to "bulkimportdatatool" (new setting for Created By field).

Options

In the Options area, configure defaults for the tool (e.g. database type and database name). Carefully consider the values entered here because those values will be entered by default whether using the tool for Initialization or Day-to-Day Replication.
For example, Database type/Database name requirements are different for Initialization and Day-to-Day Replication. So whatever defaults are configured in Options may need to be modified at runtime. Initialization is from RIL/V9 to Prism; therefore, the export database type and name will always be Oracle and rproods. For Prism-to-Prism replication, the Oracle database name is rpsods. One strategy is to configure Options as it should be for Prism-to-Prism day-to-day replication and then modify the defaults at runtime for Initialization, which is typically a one-time operation.
Note: The Import for promotion options are only required for Prism-to-Prism replication of Promotions. If importing promotions, be sure to enter the Import for promotion options before doing the import or the promotions won't fire at POS.

1.    Launch the ExportImportResources.exe program.
2.    Click the Options button. On the Settings screen, select the Database type and database name for the Export.
export import options

Setting Description
(Export) Database Type

The type of database from which data will be exported. Select Oracle or MySQL. Default = Oracle
When using the tool for initialization (CSV), the export database type should always be Oracle.
When using the tool for day-to-day replication (JSON), the export database type can be Oracle or MySQL

(Export) Database Name The name of the database from which data will be exported. Requirements for the Database name are different for Oracle and MySQL database types:
Initialization: The export database type will always be Oracle and the export Database name will always be rproods.
Day-to-day replication: If the export Database type is Oracle, the Database name is rpsods. If the export Database type is MySQL, the Database name is the hostname of the MySQL machine.
Default = rproods
Delete json, csv files after export.

(optional) The export produces a set of files in either JSON or .csv format.
RIL/V9 Initialization files are in .csv format.
Prism-to-Prism replication files are in JSON format.
If the "Delete json, csv files after export" check box is selected, then the individual files will be deleted after the export.

(Import) Database type

The type of database into which data will be imported. Select Oracle or MySQL. Default = Oracle.
The import database type can be either Oracle or MySQL for both Initialization and Day-to-Day replication.

(Import) Database name The name of the database into which data will be imported.
Initialization: If the import Database type is Oracle, the Database name is rpsods. If the import Database type is MySQL, the Database name is the hostname of the MySQL machine.
Day-to-Day Replication: If the import Database type is Oracle, the Database name is rpsods. If the import Database type is MySQL, the Database name is the hostname of the MySQL machine.
Default = rproods
(Import) Delete json, csv files after import (optional) Click this checkbox to automatically delete the individual JSON or .csv files after import.
(Import) Delete 7z file after import (optional) When importing data in compressed file format (7z), the compressed file will be unpacked and the individual .csv or JSON files extracted. Select this option to delete the compressed 7z file after it is unpacked.
Import for Promotions - Prism Host The Import for promotions settings are only required for Prism-to-Prism replication of promotions. Enter the hostname (not the database name) of the machine that will be importing promotions.
Username Enter a valid Prism username to use for the import
Password Enter the password for the entered username.