The Excel Client allows you to:

• Manipulate your Pricefx data (customers, product extensions, users etc.) using MS Excel.
• Modify the metadata of the table columns (take special care when doing this).

The Excel Client can be accessed from the Master Data menu.

Limitations:

• The Excel Client is only suitable for smaller data sets, up to about 25,000 lines.
  
• Attributes in JSON format cannot be uploaded or downloaded using Excel Client.
• Company Parameters tables with JSON Table Type and JSON2 Value Type cannot be updated using Excel Client.
• When authenticating via Excel Client, the JWT token is stored in the downloaded XLSM file. This poses a security risk. To prevent injecting JWT token to the Excel file, set the application property doNotInjectJWTInEC to true. This however means that the user will have to log in when using Excel Client.

In this section:

Open Excel Client

1. Go to Master Data > Excel Client. The PriceFxClient.xlsm file is downloaded.
2. Open it in MS Excel and click Enable Editing and Enable Content to be able to use the Pricefx functionality.
3. The Excel Client will automatically try to log you in based on your web based session information. Should this fail, you will be asked to log in using your credentials.
4. Pricefx appears in the Excel menu. 

Update Data

Using the Manage menu you change, delete and add data for various Pricefx objects.

1. As a prerequisite, make sure the table you want to manage is not empty in Pricefx. If it is, it will be listed in the Excel Client but the download action will result in an empty sheet without the expected columns being visible. You need to go to Pricefx and ensure that the table has at least one row of data.
2. Open the Excel Client. 
3. From the Manage menu select a table you wish to work with. The following objects can be handled by the Excel Client: Users, User Groups, Products, Product Competition, Product Reference, Product BoM, Product Text, Customer, Price Record, Data Source (read only), Data Change Request, Product Extension, Customer Extension, Company Parameter, Manual Price List, Price List, Simulation, Live Price Grid.
   When it comes to fetching the table values for the drop-down menu in Excel Client, there is a MS Excel limit of 32 767 items which can be displayed there.
   
   • For some of the tables you can select whether you want to download all rows or limit it by one or more filters. Note that not all operators available in Pricefx are available in the Excel Client.
     
   • If the downloaded data contains time fields, they are converted (just like in the application UI) to the browser's timezone. This is different from the behavior of the 'download to XLS' functionality available throughout the application (this download converts time to the UTC timezone). 
4. Modify the data in Excel as needed. 
   • To update data – Update the values in individual cells. The row will be marked with a yellow flag "C" (changed). 
      Do not just paste over the existing lines, as the update on the server may not succeed. It is best to keep the same order of lines and update only attributes, not keys.
     
   • To delete data – In the 'delete' column enter 'Yes' on the given line. This value can be copied into multiple lines. The row will be marked with a blue flag "D" (deleted). Some tables do not allow for deletion (i.e. Price list or Price grid tables); where possible this has to be done through the UI.
      If there is a timestamp type of column and you have applied a filter to it (directly in the Excel sheet), this filter is not kept after the delete operation is completed (i.e. after you click 'Update Server').  
   • To add data – Create new lines in Excel. The row will be marked with a yellow flag "N" (new). Some tables  do not allow for additions (i.e. Price list or Price grid tables); where possible this has to be done through the UI.
     
   Note:
   • If you use MS Excel formulas to generate the new/updated values, these formulas will not be kept because Pricefx stores the data values only.
   • Handle very carefully the following two fields in the Users data: Product Filter and Customer Filter. They contain a lengthy JSON string which is a result of setting up the filters in the Pricefx UI. This JSON string must remain valid and contain valid filters; otherwise Pricefx will not display the correct content for the given user.
   • If you enable the 'Meta Mode' option, you can modify the meta information (i.e., the data type and details for the customizable attributes) for the following objects: Customers, Products, Company Parameters, Product Extensions. This can affect your pricing logic, so be careful.
   • As you update the data in bulk, it can happen that you modify multiple objects in the same table that have different edit rights (e.g., product extensions within a category). On the server side, lines for which you do not possess editing rights are filtered out. If there are some lines left after filtering, those will be updated and you will receive no message. If no line passes the filter, you will get an access denied error.
   • Excel Client also checks if key fields of the Company Parameter, Product Extension, Customer Extension and Manual Price List tables are not empty.
   • Company Parameters fields are validated as case-sensitive. However, if you have a PP with values restricted by a Data Source whose number of values exceeds the Excel limit of 8192 characters, the validation is handled as case-insensitive. If you enter a value manually, make sure it matches the restricted values list as defined in Pricefx.
   • When updating Price Lists and LPGs, all data changes are accumulated and processed in a single asynchronous final update call. Item recalculation is triggered only if an overridable value was changed. At the end of the batch, EC always sends a command to recalculate items changed during the update(s); the action can end with recalculation of 0 items.
   • When you get this message "Previous data validation on one or more business key fields <their list> has been suppressed by the business key validation" it is because you have data restriction on some column(s) that are part of the business key. In that case, the Data Source validation rule is overridden by the business key validation rule (Excel can have only one validation formula per cell). Then you need to be careful about the entered data.
5. Click the Update Server option. When the data is synchronized with the Pricefx server, the change flags on each line disappear.
6. If you want to continue editing another object, click the Clear Data option and in the Manage menu select the table to work with.
    Clear Data has no affect on data on the Pricefx server; it just clears the Excel sheet. It does not reload the meta info (unlike the Force full login option).

Change Login

If you need to log to a different Pricefx instance while the Excel Client is open, take these steps:

1. Click the Show Login option. 
2. Enter the credentials. If the first four fields of this dialog are not active, enable the Override login information option at the bottom.
   You can can also use here the two-factor authentication. For details on the setup, see Two-Factor Management.
3. Optionally, use these options:
   • I am behind a web proxy that requires authentication – Opens another dialog where you can configure the proxy with authentication. 
   • Use server side user preferences when available – End users can define preferences in the PFX UI. The Excel Client will download only the Default preferences to display the table information in the same order as in the UI. This can be very useful if you intend to update only a subset of the data of a given table. Create a special preference and make it default. After you open the Excel Client, this preference will be used and you will see only those columns you had selected in your preference.
   • Force full login – If checked, a "full" logout and login is performed (complete wipe of data + meta info reload). This was the default behavior before the Mojito SE version. 
     If the option is not checked, only a re-login is executed; the data in Excel is not cleared. 
     
4. Click Login.

Details about the currently logged in user and partition are displayed after you click the Show Details option. 

Tips

Use Shortcuts

To speed up your work with Excel Client, you can use shortcuts. After you press the Alt key, all available shortcuts are displayed: 

The Pricefx menu opens after pressing the keys "Y" and "1" simultaneously:

Then you can continue by pressing the keys indicated by the black hints (e.g. "MAN"). To go back one level, press the Esc key.