Entering Back Data

Now that all of our existing sheep are in LambTracker and the 2014 lambs’ data is only in LambTracker I started seriously working to enter in the 16 year history of the flock into the database. I’ve discovered a number of issues and developed some workflows that seem to be making the process go much faster. This post will be updated a bit as I get everything documented.

I started LambTracker with the assumption that you would not have to enter in all the history before you started using it. This is good for allowing the users to test the viability of the software first but does make adding reams of back data more painful. Here is the current workflow I am using as I enter in 16 years of history with our flock.

1. Pick a table within the LambTracker database to work on.
2. If the table already has data in it then do a query to get all the data out and save it as a .csv file. If you do not have any data in that table now the get a copy of the blank spreadsheet document that replicates the structure of that table.

Now you have to take one of 2 paths depending on whether you are updating existing records or inserting new ones.

For updating the existing table and its data:

Bring the .csv file into a spreadsheet program. I use LibreOffice. Take care to ensure that fields that might appear to be numbers (NSIP ID, registration ID and so on) are brought in as text fields only.

Edit the spreadsheet file to update and add missing information as required.

Save a copy as a .csv file.

Use a csv to sql tool to create update statements for the file.

Back up your existing LambTracker database.

Run the queries to update the database.

Verify the new data are in correctly.

Back up the database again.

Repeat as necessary for each table to update.

For inserting new records into the table:

Edit a copy of the blank spreadsheet document that replicates the structure of that table adding data as required. Pay particular attention to the format and ensure that all text fields are really stored as text. Also dates must be in the format YYYY-MM-DD stored as text for LambTracker to work properly. Times are stored as text fields too HH:MM:SS

Save a copy as a .csv file.

Use a csv to sql tool to create update statements for the file.

Back up your existing LambTracker database.

Run the queries to insert new records into the database.

Verify the new data are in correctly.

Back up the database again.

Repeat as necessary for each table to create or add to.

There is a lot more that I’ve discovered about how to do this efficiently but this is the gist of it.