Looking to set-up your first Merchant site? Or looking for an easier way to update and manage the store you have? Well, if you have Excel and do a little investing in your website, you can save yourself hours and hours of work. And that’s what it’s all about. Focusing your attention on this more important than updating your site. Not that it’s not important to focus on updating the data to remove typos, organize more effiencently, expand and grow your business by offering more products than you can in a physical store. With Miva Merchant and Total Hosting, your store is as big as your imagination.
Welcome to Your New Friend … Excel
I’ll be honest; I have a love/hate relationship with Excel. While it does some great things and has a lot of power, Microsoft misses a few critical things for people who also work with non-Windows programs.
Miva Merchant cannot import or export directly to the .xls format that Excel uses natively. Merchant uses text files. Unfortunately, the options for exporting text files from Excel stink. You can only output as TAB-delimited or CSV (Comma Separated Values). I prefer to use the | (pipe) character. Why? Because it is not used in 99.99% of your daily data needs. So it makes a great character to use to separate values. Commas and TABs are slightly riskier since they can be used by mistake to shift a set of data. Better to "pipe-up" and use |. But Excel cannot save a custom-delimited file, unlike its big-brother, MS Access.
So what do you do? You need a way to write the output of the spreadsheet the way you want. I finally found the answer on an Excel forum. I give full credit to Earl Kiosterud and major props for his simple and elegant Text Write Program as an easy (and FREE) solution to this oversight on the part of Redmond.
I will explain more about the Text Write Program its role later. First, let’s tackle creating your store data.
What is an eCommerce site, really?
Apart from the pieces that actually process the order, there are basically two facets to your store catalog: the categories and products they contain. The categories define the structure, or navigation, of your site. Every category needs a unique code. If your site needs subcategories, you assign a "parent" to the group of categories you wish to place them under (these are then the “child” categories). By default, every category is a parent.
Unfortunately, a default Merchant store does not support importing category data directly. If you have a large store to create from scratch or update, you have three options:
1) Invest the time of you or your developer to it by hand, one category at a time in Merchant’s admin
2) Invest $175 in StoreMan from Phosphor Media, a great desktop program that allows you to manage your data easily.
3) Invest about $99 in OpenUI which adds the ability to import category data and MUCH more.
I will assume from this point on you have done either option 2 or 3. If you are looking for help for option 1, feel free to contact me.
Setting Up Categories
You first need to define your categories; otherwise your products will be "orphans" and not have any place to reside in your store. In Merchant you assign Products to Categories, not the other way around. For Merchant, a category spreadsheet needs to have, at least, the following columns:
(A) CODE – Category Code (no spaces)
(B) NAME – This is what is displayed
(C) ACTIVE – Is this category active? Values = TRUE/FALSE
(D) PARENT_CODE – If this will be a subcategory, put the parent category code here
(E) PAGE_LEN – How many products per category page? 0 = Unlimited
(F) TITLE – Display the title of this category? Values = TRUE/FALSE
(G) HEADER – Display a header for your category. Can contain HTML, images, etc. You will need some basic HTML knowledge or the WYSIWYG Module for Merchant from XXX. Displays the same on every category "page" if your category spans many pages due to large number of products.
(H) FOOTER – Same thing, as the Header.
(I) TREE_IMG – You can replace the default HTML navigation of the Category tree with images. Put the PATH to the image here. Typically you would put your images in your www/Merchant2/ folder, so they will always be secure if your customer is logged in. So the path the image might be something like this:
graphics/00000001/uniquecatcode_cattree.gif
(J) TITLE_IMG – You can also replace the default title text with an image, as with the Tree. This path could look something like this:
graphics/00000001/uniquecatcode_title.gif
If you have enhanced your store with OpenUI, you have many more options for items that you can import in. We strongly suggest a site owner invest in this "Swiss-Army" tool for Merchant. I will be reviewing more OpenUI site enhancements in future articles.
The spreadsheet columns now represent all the data elements that define your standard store structure.
The great thing about Excel is that you can use the cells to hold text that you can then combine into another cell to string together things like category codes, descriptions, etc. How? By using columns beyond J to hold extra data that can be reused. Follow along
If you set-up a formula in cell A2 in Excel, like this:
=+D2&"_"&K2&L2
where –
D2 is the Parent Code in Row 2
K2 is in a column outside of the table. It holds a value for the subcategory’s code.
L2 is the value if your navigation goes down to a third level. Need more? add more.
This will produce a Category code like this: Parent_SubCatSubcat2. This gives you up to three levels of hierarchy of navigation. It also uses consistent naming conventions that lets you then create nice navigational effects in your site. This will be the topic of another article.
You can also use this method if you have descriptions that require lots of bits of technical data to change, but you can have a basic template for the information, since it’s the same data fields (dimensions, gauges, country of origin, strengths, etc) or other quantitative data for your products or categories. If you can create a common template for the data then you can use the extra columns to drag and drop sets of repetitive data. Or if you have your data in another legacy system that you can output all the fields needed, you can probably do some simple cut-n-pastes to get your data in the right places. Then you use the same format to create what is basically a form-letter approach to data manipulation.
If you combine this with some basic HTML you can get some great mileage out of some simple data manipulation tricks.
Setting Up Products
For your Product spreadsheet, you need the following Column Headings:
CAT_CODE – The final category code from the previous spreadsheet
CODE – The Product’s Unique SKU or Code
NAME – The name that is displayed
THUMBNAIL – The path to the thumbnail image for your product
IMAGE – The full sized image
PRICE – The selling price
COST – The cost of the product (useful when offering distributor pricing, XX% over cost)
DESC – The product’s description
WEIGHT – The unit weight. Needed to calculate anything but flat rate shipping
TAXABLE – Is this item taxable? Value = TRUE/FALSE
ACTIVE – Is this product currently live? Value = TRUE/FALSE
HEADER – Can give a product a unique header
FOOTER – As well as footer
The same rules apply to products, when it comes to building naming conventions and descriptions and headers. Use formulas combined with HTML templates to automate the process as much as possible. It is also useful when naming images to use the product code as a root in your image name: prodcode.jpg, prodcode_th.jpg, etc. This way you can do more with your pages. This will be covered in the future.
You should now have a full spreadsheet for your CategorySubcategories and one for your Products.
{mospagebreak title=Working With Spreadsheets}
Working With SpreadsheetsIf you are using StoreMan, then you need to copy the cells of standard Merchant tables, then Paste > Special > Values into a new worksheet. StoreMan will fail the import process if there are columns besides the standard ones. If you just cut the other columns out, you will lose all the data you merged. This applies to both the Category and Product files.
If you are using OUI and you are going to import text files direct in Merchant, remember you need to create the Categories first for your initial store set-up.
Now you need to open your copy of Text Write Program. This will allow you to write your text file with a |-pipe delimiter.
When you first open Text Write, you will be asked to enable macros. You need to enable them for this to work. I then set up my sheet like this:
Record Delimiter: Code 13,10
Stop if Record Delimiter found in cell? No
Field Delimiter Character |
Bracketing Character Character (I leave the value blank)
Bracket all fields? No
Bracket blank fields also? Yes
Skip blank rows? No
Write entire sheet? No
If no, Expand selection? No
If no, write rectangular? Yes
The file name can be something like STORECATS.txt for categories.
Then you open your Excel spreadsheet and select all the rows and columns needed. I like to work from the bottom row, col A back up and then to the right, as opposed to selecting top down and to the right from A1.
Once you have your area of the spreadsheet selected, simply press CTRL+SHIFT+W and it will write to the text file with the | delimiter in the same folder as the spreadsheet itself.
Importing Your Data via OpenUI
Now you need to securely log into your Miva Merchant admin control panel. Once in, look under (click the triangles, not the names) Stores > Store Name > Utilities > Import Data > OpenUI Category Import
Then there is an import wizard. Browse your hard drive to find your file to upload, set the delimiter to "Other |" then click next. Once Merchant grabs your file, set the column names as appropriate and then import. And then BAM! Records created or updated in one shot 😉 Next do the same for the products.
If this is your first store set-up, you will also need to FTP the category and product images directly into the same folder as you had listed in the image paths in the spreadsheet. This way you can upload all the images at once, as well.
I hope this tutorial went a long way to show you how you can easily maintain a store with only Excel, some smart coding and planning.
