Production Lot Data: A Summary of Steps

There are three steps:

• Step 1: Upload From a Spreadsheet into the PostGreSQL Database
• Step 2: Checking / Verifying / Validating Uploaded Data
• Step 3: Conversion and Integration into Final Target Database

Production Lot Uploads: The Three Steps - Summary

A user has added new survey record. What we have so far is a veterinarian, vet group, herd and year in that record.
We need a lot more information for the herd. For the current survey the way to add more information to the survey is by uploading a spreadsheet of data.
The data to be uploaded is called "Production Lot Data" and the production lot records have associated drug information.
The spreadsheet contains important information. Each spreadsheet can represent different data depending on the source veterinarian.
Here we upload the spreadsheet of data into the production database.
Once a spreadsheet is selected and open, it is not automatically loaded into the database.
Every site keeps provides there data in some form or another.
However, every site does not seem to have the same structure compared with other sites.
It is possible, a particular site's data for a specific year may be different to the next year.
Many variations of what the source data should be and what is actually in the data can occur
Because of this we need to check the data. The user can prepare a spreadsheet to make it presentable to the database.
However, I found even after preparing a spreadsheet, it is possible the data does not match what we need. Thus the second step is to check.
Once a spreadsheet is checked and declared ready to go, then the final step is to actually "integrate" the data into the production database tables.
Once the production lot screen opens, you see the screen setup as three steps as you glance from left to right.

If you have a problem opening the spreadsheet, you may have to install a MS Office tools.
I have this file available if required: AccessDatabaseEngine2010.exe
You can google "download AccessDatabaseEngine2010" and you should be taken to the MS Downloads.

Production Lot Uploads: Step 1 - Upload

Upload, what are we doing?
Each site provides data for the survey in a spreadsheet format.
The spreadsheet format is to be an excel file.
A spreadsheet arrives in the Project Managers office.
Each spreadsheet when it arrives will have provided production lot core data along with drug data.
Step 1 upload does not assume the each spreadsheet contains core data + drug data for one and only 1 drug route.
The data in the spreadsheet will need to "fit" into the database which has pre-defined fields.
The database provides the common data structure for all site data. When all data for each year is integrated and common, lots of nice reporting can occur.
During the first step all we want to do is upload the spreadsheet into the database, whatever format it comes as!

Once the data is uploaded it will be in a format the database knows. In this case it will be PostGreSQL.

The Production Lot form will open. It is opened from the Survey form. You will have to be on a specific survey record before this form will open.
When the form opens you will see at the top the Group, Year, Vet Code, Herd Code and Project Code for the survey.
The uploaded spreadsheet will pertain to only this survey, please take note!
On the left hand side of the screen you will see a panel labelled "Step 1: Upload".
Below you will see 2 tabs. The first tab is labelled "Upload History". Here we keep track of every upload that has occurred.
The second tab is labelled "Upload File". To upload a new spreadsheet click on "Upload File" tab.
Go to "Upload File" tab.
Click on "Chose File" button which has a little picture of a file beside it.
Find the file you want to open. At this stage, we have opened the Workbook. It is not uploaded yet!
Once a file is chosen, you will see the workbook name or actual file name of the excel spreadsheet uploaded.
There is a line labelled "Select Worksheet". Click the drop down box and select the worksheet you want to upload.
Next go to line where it says "Assign Drug Route". Chose one of either Bolus, Feed or Injectable.
If you are uploading more than one drug at a time chose "Mix".
Once a drug route is chosen, click on button just to the right. It will open the spreadsheet and grabs information about the spreadsheet.
The final thing you need to do is chose a "Structure Type".
Chose one of either "By Columns", "By Rows" or "Mix Columns & Rows".
Once you have made all the selections you will see a button to the right of "Structure Type" with a lightning bolt on it.
Click on the button. You will upload the spreadsheet data into the database into a temporary table.
The upload will go thru the spreadsheet grabbing the data from every column of every row and inserting it into and "Upload" repository of spreadsheets.
During the upload an attempt is made to create the table structure having date columns into date fields in the database and so on.
Once the upload is completed, you will be automatically taken to the upload history tab in Step 1.
There you will see a listing of your spreadsheet data.
An upload history record will be created so you can always to back to open the data later.
If you select the history entry you will be able to see that data in Step 2 "Uploaded Table" tab.
If you do this a replica of the spreadsheet data will be available from the database upload repository for viewing.
There are reasons to do this as we will get to next.

Production Lot Uploads: Step 2 - Checking / Verifying / Validating

The next step is to check the data. To check we need to open a database table from a previously uploaded spreadsheet of data
Temporarily go to Step 1 and click on the tab "Upload History". Chose the table you want to open.
Now back to Step 2 and click on "Uploaded Table". Click on the button with a green top and orange bottom, like a carrot.
After clicking on it you will see all the data in the table which was uploaded.

Now on Step 2 click on tab "Check Upload". You will see "Select a Template" and a drop down box just below it.
Select the Template you have prepared previously. The templates listed are specific to the Site or Vet associated with the project.
If a template is not available there is a button above to the left of label "Step 2: Checking / Verifying / Validating".
Click on that button to open the Template Management form.
To learn more about how to setup a template click on the menu item on the left called "Templates".

Assume you have selected a template and have an open data table.
Just below your template selection is a label "Click to being checking:" and a button to the right with a check mark on it.
Click on the button and the checking process will begin. You will prompted if you want to proceed.
Click on Yes and the checking will happen. It is important to note the checking is happening on the server.
There is very little computation happening on your workstation. Just sit back and wait. Once it is completed information appears below.
Below you will see a label "Step 2: Check Diagnostics". There you will see how many rows there are, how many columns, and so on.
If the check came up with errors or omissions then you can go thru the different tabs of information.
Each tab has specific informatoin about what was checked and what happened. The final tab is "Error Messages".
The bottom of the first tab labelled "Diagnostics" has a statistic called "Total # of errors reported:". If it reports 0 we are usually good.
If it is not zero, then look at the 4 rows of diagnostic statistics above counting a number of different things.
If there are errors after a check, it is not a good idea to upload the data. Get the data as clean as you can.

The final tab to take note of on Step 2 is "Check History" tab. If you go into that tab, you can open up a list of
previously checked data. There maybe 1 or more check histories for each production lot table uploaded.
The reason to do this is you may have to check several times to finally clean up your mistakes or update a template.
Please note you can check one data table more than once.

For the final step, to upload the data into the database, you will start here at Step 2: Check History tab.

Production Lot Uploads: Step 3 - Conversion and Integration

To begin integrating the data into the database, go to Step 2: Check History tab. Open the list of checks.
They are sorted in descending order. The rows are color coded. A deep green and money green row are good enough to be uploaded.
Select which data check you want to use to integrate the data.
Now go to the Step 3: Conversion and Integration section on the right.
There are 2 tabs. The first tab is "Integrate Checked Data". The second tab is "Integrate History".
To upload checked data go to Step 3: Integrate Checked Data. I will repeat make sure you have selected a checked record from Step 2 Check History.
Under Step 3 you will see a label "Click to prepare to upload and integrate data into production:" and button to the right of the label.
Click on the button. You see some diagnostics if an attempt to upload table previously and if so how many times.
If it shows it was successful it is a good idea to not uploaded the data again.
Below this you will see a label "Click to begin uploading and integrating data into production:" with a button to the right with a + on it.
Click on that button and you are prompted if you want to upload the data and integrate into the database.
Click on Yes. All the work will be done on the server, not on the users workstation. Once completed you will a row of tabs at the bottom.
Click on tab "Integrated Diagnostics". Please take note if any errors occurred.
You can upload a data table several times. The integration will not overwrite existing records. If it finds any core or drug
data it will document it found these records, skip over them and move to the next record.
You have uploaded data into the database.

If you want to see the data, close the Production Lot form and return to the survey. Refresh you records and you will see your new data.