Welcome to Beer Canada’s Wiki Site.

Table of Contents

PEILCC Sales Data

  • File Type: Excel
  • Sheetname: Monthly_Vender_Base0

File Layout

Column # Name Type
1 Year varchar
2 Month varchar
3 Vendor varchar
4 Store # varchar
5 Description varchar
6 Upc varchar
7 Litres float

Sample Data

Year Month Vendor Store # Description Upc Litres
2009 Oct BREWER1 001 Alpine 776029700046 274.16
2009 Oct BREWER1 001 Alpine 776029700909 107.92

Notes

  • Make sure that Year and Month are stored as text in the input excel file.
  • In the previous input files the Year and Month were stored as date values and displayed using custom formats: yyyy and mmm, respectively.
  • No exclusions are done since all products are beer.

NSLC Sales Data

ACD SALES File Layout

  • File Type: semi-colon separated text file
  • Not used. No longer being sent.

^Column # ^Name ^Type^

1 site int
2 article_no int
3 ups_article_number varchar(256)
4 total_mtd int
5 sales_ytd int
6 licensee_sales_mtd int
7 agency_sales_mtd int
8 general_sales_mtd int
9 specialty_stores_sales_mtd int
10 other_sales_mtd int
11 licensee_sales_ytd int
12 agency_sales_ytd int
13 general_sales_ytd int
14 specialty_stores_sales_ytd int
15 other_sales_ytd int
16 inventory_stores int

Sample Data 

2099;000000000001000001;56327073852;150;0;0;150;0;0;0;0;0;0;0;0;354
2099;000000000001000002;62067335457;6181;0;18;6163;0;0;0;0;0;0;0;0;21
2099;000000000001000003;56327072558;362;0;0;362;0;0;0;0;0;0;0;0;2651

CR_BRAND

  • File Type: semi-colon separated text file
  • Used as the product info file
Column # Name Type
1 Article Numberint
2 Article Descriptionvarchar
3 UPC/EAN Codevarchar
4 Merchandise Category 1varchar
5 Merchandise Category 2varchar
6 Merchandise Category 3varchar
7 Material Group Numbervarchar
8 Material Groupvarchar
9 Country of Originvarchar
10 Consumer Countryvarchar
11 Consumer Regionvarchar
12 Containervarchar
13 Container Typevarchar
14 Stock On Handvarchar
15 Retail Pricevarchar
16 Vendor Numbervarchar
17 Vendor Descriptionvarchar

CR_STORE

  • File Type: semi-colon separated text file
  • Not used.
Column # Name Type
1 Store Numberint
2 Store Descriptionint

Detailed Supplier Stats

  • File Type: semi-colon separated text file
  • Not used. Huge file and does not contain container volume.
Column # Name Type
1 site int
2 Vendor Numberint
3 Store Numberint
4 Article Numberint
5 UPC/EAN Codevarchar
6 Salesint
7 On Handint
8 Sales Last Monthint
9 Sales Previous Monthint
10 General Salesint
11 Licensee Salesint
12 Agency Salesint
13 Other Salesint

Monthly Supplier Report

  • File Type: comma-separated text file
  • Used for sales info. Needs to be formatted to tab-delimited.
  • need to match with product info to get country.
Column # Name Type
1 Article Numberint
2 Article Descriptionvarchar
3 EAN/UPCvarchar
4 Containervarchar
5 Container Typevarchar
6 Bottle Volume (ml)int
7 Units per selling unint
8 Retail Price (Char)decimal(8,2)
9 Merchandise Cat.1varchar
10 Merchandise Cat. 2varchar
11 Material Groupvarchar
12 Material Group Descriptionvarchar
13 Vendor Numberint
14 Vendor Descriptionvarchar
15 Warehouse Bottlesint
16 Warehouse Casesint
17 Total Salesint
18 Active Storesint
19 On-hand (Stores)int
20 On-orderint
21 Sales last 13mthsint
22 Prev-Monthint
23 Prev-2-Monthint
24 Prev-3-Monthint
25 Prev-4-Monthint
26 Prev-5-Monthint
27 Prev-6-Monthint
28 Prev-7-Monthint
29 Prev-8-Monthint
30 Prev-9-Monthint
31 Prev-10-Monthint
32 Prev-11-Monthint
33 Prev-12-Monthint
34 Prev-13-Monthint

SLGA Sales

  • File Type: tab separated text file
  • Column names in the first row

File Layout

Column # Name Type
1 sale_year int
2 sale_month int
3 prdct_sys_key_id int
4 long_nm varchar(100)
5 country varchar(50)
6 mnfct varchar(50)
7 location_of_sale varchar(10)
8 cntnr_typ varchar(50)
9 vol_in_mls bigint

Sample Data 

sale_year	sale_month	prdct_sys_key_id	long_nm	country	mnfct	location_of_sale	cntnr_typ	vol_in_mls
2008	1	6279	PATAGONIA (ARGENTINA)	Argentina	CASA ISENBECK	Off site	Bottles	55555
2008	1	6279	PATAGONIA (ARGENTINA)	Argentina	CASA ISENBECK	On Site	Bottles	99999

ANBL Sales

  • File Type:

File Layout

Column # Name Type
1 D Department. 1 = Beer, 2 = Wines, 3 = Spirits, 4 = Other Beverages, 5 = Non-Liquor, 6 = Non-Liquor-Non-Revenue
2 C Class. For Beer, 100 = Domestic Beer, 200 = Domestic Premium Beer, 300 = Specialty Beer, 400 = Imported Beer
3 L Line. For Beer, 5 = Ale, 10 = Lager, 15 = Strong, 20 = Light, 25 = Stout/Porter, 30 = Malt Liquor, 35 = Flavoured, 40 = Other, 45 = Draught
4 UPC
5 Name
6 Size
7 Liquor Rep
8 Volume (HL?)

Sample Data

D C L UPC Name Size Liquor Rep Volume (HL?)
1 100 5 062067121012 Beer 24 341 Brewer 55,555
1 100 5 062067121029 Beer 12 341 Brewer 55,555

On the DCL report, the D is Department (Beer, Wine, Spirits, Other Bevs), the C is Class and the L is Line. There are three tabs in that attachment. Basically it is a structure that allows us to classify all our products. For example, Bud Light 12-can is a DCL of 1-100-20…1 means beer, 100 means domestic beer and 20 means light beer. Heineken Lager 12 is a DCL of 1-400-10…1 means beer, 400 means imported beer and 10 means lager.

Ontario Sales

LCBO Packages

  • File Type: Fixed-length text file
  • First column does not contain column names.
  • Each file contains one-week of data that starts from Sunday to Saturday. Overlaps between adjacent months are allowed.The exception to this rule is at the LCBO year-end which is March 31. At year-end, there will be a that contains the last week of the year from Sunday up to March 31. The next year's data will start on April 1 till the first Saturday of the week.
  • The weekly files are downloaded from the LCBO Sale of Data website.
  • The weekly filename follows the format: w0728ww3 where ww represents the week number. eg., w0728223 is for week 22.
  • There are also updated product listings for allproducts and for activeproducts. These should be loaded as well for correct processing.
  • Non-beer products must be filtered out of the file.
  • Beer products are identified by searching the SET_SUBSET_NAME for the wildcard '%BEER%'.
  • Shipments to The Beer Store must be filtered out by excluding OT_SQTY and OT_DQTY from the reported volume.
  • To get a monthly volume:
    • The weekly volume is divided by 5 (or the number of days in the week minus one for year-end) to get a average daily volume.
    • The average daily volume is distributed for each of the days from Monday to Friday (special handling for the year-end).
    • The monthly average value is calculated by allocating the daily values by the month.

File Layout

FIELD NAME FIELD LENGTH START POSITION END POSITION
AGENT_CODE 4 1 4
SET_SUBSET_CODE 6 5 10
PRODUCT_NUMBER 7 11 17
PRODUCT_NAME 17 18 34
RETAIL_PRICE 9 35 43
STARTING_WEEK 8 44 51
ENDING_WEEK 8 52 59
PRODUCT_SIZE 8 60 68
NO_ITEM_PER_SEL_UNIT 3 69 71
NO_SEL_UNIT_PER_CASE 3 72 74
RG_SQTY 9 75 83
AG_SQTY 9 84 92
LI_SQTY 9 93 101
OT_SQTY 9 102 110
RG_DQTY 9 111 119
AG_DQTY 9 120 128
LI_DQTY 9 129 137
OT_DQTY 9 138 146
NO_OF_WHSES 5 147 151
NO_OF_STORES 3 152 154
SQTY_ON_HAND 9 155 163
DQTY_ON_HAND 9 164 172
WQTY_ON_HAND 9 173 181

LCBO Kegs

  • File Type: CSV
  • First row contains column names.
  • File must be converted from the original Excel spreadsheet into a comma-separated text file with the file layout described below.

File Layout

Column # Name Type
1 Period int
2 Supplier nvarchar
3 Product/Brand nvarchar
4 Caps nvarchar
5 Kegs float
6 Hectolitres float

Sample Data 

file

The Beer Store

  • File Type: CSV
  • First column contains column names.
  • Shipments to the LCBO must be excluded by filtering out transaction_code 50.

File Layout

Column # Name Type
1 Year smallint
2 Month nvarchar
3 Brewer nvarchar
4 Nationality nvarchar
5 Transaction Code smallint
6 Transaction Name nvarchar
7 Container Type nvarchar
8 Volume in HLS real

Sample Data

Year Month Brewer Nationality Transaction Code Transaction Name Container Type Volume in HLS
2010 January AB GUBERNIJA Lithuania 20 COUNTER SALE C 5.555
2010 January AB INBEV Belgium 20 COUNTER SALE B 5555.555

Trace: • brewstats_file_formats

Toolbox