Fieldpine Logo Documentation Home  

Bulk Data Load via Excel


Bulk loading supplier pricebooks or pricelists is documented specifically here

In many places in the Point of Sale you can load Microsoft Excel spreadsheets containing information as a quick way to bulk load data. It works by taking a specifically formatted spreadsheet and uploading it to the server, where each row in the spreadsheet is processed. The bulk loader enforces many of the same rules as individually entering values.

Header Row(s)

The first line of the spreadsheet contains the heading fields for each column. These names must match the names the system uses in order for information to be loaded.

Repeated Information

Some items allow you to upload a parent row and multiple similar pieces of information. For example when uploading supplier definitions you may upload any number of contacts associated with that supplier record. In IT circles this is called a parent/child relationship.

To define child values in a row, the header title needs to include the child details and the field name of the child. An example makes this clearer.

A B C D E F
 1  Name Address Name (Contact 1) Phone (Contact 1) Name (Contact 2) Phone (Contact 2)
2 Burger Supplies 1 Pattie Place John Smith 111-2222 Mary McKeown 234-5678
3 Pencils Ltd B Biro 999-234
4 Breadmakers 88 Loaf Way Sue Shepard 345-345 Liz McManus 345-346
5 Cooking Supplies

Columns A and B, are defining the Name and Address of the supplier itself, while columns C and D are defining the name/phone for Contact 1. And columns E and F are defining Contact 2.

The header row name for repeating columns should use any one of the following patterns:

Store to Store Transfer - Tracked

A tracked store to store transfer moves stock from one location to another but requires the receiving store to confirm receipt of the stock. This differs from a "Quick transfer" which simply alters stock levels in the system and trusts that physical movement will happen.

Normally, stores create tracked transfers using the POS screens and print a packing slip for inclusion in the delivery parcel. In some environments you may wish to bulk load tracked transfers. This can be done with an Excel spreadsheet containing one line per store/product combination.

A B C D E
 1  retailmax.elink.stock.transit header=3
2 from to pid qty
3 f100 f101 f200 f202
4 2 4 6001 3
5 2 4 8823 11
6 2 3 6011 7

The first three lines are required, lines 4,5 and 6 is sample data. Download template Excel Spreadsheet

Store to Store Transfer - Tracked using Grid

An alternative method of loading tracked store transfers is with a grid layout, as shown below.

A B C D E
 1  retailmax.elink.stock.transit header=3
2 from pid
3 f100 f200 store 102 qty store 401 qty store 653 qty
4 2 6001 1 3
5 2 7317 1 1
6 2 13039 2 4 7

Locations & Stores

A location defines the sales or stock holding points in your retail network.

A B C D E F
1 Action Locid Name Latitude Longitude Phone
2 insert Queen St -41.8 174.82 123 4567
3 edit 12 Manners Mall -40.7643 173.02 23 23 234
4 edit 17 Queensgate 8765 4321

The "action" column indicates whether you are inserting new rows or editing current rows. For locations which are rarely created, we recommend always setting this column to "edit" so that new locations are not created in error. If you specify "edit", you must specify key information such as locid (shown in column B above) or physkey.

Reorder Levels - Simple

The simple spreadsheet method of loading store reorder levels lets you define reorder minimum and maximum values on a single line

A B C D E F
1 Department Pid Description Store Level Maximum
2 Bolle 116 Shiny Black Photo V3 Golf 4 67 115
3 Bolle 123 Greta Shiny Tortoise 4 4 4
4 Bolle 123 Greta Shiny Tortoise 3 3 5

The above will set the levels and maximums as given in columns D, E and F. Columns A and C will not be used or loaded, but can be left on the spreadsheet. They will appear as unknowns, but you can ignore this warning and continue wth the load

Reorder Levels - Grid

A grid method of loading reorder levels and maximums allows you to specify the products in rows, with different stores in various columns. This method of creating the spreadsheet can be easier for you to create.

A B C D E F
1 Department Pid Description Store 301 Level Store 450 Level Store 501 Level
2 Bolle 116 Shiny Black Photo V3 Golf 14 67 115
3 Bolle 123 Greta Shiny Tortoise 4 4 6
4 Bolle 123 Greta Shiny Tortoise 1 3 5

This spreadsheet is defining 9 different reorder levels (cells D2:F4) for 3 products and 3 stores. This grid method of loading is not available for all places you can upload data, only selected functions are able to sensibly use a grid upload.

The title of columns D, E and F are formatted so that the store and number is first, followed by the attribute being loaded.

Stocktake Counts

While Fieldpine do not recommend loading stocktake counts by typing numbers (more error prone than using barcode scanners), you can bulk load counts by creating an Excel spreadsheet. This can be useful for minor adjustments, or where electronic counting is not feasible.

A B
1 Pid Quantity
2 12657 2
3 123 4
4 2563 1

The above shows the simple form without store location or stocktake number being manually entered. If you are loading extracts of another system, you can also provide location number (column title 'f104') and stocktake number (column title 'f100')

Price Overrides

A B C D E F G
1 Pid Price enddate location customer department percentage
2 12657 2.50 1-nov-2015 4 1237
3 1-nov-2015 4 23 10.5

This shows two overrride prices being loaded.

The first (row 2) is setting a price of $ 2.50 for product# 12657, purchased in store #4, by customer # 1237, before the 1st November 2015

The second (row 3) is giving a 10.5% discount on all products in department #23 (at time of sale) in store #4, before the 1st November 2015