Entering Back Data

Note: this blog post was originally posted on lambtracker.com and has been moved here for preservation. Some information may be outdated.

Now that all of our existing sheep and 2014 lambs’ data are in LambTracker®, I’ve started seriously working to migrate our 16-year flock history into the database. I discovered a number of issues along the way. Software workflows seem to make the process go faster. This post will be updated as I get everything documented.

I started LambTracker® with the idea that you wouldn’t have to enter your entire history before using it. This is good for allowing users to test the viability of the software. It does make adding reams of back data more painful, though. Here is the current workflow I’m using as I enter our flock history. First, pick a table within the LambTracker® database to work on. If the table already has data in it, do a query to get it and save it as a .csv. If there’s no data in the table, prepare a blank spreadsheet document that replicates the structure of that table.

Now you have to take one of two paths, depending on whether you’re updating existing records or inserting new ones.

For updating an existing table and its data, do the following:

1. Bring the .csv file into a spreadsheet program (I use LibreOffice). Note that fields that might appear as numbers (NSIP ID, registration ID, and so on) should be brought in as text fields only.
2. Edit the spreadsheet file to update and add missing information as required.
3. Save a copy as a .csv file.
4. Use a CSV to SQL tool to create update statements for the file.
5. Back up your existing LambTracker® database.
6. Run the queries to update the database.
7. Verify that the new data are in correctly.
8. Back up the database again.
9. Repeat as necessary for each table you update.

To insert new records into the database, do the following:

1. Edit a copy of the blank spreadsheet document, adding data as required. Pay attention to the format and ensure that all text fields are stored as text. All dates must be in the text format YYYY-MM-DD for LambTracker® to work properly. Times are stored as text fields, too (HH:MM:SS).
2. Save a copy as a .csv file.
3. Use a CSV to SQL tool to create update statements for the file.
4. Back up your existing LambTracker® database.
5. Run the queries to insert new records into the database.
6. Verify that the new data are in correctly.
7. Back up the database again.
8. Repeat as necessary for each table you create.

I’ve discovered much more about how to do it efficiently, but this is the gist of it.