It may seem odd for a Miva Merchant web designer to be posting tips for using MS Excel, but for Miva Merchant store owners who use the flat file import and export features of their store, learning a few little Excel tidbits can come in pretty darned handy. There are so many things you can do to make your life as an ecommerce site owner easier with Excel, so I’m planning to make this a series, with a handful of tricks in each. For starters, let’s see how to get your import file into Excel, and then split it up, and put it back together.
Importing Data From Miva Merchant Into Excel
First, let’s go over how to get your data into the Excel file in the first place. When you run an export from the Utilities menu, Miva Merchant exports your data into a .dat file, which it stores on your server. The exact location varies from store to store; if you’re not sure where your files are being stored, check with your host. If you’re running all the current patches and updates, you also now have the option to have the export file emailed to you (I love this feature!).
The complete instructions for importing the .dat file, including handy screenshots, can be found in my article on Creating a Useful Miva Merchant Order Spreadsheet, but here’s the nitty gritty:
- Open a new workbook in Microsoft Excel.
- From the Data menu, select Get External Data> Import Text File.
- Browse to the location of your .dat file. Be sure to change the setting in the “Files of Type” box to All Files (*.*).
- Select your file and click Import. On the following screen, be sure the data type selector is set to Delimited and click Next.
- Select the appropriate Delimiter for your file and click Next.
- Finally, click Finish. You will be asked to specify which cell you want your data import to start in. In a new worksheet you should not need to change this reference. Click Ok.
Breaking Data Out: Text to Columns
Sometimes you may want to break out data, such as category assignments, that’s in a single column. Or, you may have a url that you want to split out. The Text to Columns function makes this a snap. Let’s use breaking out category assignments as an example:
- First, you’ll need to make sure that you have enough empty columns beside your CATEGORY_CODES column to accommodate the maximum number of categories assigned to your products. If you only have two or three possible categories, it’s easy enough to insert new columns beside the CATEGORY_CODES column, but if you have more than that, the simplest thing to do is to move the CATEGORY_CODES column to the end of your spreadsheet. Click to highlight the column, go to Edit > Cut. Scroll to the first empty column in your spreadsheet, and highlight it. Go to Edit > Paste.
- Now, to break out your category assignments into individual columns, highlight the CATEGORY_CODES column and go to Data > Text to Columns.
- For data type, select delimited.
- Set the delimiter to Comma. In the preview pane, you will see your category codes, broken out into individual columns.
- Click Next, then Finish. You will now have separate columns, with a single category code in each column, and can use various sorting features to work with your product data.
Putting it Back Together: Concatenate
Now that we’ve gone to all the trouble to break our data apart, let’s see how we can put it back together. The concatenate function is handy for all sorts of purposes, but one of my favorites is to use it to create file paths for product images.
I always recommend to new store owners that they use a structured system for naming product images, and one of the best foundations for a naming scheme is to include the product code as part of the image name. Not only does this open all sorts of possibilities for coding images within the store, it can make setting up your import spreadsheet a snap.
Let’s assume that you’re going to put your images in the default Miva Merchant graphics directory, and that your spreadsheet has your product codes in column A, the first column.
- Click in cell J2 (or whichever cell is at the top of your PRODUCT_THUMBNAIL column in your spreadsheet) and enter the following formula:
Let’s take a look at what this formula does. First, we have the beginning of our file path (the store 00000001 directory in the main graphics directory on the site), surrounded by double quotes. You can use double quotes to put any text string into a concatenate function. After the file path is an ampersand (&) and then a reference to cell A2, which you’ll recall is where our product code is. Following the reference to cell A2 is another ampersand, and then the text string -thumb.jpg. If you are using a different naming scheme to identify your product thumbnails, you can of course change this string accordingly.
The first ampersand joins the file path to the content of cell A2, and the second ampersand joins those items to the file extension. You can concatenate as many strings and cell references as you like — just remember that strings must be enclosed in double quotes.
- After entering the formula, you should get something like this (in this example, the product code is 01-347752):
- To fill in the formula for the rest of your products, just hover over the lower right corner of cell J2 until the cursor turns to a thin black cross, then click and drag down across the rows containing product data. The formula will automatically fill in to the remaining products.