Trying to get your Miva Merchant store built or updated easily? All you need is Excel and some smart planning, and your website can be a breeze to build and manage.
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 it’s 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 XXX and major props for his simple and elegant solution to the oversight on the part of Redmond.
I will explain more about the Text Write Programand it’s role later. First, let’s tackle creating your store data.
What makes up your Merchant Store?
There are basically two facets to your store: the categories and products. The categories define the structure, or navigation, of your site. Every category needs a unique code. If your site needs subcategories, you assign one of the categories as it’s "parent." By default, every category is a parent. So you make it a child, or subcategory.
By default, Merchant does not support importing category data directly. If you have a large store to create from scratch or update, you have three options:
- Invest the time of you or your developer to it by hand, one category at a time in Merchant’s admin
- Invest $175 in StoreMan, a great desktop program that allows you to manage your data easily.
- 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.
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 otherway 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 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.
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 heirarchy of navigation. It also uses a consistent naming convention 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, guages, 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 milage out of some simple data manipulation tricks.
You should now have a very full CategorySubcategory spreadsheet and one for your products.
