Production Lot Upload Data Preparation: Coaldale Veterinary Clinic (CVC)
Site Information Details
Coaldale Veterinary Clinic (3) (CVC)Vet: CSBV00004 (4)
Project Year: 2023
The site has seven (7) herds. They are:
CSBH0000030
CSBH0000031
CSBH0000032
CSBH0000033
CSBH0000050
CSBH0000051
CSBH0000052
CVC 2019
CVC 2020
CVC 2021
CVC 2022
Production Lot Upload Data Preparation: Coaldale Veterinary Clinic (CVC)
Data Source Information
CVC has 7 herds for 2023.Each spreadsheet/workbook from CVC represents all herds for a specific sampling year.
The core fields in a sheet have drug fields listed at the end of the core field data.
The drugs are listed as rows, not columns, within the sheet.
Core field data is repeated for each associated row of drug information.
However, the repeated core field data is implied, meaning core field information has 1 row filled in,
the remaining rows are blank until you switch to a different production lot.
During the upload of the spreadsheet into the database, missing or implied core field data is filled in.
A new column is to be added to the source spreadsheet. The column name is called "change_to_rows".
As the name implies, insert the column where the core data as columns of information, changes to drug information
which are rows of data. Each value in the "change_to_rows" column is "<<row>>". When it is setup in this way
the program to upload the data knows where column data changes to row based data for the drugs.
The rows of drug data represent 1 drug, usually a product name and active ingredient for 1 drug route.
The product name in the target database is called a trade name. The combination of both trade name and active ingredient
are used in combination to acquire the correct trade name id and active ingredient id. What is new for 2020 is
each row in the source spreadsheet with a product name and active ingredient needs to be checked.
What is done is to make certain the trade name has 1 and only 1 active ingredient associated with it. If not,
the the upload takes into account and uploads the multiple active ingredients associated with the specific trade name.
All drug routes and associated drugs for a production lot are available.
There are several extra things you should do in the spreadsheet before uploading the data:
The season or "closeout" field is not added and needs to be calculated.
Define season using the Average Date Out field in the following way:
Winter -> January 1 thru March 31
Spring -> April 1 thru June 30
Summer -> July 1 thru September 30
Fall -> October 1 thru December 31
The days on feed field needs to be calculated.
Define days on feed -> Average Date Out minus (-) Average Date In
Make sure to format all date columns as "date" in the spreadsheet. When uploading into the database the data table
will take on the date format as expected. If you do not do this it appears it is less reliable to pickup we have a date.
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.
For the 2020 CVC data all feeds are the same. Only a few new columns were added to complete the data for upload.
Production Lot Upload Data Preparation: Coaldale Veterinary Clinic (CVC)
Spreadsheet Upload Suggestions
To upload a spreadsheet into the database, here are a few suggestions and settings to successfully upload the data.The most important thing to do is to prepare the spreadsheet as best as you can.
There suggestions in pages of the website to learn what to do, but do these things and it will cut down on any upload problems.
Please note, you are uploading a worksheet of data, from a Excel workbook. Spreadsheets are loose with the rules on field names
and data structures. It is useful to a point, but when it comes to uploading data into the database we need more information.
Step 1 is open the db application, and open a survey. In the filter surveys box, set year to 2023, Vet Code to 4 and Herd Code to 30.
Click to get the survey. You will see 1 record in a list and the details about that survey record below.
In the bottom right corner of the screen you see a section called "Production Lots Summary (Read Only)".
There is a navigator bar just below. There is a button with an Up pointing arrow. Click this! It will open the screen to upload a spreadsheet.
Step 2 you will now see the Production Lot UPloads, Checks &n Integration form open.
Under Step 1:Upload tab, there are two subtabs. The first tab open is the Upload History tab. Here you can find the latest upladed spreadsheets.
The filter is filled in with the default information about the year, vet and herd codes. Click on the Green and orange button to get the list.
You will see the list of the spreadsheets which have been uploaded. It is important to note, you will have to upload the spreadsheet several times.
The reason is simple. Not all of the data will be correct. You may end up uploading a spreadsheet over 5 times before things are correct.
It is the reason we upload the data into development, not into production databases. We are developing the data, getting it right.
Select a worksheet from the list, then in the Step 2: Checking / Verfying / Validating section you can view the uploaded data table.
Now let us upload a spreadsheet for CVC. Under Step 1: Upload section, click on subtab below called Upload File.
Find Chose File and click on the Open File button. Move to the spreadsheet you want to upload into the database.
Once you do this, the folder name and workbook name will be displayed. Below that, there are some settings to be chosen to upload data.
Here are the settings to use for CVC Core Data:
Do you want to fill in core data values? Check it Select Worksheet: ToUpload Herd30
To the right of Assign Drug Route click on Open Up button
Assign Drug Route: n/a Structure Type: Mix Columns & Rows The Lightning bolt button will now be activated. Click it to upload the spreadsheet.
To view the data just uploaded, go to the Upload History tab. The filter is filled in with the most recent worksheet information.
There will be just 1 item in the list. Go to Step 2: to the Uploaded Table subtab, and click on the Greend and Organge button.
You should see your uploaded data.
Production Lot Upload Data Preparation: Coaldale Veterinary Clinic (CVC)
Template Setup Suggestions
A two template setup is used for one or more years batch of data.For the current document, assume we place all known drug fields for all drug routes at the end of the core fields.
Since the data switches from Core field columns to Drug fields as rows we insert a field in the spreadsheet
The field is called "change_to_rows". It is a column header in the spreadsheet and a field name in the uploaded data table.
When we do this, the primary, top level template created represents the Core fields.
The secondary, second level child template created represents the Drug fields associated with a Core row of data.
The Core Template is populated from the Target Table from the production lot table (core fields).
The Child Drug Template is populated from the Target Table from the drug table (drug fields).
When preparing to do the final integrate of CVC data, after the uploaded data has been checked, from the check history,
select the row where the core data has been checked.
Here are the typical template settings used for CVC.
Name: CVC 2023 - Core All Herds
Level: Top Level (1)
Type: Core (1)
Route: Not Applicable (98)
Version: Version 1 (1)
By Row or Column: By Column (2)
Method: Populate template fields from Target (1)
|
||||||
|---|---|---|---|---|---|---|
| column_number | target_column_classify_field_setup | target_field_category | target_column_type | target_column_name | source_column_name | column_not_used |
| 1 | Setup Standard Field (1) | Core Field (0) | bigint | csb_survey_production_lot_id | csb_survey_production_lot_id | Not Used (1) |
| 2 | Setup Standard Field (1) | Core Field (0) | bigint | csb_survey_main_id | csb_survey_main_id | Not Used (1) |
| 3 | Setup Standard Field (1) | Core Field (0) | bigint | csb_record_group_id | csb_record_group_id | Not Used (1) |
| 4 | Setup Standard Field (1) | Core Field (0) | bigint | csb_account_id | csb_account_id | Not Used (1) |
| 5 | Setup Standard Field (1) | Core Field (0) | bigint | csb_survey_production_lot_upload_id | csb_survey_production_lot_upload_id | Not Used (1) |
| 6 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_template_id | csb_pl_template_id | Not Used (1) |
| 7 | Setup Standard Field (1) | Core Field (0) | varchar(128) | csb_pl_production_lot_code | feedlot_lot_name | Used (0) |
| 8 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_sex_id | sex | Used (0) |
| 9 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_sex_percent_steers | csb_pl_sex_percent_steers | Not Used (1) |
| 10 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_sex_percent_cows | csb_pl_sex_percent_cows | Not Used (1) |
| 11 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_sex_percent_heifers | csb_pl_sex_percent_heifers | Not Used (1) |
| 12 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_sex_percent_bulls | csb_pl_sex_percent_bulls | Not Used (1) |
| 13 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_animal_age_category_id | cattle_type | Used (0) |
| 14 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_percent_calves | csb_pl_percent_calves | Not Used (1) |
| 15 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_percent_yearlings | csb_pl_percent_yearlings | Not Used (1) |
| 16 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_percent_adults | csb_pl_percent_adults | Not Used (1) |
| 17 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_origin_id | origin | Used (0) |
| 18 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_origin_percent_auction | csb_pl_origin_percent_auction | Not Used (1) |
| 19 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_origin_percent_backgrounded | csb_pl_origin_percent_backgrounded | Not Used (1) |
| 20 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_origin_percent_ranch_direct | csb_pl_origin_percent_ranch_direct | Not Used (1) |
| 21 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_brd_risk_id | brd_risk | Used (0) |
| 22 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_brd_risk_percent_low | csb_pl_brd_risk_percent_low | Not Used (1) |
| 23 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_brd_risk_percent_medium | csb_pl_brd_risk_percent_medium | Not Used (1) |
| 24 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_brd_risk_percent_high | csb_pl_brd_risk_percent_high | Not Used (1) |
| 25 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_quarter_of_year_closeout_id | closeout | Used (0) |
| 26 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_n_animals_in | hd_in | Used (0) |
| 27 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_n_animals_railed | csb_pl_n_animals_railed | Not Used (1) |
| 28 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_n_animals_dead | deaths | Used (0) |
| 29 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_n_animals_slaughtered | hd_out | Used (0) |
| 30 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_source_weight_in_units_id | csb_pl_source_weight_in_units_id | Not Used (1) |
| 31 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_source_weight_in_value | csb_pl_source_weight_in_value | Not Used (1) |
| 32 | Setup Multiple Target Fields (3) | Core Field (0) | numeric | csb_pl_avg_weight_in_kgs | avg_wt_in | Used (0) |
| 33 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_source_weight_out_units_id | csb_pl_source_weight_out_units_id | Not Used (1) |
| 34 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_source_weight_out_value | csb_pl_source_weight_out_value | Not Used (1) |
| 35 | Setup Multiple Target Fields (3) | Core Field (0) | numeric | csb_pl_avg_weight_out_kgs | avg_wt_out | Used (0) |
| 36 | Setup Standard Field (1) | Core Field (0) | date | csb_pl_avg_date_in | avg_date_in | Used (0) |
| 37 | Setup Standard Field (1) | Core Field (0) | date | csb_pl_avg_date_out | avg_date_out | Used (0) |
| 38 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_total_days_on_feed | dof | Used (0) |
| 39 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_total_head_days | hddays | Used (0) |
| 40 | Setup Standard Field (1) | Core Field (0) | varchar | csb_pl_notes | csb_pl_notes | Not Used (1) |
Name: CVC 2023 - Drugs All Herds
Level: Second Level (2)
Type: Drug (2)
Route: Multiple Drugs (4)
Version: Version 1 (1)
By Row or Column: By Row (1)
Method: Populate template fields from Target (1)
|
||||||
|---|---|---|---|---|---|---|
| column_number | target_column_classify_field_setup | target_field_category | target_column_type | target_column_name | source_column_name | column_not_used |
| 1 | Setup Standard Field (1) | Core Field (0) | bigint | csb_survey_production_lot_drug_id | csb_survey_production_lot_drug_id | Not Used (1) |
| 2 | Setup Standard Field (1) | Core Field (0) | bigint | csb_survey_production_lot_id | csb_survey_production_lot_id | Not Used (1) |
| 3 | Setup Standard Field (1) | Core Field (0) | bigint | csb_survey_main_id | csb_survey_main_id | Not Used (1) |
| 4 | Setup Standard Field (1) | Core Field (0) | bigint | csb_record_group_id | csb_record_group_id | Not Used (1) |
| 5 | Setup Standard Field (1) | Core Field (0) | bigint | csb_account_id | csb_account_id | Not Used (1) |
| 6 | Setup Standard Field (1) | Core Field (0) | bigint | csb_survey_production_lot_upload_id | csb_survey_production_lot_upload_id | Not Used (1) |
| 7 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_template_id | csb_pl_template_id | Not Used (1) |
| 8 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_ordinal | csb_pl_drug_ordinal | Not Used (1) |
| 9 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_drug_route_id | route | Used (0) |
| 10 | Setup Standard Field (1) | Core Field (0) | varchar(192) | csb_pl_source_drug_name | active_ingredient_name | Used (0) |
| 11 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_source_drug_value | final_active_ingredient_amount | Used (0) |
| 12 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_source_drug_value_units_id | final_unit | Used (0) |
| 13 | Setup Standard Field (1) | Trade Name (2) | integer | csb_pl_drug_trade_name_id | product | Used (0) |
| 14 | Setup Standard Field (1) | Active Ingredient (1) | integer | csb_pl_drug_active_ingredient_id | active_ingredient_name | Used (0) |
| 15 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_drug_total_mg | final_active_ingredient_amount | Used (0) |
| 16 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_drug_total_ml | csb_pl_drug_total_ml | Not Used (1) |
| 17 | Setup Standard Field (1) | Core Field (0) | bigint | csb_pl_drug_regimen_id | csb_pl_drug_regimen_id | Not Used (1) |
| 18 | Setup Standard Field (1) | Core Field (0) | bigint | csb_pl_drug_regimen_detail_id | csb_pl_drug_regimen_detail_id | Not Used (1) |
| 19 | Setup Lookup Field (2) | Core Field (0) | integer | csb_pl_drug_primary_reason_for_use | reason_for_use | Used (0) |
| 20 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_drug_primary_reason_for_use_pct_gp | csb_pl_drug_primary_reason_for_use_pc | Not Used (1) |
| 21 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_drug_primary_reason_for_use_pct_dp | csb_pl_drug_primary_reason_for_use_pc | Not Used (1) |
| 22 | Setup Standard Field (1) | Core Field (0) | numeric | csb_pl_drug_primary_reason_for_use_pct_dt | csb_pl_drug_primary_reason_for_use_pc | Not Used (1) |
| 23 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_respiratory | condition | Used (0) |
| 24 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_digestive | csb_pl_drug_secondary_reason_digestiv | Not Used (1) |
| 25 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_lameness | csb_pl_drug_secondary_reason_lamenes | Not Used (1) |
| 26 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_neurologic | csb_pl_drug_secondary_reason_neurolo | Not Used (1) |
| 27 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_liver_abscess | csb_pl_drug_secondary_reason_liver_ab | Not Used (1) |
| 28 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_coccidiosis | csb_pl_drug_secondary_reason_coccidio | Not Used (1) |
| 29 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_enteritis | csb_pl_drug_secondary_reason_enteritis | Not Used (1) |
| 30 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_mixed | csb_pl_drug_secondary_reason_mixed | Not Used (1) |
| 31 | Setup Standard Field (1) | Core Field (0) | varchar(128) | csb_pl_drug_secondary_reason_mixed_description | csb_pl_drug_secondary_reason_mixed_d | Not Used (1) |
| 32 | Setup Standard Field (1) | Core Field (0) | integer | csb_pl_drug_secondary_reason_other | csb_pl_drug_secondary_reason_other | Not Used (1) |
| 33 | Setup Standard Field (1) | Core Field (0) | varchar(128) | csb_pl_drug_secondary_reason_other_description | csb_pl_drug_secondary_reason_other_d | Not Used (1) |
| 34 | Setup Standard Field (1) | Core Field (0) | varchar | csb_pl_drug_secondary_reason_source | condition | Used (0) |
| 35 | Setup Standard Field (1) | Core Field (0) | varchar | csb_pl_drug_notes | csb_pl_drug_notes | Not Used (1) |