Skip to content

Instantly share code, notes, and snippets.

@rgstephens

rgstephens/exportPepper.sh

Created November 6, 2016 22:11
Show Gist options
  • Save rgstephens/bd76d83510b772bacfa98e7d93746c5b to your computer and use it in GitHub Desktop.
Save rgstephens/bd76d83510b772bacfa98e7d93746c5b to your computer and use it in GitHub Desktop.
Download ZIP
Pepperplate Export to Paprika YAML
Raw
exportPepper.sh
#!/bin/sh
ids=`sqlite3 ./recipes_2.db <<!
.mode csv
select Id from Recipe;
!`
dumprecipe() {
sqlite3 ./recipes_2.db <<!
.separator ' '
select '- name: ', Title from Recipe where Id = $1;
select ' servings: ', Yield from Recipe where Id = $1;
select ' source: ', Source from Recipe where Id = $1;
select ' description: ', Description from Recipe where Id = $1;
select ' prep_time: ', ActiveTime from Recipe where Id = $1;
select ' cook_time: ', TotalTime from Recipe where Id = $1;
select ' notes: ', Note from Recipe where Id = $1;
!
sqlite3 ./recipes_2.db <<!
.separator ' '
select 'categories: [', Tag.Text, ']' from ItemTag join Tag on Tag.Id = ItemTag.TagId where ItemTag.RecipeId = $1;
!
echo ' ingredients: |'
sqlite3 ./recipes_2.db <<!
.separator ' '
select ' ', IngredientGroup.Title, Ingredient.Quantity, Ingredient.Text from IngredientGroup join Ingredient on Ingredient.GroupId = IngredientGroup.Id where IngredientGroup.RecipeId = $1 order by IngredientGroup.DisplayOrder, Ingredient.DisplayOrder;
!
echo ' directions: |'
sqlite3 ./recipes_2.db <<!
.separator ' '
select ' ', DirectionGroup.Title, Direction.Text from DirectionGroup join Direction on Direction.GroupId = DirectionGroup.Id where DirectionGroup.RecipeId = $1 order by DirectionGroup.DisplayOrder, Direction.DisplayOrder;
!
}
#for id in $ids
for id in 483 1 435
do
dumprecipe $id
echo
done
@wsryno
Copy link

wsryno commented Apr 1, 2019 •
edited
Loading

Many thanks @rgstephens for the script and @EliTayrien for additional information I needed to figure this out. I am sure there are many ways to do this, but after hours of trial and error, I came to this method. It is not perfect but works well enough. I found it easiest to use a Windows computer to get a copy of the PepperPlate database file (the most confounding part of the whole process) and a Mac computer to run the .sh script. If you can successfully install Cygwin and Sqlite3 on your Windows computer then you are more capable than me and can run the .sh script on your Windows machine. And you probably don't need this tutorial!

On the Windows computer:

  1. Install PepperPlate client (app) on Windows machine using Microsoft Store. If you cannot figure out how to do this, then stop here. You will not be able to complete the rest of this procedure.
  2. Launch PepperPlate and login to your existing PepperPlate account.
  3. After all of your recipes have loaded, use File Explorer to locate the recipe database file. You can either search for recipes_2.db or navigate to the following folder for the database file:
    c:/Users/USER/AppData/Local/Packages/PepperPlate.RecipeMenuCookingPlanner…/LocalState/
  4. Copy recipes_2.db to a thumb drive (to transfer to the Mac).
  5. Navigate to github for the script. (You are already here but the link below will allow this procedure to be copied/pasted anywhere.) Many thanks to the authors and commentators at this thread:
    https://gist.github.com/rgstephens/bd76d83510b772bacfa98e7d93746c5b
  6. Highlight the script, Ctrl-C to copy it, launch Notepad and Ctrl-V to paste it. Save the file as exportPepper.sh. Note that I used the first script <> Code and neither of the revised scripts.
  7. Copy exportPepper.sh to a thumb drive (to transfer to the Mac).

You now have the 2 files on a thumb drive needed to do the conversion: the PepperPlate database file recipes_2.db and the script exportPepper.sh.

On the Mac OSX computer:

  1. Copy recipes_2.db and exportPepper.sh from the thumb drive to the USER folder on the Mac. In Finder, this is the folder with the house icon next to it.
  2. Launch Terminal and type this command:
    bash ./exportPepper.sh >> converted_database.yml
  3. Launch TextEdit (or a better text editor such as Brackets or BBEdit) and open the new file converted_database.yml.
  4. Use Command-A and Command-C to select all the text and copy it to the clipboard.
  5. Navigate your browser to a YAML validator, I found this one works well:
    https://codebeautify.org/yaml-validator
  6. Click in Line 1 of the validator and Command-V to paste the text (converted database).
  7. Scroll to the bottom of the validator web page and click Validate.
  8. Errors in the YAML will be identified by line number. Scroll to the line number and fix the text. I found 2 types of “errors” in my converted recipes_2.db file. One was caused by Notes fields with multiple lines (paragraph marks) and the other was text with multiple lines (paragraph marks) in the Description field. Simply use the Delete function within the YAML validator to “bring together” the multiple lines or delete the unwanted text in fields that have errors. There may be other types of errors you’ll need to fix. Click Validate to find the next error in the file.
  9. Continue to fix the errors and click Validate until the message “Valid YAML” is shown.
  10. Click in the text field on the validator, Select All and Copy the text, navigate to TextEdit and open a new file, Paste the corrected YAML text into the editor, and save the file as converted_fixed.yml
  11. Import the converted and fixed YAML file into your desired recipe manager, such as Paprika.

There now, wasn’t that easy!

@kisel1
Copy link

kisel1 commented Apr 3, 2019

Many thanks @rgstephens for the script and @EliTayrien for additional information I needed to figure this out. I am sure there are many ways to do this, but after hours of trial and error, I came to this method. It is not perfect but works well enough. I found it easiest to use a Windows computer to get a copy of the PepperPlate database file (the most confounding part of the whole process) and a Mac computer to run the .sh script. If you can successfully install Cygwin and Sqlite3 on your Windows computer then you are more capable than me and can run the .sh script on your Windows machine. And you probably don't need this tutorial!

On the Windows computer:

  1. Install PepperPlate client (app) on Windows machine using Microsoft Store. If you cannot figure out how to do this, then stop here. You will not be able to complete the rest of this procedure.
  2. Launch PepperPlate and login to your existing PepperPlate account.
  3. After all of your recipes have loaded, use File Explorer to locate the recipe database file. You can either search for recipes_2.db or navigate to the following folder for the database file:
    c:/Users/USER/AppData/Local/Packages/PepperPlate.RecipeMenuCookingPlanner…/LocalState/
  4. Copy recipes_2.db to a thumb drive (to transfer to the Mac).
  5. Navigate to github for the script. (You are already here but the link below will allow this procedure to be copied/pasted anywhere.) Many thanks to the authors and commentators at this thread:
    https://gist.github.com/rgstephens/bd76d83510b772bacfa98e7d93746c5b
  6. Highlight the script, Ctrl-C to copy it, launch Notepad and Ctrl-V to paste it. Save the file as exportPepper.sh. Note that I used the first script <> Code and neither of the revised scripts.
  7. Copy exportPepper.sh to a thumb drive (to transfer to the Mac).

You now have the 2 files on a thumb drive needed to do the conversion: the PepperPlate database file recipes_2.db and the script exportPepper.sh.

On the Mac OSX computer:

  1. Copy recipes_2.db and exportPepper.sh from the thumb drive to the USER folder on the Mac. In Finder, this is the folder with the house icon next to it.
  2. Launch Terminal and type this command:
    bash ./exportPepper.sh >> converted_database.yml
  3. Launch TextEdit (or a better text editor such as Brackets or BBEdit) and open the new file converted_database.yml.
  4. Use Command-A and Command-C to select all the text and copy it to the clipboard.
  5. Navigate your browser to a YAML validator, I found this one works well:
    https://codebeautify.org/yaml-validator
  6. Click in Line 1 of the validator and Command-V to paste the text (converted database).
  7. Scroll to the bottom of the validator web page and click Validate.
  8. Errors in the YAML will be identified by line number. Scroll to the line number and fix the text. I found 2 types of “errors” in my converted recipes_2.db file. One was caused by Notes fields with multiple lines (paragraph marks) and the other was text with multiple lines (paragraph marks) in the Description field. Simply use the Delete function within the YAML validator to “bring together” the multiple lines or delete the unwanted text in fields that have errors. There may be other types of errors you’ll need to fix. Click Validate to find the next error in the file.
  9. Continue to fix the errors and click Validate until the message “Valid YAML” is shown.
  10. Click in the text field on the validator, Select All and Copy the text, navigate to TextEdit and open a new file, Paste the corrected YAML text into the editor, and save the file as converted_fixed.yml
  11. Import the converted and fixed YAML file into your desired recipe manager, such as Paprika.

There now, wasn’t that easy!

Is there by any chance a way to complete the same task but only on a Windows computer, without a Mac? Thank you!

@alimbocl
Copy link

Above instructions work great however I have one issue.

recipes_2.db looks pretty small to me - only 425 KB. When I run the script on it it only outputs one recipe. We have ~100 recipes in Pepperplate. Any clue as to why only one?

@wsryno
Copy link

wsryno commented May 12, 2019 •
edited
Loading

@kisel1 yes, to do the conversion on a Windows computer you need to be able to execute a .sh file on it. There are instructions on Github and elsewhere, however I tried to install the programs necessary to do so but could not get it to work. It was easier to just bash using the Mac's native terminal. I happen to have access to a Mac.

@alimbocl I have about 180 recipes and the db was about 570kb, so I think your 425kb file sounds right. There is probably an issue with the .yaml where the delimiters for recipes are not formatted properly. Did you run the .yaml through the YAML validator?

Anybody reading this, if you want to PM me your recipe_2.db file I can process it for you and give you back the .yaml. It only takes a minute for me to do this. You'd still need to process the .yaml through the validator before importing into Paprika. I do not know Github very well but I assume there is a way to PM users.

@alimbocl
Copy link

@kisel1 yes, to do the conversion on a Windows computer you need to be able to execute a .sh file on it. There are instructions on Github and elsewhere, however I tried to install the programs necessary to do so but could not get it to work. It was easier to just bash using the Mac's native terminal. I happen to have access to a Mac.

@alimbocl I have about 180 recipes and the db was about 570kb, so I think your 425kb file sounds right. There is probably an issue with the .yaml where the delimiters for recipes are not formatted properly. Did you run the .yaml through the YAML validator?

Anybody reading this, if you want to PM me your recipe_2.db file I can process it for you and give you back the .yaml. It only takes a minute for me to do this. You'd still need to process the .yaml through the validator before importing into Paprika. I do not know Github very well but I assume there is a way to PM users.

I did run it through the YAML validator. Took a look at the file too and there was only one recipe in there. Definitely odd but not a huge deal. We ended up just importing stuff manually into Paprika, it took about 20 minutes. Their import process is well thought out and easy to execute.

Also just wanted to say that I appreciate you reaching out to the peeps in this thread and offering help. Folks should take a lesson from this and pay it forward.

@shackrock
Copy link

@wsryno this is embarassing but I can't for the life of me figure out how to PM somebody in Github, I'd love to pass you my DB file, I'm windows only and it's rough!

@rgstephens
Copy link
Author

Unfortunately, I don't have the development environment for this print running anymore. The source code is in Grails and I can post that if you want to run it yourself

@wsryno
Copy link

wsryno commented Jun 23, 2019 via email

@shackrock
Copy link

shackrock commented Jun 23, 2019 via email •
edited
Loading

@wsryno
Copy link

wsryno commented Jun 23, 2019

@shackrock OK I did not realize that responding to the email thread resulted in posts here! I told you I don't know much about how GitHub works. I downloaded the DB and will try processing it for you.

@wsryno
Copy link

wsryno commented Jun 23, 2019 •
edited
Loading

converted_database yml

Ok I did the same thing, I converted the file to yml then renamed .png. You should be able to download and remove the .png extension then process through the yaml validator. It looks like all the recipes are there, but it will take a lot of work with the validator. Any recipes with Notes will be problematic if the notes contain " anywhere inside them, as many of your notes seem to. The " is used as an "end of Notes" delimiter so the yaml validator will flag many errors before you can clean it up. Let me know if the conversion works for you.

Edit: Also, notes with line breaks can be fixed by removing the line breaks.

@shackrock
Copy link

Got it, thanks!! Hope everyone enjoys my recipes =)

@wsryno
Copy link

wsryno commented Jun 24, 2019 via email

@MrRobotMan
Copy link

I was having a ton of trouble getting this to run (mostly because I don't use a mac), so I recreated it in Python.
https://gitlab.com/dweiss2/pepperplate-export-to-paprika

@bjb1959
Copy link

bjb1959 commented Oct 9, 2019

I was having a ton of trouble getting this to run (mostly because I don't use a mac), so I recreated it in Python.
https://gitlab.com/dweiss2/pepperplate-export-to-paprika

David, I tried your script in windows 10 and get a syntax error in line 1 {!DOCTYPE html}

@MrRobotMan
Copy link

Oddly this did run on my machine which is also Win10. There's a typo in the first line where I put an extra space after the "!". On windows you don't need this line at all however.

@SpatiallyME
Copy link

I was having a ton of trouble getting this to run (mostly because I don't use a mac), so I recreated it in Python.
https://gitlab.com/dweiss2/pepperplate-export-to-paprika

Thanks for creating this Python to use on Windows!
I have tried running it and it states that there is a syntax error in line 28 position 29. Any thoughts?
Can you provide a little bit of instruction similar to what people have posted above in regards to the Mac instructions? i.e. Place downloaded files at this location, change this to point to pepperplate file, etc.

@MrRobotMan
Copy link

MrRobotMan commented Oct 17, 2019 via email

@bjb1959
Copy link

bjb1959 commented Oct 18, 2019 via email

@stevesw
Copy link

stevesw commented Oct 20, 2019

Hi everyone. We have added support for directly importing Pepperplate .db files into the latest version of Paprika, so you don't need to go through this process anymore. :)

@julianpoy
Copy link

julianpoy commented Nov 2, 2019 •
edited
Loading

Same with RecipeSage (recipesage.com) if you're looking for a free alternative :)

Doesn't require a .db file since it grabs the recipes directly from Pepperplate itself.

@bjb1959
Copy link

bjb1959 commented Nov 2, 2019 via email

@bjb1959
Copy link

bjb1959 commented Nov 5, 2019 via email

@wsryno
Copy link

wsryno commented Nov 5, 2019

I tried importing from Pepperplate into recipesage.com and it appears to have worked quite well. I have not had time yet to try recipesage but I look forward to seeing if it suits my needs.

@julianpoy
Copy link

julianpoy commented Jan 23, 2020 •
edited
Loading

I've updated the RecipeSage import tool after some feedback from users. It's a bit faster and more reliable now.

Also, just an FYI - RecipeSage supports export, so even if you're not looking to use RecipeSage long term, you can use it to transform your recipes into text, JSON, or XML.

Link: https://recipesage.com

@jlfwong
Copy link

jlfwong commented Jan 23, 2020

For anyone else using this and seeing the export contain only 3 recipes, it's because of the following lines:

#for id in $ids
for id in 483 1 435

If you change this to just:

for id in $ids

then all recipes will be exported. The version with the "483 1 435" was presumably there for debugging to avoid dumping too much data at once. (Sorry if this is stated elsewhere in the thread -- I didn't see it)

@ProfEiz
Copy link

ProfEiz commented Sep 28, 2020

For anyone looking, please note that at least as of late September 2020, Paprika now directly imports Pepperplate's text file export that Pepperplate will allow you to do for free if you don't pay their extortionate rate to continue using their service. Everyone should be incredibly thankful for people like rgstephens writing code to help before the developers get their act together -- I certainly am, but at this point, no reason to muck with any of this, simply go to Pepperplate.com, export your recipes as a .zip file, and import that .zip file directly into Paprika.
Once again, thank you to the developer for providing this option prior to the development team.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment