Production Lot Upload Data Preparation: Alberta Health Solutions (ABHS)
Site Information Details
Alberta Beef Health Solutions (ABHS)Vet: CSBV00003 (3)
Project Year: 2019
The site has three (3) herds. They are:
CSBH000027
CSBH000028
CSBH000029
Access to current years documentation click on the appropriate link below:
ABHS 2020
ABHS 2021
Production Lot Upload Data Preparation: Alberta Health Solutions (ABHS)
Data Source Information
ABHS has 3 herds.Each spreadsheet/workbook from ABHS represents a specific herd for a specific sampling year.
Within the workbook there is usually just 2 drugs: feed and injectable (parenteral).
The core fields in a sheet have the drug fields at the end of the core field data.
The drug fields are assembled as columns in the spreadsheet.
There are options how to setup each drug.
Each drug sheet can have all associated Core fields and Drug fields together.
Another option is to place all Drug field information, Feed and Injectable, at the end of the Core fields.
Depending on what option is used, the setup of the upload templates will vary.
Before creating and setting up a template, go thru all spreadsheets provided from the site.
Collect field names from each site and put them together. Review the field names to make sure they all
match. My experience is the field names do not match. To reduce the number of templates to create,
get the field names to match. The fields do not need to be in the same order.
The other reason to check all spreadsheets before creating a template is to see if there are the same number of drugs.
I have discovered it is the case the # of columns of drugs are not the same or there are more drugs in
a spreadsheet compared to another. To make it easier, prepare only 1 template, get all
drugs together across all drug routes for all spreadsheet data.
If you do it in this way, it is a possible route to introduce more errors. Be careful!
ABHS 2019: Final Source Spreadsheet columns
| Final Fields | Source Fields | Source Fields | Source Fields |
| All Herds - 2019 | Herd 27 | Herd 28 | Herd 29 |
| CIPARS | CIPARS | Feedlot | Feedlot |
| Code | Code | Code | Code |
| Sex | Sex | Sex | Hd In |
| Type | Type | Type | Type |
| Origin | Origin | Origin | Sex |
| Weaned | Weaned | Weaned | Weaned |
| BRD Risk | BRD Risk | BRD risk | Source |
| # HD IN | # HD IN | In Wt (lbs) | BRD Risk |
| In Date | In Date | Hd IN | In Date |
| Total InWt (lb) | Total InWt (lb) | In Date | In Wt (lbs) |
| Ave. In Wt (lbs) | Ave. In Wt (lbs) | Ave Out Wt (lbs) | Hd In |
| ave ship date | ave ship date | Out Date | Ave Out Wt (lbs) |
| Slaughter Date | Slaughter Date | Quarter closed | Ave. Out Date |
| Quarter closed | Quarter closed | Hd Out | Hd Out |
| Sale Wt (lb) | Sale Wt (lb) | Deads | Deads |
| Total Out Wt (lb) | Total Out Wt (lb) | DOF | DOF |
| Ave. Out Wt (lbs) | Ave. Out Wt (lbs) | DDMI (lb/day) | DDMI (lb/day) |
| Deads | Deads | Head Days | Head Days |
| Head Out | Head Out | tulathromycin (ml) | tulathromycin (ml) |
| DOF | DOF | tulathromycin (mg) | tulathromycin (mg) |
| Head Days | Head Days | gamithromycin (ml) | gamithromycin (ml) |
| DDMI (lb/day) | tulathromycin (ml) | gamthromycin (mg) | gamthromycin (mg) |
| tulathromycin (ml) | tulathromycin (mg) | tilmicosin (ml) | tilmicosin (ml) |
| tulathromycin (mg) | gamithromycin (ml) | tilmicosin (mg) | tilmicosin (mg) |
| gamithromycin (ml) | gamthromycin (mg) | danofloxacin (ml) | danofloxacin (ml) |
| gamthromycin (mg) | tilmicosin (ml) | danofloxacin (mg) | danofloxacin (mg) |
| tilmicosin (ml) | tilmicosin (mg) | enrofloxacin (ml) | enrofloxacin (ml) |
| tilmicosin (mg) | danofloxacin (ml) | enrofloxacin (mg) | enrofloxacin (mg) |
| danofloxacin (ml) | danofloxacin (mg) | oxytetracycline (ml) | oxytetracycline (ml) |
| danofloxacin (mg) | enrofloxacin (ml) | enrofloxacin (mg) | enrofloxacin (mg) |
| enrofloxacin (ml) | enrofloxacin (mg) | ceftiofur-crystalline free acid (ml) | ceftiofur-crystalline free acid (ml) |
| enrofloxacin (mg) | oxytetracycline (ml) | ceftiofur cyrstalline (mg) | ceftiofur cyrstalline (mg) |
| oxytetracycline (ml) | enrofloxacin (mg) | ceftiofur sodium powder (ml) | ceftiofur sodium powder (ml) |
| oxytetracycline (mg) | ceftiofur-crystalline free acid (ml) | ceftiofur sodium (mg) | ceftiofur sodium (mg) |
| ceftiofur-crystalline free acid (ml) | ceftiofur cyrstalline (mg) | procaine penicillin G (ml) | procaine penicillin G (ml) |
| ceftiofur cyrstalline (mg) | ceftiofur sodium powder (ml) | procaine penicillin G (mg) | procaine penicillin G (mg) |
| ceftiofur sodium powder (ml) | ceftiofur sodium (mg) | Florfenicol (ml) | Florfenicol (ml) |
| ceftiofur sodium (mg) | procaine penicillin G (ml) | florfenicol (mg) | florfenicol (mg) |
| procaine penicillin G (ml) | procaine penicillin G (mg) | Trimethoprim-sulfadoxine (ml) | Trimethoprim-sulfadoxine (ml) |
| procaine penicillin G (mg) | Florfenicol (ml) | TMP-Sulfa (mg TMP) | TMP-Sulfa (mg TMP) |
| Florfenicol (ml) | florfenicol (mg) | TMP-Sulfa (mg Sulfadoxine) | TMP-Sulfa (mg Sulfadoxine) |
| florfenicol (mg) | Trimethoprim-sulfadoxine (ml) | marbofloxacin (ml) | marbofloxacin (ml) |
| Trimethoprim-sulfadoxine (ml) | TMP-Sulfa (mg TMP) | marbofloxacin (mg) | marbofloxacin (mg) |
| TMP-Sulfa (mg TMP) | TMP-Sulfa (mg Sulfadoxine) | Total Mg Parenteral AM | Sulfa boluses |
| TMP-Sulfa (mg Sulfadoxine) | Total Mg Parenteral AM | monensin sodium (mg) | sulfamethazine (mg) |
| marbofloxacin (ml) | monensin sodium (mg) | tylosin phosphate (mg) | Total Mg Parenteral AM |
| marbofloxacin (mg) | tylosin phosphate (mg) | Chlortetracycline HCL | monensin sodium (mg) |
| Sulfa boluses | Chlortetracycline HCL | tylosin phosphate (mg) | |
| sulfamethazine (mg) | Chlortetracycline HCL (mg) | ||
| monensin sodium (mg) | |||
| tylosin phosphate (mg) | |||
| Chlortetracycline HCL | |||
| Total Mg Parenteral AM |
ABHS 2019: Reconcile Columns from Source Spreadsheets
Production Lot Upload Data Preparation: Alberta Health Solutions (ABHS)
Template Setup Suggestions
A single upload Template can be setup for one or more years batch of data.For the current document, assume we place all drug known fields for all drug routes at the end of the core fields.
When do this, then one Template is created.
The Template is populated from a worksheet from a specific spreadsheet or workbook.
The reason for this is because the drug fields are in columns not rows.
The system is designed to deal with it but you must upload by Source data to get it to work correctly.
The Template should be setup to have multiple or mix of drugs, data is by column and populated by source.
Here are the typical template settings used for ABHS.
Name: ABHS 2020 - Core and Injectable
Level: Top Level
Type: Both
Route: Injectable
Version: Version 1
By Row or Column: By Column
ABHS Template Settings: Core and Injectables (Template Id=30)
Beef Cattle Study: Template Information
| Template Id: | 43 Development Server |
| Template Name: | ABHS 2019 - Core, Injectable, Bolus, Feed |
| Group: | Alberta Beef Health Solutions (2) |
| Template Type: | Both (3) |
| Template Population: | Populate template fields from Source (2) |
| Template By Row or Column: | By Column (2) |
| Template Drug Route: | Multiple Drugs (4) |
| Template Level: | Top Level (1) |
| Template Version: | Version 1 (1) |
| Data by Row or Column: | By Column (2) |
| Template Created: | 2021-03-26 20:37:14.251053+00 |
Beef Cattle Study: Template Fields: Core Fields
| Field # | Field Id | Not Used | Source Name | Source Type | Category | Target Name | Target Type | Target Setup | Lookup Table | Lookup Id | Range | Values | Transformation | Description |
| 1 | 125 | 1 | ssrow | bigint | Core Field (0) | ssrow | bigint | Setup Standard Field (1) | ||||||
| 2 | 126 | 1 | ssgroup | bigint | Core Field (0) | ssgroup | bigint | Setup Standard Field (1) | ||||||
| 3 | 127 | 1 | cipars | varchar(192) | Core Field (0) | cipars | varchar(192) | Setup Standard Field (1) | ||||||
| 4 | 128 | 0 | code | numeric | Core Field (0) | csb_pl_production_lot_code | varchar(128) | Setup Standard Field (1) | ||||||
| 5 | 129 | 0 | sex | varchar(192) | Core Field (0) | csb_pl_sex_id | varchar(192) | Setup Lookup Field (2) | survey_cattle_beef.lu_pl_sex | 1 | ||||
| 6 | 130 | 0 | type | varchar(192) | Core Field (0) | csb_pl_animal_age_category_id | varchar(192) | Setup Lookup Field (2) | survey_cattle_beef.lu_pl_age_group | 1 | ||||
| 7 | 131 | 0 | origin | varchar(192) | Core Field (0) | csb_pl_origin_id | varchar(192) | Setup Lookup Field (2) | survey_cattle_beef.lu_pl_origin | 1 | ||||
| 8 | 132 | 1 | weaned | varchar(192) | Core Field (0) | weaned | varchar(192) | Setup Standard Field (1) | ||||||
| 9 | 133 | 0 | brd_risk | varchar(192) | Core Field (0) | csb_pl_brd_risk_id | varchar(192) | Setup Lookup Field (2) | survey_cattle_beef.lu_pl_brd_risk | 1 | ||||
| 10 | 134 | 0 | hd_in | numeric | Core Field (0) | csb_pl_n_animals_in | numeric | Setup Standard Field (1) | ||||||
| 11 | 135 | 0 | in_date | date | Core Field (0) | csb_pl_avg_date_in | date | Setup Standard Field (1) | ||||||
| 12 | 136 | 1 | total_inwt_lb | varchar(192) | Core Field (0) | total_inwt_lb | varchar(192) | Setup Standard Field (1) | ||||||
| 13 | 137 | 0 | ave_in_wt_lbs | numeric | Core Field (0) | csb_pl_avg_weight_in_kgs | numeric | Setup Multiple Target Fields (3) | ||||||
| 14 | 138 | 0 | ave_ship_date | date | Core Field (0) | csb_pl_avg_date_out | date | Setup Standard Field (1) | ||||||
| 15 | 139 | 1 | slaughter_date | varchar(192) | Core Field (0) | slaughter_date | date | Setup Standard Field (1) | ||||||
| 16 | 140 | 0 | quarter_closed | varchar(192) | Core Field (0) | csb_pl_quarter_of_year_closeout_id | varchar(192) | Setup Lookup Field (2) | survey_cattle_beef.lu_pl_season | 1 | ||||
| 17 | 141 | 1 | sale_wt_lb | varchar(192) | Core Field (0) | sale_wt_lb | varchar(192) | Setup Standard Field (1) | ||||||
| 18 | 142 | 1 | total_out_wt_lb | varchar(192) | Core Field (0) | total_out_wt_lb | varchar(192) | Setup Standard Field (1) | ||||||
| 19 | 143 | 0 | ave_out_wt_lbs | numeric | Core Field (0) | csb_pl_avg_weight_out_kgs | numeric | Setup Multiple Target Fields (3) | ||||||
| 20 | 144 | 0 | deads | numeric | Core Field (0) | csb_pl_n_animals_dead | numeric | Setup Standard Field (1) | ||||||
| 21 | 145 | 0 | head_out | numeric | Core Field (0) | csb_pl_n_animals_slaughtered | numeric | Setup Standard Field (1) | ||||||
| 22 | 146 | 0 | dof | numeric | Core Field (0) | csb_pl_total_days_on_feed | numeric | Setup Standard Field (1) | ||||||
| 23 | 147 | 0 | head_days | numeric | Core Field (0) | csb_pl_total_head_days | numeric | Setup Standard Field (1) | ||||||
| 24 | 148 | 1 | ddmi_lbday | numeric | Core Field (0) | ddmi_lbday | numeric | Setup Standard Field (1) |
Beef Cattle Study: Template Drug Fields: Active Ingredients
| Field # | Field Id | Not Used | Source Name | Source Type | Category | Target Name | Target Type | Drug Route | Source Drug Name | Source Drug Units | AI Field Name | Standard AI Name | Standard AI Id |
| 26 | 921 | 0 | tulathromycin_mg | numeric | Active Ingredient (1) | csb_pl_drug_active_ingredient_id | numeric | 3 (3) | tulathromycin_mg | 1 (1) | csb_pl_drug_active_ingredient_id | Tulathromycin (LA) (100 mg/ml) | 20 |
| 28 | 923 | 0 | gamthromycin_mg | numeric | Active Ingredient (1) | csb_pl_drug_active_ingredient_id | numeric | 3 (3) | gamthromycin_mg | 1 (1) | csb_pl_drug_active_ingredient_id | Gamithromycin (LA) (150 mg/ml) | 10 |
| 30 | 925 | 0 | tilmicosin_mg | numeric | Active Ingredient (1) | csb_pl_drug_active_ingredient_id | numeric | 3 (3) | tilmicosin_mg | 1 (1) | csb_pl_drug_active_ingredient_id | Tilmicosin (300 mg/ml) | 18 |
| 32 | 927 | 0 | danofloxacin_mg | numeric | Active Ingredient (1) | csb_pl_drug_active_ingredient_id | numeric | 3 (3) | danofloxacin_mg | 1 (1) | csb_pl_drug_active_ingredient_id | Danofloxacin (LA) (180 mg/ml) | 7 |
| 34 | 929 | 0 | enrofloxacin_mg | numeric | Active Ingredient (1) | csb_pl_drug_active_ingredient_id | numeric | 3 (3) | enrofloxacin_mg | 1 (1) | csb_pl_drug_active_ingredient_id | Enrofloxacin (100 mg/ml) | 8 |
| 36 | 931 | 0 | oxytetracycline_mg | numeric | Active Ingredient (1) | csb_pl_drug_active_ingredient_id | numeric | 3 (3) | oxytetracycline_mg | 1 (1) | csb_pl_drug_active_ingredient_id | Oxytetracycline (200 mg/ml) | 12 |
| 38 | 933 | 0 | ceftiofur_cyrstalline_mg | numeric | Active Ingredient (1) | csb_pl_drug_active_ingredient_id | numeric | 3 (3) | ceftiofur_cyrstalline_mg | 1 (1) | csb_pl_drug_active_ingredient_id | Ceftiofur (200 mg/ml) | 6 |
| 40 | 935 | 0 | ceftiofur_sodium_mg | numeric | Active Ingredient (1) | csb_pl_drug_active_ingredient_id | numeric | 3 (3) | ceftiofur_sodium_mg | 1 (1) | csb_pl_drug_active_ingredient_id | Ceftiofur (50 mg/ml) | 5 |
| 42 | 937 | 0 | procaine_penicillin_g_mg | numeric | Active Ingredient (1) | csb_pl_drug_active_ingredient_id | numeric | 3 (3) | procaine_penicillin_g_mg | 1 (1) | csb_pl_drug_active_ingredient_id | Procaine Penicillin G (300 mg/ml) | 15 |
| 44 | 939 | 0 | florfenicol_mg | numeric | Active Ingredient (1) | csb_pl_drug_active_ingredient_id | numeric | 3 (3) | florfenicol_mg | 1 (1) | csb_pl_drug_active_ingredient_id | Florfenicol (300 mg/ml) | 9 |
| 46 | 941 | 0 | tmpsulfa_mg_tmp | numeric | Active Ingredient (1) | csb_pl_drug_active_ingredient_id | numeric | 3 (3) | tmpsulfa_mg_tmp | 1 (1) | csb_pl_drug_active_ingredient_id | Trimethoprim (40 mg/ml) | 19 |
| 47 | 942 | 0 | tmpsulfa_mg_sulfadoxine | numeric | Active Ingredient (1) | csb_pl_drug_active_ingredient_id | numeric | 3 (3) | tmpsulfa_mg_sulfadoxine | 1 (1) | csb_pl_drug_active_ingredient_id | Sulfadoxine (200 mg/ml) | 16 |
| 49 | 944 | 0 | marbofloxacin_mg | numeric | Active Ingredient (1) | csb_pl_drug_active_ingredient_id | numeric | 3 (3) | marbofloxacin_mg | 1 (1) | csb_pl_drug_active_ingredient_id | Marbofloxacin (160 mg/ml) | 22 |
| 51 | 946 | 0 | sulfamethazine_mg | numeric | Active Ingredient (1) | csb_pl_drug_active_ingredient_id | numeric | 1 (1) | sulfamethazine_mg | 1 (1) | csb_pl_drug_active_ingredient_id | Sulfamethazine (32100 mg/bolus) | 4 |
| 52 | 947 | 0 | monensin_sodium_mg | numeric | Active Ingredient (1) | csb_pl_drug_active_ingredient_id | numeric | 2 (2) | monensin_sodium_mg | 1 (1) | csb_pl_drug_active_ingredient_id | Monensin | 3 |
| 53 | 948 | 0 | tylosin_phosphate_mg | numeric | Active Ingredient (1) | csb_pl_drug_active_ingredient_id | numeric | 2 (2) | tylosin_phosphate_mg | 1 (1) | csb_pl_drug_active_ingredient_id | Tylosin | 7 |
| 54 | 949 | 0 | chlortetracycline_hcl | numeric | Active Ingredient (1) | csb_pl_drug_active_ingredient_id | numeric | 2 (2) | chlortetracycline_hcl | 1 (1) | csb_pl_drug_active_ingredient_id | Chlortetracycline | 1 |
Beef Cattle Study: Template Drug Fields: Trade Names
| Field # | Field Id | Not Used | Source Name | Source Type | Category | Target Name | Target Type | Drug Route | Source Drug Name | Source Drug Units | TN Field Name | Standard TN Name | Standard TN Id |