The Export Import Tool 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.
ExportImportResources.exe is really two tools in one, with each tool used for specific tasks:
- Prism to Prism replication JSON tool: During day-to-day replication between Prism servers, certain resources (Inventory, Promotions, Coupons Sets) can become bottlenecks because of the size and complexity of the resources. Use the tool to export/import any or all these resources in JSON format only when needed (e.g., when new items, promotions or coupon sets have been added or changed). Replicating these complex resources separately, only when needed, results in faster day-to-day replication. Note: This area of the tool is intended for use an already-initialized Prism system that is currently replicating D2D.
- RIL to Prism replication CSV tool: The RIL to Prism replication CSV tool plays a key role in helping retailers migrate from legacy versions of Retail Pro. Retail Pro 8 data can be exported to .csv format using V8ToPrism.exe and then imported into a new Prism server using ExportImportResources.exe. Retail Pro 9 users will use ExportImportResources.exe for both the export of RIL (V9) data and the import of that data into a NEW Prism database into the default SBS001.
Sample Export Import Tool main screen showing the two separate tools available:
Download the ExportImport tool from the following location: https://my.retailpro.com/Solutions/RPPrism/Downloads/
Copy the ImportExport folder containing ExportImportResources.exe to the machine where data will be exported or imported. If exporting data, you will typically want to add a new subfolder to store the exported .csv files.
Double-click ExportImportResources.exe to launch.
Refer to the following table for information about the buttons on the main screen:
|Prism to Prism replication JSON tool (Oracle and MySQL)||Click this button to use the tool for bulk replication of coupon sets, promotions, and inventory between Prism servers. The Prism servers can both be Oracle or can be Oracle and MySQL.|
|RIL to Prism replication CSV Tool (Oracle to Oracle and MySQL)||Click this button to use the tool for migrating data from RIL (or Retail Pro 9) to Prism. This means the data is exported from Oracle and can be imported into an Oracle database or a MySQL database.|
|Options||The Options area has two main areas:
The settings on the left side are for RIL-to-Prism initialization. Set the Database Type and Database Name of the Export and Import systems. Select the level of detail to export.
On the right side are some settings used for Prism-to-Prism bulk replication of promotions. Set the Prism host and login credentials for the system into which the promotions will be imported.
|Exit||Click to exit ExportImportResources.exe|
RIL to Prism Initialization
This section explains how to use the RIL to Prism replication CSV Tool to export data from RIL (or Retail Pro 9) in .csv format and import the data into a new Prism database into the default subsidiary 001. This provides a convenient way for retailers to migrate from legacy versions of Retail Pro to Prism.
When exporting data, not all data is exported. Only those data types selected in the tool's "Options" area will be exported. The data that can be exported from RIL/V9 and imported using the tool includes Inventory, Customers and Receipts as well as the mandatory parent tables for each such as subsidiary, store etc. See the tables list in the "Exported Tables" section. All other resources must be initialized using standard initialization.
The tool can be used to move data from Oracle to Oracle or Oracle to MySQL. Both databases must be available when using the tool. The Prism import database must be a fresh clean install for the tool to work.
Before export, it is important to clean the data of carriage returns and line feeds and then run the "Check" to check for common errors.
1. Before exporting, clean data to remove carriage returns (character code 13) and line feeds (character code 10)
2. Configure TNSNAMES.ORA on both the source and target systems to point to the other machine.
3. Configure Options area of ExportImportResources.exe to select the data types to export.
4. Check data for errors.
5. Export Data.
6. Import Data.
- The target database for the initialization must be a fresh, clean install of Prism.
- Before exporting data, clean the database to remove carriage returns (character code 13) and line feeds (character code 10).
- Configure TNSNAMES.ORA on both the source and target systems to point to the other machine. Note: Only required when source and target are both Oracle databases, or when running the tool from a target machine running MySQL that has an RIL/V9 Oracle client installed.
- For Oracle-to-MySQL initialization, you must install an RIL/V9 Oracle client install on the MySQL machine where you intend to import the data. The client install is used to resolve the location of and connect to the Oracle DB that is exporting the data. If running the tool on the Oracle machine and pointing to the MySQL machine, no Oracle client install is needed.
- You must configure the Options area of ExportImportResources.exe correctly based on whether the tool is being used to export or import data.
Before exporting data, clean the export database to remove carriage returns (character code 13) and line feeds (character code 10).
Define Database Names in TNSNAMES.ORA
For initialization, a connection must exist between the source and target machines. At both source and target machines, edit the tnsnames.ora file in each of the following two locations to include an entry for the other machine:
If you are on the original server (the server from which data is being exported), add an entry with information about the new server (the server into which data will be imported).
If you are at the new server, add an entry with information for the original server.
Example of TNSNAMES.ORA file with new entry added for the other server:
Configure the Options area of the Tool
Configuration of the Options area will differ at the source machine and target machines.
At the original server, enter the hostname of the new server in the "Import" section.
At the new server, enter the hostname of the original server in the "Export" section.
Check Data for errors
Check the data for errors before exporting to .csv files. The benefit of doing the check is that depending on the amount of data, the export/import process can take several hours. The check process will analyze the data looking for common problems that can cause the import to fail. Taking a small amount of time to analyze the data for common problems before the export often saves time in the long run.
When a user clicks the "Check" button, the tool will analyze the data in the database of the export system (not in export files) for known (common) errors. If any of these errors are found, the user is alerted so the problems can be corrected BEFORE exporting to .csv. An example of the type of problem that the tool checks for is an employee entered twice in the database, once with a normal name, once with an unnecessary space after the name.
The check is only for a limited set of known problems and is intended to avoid spending hours or even days importing data only to have an error occur. When an error occurs during import, the user must restart the process from the beginning. Doing the check for common errors seeks to avoid this type of situation. However, even with the check, it is still possible the exported data will cause errors upon import.
Note: Before exporting data, make sure you have cleaned the database to remove: carriage returns (character code 13) and line feeds (character code 10). Make sure you are exporting from the "rpsods" database (default is "rproods")
1. Launch ExportImportResources.exe. Click the second button RIL to Prism replication - CSV tool.
The Turbo Initialization screen is displayed:
2. Click the ellipses and navigate to the folder where data will be exported (create the folder, if necessary).
3. To export the files in compressed format (7z), click the Zip button.
4. Click Export.
5. You will see the data being exported. When the process is finished, click OK.
6. Navigate to the export folder. In the folder you will see a set of .csv files. Child tables needed by the resources are exported, too (e.g., address_type for customers and DCS for inventory). If you selected the Zip checkbox on the Export screen, you will also see a compressed file.
7. Import the files at the new machine.
1. Launch the Export Import Tool and select the "RIL to Prism replication CSV tool" button.
2. In the Import File area of the tool, click the ellipses.
3. 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.
4. Click Import.
5. In the text box, you will see the data being imported. When the process is finished, click OK.
"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 "PrismMQ".
Resources included in Initialization
The tool only initializes Inventory, Customers and Documents (Receipts). Other data must be initialized/replicated using normal methods.
This section explains how to use the Prism to Prism Replication tool to replicate specific large data resources (Promotions, CouponSets and Inventory resources only) that tend to produce bottlenecks during Day2Day replication. In many cases, these resources do not change frequently; therefore, it makes more sense to replicate these resources when needed using the tool and thus allow normal D2D replication to go more smoothly.
The tool is designed for an install that is already initialized and replicating day to day resources. The tool can work with both Oracle and MySQL database types. In this mode, the databases used for the export and import do not need to have an established connection. Simply use the tool to export the resources at the desired location, copy the files to the target location and then use the tool to import the files.
NOTE: 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.
Each of these basic steps are explained in more detail in the sections that follow.
1. If exporting promotions, configure the settings for exporting promotions in the OPTIONS area of the tool
2. Export data.
3. Import data.
Configure Options for Exporting Promotions
In the Options area, for Promotions, enter the hostname of the Prism system into which Promotions will be imported. Enter the Prism username/password for connecting to the host system.
1. Launch the ExportImportResources.exe tool.
2. Click the top button "Prism to Prism replication JSON tool." The Export Import Resources screen is displayed.
3. Set the Start and End of the date range. The date range determines which records will be included in the export. Records (for the selected data types) created or modified within the date range will be exported.
4. Select the individual data types you want to export.
- Coupon Set: This option will export all existing coupon sets. Each coupon set could consist of many coupons.
- 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.
Navigate to the export folder and you will see a .json file for each selected data type as well as a compressed file that will be used for the data import.
Note: About the Import for Promotion Options
Important! Before importing promotions, you must enter the "Import for promotion" settings in the Options area of the tool. If you don't enter the "Import for promotion" settings and then import promotions, the promotions will not trigger at POS.
For Prism-to-Prism replication, copy the exported 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 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. The tool assumes that any required child records are replicated already (e.g., via normal day-to-day replication) to allow the import.
1. Launch ExportImportResources.exe.
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.
This section has additional information about configuring the Options area of the tool. The Options area is where you specify the Database Type and Database Name of the other server when using the tool for initialization. When using the tool for Prism-to-Prism replication, the Options area has certain settings required for replicating promotions.
Options screen fields
Refer to the following table for information about the available fields on the Options screen.
|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. At the source machine, the export Database name will always be rproods. At the target machine, set the export Database name to the source machine.
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
|Export Level||Select the desired level of detail for the export:
1 - All Main Tables: (default) If selected, all main tables are exported
2 - Customer (with 1): If selected, all main and customer tables are exported.
3 - Inventory (with 1 and 2): If selected, all main, customer, and inventory tables are exported.
4 - Document (with 1, 2 and 3): If selected, all main, customer, inventory and document tables are exported.
|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.|
|Import - Database Name||Enter the hostname of the machine into which data will be imported.
Initialization: At the source machine, set the import Database name to the name of the target machine. At the target machine, if the Database Type is Oracle, leave the Database name set to rpsods. If the Database Type is MySQL, set the Database name to the hostname of the MySQL machine.
Day-to-Day Replication: If import Database type is Oracle, Database name is rpsods. If import Database type is MySQL, Database name is the hostname of the MySQL machine.
Default = rproods
|Delete json, csv files after import||(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.|
|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 Promotion||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.
|Prism Username||Enter a valid Prism username to use for the import.|
|Prism Password||Enter the password for the entered username.|
When initializing Prism, the following tables are exported..
When a user runs the import process using ExportImportResources.exe, the tool creates a "\Logs" folder within the main tool folder and writes entries to the log file(s) as data is imported.
Errors captured in the log file are indicated with a string of arrow characters "<<<…" Therefore, to search the log file for errors, search for a string like "<<<".
Sample Errors Captured in Log Files
Problem: The records being imported have spaces after the sbs_sid and employee name
02.09.2021, 20:49:17.914 Executed with 10 Messages <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
02.09.2021, 20:49:17.974 Duplicate entry '608237451000113004-POS ' for key 'employee.idx_employee1'
02.09.2021, 20:49:18.022 Duplicate entry '608237451000113004-RASHA ' for key 'employee.idx_employee1'
02.09.2021, 20:49:18.068 Duplicate entry '608237451000113004-POS ' for key 'employee.idx_employee1'
Problem: A child record is not present
02.09.2021, 20:49:19.039 Executed with 3 Messages <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
02.09.2021, 20:49:19.101 Cannot add or update a child row: a foreign key constraint fails (`rpsods`.`employee_subsidiary`, CONSTRAINT `employee_employee_sbs` FOREIGN KEY (`empl_sid`) REFERENCES `employee` (`sid`) ON DELETE CASCADE)
02.09.2021, 20:49:19.147 Cannot add or update a child row: a foreign key constraint fails (`rpsods`.`employee_subsidiary`, CONSTRAINT `employee_employee_sbs` FOREIGN KEY (`empl_sid`) REFERENCES `employee` (`sid`) ON DELETE CASCADE)
Problem: Data field lengths were shorted to accommodate the database structure of the importing system. This is not a problem that requires interaction; it is expected behavior.
03.09.2021, 12:03:43.272 Checking for Db Messages
03.09.2021, 12:03:43.304 Executed with 496 Messages <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
03.09.2021, 12:03:43.337 Data truncated for column 'sale_subtotal_with_tax' at row 8446
03.09.2021, 12:03:43.398 Data truncated for column 'sale_total_amt' at row 8446
03.09.2021, 12:03:43.429 Data truncated for column 'transaction_subtotal_with_tax' at row 8446
03.09.2021, 12:03:43.474 Data truncated for column 'sale_subtotal_with_tax' at row 8447
03.09.2021, 12:03:43.523 Data truncated for column 'sale_total_amt' at row 8447
03.09.2021, 12:03:43.570 Data truncated for column 'transaction_subtotal_with_tax' at row 8447
When errors occur in the export or import process it can be difficult to precisely pinpoint the source of the error due to the different database schemas used by Prism and legacy versions of Retail Pro. Below is a list of lookup dictionaries which are used to translate certain data from one schema to the other. The Export Import tool log may reference a specific dictionary when there are errors looking up and referencing a specific piece of information for inclusion in another table. The ‘dictionaries' are created at runtime and not stored as a file.
For example: There are times when correcting an error may be as simple as changing a trailing space in an employee name to another character to prevent a potential duplicate entry (trailing spaces may be trimmed). At other times an indexed list in preferences (such as Reason Code) may be required to have an assigned SID in the new schema so that the imported SID can replace an index value stored on the exported document. The export/import process uses these dictionary entries to provide such a cross reference.
For each dictionary in the table on the following page the ‘Key=' value(s) are what will be shown in the log to assist in understanding what you are looking at.
*sometimes a Get "0" could also represent a null or blank value was encountered.
Here are some examples to aid in understanding the errors:
Log: TDictExpSubsidiary: Missing Key for Get "0"
Looking up ‘TDictExpSubsidiary' in the table you will note that the ‘Key=' value represents the SbsNo.
This tells you that while attempting to apply the SbsSID to some document or item to be imported, the import tool attempted to cross reference the SbsNo to the SbsSID and an entry was not found in the dictionary. This means that some document has a SbsNo of "0" and the dictionary that was created in memory to cross reference SbsNo to SbsSID does not contain an entry for SbsNo=0.
Log: TDictExpCountry: Missing Key for Get "1"
Looking up ‘TDictExpCountry' in the table you will note that the ‘Key=' value represents the CountryID.
This tells you that while attempting to apply the CountrySID to some document to be imported, the import tool attempted to cross reference the CountryNo to the CountrySID and an entry was not found in the dictionary. This means that some document has a CountryNo of "1" and the dictionary that was created in memory to cross reference CountryNo to CountrySID does not contain an entry for CountryNo=1. It could be that CountryNo=1 is in the exported data but no valid CountryName or CountryCode existed so that the entry could not be assigned a SID.
Log: TDictExpReasonName: Missing Key for Get "605149445000107257 10 0"
Looking up ‘TDictExpReasonName' in the table you will note that the ‘Key=' value represents SbsSID, ReasonType, and ReasonID. This tells you that SbsSID, ReasonType is in the inventory, document, voucher, or adjustment data but when attempting to look up a ReasonID=0 (the first in the list), it is not present in the dictionary.
|TDictExpSubsidiary||Key = SbsNo|
|TDictExpStore||Key = SbsSid, StoreNo|
|TDictExpAllocationPattern||Key = SbsSid, PatternID|
|TDictExpDCS||Key = SbsSid, DCSCode|
|TDictExpSeason||Key = SeasonID|
|TDictExpPriceLevel||Key = SbsSid, PriceLevel|
|TDictExpCurrency||Key = CurrencyID|
|TDictExpTaxCode||Key = SbsSID, TaxCode|
|TDictExpTaxArea||Key = SbsSID, TaxAreaID|
|TDictExpTaxRule||Key = SbsSID, TaxAreaSid, TaxRuleCode|
|TDictExpScale||Key = SbsSID, ScaleNo|
|TDictExpScaleAttribute||Key = SbsSID, ScaleNo, Attribute|
|TDictExpScaleSize||Key = SbsSID, ScaleNo, Size|
|TDictExpScalePattern||Key = SbsSID, ScaleNo, PatternName|
|TDictExpVendor||Key = SbsSID, VendCode|
|TDictExpMediaType||Key = SbsSID, MediaTypeID|
|TDictExpKitComponent||Key = SbsSID, ComponentID|
|TDictExpInventoryUDF||Key = UDFID|
|TDictExpTitle||Key = TitleID|
|TDictExpCountry||Key = CountryID|
|TDictExpCustomerClass||Key = CustomerClassID|
|TDictExpCompany||Key = CompanyID|
|TDictExpAddressType||Key = AddressTypeID|
|TDictExpCustomerUDF||Key = UDFID|
|TDictExpInventory||Key = SbsSID, Invn_UID(V9 SID)|
|TDictExpInventoryLot||Key = SbsSID, item_sid, LotNumber|
|TDictExpInventorySerial||Key = SbsSID, item_sid, SerialNo|
|TDictExpTill||Key = TillID|
|TDictExpJob||Key = JobID|
|TDictExpReason||Key = SbsSID, ReasonType, ReasonID|
|TDictExpReasonName||Key = SbsSID, ReasonType, ReasonID|
|TDictExpEmployee||Key = EmplID|
|TDictExpDocumentFeeType||Key = SbsSID, FeeType|
|TDictExpTenderType||Key = InvcSid|
|TDictExpMissedDocument||Key = InvcSid|
|TDictExpVendorUDF||Key = UDFID|