Help get this topic noticed by sharing it on Twitter, Facebook, or email.

EshaPort SQL Export Options

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?
1 person has
this question
+1
Reply
  • 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.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • 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.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • The exported format is CSV. This is to allow import into other programs that may or may not be compatible with SQL.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • 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
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • 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.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • 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
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • 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?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • 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
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • 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.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • 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
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • 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.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated