EshaPort SQL Export Options

  • Question
  • Updated 4 years ago
Using the EshaPort SQL program, what options are available for export, and is there a sample file available with a full export of recipes and ingredients will all nutritional data?
Photo of Ben Sarsgard

Ben Sarsgard

  • 6 Posts
  • 0 Reply Likes

Posted 4 years ago

  • 1
Photo of Josh

Josh, Developer

  • 22 Posts
  • 0 Reply Likes
With EshaPort you are able to export any user added ingredients, recipes and labels. You select what nutrients you want exported and the order those nutrients will be in. I don't have a sample file handy, but if you would like, email support@esha.com and we can send you an exported file.
Photo of Ben Sarsgard

Ben Sarsgard

  • 6 Posts
  • 0 Reply Likes
Thanks; from the information I've found, it looks like the export format is a CSV file, correct? I was hoping from the name that getting an actual SQL file might be possible so that I can easily load it into a database and query it.
Photo of Josh

Josh, Developer

  • 22 Posts
  • 0 Reply Likes
The exported format is CSV. This is to allow import into other programs that may or may not be compatible with SQL.
Photo of Ben Sarsgard

Ben Sarsgard

  • 6 Posts
  • 0 Reply Likes
I haven't received a reply to my support@esha.com email, so I was hoping you could help since your responses have been very prompt. I'm thinking I need to write my import process to take three separate exports:

1) Operation Type: Ingredient
2) Operation Type: Recipe - Data
3) Operation Type: Recipe - Ingredients

1 & 2 can be imported into my database to build a reference of nutrition information, and then 3 can link the two together. So a set might look something like this?

1) Ingredient
"Name", "Primary Key", "Calories"
"Tomatoes", 123456, 100
"Lettuce", 234567, 50
"Ground Beef", 345678, 200
"Bacon", 456789, 300
"Bun", 567891, 100

2) Recipe - Data
"Name", "Primary Key", "Calories"
"Burger", 987654, 450
"Bacon Burger", 876543, 750
"BLT", 765432, 550

3) Recipe - Ingredients
"Recipe", "Ingredient"
987654, 123456
987654, 234567
987654, 345678
987654, 567891
876543, 123456
876543, 234567
876543, 345678
876543, 456789
876543, 567891
765432, 123456
765432, 234567
765432, 456789
765432, 567891

Is this correct? The one I'm least clear on is #3

Thanks,
Ben
Photo of Josh

Josh, Developer

  • 22 Posts
  • 0 Reply Likes
You're very close. First, if your intention is to create the ingredients and recipes, then you won't need the Primary Key field because that will be generated in the database for you. You only need this field if you want to then later update data that you've already imported.

For Ingredients, you are required to define a Name and a Gram Weight. You can then also include any additional fields such as Nutrients. So:

1) Ingredient
"Name","Gram Weight","Calories"
"Tomatoes",100,100
"Lettuce",50,50

For Recipes, you don't need to import the analyzed data, because that is calculated based on the Ingredients. To link up the Ingredient, it would be best for you to "export" the ingredients you just imported to get their proper Primary Key. You can just rely on name match, but that won't be as accurate. So a sample Recipe import at a minimum would look like:

2) Recipe
"Name","Item Primary Key","Item Quantity","Item Measure"
"Burger",12345,100,"Gram"
"Burger",12347,50,"Gram"
"Burger",12398,30,"Gram"

This will create "Burger" with 3 ingredients.

Let me know if you have more questions.
Photo of Ben Sarsgard

Ben Sarsgard

  • 6 Posts
  • 0 Reply Likes
Thanks for the reply, and yes I did indeed intend on exporting the primary key from EshaPort to store in a reference field in my database, so that I can perform regular updates after the initial import.

Using you example, I'm not sure how the "Burger" item is matching up with the ingredients? What is the "Item Primary Key" column referencing if the Ingredient import didn't specify it? If I'm not pulling a primary key for the recipe, what if there are two recipes named "Burger?"

Can you also clarify for me what's going on with the gram weights? I think what I'm seeing here is that an ingredient might be specified as being a measure of 100 grams. So in that case, if I associate a recipe as having 100 grams of that ingredient, it's essentially one "measure" of that ingredient? Is it possible that I could experience half-measures of ingredients, so for instance 150 grams of tomatoes would mean I have to multiply all nutrition data by 1.5?

Thanks,
Ben
Photo of Josh

Josh, Developer

  • 22 Posts
  • 0 Reply Likes
The "Item Primary Key" is what links a recipe to the Ingredient (or sub recipe). So the first Import that I labeled above will "create" the 3 ingredients. You then take the primary key's created during that import to build your recipe import.

The recipe is imported with each item in the recipe on a new line. In the example I showed above a recipe called Burger will be created. The way I have that set up is it will match on Name. If the recipe already exists in the database, you could use a Primary Key field in place of Name as well. But during the initial import, Name will be needed to create the Recipe. So if you have multiple recipes with the same name, you will have to modify the name slightly so they don't just add all the ingredients to one big recipe.

As far as the Amounts go with the Recipe Items. Since weight has been established for the ingredients in the initial import, you can use whatever variation of weight amounts and measures when you import your recipes. For example, I could have easily done this:

Recipe Import
"Name","Item Primary Key","Item Quantity","Item Measure"
"Burger",12345,3.876,"Pounds"
"Burger",12347,0.34,"Ounce-weight"
etc.

Does that help out?
Photo of Ben Sarsgard

Ben Sarsgard

  • 6 Posts
  • 0 Reply Likes
Thanks very much Josh, this is definitely helping, but I think there's some miscommunication going on. What I'm looking for is a one-way only export of data from the Genesis system into my own database. I'm either confused about something, or you appear to be describing an import into the Genesis system?

Thanks,
Ben
Photo of Josh

Josh, Developer

  • 22 Posts
  • 0 Reply Likes
Whoops. You are correct. I switched gears on you mid-stream it seems. I apologize for that. Back to your original post then, you're 1 and 2 would be correct. And for 3 your export can look like:

"Name","Primary Key","Item Primary Key","Item Quantity","Item Measure"

Or since it is an export, you can remove Name and just link your Primary Key with the one exported from the database. There are no required fields for exports, so you can choose whatever you would like.

Sorry for the confusion.
Photo of Ben Sarsgard

Ben Sarsgard

  • 6 Posts
  • 0 Reply Likes
Perfect, that sounds like it will work great then. There are only two things I still have left unaccounted for, which I'm not sure if there's a standard process for or if it really needs to wait until I'm given access to the source data and see how it's been set up.

First, how will multi-size items be presented? For instance, small/medium/large beverages or burgers with one or two patties? Are those sent over as completely distinct recipes or is there some way to mix them?

Second, can a complete recipe be used as an ingredient for another recipe? For instance, if french fries or mozzarella sticks are added as a topping on a burger? Is there some way that a recipe is indicated as included within another recipe, or is it simpler to just consider these as distinct items as well as ingredients?

Thanks,
Ben
Photo of Josh

Josh, Developer

  • 22 Posts
  • 0 Reply Likes
Multi-size items like you mention are generally kept as separate ingredients and/or recipes. Users can define measures other than gram weight for the ingredients. Those are exported in the Volume Quantity/Measure/Gram Weight and Unit Quantity/Measure/Gram Weight fields.

For example:

"Name","Primary Key","Gram Weight","Nutrient1","Nutrient2","Unit Quantity","Unit Measure","Unit Gram Weight"
"Root Beer",12345,100,50,400,1,"Can",200

What the Unit Quantity/Measure/Gram Weight then means is "1 can of Root Beer weighs 200 grams". I only bring this up in case you come across data like that.

In regards to your second question, yes recipes can be used within recipes. That is why in the Recipe Items export it is "Item Primary Key" and not "Ingredient Primary Key". The Item Primary Key can be either an Ingredient or a Recipe. You can include the "Item Document Type" field to determine whether or not this item is a recipe or ingredient.