Once you have the feed on your desktop computer, you will need to see if it is in a compressed format. Many feeds are uncompressed, and come in a file with a “.TXT” or “CSV” extension. One of the exceptions to this are feeds provided by Performics. These feeds are compressed (and disguised a little), so you’ll need to change the extension to “GZIP.” GZIP files can be decompressed with a program like WinZip. Also, files with the “ZIP” extension can obviously be decompressed by WinZip. Once you have a file that you know is not compressed, you are ready to do something with it.
Note: our experience with "comma" delimited or files with a ".csv" extension have a greater rate of errors. If you are given a choice between types of delimited files, try to choose pipe or tab delimited formats, using comma delimited as your last choice. This selection (if available) is via the merchant or network, when you initially get your feed. By the same token, it is a good idea not to save your cleaned up feed in the "comma" delimited format.
The next step is to open the file in a program like MS Excel or a nifty tool called the "DataCleaner" from the folks at AMWSO. There are others, but we’ll use Excel in our example here. When opening any text file (or any file that isn’t a bona fide Excel file with a “XLS” extension), you will have to tell Excel that you want to look at “All Files (*.*)” Once you have selected the data feed file, you’ll get the “Text Import Wizard” that pops-up asking how Excel wants to handle the file. Select the radio button marked “Delimited,” (there’s that word we learned about earlier). Click “Next.”
Now we have some choices. How is your file delimited? You might know, and can simply check the appropriate box (for example “Tab” – the default). If it is Pipe delimited, all you have to do is type in the Pipe character in the “Other” box. When you have made the correct choice, you’ll notice an immediate change in the way the data looks in the Data Preview window. If you have selected the correct delimiter, you’ll see your data organized, with neat vertical lines outlining the columns.
Usually, the text qualifier can be left at the default – double quotes. Leave the “Treat consecutive delimiters as one” checkbox un-checked. At this point you are ready to go, and there should be no reason to click Next, just go ahead and click Finish. The data feed should now load into Excel.
Unfortunately, many merchants don’t provide perfect “data.” Somewhere in most feeds you will likely find illegal characters, or other things that may cause WebMerge to either stop, or do strange things. There are a couple ways to deal with this.
You can clean them up in a program like Excel, or try to deal with them in WebMerge. Future releases of WebMerge will have some of Excel’s functionality, meaning that with release 3.0, you may be able to simply take care of many tasks with WebMerge.
One of the items in your data feed that will cause problems, is where you need to create file names or directories based on information in the data feed. For example, if you are trying to build file names based on categories in the data feed, and there are forward slashes “/” in the category name, you’ll need to get rid of them. WebMerge might create a directory where you want a filename, because it thinks there might be a Path there (as in path to a directory or URL).
Search and Replace:
The solution is to perform a “Search and Replace.” This
is something you’ll want to get very familiar with, in creating a really effective data feed file. It also takes a little imagination
as to what will happen, or what are the consequences of replacing
characters in the feed. If you replace the slashes in the Main_Category field names, should they be replaced with something else, or nothing?
The answer is – it depends!
If the slashes are just there to
separate a group of numbers, then you can replace the slashes with
a dash “-“ The replacement character does not have to be a dash, it can be anything that will
work into your scheme, a space can be inserted, or nothing at all.
See the example below where the slash was replaced by a dash.
Original Field (Before Search and Replace):
|
Cleaner 1/5/10 gallon sizes |
New Field (After Search and Replace):
|
Cleaner 1-5-10 gallon sizes |
WebMerge will make an index filename that looks like this*:
Cleaner_1-5-10_gallon_sizes.html
Of course, you’ll want to highlight the entire column by clicking on the appropriate column letter (“J” for example), and have the search a replace function fix the entire column at one time. In Excel, the “Replace” function can be found in the “Edit” drop down menu, or by pressing CTRL+H on your keyboard.
* The current version of WebMerge allows the user to make filenames with either underscores “_” or dashes “-“ in place of spaces in the proposed filename. Underscores were used in our example.
TIP: What if you made a mistake? It is always a good idea to check the results right away, if there was a mistake, pressing CTRL+Z will undo the entire Search and Replace operation you just preformed. This undo will work for all major operations in excel, and can undo the entire operation (not just one cell at a time).
Text to Columns:
If the folks that created the data feed have a field name (Column
header in Excel) called "Category," but you can see there are actually more than one category (or sub-categories) in each column separated
by slashes, then maybe you should think about separating the categories, and making new field names (columns) based on these new splits. Take a look at the example.
Original Field called "Category" (Before Text to Column Operation):
|
Cleaning & Sanitation/Cleaning Chemicals/Lubricants/Wd40 |
Your initial reaction might be “Sheesh, how come they didn’t make different field names (column headers) for each category and sub-category and separate them?” In this case regretfully, that is the job of the affiliate. Some feeds have different categories already separated for you, and some don’t. You need to be prepared to handle this yourself, if you are creating a multiple level hierarchy of index pages. So how do we fix it?
Well first, you need to make new columns for the new field names to be used. If you don’t do that, during the Text to Column Operation Excel will ask you a question that is very important to answer correctly. “Do you want to replace the contents of the destination cells?” The answer is “No way!” If you let Excel do that, the information in adjoining columns gets erased ! Actually, the answer is “Cancel.” What Excel is trying to tell you is that there is information in the columns to the right or left of the column you are trying to break apart. It is really saying that you need more room.
Here’s the procedure. Click the letter of the column adjacent and to the right of the Category column you want to break apart. Go to the “Insert” drop down menu, and click on “Columns.” Excel makes a new empty column just to the right of your original Category column. You need to do this several times, depending on how many sub-categories you think exist in the original Category column. We have not encountered more than six, but there may be feeds that have more than that (how practical is it to have more than six sub-categories anyway?). So make six empty columns to the right of the original Category column.
Once accomplished, highlight the Category column. Go to the “Data” pull down menu, and click on “Text to columns…” The “Convert text to columns wizard” then pops-up asking if the information is “Delimited” Click this radio button and then click “Next.” This should all look very familiar, with check boxes for different delimiters (just like when you opened the file in Excel to begin with). Your delimiter may already be checked, in case it is not, type the character in, or choose one of the defaults (if that is your type). In our example, we’re using a slash – so we type that in. You should immediately see the information separated for you in the “Data preview window.” Click “Finish,” to make the conversion. If you see the “Do you want to replace the contents of the destination cells?,” then there were not enough blank columns created to put the separated data in. If true, then click “cancel” and go create more empty columns to the right.
You should now see several new columns with the Sub-Category information in them. You now have to assign field names to the columns, so that WebMerge will have something to recognize the data properly. We’ve used things like “sub-category1” and “sub-category2” to name the new columns. These names can be anything, but it is always a good idea to be consistent.
Before Text to Columns Operation:
Category |
Cleaning & Sanitation/Cleaning Chemicals/Lubricants/Wd40 |
After a successful Text to Columns Operation (with field names (column headers) added)):
Category |
sub-category1 |
sub-category2 |
sub-category3 |
Cleaning & Sanitation |
Cleaning Chemicals |
Lubricants |
Wd40 |
Before finishing cleaning up your feed, you should determine if you need all of the fields included in the feed. If you don’t need all of them, think about removing them by clicking on the letter for the column you want to get rid of and click on Delete in the Edit drop down menu. This will make the feed smaller in size, and may speed up the processing and creation of files. As an example, the Performics feeds contain a couple of fields that have nearly identical information, but only one field is required. The “ProductURL” is required, where the “BuyURL” is not – so you can get rid of it. This will significantly reduce the size of the file.
After you have built your templates, take a look at the fields you have chosen to use. If there are other fields in the feed that aren’t used, go ahead and delete them.
TIP: You may also want to sort your data in order to look at it in a more organized fashion. For example, you can sort by the category you intend to use to build index pages. When sorting in Excel, be sure to highlight everything except the field names (column headers) at the top.
Without going into detail, there are many things you can do to enhance the SE optimization of your WebMerge generated pages. These involve optimizing keywords, or even adding keywords to a feed that doesn’t include any keywords. There are also some nifty ways in which you can format the descriptions included in a feed, to give more value and a better look to your pages. These enhancements if done in the feed itself, can be more effective than simply making your template look good. These techniques are complicated and proprietary and may be covered at a later date.
NEXT SECTION - Preparing your Templates
Home :: About Us :: Privacy :: Web Design :: Graphics :: Links :: Purchase WebMerge