RP8 Import Utility (V8ToPrism.exe)
[Disclaimer: This tool is scheduled for release with Prism 1.14.7]
The RP8 Import Utility enables legacy Retail Pro 8 users to export RP8 data and import it into Prism without first bringing the data into Retail Pro 9. This direct import path simplifies the data migration process for RP8 users.
The RP8 Import Utility uses two different programs for the export and import:
1. Data is exported from Retail Pro 8 using V8ToPrism.exe. This step transforms the RP8 data into a set of .csv files suitable for import into Prism.
2. The .csv files are imported into Prism using ExportImportResources.exe.
Both V8ToPrism.exe and ExportImportResources.exe are available in the /PrismTools folder of the Prism installation files.
To ensure RP8 data is exported successfully, the utility requires certain information about the Prism server into which the data will be imported and instructions for handling certain differences in database formats, fields and program logic.
Before You Begin
1. Run the V8ToPrism.exe tool at the Retail Pro 8 Main station.
2. Complete a full data replication cycle before running the V8ToPrism.exe tool (make sure the Retail Pro 8 Main has received and processed in all receipts and other documents from stores).
3. Make sure the Retail Pro 8 data and history files are on the local machine or available via a mapped network drive.
The following considerations will impact how users should configure the utility:
Does V8ToPrism.exe have access to the Prism system?
Ideally, the Prism server into which the files will be imported is on the same machine as the RP8 Main and V8ToPrism.exe (or available via a mapped network drive). If the tool has access to Prism, information about the Prism server can be pulled directly from Prism. If the tool does not have access to the Prism system, the user must obtain the information from the Prism system and then enter the information manually in the V8ToPrism.ini file.
Does the Prism system have existing subsidiaries?
Data from RP8 is imported into a single Prism subsidiary that you specify when configuring the utility. Ideally, data is imported into a "clean" Prism system; however, there may be one or more existing subsidiaries whose data needs to be preserved. The following table has information about how to handle these different scenarios:
|Clean Prism system||No data has been entered in the default Prism SBS 001, so the RP8 data can be put in SBS 001. Required configuration:
|Prism system with existing subsidiaries||Set the Subsidiary No and Name to the next available SBS number. For example, a retailer who has been using Prism SBS 001 for general operation of some stores by entering data directly into Prism and RP9. Now the retailer wishes to import the last remaining RP8 Main station into RP Prism. Required configuration:
Subsidiary_No=(next available number)
Subsidiary_Name=Sub(next available number)
The utility will bring the data into the database and create the chosen SBS with all required system information.
|Import RP8 Data into default SBS 001 that has existing data||Although not recommended or supported, it is possible for a user to manually enter data in the default SBS 001 and export RP8 data to the same subsidiary (e.g. by clearing the Export Subsidiary checkbox and setting Subsidiary No to "1." In this case, the data exported from RP8 will be forcibly merged with the existing user-created data in Prism SBS 001.|
1. Navigate to the …\PrismTools\V8ToPrism folder and launch V8ToPrism.exe. The home screen has fields for browsing to the path to the RP8 data and history files.
At the bottom of the screen is a set of buttons for navigating the tool:
|About||Displays version information for the tool.|
|Prism||Displays a screen for defining Prism-related settings and read data from Prism.|
|Preferences||Displays a screen for defining formatting options and map store address fields.|
|Process||Starts the process of generating .csv files.|
About the V8ToPrism.ini file
When you launch the V8ToPrism.exe the first time, the tool creates a configuration file: V8ToPrism.ini. This is a standard Windows .ini file (i.e. a standard text file). The settings in the .ini are read and used to generate .csv files when the user clicks the Process button. These settings include required information about the Prism subsidiary into which the RP8 data will be imported. Default values are entered in the .ini file. If the Prism server is on the same network as the machine running V8ToPrism.exe, you can use the Read Lists/Read Data feature to populate most of the fields of the .ini file. If the Prism server cannot be reached, users must manually edit the .ini file. Some options are only available in the .ini file. Important! Do not edit the .ini file with Microsoft Word. Microsoft Word will add invisible information that can cause trouble when the program reads the file. Instead, use a text editor like Notepad or Notepad++. If Prism is not on the same machine as the RP8 Main where you are running the tool, then the user must edit the .ini file will be necessary (e.g. entering SIDs).
Path to Data and History Files
The main screen of the V8ToPrism.exe tool has fields for entering the path to the RP8 data and history files that you want to export. By default, these files are saved to the …\Retail\Rpro folder (e.g. C:\Retail\Rpro).
1. Enter or browse to the path for the Retail Pro 8 data files and history files.
2. When finished, click the Prism button and enter information about the Prism server into which the RP8 data will be imported.
If the V8ToPrism.exe tool has access to Prism, click the Prism button to enter information about the Prism system into which the RP8 data will be imported. This information is needed to resolve differences in the RP 8 and Prism data structures. (Note: Some options are only available in the .ini file.)
1. Edit Prism Db Name, Subsidiary No, Subsidiary SID and Subsidiary Name, if needed.
2. Click Read Lists. If the tool has access to Prism, the lists are populated with data.
3. Select the correct Country Code and Currency Code from the drop-down lists.
4. Click Read Data. SID and Name fields are populated.
Subsidiary No, Subsidiary SID, Subsidiary Name
By default, when you launch the V8ToPrism.exe tool and go into the Prism screen, the Subsidiary No is set to "2." You might wonder why the Subsidiary Number is set to "2" instead of "1." This is because the Prism install program creates a default subsidiary (SBS 001). Ideally, the RP8 data will be exported to a clean Prism system, but this may not always be the case. By setting the default Subsidiary No in the tool to "2," data in Prism SBS 001 will be preserved and RP8 data will be placed in a new SBS 002.
Guidelines for editing the Subsidiary No, Subsidiary SID and Subsidiary Name
Clean Prism System: Change the Subsidiary No to "1" and change the Subsidiary Name to "Sub 1." The RP8 data upon import into Prism will go into the empty default SBS 001.
Existing subsidiaries: Set the Subsidiary_No and Subsidiary_Name to the next available number. For example if the Prism server has SBS 001 and 002, change the tool's settings to Subsidiary_No=3 and Subsidiary_Name=Sub3. When the RP8 data is imported into Prism, a new Sub 3 will be created to store the RP8 data.
If the tool is being run is on the same machine as the Prism system, click Read Lists to fill in the lists of Country Codes and Currency Codes, etc. Once the lists are filled in, select the desired values from the drop-downs. [Note: These are global values in the database; not subsidiary-specific values.]
After selecting the desired Country Code and Currency Code, click Read Data. When you click Read Data, corresponding SIDs and Name fields are populated. Note: The Active Price Level SID and Active Store SID are NOT populated.
Click OK. When you click OK, the information is written to the .ini file.
Click Clean Prism.
Prism Screen Settings
Refer to the following table for information about the settings on the Prism screen:
|Prism Db Name||Database name. Default = rpsods|
|Prism Db Type||Select a database type from the dropdown: Oracle or MySQL. (0=Oracle; 1=MySQL)|
|Subsidiary No||The default Subsidiary No is "2" because the Prism install creates a default SBS 001 and users might enter data in SBS 001 and/or create additional subsidiaries. Consider any existing subsidiaries and whether the data in those subsidiaries needs to be preserved. For example, if Prism has SBS 001 and 002 and you want to keep the existing data, set Subsidiary_No=3. This will preserve data in Prism Sub 1 and Sub 2 and create a new Sub 3 for the RP8 data.
If you change the tool's Subsidiary_No=1 the data import will overwrite data entered in the Prism SBS 001.
Default = 2
|Subsidiary SID||The Subsidiary SID of the subsidiary into which the RP8 data will be put. This data will be filled in/updated when the user clicks the Read Data button.|
|Subsidiary Name||The Subsidiary Name of the subsidiary into which the RP 8 data will be put.|
|Export Subsidiary||If enabled, indicates subsidiary information should be exported. Enable the setting if RP8 Data will be exported into a Prism system with existing subsidiaries. This will put the RP8 data into a new Prism subsidiary, preserving the existing subsidiaries. If disabled, indicates subsidiary information should not be exported. This is the proper setting if RP8 data will be imported into a clean Prism system into the default SBS 001. Default = Enabled|
|Controller SID||Unique system ID for the controller record. Used to reference the CONTROLLER table.|
|Tenant SID||Unique system ID for the tenant record. Used to reference the TENANT table.|
|Country Code||By default, USA is the only Country Code available. Click Read Lists and then select a Country Code from the dropdown (e.g. FRA for France).|
|Country SID||Unique system ID for the country. Used to reference the COUNTRY table.|
|Country Name||Country name. When you click Read Data, the tool will update the Country Name field based on the selected Country Code.|
|Currency Code||Select a Currency code from the dropdown (e.g. USD, JPY).|
|Currency SID||Unique system identifier for the currency. Used to reference the CURRENCY table.|
|Currency Name||Currency name (e.g. US Dollar, Japanese Yen).|
|Season SID||Unique system identifier for the currently active Season. Used to reference the SEASON table.|
|Active Price Level SID||Unique system identifier for the currently active price level. Used to reference the PRICE_LVL table. Data for this field is not pulled from Prism. The data will be filled in when the user clicks the Process button.|
|Active Store SID||Unique system identifier for the currently active Store. Used to reference the STORE table. Data for this field is not pulled from Prism. The data will be filled in when the user clicks the Process button.|
Click the Preferences button on the main screen to customize the following settings:
Formatting Options: Customize how price levels, stores and tax areas are displayed. This enables these to be displayed in a manner familiar to users.
Mappings between RP8 Store Header fields and Prism Store Address fields: RP8 and Prism handle store addresses differently. RP8 uses a set of "Store Header" fields while Prism uses a set of "Store Address" fields. Map theRP8 Store Heading field to Prism Store Address field. Default = "0" or None.
When you modify a format, the "Example" area will dynamically update to show how the new formatting will appear. The maximum number of characters allowed for each field is shown on the far right.
Example: Format Price Level Name: %0:s-%1:s
|%||Indicates the start of a new formatting instructions|
|0||Indicates which parameter of the Price Level Name value is being referenced by the formatting instruction (using zero-based numbering).|
|:s||Displays the parameter string being referenced. In the example, both the first and second parameters (0 and 1 using zero-based numbering) display the string "1."|
Field Source: Store Mapping
For each RP8 Store Address field listed, select the field to which it should be mapped. Select None if the Store Address field is not used. Select which field to map to the Prism Zip field. Select None if the Zip field is not used. For each V8 phone field, select which Prism field to which it should be mapped. Select None if the phone field is not used.
Sample V8ToPrism.exe Preferences
Displays the parameter string being referenced. In the example, both the first and second parameters (0 and 1 using zero-based numbering) display the string "1."
After entering the path to the RP8 data and history files and configuring the Prism and Preferences screens, click the Process button on the main screen. When you click Process, the RP8 data is converted to a set of files in .csv format
1. Click Process.
2. The .csv files are generated and saved to the \PrismTools\V8ToPrism\Out\Save folder.
3. Copy the exported .csv files 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.
History Documents that Reference Deleted Items, Vendors or Departments
In certain situations, the history files exported from RP8 may include documents with items that were later deleted from Inventory (e.g. using Clean House). For such a document item to be imported into Prism, an item needs to be added to the Prism database that has this deleted item's item number and UID (unique ID, the RP8 SID). In this situation, the utility will create a basic Inventory item (set to Inactive status) using the item information on the history document. This basic information includes UPC, Description 1 and Description 2. Deleted items that are recreated in this situation will not have ALU, Description 3, or Description 4 information.
In a similar manner, if the Vendor or Department for an item on a history document has been deleted, the utility will create a basic Vendor or Department record using the information on the history document.
Import RP8 Data into Prism
The ExportImportResources.exe can be found within the installation (or upgrade) folders. Navigate to the PrismTools\ImportExport folder.
1. Use the right-click "Run as Administrator" option to launch ExportImportResources.exe
2. Launch the Export Import Tool and click the RIL to Prism replication CSV tool button.
3. In the Import File area of the tool, click the ellipses.
4. Navigate to the location of the .csv files. Clear the Zip checkbox. Select the files and click Open.
5. Click Import. In the text box, you will see the data being imported. When the process is finished, click OK. After the import, return to the V8ToPrism.exe tool. Click the Prism button. On the Prism screen, click Finish Prism.
Verify/Configure Prism SBS Settings
1. Navigate to the Admin Console > Node Selection.
2. Click the pencil icon for the subsidiary.
3. Edit the following settings, if necessary: Price Level, Base Currency and Season.
4. Click Save & Exit.
Appendix: V8ToPrism.ini File
The RP8 Import utility exports data from RP8 according to the instructions in the V8ToPrism.ini file. This section reviews section-by-section the information found in the V8ToPrism.ini file. Note: Blank lines are added to this document to separate out the sections of the .ini file.
Some sections of the .ini file start with "Desc0=0:None, 1: …, 2: …, " . These lines are not used by the tool itself; they are labels to provide the user with information. The lines help the user know the available entries for the field. An example is the [Store_Field_Source] which shows the user the RP8 Store Header field assigned to each Prism Store Address field.
This section sets the Prism database name and type. For Oracle, the Db_Name is the name entry in the tnsnames.ora file (default=RProODS).
|Db_name||The name of the database. Default = RPSODS|
|Db_Type||0 = Oracle
1 = MySQL
Note: This section is not currently used.
This section has general system preferences.
|PrefNow||The preference now is to override the timestamp it puts on the records. This value is for advanced users and should left at the default of 0.|
|Text_File_Limit||Text_File_Limit is a limit on the size of some of the exported .csv files. The .csv files that are controlled by this parameter will have the name format FileName_#.csv. Examples are Inventory and Document:
As the tool is creating the files, if the file size reaches the Text_File_Limit, the tool will close the file and create a new one with the next number. It will then continue to export the data into this new file. It will continue until it has exported all the data. The number of files will be controlled by the total size of the data, and the size limit of the files. If the tool needs to export 100 MB of data and the Text_File_Limit is 1 MB, the tool will export 100 files.
|DataPath||DataPath is the path to the main data files in V8. This will be where you find files such as Invn.data and Price.dat. This will usually be something like C:\Retail\RPro|
|HistoryPath||HistoryPath is the path to the history files. Examples of files found in this path:
The HistoryPath is usually C:\Retail\RPro (the same as the DataPath).
|Subsidiary_Export||Subsidiary_Export controls if the tool will export the subsidiary information. If you are transferring (exporting from V8 and importing into Prism) into an existing subsidiary, you do not want to export subsidiary information. Instead, you will use the information that is already in Prism.
If you are transferring .csv files to a new Subsidiary, you will need to export the subsidiary information so it can be imported into the Prism database.
|Subsidiary_Number||The number of the subsidiary into which the V8 data is being transferred.|
|Subsidiary_Name||The name of the subsidiary into which the V8 data is being transferred.|
|Ws_No||The V8 workstation from which preferences are pulled. Default=1|
The SID section is the collection of SIDs needed to export the data. This is all the SIDs that need to be collected from the Prism system to export the data. The one exception is Currency_SID, which is in the [CURRENCY] section. All the fields in this section are SIDS, except for the Country_Code field (the Country_Code is used to extract the Country_SID from the Prism database).
|Subsidiary||Subsidiary SID is the SID of either an existing Subsidiary to which the data is being transferred, or the SID of a new subsidiary to create.|
|Controller||Generated by Prism when Prism is installed.|
|Tenant||Generated by Prism when Prism is installed.|
|Country_Code||The country to be used by Prism|
|Country||The SID corresponding to the Country_Code|
|SID||This is the SID for the base currency used by Prism. Export controls whether the currency information is exported to Prism. At this time, Prism contains all the known currencies so no currency should be transferred from V8 to Prism; therefore this field should be set to 0.|
|Code||Code is the currency code as defined by Prism. This is used to look up the currency SID in the Prism database.|
|Name||This is contained in the Prism database and is the actual name Prism uses for the base currency.|
|Decimals||The number of decimals used with the base currency.|
|Rounding||0 = No rounding used; 1 = Rounding used.|
The Season section has the Season SID.
|SID||The SID for the season used by Prism. The tool uses the SID for the ‘None' season.|
This section contains the SIDs of the active price level and the active store. The data is collected as the tool exports the data from the V8 database. It will be available after the data has been exported.
This section controls the creation of employees. When an employee record is created, it also creates child customer record. This record requires a customer ID.
|CustIdPref||The customer ID with its prefix. The prefix is added to the beginning of the employee number to create the customer number. An example using the prefix 99999 would be 99999001|
The address type section controls the creation of the address type table entry. This will be the entry for all address types used in transferring the data from V8 to Prism.
|SID||The SID of the address type.|
|Name||The default name is "Home"|
Some Prism fields are not available in V8. These fields could be left blank, or they can be generated by the V8-to-Prism tool. To give some flexibility in the generation of these fields, a way has been made to control this process.
- Static text: e.g. "This is a test." The text will be entered into the field for every relevant record in the database.
- Substitution fields: These have the format "%#:s" where the number (#) sign is an actual number. Examples are "%0:s" and "%2:s". The # is replaced by data from the V8 database. The field used depends on the table being used to create the data. For example, when exporting the price level table, Field0 is the subsidiary number and Field1 is the price level code, also a number. Let's say data is being exported into Subsidiary 5. The tool is exporting the price level record with a price level code of 9. If you have the format string Sub:%0:s-PrcCd:%1s, the actual field will contain "Sub:5-PrcCd:9"
|Price_Lvl_Name||Used when transferring the price level table. Field0 is the Subsidiary number and Field1 is the Price Level Code.|
|Store_Code_Local||Store information. Field0 is for the Subsidiary number and Field1 is for the Store Code.|
|Store_Code_Global||Store information. Field0 is for the Subsidiary number and Field1 is for the Store Code.|
|Store_Name||This field is handled differently than the other fields. This field controls the default store name field if it is chosen by the store field source for store name.|
|Alt_Tax_Area_Name||Alt_Tax_Area_Name is for transferring tax information. Tax code is handled separately. This takes the single v8 TAX_AREA table and creates three Prism tables: TAX_AREA, TAX_RULE and TAX. Field 0 is Subsidiary No, Field 1 is Tax Area Name, Field 2 is Tax Code Number, Field 3 is Tax Code. Alt-Tax_Area_Name is a maximum of 10 characters, so it defaults to field 3.|
Store_Field_Source, Inven_Field_Source, Customer_Field_Source, Invoice_Item_Field_Source
There are several sections that control the source of the fields being transferred from RP8 to Prism. Some of them have default values. Some of them default to 0, which will make those fields blank in Prism. The reason why this was done was that there are fields in RP8 that do not have a direct counterpart in Prism. This allows us to control which RP8 field is mapped to a Prism field.
Each type has a set of entries to control where the data is being stored in Prism. For each entry, there is a list of possible RP8 source fields from which the data is obtained. Each type is discussed in the following sections.
Note that each section lines that with the label "Desc#". Example, Desc0 or Desc2. These labels are there for information when editing the data. These lines are not used by the utility itself. They help users to know what sources are available while editing the entries.
Store_Field_Soure controls the fields that are transferred while the STORE table is being exported. The entries are labeled with where in Prism the field is.
Name is the store name. Like other Source Control fields, an entry of 0 will make the Prism field blank. A value of 1 in the Name field means the utility will get the entry from the store name formatting field (described earlier).
In the RP8 data, the primary use of the store information fields is to print the store information on receipts and reports. The user can put store information in any of the six available Prism store information header fields. For example, the user might put the Street Name in header field 1 and the address (e.g. 123 Main St) in header field 2.
Alternatively, the user might put a message in header field 0, (e.g. "Thank you for shopping with us") and then put the store name in header field 1.
Desc0=0None, 1:Default, 2:Hdr1, 3:Hdr2
Desc1=4:Hdr3, 5:Hdr4, 6:Hdr5, 7:Hdr6
The Inven_Field_Source section is to control the Inventory UDF fields. There are four that are read from the RP8 data. These are UDF fields 1 to 4. You can decide where to put these in the Prism data
Desc0=0:None, 1:Udf1, 2:Udf2, 3:Udf3, 4:Udf4
For creating the customer table, there are 20 possible source fields. The first two are UDF date field 1 and 2. Next are the rest of the UDF fields, Udf3 to Udf8. These correspond to a value of 3 to 8. Then there are 12 auxiliary fields. The auxiliary fields correspond to values of 9 through 20.
Desc1=1:UdfDt1, 2:UdfDt2, 3:Udf3, 4:Udf4, 5:Udf5
Desc2=6:Udf6, 7:Udf7, 8:Udf8, 9:Aux1, 10:Aux2
Desc3=11:Aux8, 12:Aux4, 13:Aux5, 14:Aux6, 15:Aux7
Desc4=16:Aux8, 17:Aux9, 18:Aux10, 19:Aux11, 20:Aux12
There is the invoice item source field control. In Prism, the fields are Note 1 to Note 10. In V8, the fields are UdfDt, which has a value of 1. Then there is UdfStr, which is a value of 2. Finally, there are Udf1 to Udf8. These correspond to values of 3 to 10.
Desc2=3:Udf1, 4:Udf2, 5:Udf3, 6:Aux4
Desc3=7:Udf5, 8:Udf6, 9:Udf7, 10:Udf8