User's Guide: HandyShopper Import Formatter (using an Excel Spreadsheet)


Background
Column Descriptions
Worksheet Descriptions
...
HandyShopper Import Guidelines


Background
One of the great things about HandyShopper is its ability to import/export databases with the Memo application that comes with PalmOS.  Although you are limited in the fields it will allow, the ease in which a large list of items can be entered is amazing.

The only problem for me, was that the format the data must be in was a bit confusing, so I decided to create a spreadsheet that would automatically format the data I input into the format that HandyShopper is looking for.  With help from Peter Ístlund, I was also able to create a version that would be compatible with international currency formats.

The HandyShopper Input Formatter was designed to automatically format a simple list of items into a format that HandyShopper can easily import.




Column Descriptions
Figure 99AEW.1
"MAIN" Worksheet
Figure 99AEW.2
"International" Worksheet
Currency Format ("MAIN" Worksheet) — The currency format that will be used to format the price for import into HandyShopper.

Currency ("International" Worksheet) — The currency symbol that will be used to format the price for import into HandyShopper.

Separator ("International" Worksheet) — The decimal separator that will be used to format the price for import into HandyShopper.

Figure 99AEW.3
Quantity — Any numeric value can be added here.

Units — Any alphanumeric value up to 4-characters in length can be entered here.  Normally it is used for oz, cups, Tbsp, etc., but any custom value will be accepted.

Price — The value entered here will be formatted to the style in the Currency Format area.

Aisle — Any whole value 0-200, or upper case letter A-Z can normally be entered here.  An upper case letter (A-Z) will be converted to the corresponding value of 201-226 (within HandyShopper they will be A-Z.)  A lower case letter (a-z) or value of 227-258 can be entered and will be imported, but aisles cannot be changed to these values from within HandyShopper.

Figure 99AEW.4
Description — Description of the item.

Notes — Any information entered here will appear in the Notes for that item.

HandyShopper Import — Formatted text of the above columns, to be copied and pasted into a Memo file on the Palm Desktop application. 




Worksheet Descriptions
Figure 99AEW.5
"MAIN" Worksheet — Worksheet to be used for U.S. and other basic currency formats.  NOTE: The instruction section and the currency format are in a light blue color for this worksheet.

"International" Worksheet — Worksheet to be used for international currency formats (specifically where a comma is used as a decimal.)  NOTE: The instruction section and the currency format are in a light yellow color for this worksheet.




HandyShopper Import Guidelines
From the instructions for HandyShopper v.1.17:
Importing from MemoPad:

You can import a list from a MemoPad memo.  The list is imported into the current category and current store.
  1. Tap the Menu area next to the graffiti area.
  2. Select "Options", then "Import from MemoPad..."
  3. Select the memo from which to import the list.
  4. Tap the "Import" button.
When importing, the new items are placed in the current category (or Unfiled if "All" is the current category).  If you are in the "Need" view, then the new items are also marked as needed items. The items are also associated with the currently selected store (if "All Stores" is selected, then the new items are not associated with any stores).

Memo Format for Importing

HandyShopper currently support two different basic formats for the list that can be imported.

Format 1: Bullet Marks

This is the format that HandyShopper uses when it exports a list to the MemoPad.
  1. Each item begins with a bullet mark, which is either a dash ('-') or the bullet character (the graffiti stroke is backslash, dot), followed by a space.
  2. Next comes the Quantity, which is a number followed by '@'.  (This field is optional; if you omit it, the quantity is set to 0).
  3. Next is the Price, which may or may not begin with a '$'.  (This field is optional; if you omit it, the price is set to $0).
  4. Next is the Aisle, which must be 'a:' followed by a number (for instance, 'a:3' means aisle 3).  (This field is optional; if you omit it, the aisle is set to 1).
  5. Anything following the above fields is put into the Name field.  Line breaks are considered to be part of the Name field, unless they are immediately followed by a bullet character (or dash).
Example:
My shopping list:
- cereal
- 2@ 1.49 Pringles
- 5.99 batteries
- a:27 sci-fi books
- 4@ .99 2-Liter bottles of pop
- 3@ a:4 cookies for party
party is at Greg's house
(this is 3rd line of party item)
- 14.99 box of logs for fire

Format 2: No Bullet Marks

If you do not use bullet marks, then line breaks are used to determine when to start a new item.  Other than that, this format is the same as Format 1.

Example:
newspaper
3@ towels
$5 cat food
1@ $5.99 AAA batteries
2@ $1.89 a:3 soup

Known Limitations

If you export a list, make some changes, and import it again, the imported items are ADDED to your list.  This means that you will now have duplicate copies of each item that you exported and then imported.  You can use the "Purge for Import..." command to delete all the items from the current view, and then import the list.  Note, the "current view" is filtered to show only All, Needed, or Coupon items from the current store and current category.  You can put bullet characters in the description of an item.  But if you export the list, and then import it again, the bullet characters may be interpreted as new items instead of as part of the description.  To avoid this, make sure that the bullet character does not immediately follow a line break.  Not all fields can be exported.  Only the Quantity, Price, Aisle, and Description can be exported.  There is currently no way to export which stores an item is associated with.



©2001 Andy E. Wold