Monitoring health data - populating Food tables

This article continue the discussion about the building of the system to collect my health numbers.

There are already many data stored in LibreOffice Calc tables and other LibreOffice documents.
Because of that when/where possible macro will be used to generate the mySQL commands to populate the tables.

Populate T_FOOD_TYPE

Populating this table it requires more work because it is necessary to find the information about each specific food used. In order to simplify the input mechanism, a new spreadsheet that collect the information to be put in the database, is created.
Unfortunately there is no easy way to populate the spreadsheet, i.e. it must be done manually, copying the data from the existing LibreOffice word documents as described in the Monitoring health data - my way  #2 article.

Let's examine the spreadsheet used to format the insert statement.

The spreadsheet has two tabs:

• Food type
• Food

The Food type tab, is structured as the database, raw.
A hash key is prepared as index, using the Food name, the brand and the number of carbs.
This should allow to generate a unique key for the each record.

Here the fields of the tab:

Index (food type)

Name

1 Serving

Serving unit

Serv X cont

Carbs

Calories

Protein

Fat

Sodium

Potassium

Brand

Notes

The index in the cell A2 use the Hash macro (it is implied the third Party Hash macro is installed):

=TEXTHASH(CONCATENATE(B2,L2,F2),"MD5")

The goal is to have a unique index and to do so the hash is calculated using the name of the food (B2), the value in carbs (F2) and the brand (L2).
The field in the table are mostly following the SQL table structure (see Monitoring health data - an improvement).
For extra info there are these fields:

• Serv X cont
  Serving per container - the number of serving you can expect from the container or how many servings can be obtained from a recipe
• Notes
  Just extra notes about the food

After the table, there is the formula for the formatting of the SQL insert statement.

First of all, a cell (O1) contains the starting structure of the statement :

Insert into t_food_type values (

Then below, this formula is applied :

=IF(B2="","",(CONCATENATE($O$1,"""",A2,"""", ",", """", B2, """", ",", C2, ",", """", D2, """", ",", F2, ",", G2, ",", H2, ",", I2, ",", J2, ",", K2, ",",  """", L2, """", ",", """", M2, """", ");")))

The meaning is :

• if the field "Name" exists (not blank) then 
• create a string using the field O1, plus the other fields, starting from the field A2 (hash key) and ending with the field M2 (Notes)
• otherwise create a blank entry

The formula is then copied in all subsequent cells.

Here an example of the insert statement generation starting with the data on the table :

Insert into t_food_type values ("ce7993e549b4d010c825766dfd034a99","Pork Patties",2,"patty",2,270,11,24,500,0,"Jimmy Dean","");
Insert into t_food_type values ("981a21f5a752cad048a834b9fd6cd258","Cooked Ham",4,"slices",1,60,8,2,580,0,"Land O'Frost","");
Insert into t_food_type values ("1a52451ea65def964ddb3c2be50811ce","Big Cup Noodles Homestyle Beef Flavor Ramen Noodle",1,"1/2 container",24,190,3,8,760,0,"Nissin","");
Insert into t_food_type values ("74ccbb6a8f0e507375ca5f727074a4e1","Texas Style Angus Beef Chili with Black Beans Soup",1,"cup",21,210,15,5,790,0,"Campbell","");
Insert into t_food_type values ("76d5f224ea4a78e3d737caa4eba0d1e","iesta Chicken Lime Tortilla Soup with White Meat Chicken",1,"cup",13,110,9,1.5,790,0,"Campbell","");

.............................................................

.............................................................

After the table is manually populated, we end up with a list of insert capable to fill up the T_FOOD_TYPE table.

Populate T_FOOD

The same mechanism used  to populate the T_FOOD_TYPE table, is used to populate the T_FOOD table too.

Like before, a new spreadsheet that collect the information to be put in the database is created (still refer to the Monitoring health data - my way  #2 article)

Let's examine the spreadsheet used to format the insert statement.

The spreadsheet has two tabs:

• Food type
• Food

The Food tab, is structured as the database, raw.

Here the table structure:

Index

Date Meal

Type Meal

FoodIndex

Food name

Servings

Calculated carbs

Calculated salt

The Calculated carbs and Calculated Salt are populated using data from the previous table.

Calculated carbs is :

=VLOOKUP(D2,'Food type'.$A$2:$M$156, 6, 0)*F2

The meaning is to look in the all previous tab (Food type) of the spreadsheet for the FoodIndex key, retrieve the number of carbs per serving and multiply for the number of serving (field Servings on this tab).

Similarly for the Calculated salt :

=VLOOKUP(D2,'Food type'.$A$2:$M$156, 10, 0)*F2

The meaning is to look in the all previous tab (Food type) of the spreadsheet for the FoodIndex key, retrieve the number of mg of salt per serving and multiply for the number of serving (field Servings on this tab).
Just to have a quick idea about the amount of salt of the selected food.

The Index is an hash key using the Date Meal, the FoodIndex and the Food name fields. 

This should allow to generate a unique key for the each record.
The cell A2 contains this formula :

=TEXTHASH(CONCATENATE(B2,C2,D2,E2),"MD5")

Again the purpose is to obtain a unique key for each entry, to do so it used the date of the meal (B2), the type of the meal (C2, e.g. Breakfast or Lunch or Dinner or Snack), the food index (D2, foreign key) and the food name (E2).
Note that the food name field is populated from the other Calc table (see above).

After the table, there is the formula for the formatting of the SQL insert statement.

First of all, a cell (I1) contains the starting structure of the statement :

Insert into t_food values (

Then below, this formula is applied :

=IF(B2="","",(CONCATENATE($H$1, """", A2, """", ", str_to_date('",(TEXT(B2,"mm/dd/yy")), "', ", """", "%m/%d/%Y", """", "), ",        """",C2,"""",", ","""",D2,"""", ", " ,F2, ");")))

The meaning is :

• if the field "Date Meal" exists (not blank) then 
• create a string using the field H1, plus the other fields, starting from the field A2 (hash key) and ending with the field F2 (Servings)
• otherwise create a blank entry

The formula is then copied in all subsequent cells.

Here an example of the insert statement generation starting with the data on the table :

Insert into t_food values (84280b208c8c61a7aab5c4be5fb25385, str_to_date('12/29/14', "%m/%d/%Y"), "Breakfast", "ac118ebecb401ae2e9fe42b8f7b1da8c", 2);
Insert into t_food values (16df74f3237c526a71186884b0cd7aa1, str_to_date('12/29/14', "%m/%d/%Y"), "Breakfast", "254a86d2153bfbc149f51d63c7113952", 1);
Insert into t_food values (532061254f4761e69bcf627c4916f3c2, str_to_date('12/29/14', "%m/%d/%Y"), "Lunch", "1b4f22d0d92b9185406cf6d815fd2c4c", 1);
Insert into t_food values (34c6041c3126488e599aa467e8a0d4de, str_to_date('12/29/14', "%m/%d/%Y"), "Lunch", "af3a803a27460603422bf47322ba6d4", 2);
Insert into t_food values (532061254f4761e69bcf627c4916f3c2, str_to_date('12/29/14', "%m/%d/%Y"), "Dinner", "1b4f22d0d92b9185406cf6d815fd2c4c", 2);
....................................................................
....................................................................

Basically after the table is manually populated, we end up with a list of insert capable to fill up the T_FOOD table.