Price Collection ToolPack™

Batch Upload Utility

DRAFT

September 2004

Contents

Introduction

Section I: Installation Steps

Section II: Installation Verification

Section III: Application Guide

Annex 1: Format of the CSV file
Introduction

The Batch Upload program is a new utility in the ICP Tool Pack family of software. It is a program that facilitates the upload of the master product list, units of measure, characteristics, characteristic values, and product specifications into the Price Administration Module (PAM) database, without having to login to the PAM. The Batch Upload Utility also allows the upload of raw Price Observations to the PAM.

The Batch Upload utility is a stand-alone program. That is, it is not installed with the PAM software, but it needs to be installed separately. It should be installed on the same computer where the PAM is installed. Installing the Batch Upload Utility requires an Administrator login access to the computer. It should be only used by the PAM Administrator to upload the Product List components or the raw Price Observations to the PAM.

Uploading Product List

The Product List files are generated from the Bridge software. The files are:

1_ICP_Products.xls

2_ICP_UoM.xls

3_ICP_ProductCharacteristics.xls

4_ICP_CharacteristicValues.xls

5_ICP_ProductParameters.xls

6_ICP_StructuredProductDescription.xls

These files should be kept together in a folder, and then uploaded to PAM using the Batch Utility.
Uploading Raw Price Observations
Countries that are using their National systems for Data Collection, and not using the Price Collection Module partially or totally, will have to convert the output of their systems into a CSV (Comma Separated Values) formatted file, the layout for which is to be provided by the Global Office.
The CSV file with raw price observations can be uploaded into the Price Administration Module using the Batch Upload facility. The observations should be kept in a file under the name:
7_ICP_PriceObservations.csv
About this Guide

This guide explains how to install the Batch Upload Utility, and offers some optional tests to verify whether the installation was successful. It also provides an overview of the functionality of the Batch.

Section I: Installation Steps
STEP 1

Welcome screen

Click Next


STEP 2

Select the Target folder in which you would like to deploy the Utility and click Next.

Click Browse to get the following dialog box for changing the folder path:


Note:

You might be prompted if the folder you selected already exists. Click Yes to overwrite the installation. Click No to go back to the previous screen and use the Browse button to change the folder path.

STEP 3

To confirm your selections click Install. Click Back to make changes.


STEP 4

The screen below displays the installed Price Administration Module Applications on this target machine and asks the user to fill in the correct Username and Password to the SQL Instance <Machine Name>\PACT

Note:

Only if you use the Test DB Connection with a successful outcome will the Install Patch button get enabled.

The following message would be shown for invalid Data Base credentials:

The following message would be shown for valid Data Base credentials:

Only after this message is received will the Installation disable the Test DB Connection button and Enable the “Install Patch” button.

Click on the “Install Patch” button. The following message is shown for successful execution of the Patch.


STEP 5

The following Finish screen is displayed after the OK is clicked in the last message box of the previous step.

Click Finish to complete the Installation.

Perform the Installation Tests as documented in the next section.


Section II: Installation Verification

TEST 1

Execution Steps:

Right click on the desktop icon “PAM Batch Upload Utility” and select PROPERTIES to launch the following screen:

Desired Outcome:

“Start In” Field should be populated with installed Path.


TEST 2

Execution Steps:

Right click on the Program Folder Shortcut “PAM Batch Upload Utility” and select PROPERTIES to launch the same screen as above:

Desired Outcome:

“Start In” Field should be populated with installed Path.


TEST 3

Execution Steps:

Right click the desktop icon “PAM Batch Upload Utility” and select PROPERTIES to launch the following screen:

Click on Find Target to reach the installed folder.

Right Click on the “PAM_BatchUpLoadUtility.exe” and select properties.

Desired Outcome:

Created: September 27 2004, 5:15:16 PM


Section III: Application Guide

After successful installation, click on the PAM Batch Upload Utility icon. The following default screen will appear:

Note:

a)  The Upload button would be disabled unless an Application is selected.

b)  Browse for the folder path where all the files are kept by using the “…” button.

c)  At least one option should be selected for Upload to proceed. Otherwise the following message would appear:

d)  The files pertaining to the selected Data should be stored in the folder path. The Utility is designed to give the user an informative alert conveying the names of the files that should be found in the Selected Path.

e)  If the utility detects a problem with the data in one or more files, it will display you an error message and a blue link, “View Error Details”, will appear at the bottom of the screen.

Upon clicking the blue link “View Error Details”, the user will get the following message box. It displays the file name and the row numbers that contain invalid entries:

16

Annex 1: Format of the CSV file

About this file (IMPORTANT)
This is a mandatory file that needs to be generated by any price collection software in the above data structure so that it is compatible with the PAM.
The file should be stored under the name: 7_ICP_PriceObservations.csv
All the codes mentioned below (Survey Code, User Id, Survey Location Code, Product Code, etc…) should correspond to the codes in your Survey Setup in the PAM.
The column headers in your file should correspond exactly to the column headers mentioned below. They are case and space sensitive.
Data Structure
7_ICP_PriceObservations.csv
Corresponding Column in the PAM Database / Position in CSV File / Mandatory / Max Length / Permissible Values / Sample Data / Remarks
Survey Code / 1 / Yes / 15 / Alphanumeric / 0101001 or Q1
User Id / 2 / Yes / 10 / Alphanumeric / PC1
Survey Record Code / 3 / Yes / 10 / 0-9 / 21
Product Code / 4 / Yes / 30 / Alphanumeric (hyphens and colons are not allowed) / 01.01.001 or
01_01_001 or
0101001 / Stands for the ICP Product Codes as determined in the regional list of products.
Survey Location Code / 5 / Yes / 15 / Alphanumeric / 0101001 or O1 / Stands for the Outlet code.
Observation Date / 6 / Yes / 19 / 0-9, "/" , ":" ," " / 01/21/2004 / Has to be in the following format MM/DD/YYYY
Observation Type / 7 / Yes / 1 / "R","D","C","S", "M" / R / R = Regular Price, D= Discount Price, C= Coupon Price, S= Substitute Price, M=Missing Price
Observed Qty / 8 / Yes / 16 / Numeric (0-9 and ".") / 1.50 / Including the decimal the length can be 16 characters.
Only 15 numeric digits should be there overall.
Decimal can be in any position of the data and even as the first character of the data.
Observed UoM / 9 / Yes / 5 / Numeric (0-9) / 12 / Stands for the UoM code. Can contain only numeric data so as to be compatible in a multilingual environment.
Observed Price / 10 / Yes / 16 / Numeric (0-9 and ".") / 450.50 / Including the decimal the length can be 16 characters.
Only 15 numeric digits should be there overall.
Decimal can be in any position of the data and even as the first character of the data.
Converted Price / 11 / Yes / 16 / Numeric (0-9 and ".") / 450.50 / Including the decimal the length can be 16 characters.
Only 15 numeric digits should be there overall.
Decimal can be in any position of the data and even as the first character of the data.
Remarks / 12 / No / 250 / Any character / The Brand is Nestle instead of Hersheys. / An empty value is represented without a gap between the two commas. Remarks should not include commas in the text.
Representative / 13 / No / 1 / " ", "1", "0" / 1 / Stands for the representativity of the observation. 1 stands for Representative and 0 stands for Not Representative.

16