Edit a CSV Without Changing Number Formats

This tutorial demonstrates how to prevent Excel from changing your csv data and points out some other problems that can occur when editing a csv file. You can jump to the video at the end or read on for a bit more detailed version.

Many users believe csv is just another type of Excel file, however this is not the case. Excel can open csv files and sets itself as the default editor when it is installed, but csv is nothing more than a text file with a different file extension. The most common use of the csv file format is in moving data (represented in the form of a table) from one database or program to another incompatible database or program. A csv cannot store formatting or data that is not text (such as images)

Editing a csv file in Excel can cause fields with numbers to change automatically into some undesired format. This can go unnoticed in large data sets, only to be discovered weeks or even months later in your eCommerce site or other destination database. At this point it can be difficult to track down the erroneous records and correct the information. The most common cause of surprise format changes is opening the csv directly either by double-clicking the file or using the File > Open dialog in Excel. Importing a csv into an blank Excel sheet will prevent the majority of undesired results.

The most common cause of surprise format changes is opening the csv directly either by double-clicking the file or using the File > Open dialog in Excel. Importing a csv into an blank Excel sheet will prevent the majority of undesired results. Here's how:

1. Open a new Excel sheet, select the Data tab, then click 'From Text' in the Get External Data group.
2. Browse to the CSV file and select 'Import'.
3. In step 1 of the Import Wizard choose 'Delimited' as the original data type. Typically you don't need to make any other changes here but be sure your header row is actually on row 1 and change this number accordingly if it begins further down the sheet. Click 'Next'.
4. In step 2 of the Import Wizard choose Comma as the delimiter (deselect the Tab check box) and click 'Next'.
5. Step 3 is where you tell Excel not to change your formats. With the first column in the Data Preview selected, scroll across to the last column and select it while holding the SHIFT key (all columns should now be selected).
6. Select 'Text' from the Column Data Format group and then 'Finish'. Click OK to insert the data into cell A1.

Note that if the source data contains forced line breaks (ALT + ENTER was used to create a new line of text) these will come in as a broken row when importing. If this happens you need to fix the source data or open the file in a text editor such as Notepad and remove the line breaks (they are represented in Notepad as a small square). If these are not fixed the import will most likely fail completely or partially when you attempt to get the csv data to it's destination.

Summary of Best Practices:

• If you are the one generating the csv, retrieve only the data you need (get the least amount of columns and rows to accomplish the task).
• Import the csv as text into Excel (don't open it directly)
• If the editing task is extensive save it as an Excel file and re-save it as a csv when finished (this will retain column widths, formatting and formulas until you're done)
• Fix spelling and data errors, line breaks, etc. in the source database if possible.

Are PPC Shopping Comparison Sites Worthwhile?

I see forum and blog posts all the time professing that PPC (Pay Per Click) sites such as Amazon, Nextag, Shopzilla, etc. are not cost effective. Some would say that the overall conversion rates show the cost of clicks to be more than the profit realized from the sales. From my perspective, looking in from the outside, do I think they are worth it?

Absolutely. Utilizing PPC sites can drive a substantial amount of additional traffic to your eCommerce site and increase sales considerably. But that's not where the gold is...

The gold lies in what's not selling. Everyone who clicks a link to your store wants to spend money, including 'window shoppers'. So why didn't they buy what they clicked on? Many, including the comparison sites themselves would have you just remove the under-performing products from your feed and concentrate on those that are currently generating sales. That philosophy will get you nowhere except right where you are. By focusing on that one question: "Why didn't they buy what they clicked on?", you'll not only increase sales and improve the performance of your dollars spent on the PPC sites, you'll also improve the conversion of every visitor to that product (and your site as a whole) no matter where they come from.

Although it may be a good idea to drop some products from the feed temporarily if you have a lot of products getting multiple clicks with no return, once the cause is found you shouldn't have to remove anything. Here's some tips and self-assessment questions to get you on the path to spinning that straw into gold:

• Don't focus on generating clicks, focus on generating qualified clicks - Bringing in traffic is never a bad thing, even if that traffic isn't interested in your products at the moment - as long as you can leave them with a positive mental sticky note. But how much are you willing to pay people looking for casual shoes to come into your boating store? It might be worth adding a couple 'vague' products to a PPC feed that will bring in some window shoppers, but certainly not every product.
• Provide accurate names and descriptions - What shows up on a shopping comparison site varies widely from site to site. Some, like Bing only show the first 50 (I think) characters of the name and no description. Amazon goes to about 70 with no description (for Product Ads From External Websites). You have to do your best to fully describe the main attributes in the product name. If you sell gloves for instance and your product name is "Insulated Gloves", that might generate a lot of clicks with no return because it's just an investigative click. "15deg Gortex Insulated Gloves, Fleece Lined" on the other hand weeds out a lot of potential useless clicks. Adding brand to the product name can be a positive for brand loyalists but whether it should be at the beginning or end of the name is a bit of a Coke/Pepsi debate. I say put it at the end so if the name gets truncated we lose the brand but not the description.
• Do you sell low priced items with a huge shipping fee attached? - Imagine walking into Best Bin to pick up a new mouse for your computer. The price tag says $27.95 but when it's finally your turn at the cash register it's $42.95! (they have to pay the cashier somehow right?) Would you still buy it or just walk away disgusted? Would you go back to Best Bin in the future? Finding a way around this, even if it means higher prices could leave your customers without the mental black eye. Find a way to offer free shipping and you'll obtain a double positive; the user has the feeling of getting something for free and doesn't suffer the emotional let-down at the check stand.
• Do people leave your site with a mental note to remember you or is it forgotten the moment they click out? - Think about the eCommerce sites you've visited but not made a purchase and ask yourself why you remember them and why you didn't buy. Apply that to your own site and see if your site has the same memory benefits or suffers from the same symptoms that caused you to move on.
• Can the visitor tell instantly what other products or services you offer? - Consider the unqualified clickers that you attract. They'll undoubtedly have a wide variety of interests and tastes. Assume they got to your site by clicking the "Insulated Gloves" link and landed on the Insulated Gloves product page (of course it's not what they're looking for). Is every other interest that your store has to offer easy to find or is there too much fluff to see anything without micro-analyzing the page? They were on a mission when they came to your site, have you managed to convince them to take a minor detour?

Remember the conversion numbers shown in the reports generated by the comparison sites don't tell the whole story. Some people bookmark the site and come back, some just remember the site and come back, others block the conversion tracking code through various means. These reports can however spot trends and be your best ally in finding the weaknesses of your site and turning movers into shakers.

We've Moved!

Well, virtually anyway. Our web site has been split from our SharePoint project management site and received a facelift in the process. What started out as a 1-2 week project turned into a five month ordeal.

When Triage was started we had everything under one roof in our SharePoint site which functioned as our public company site, blog and client project site. We now have many channels that serve to both inform the public as well as serve our clients needs even better. These channels include:

• Our Company Website - Completely redesigned and updated to reflect all we have to offer at Triage
• This Blog - Moved to it's own sub-domain with far better ability to handle newer web technologies and media options. (SharePoint is really limited in it's blogging abilities)
• Our YouTube Channel - Our media was getting a bit scattered. This gives us a single source for any instructional or commentary videos.
• Our SharePoint Project Management Site - Upgraded to SharePoint 2010, this was a bit of a trade-off that we felt was really worth doing. The new version of SharePoint has much tighter integration with MS Office as well as a new modern ribbon tool bar. Unfortunately the old SPS application templates as well as many of the "Fabulous Forty" templates don't work in the new version.
• Our eCommerce Site - Formerly just a private test site, our Big Commerce site is now public and serves double duty as a fully functional eCommerce site as well as a test bed.

"What, no Facebook?" you ask. Nope, but I'll get on that soap box in another post :)

Hope you like what we've done and remember: Stay Organized!

Quickly Put A Brand Or Category On Sale In Big Commerce

With the lack of a built-in sale function in Big Commerce many new users have a difficult time putting a group of products on sale, resorting to changing prices one at a time or downloading a full Bulk Edit CSV and picking through it one by one. Here's how to get that brand or category on sale quickly:

1. Copy the Bulk Edit export template to create a custom export template that contains only the fields you need. This would be Product Name, Category, Sale Price and any other price fields (retail, cost or selling price) that you may want to base your sale price on. You can also uncheck the "Yes, include product options in the products export" box unless you really need the options in the export - you can't put an option sku on sale, you can only change a fixed price. Note that I also include Product ID in all my custom exports even though it is technically not required unless you are changing the product name.
2. Next go to Products > Search Products and select only the criteria you want to put on sale (Brand, Category, everything over $100, whatever).
3. Export the search results using your custom export template.
4. Add sale prices based on any formula or enter the price directly into the Sale Price field.
5. Re-import the csv (check the box "Yes, I exported this file using the 'Bulk Edit' template").

There you have it, your brand, category or other product group on sale.

Display Single Item Price When Selling Package Quantities in Big Commerce

We've been seeing a lot of Big Commerce users wondering how to display the single unit price of a product when the product only comes in package quantities. Here's one technique to accomplish this that can also be used to add a setup or handling fee that is not included in the advertised price.

1. Create a multiple choice Option that contains the package quantities available (or a single choice for the setup fee). Setting a default here will multiply the price automatically as soon as the product page is displayed. It's probably best to have no default so the single unit price displays until the user checks an option - shopping comparison sites may disqualify your products if they audit your site and see a different price on the page.
2. Create an Option Set and add the Option, making it mandatory.
3. Here you need to make a decision. We need to set up rules to change the price for each package quantity. If rules can be created in the Option Set that will apply to all products this will keep maintainance simple and is the best choice. If the package quantities cannot be based on a formula then the rules need to be created within each product.
4. Add the Option Set to each product that is sold in package quantity only.

Here's a video to demonstrate (sorry for the crappy audio):

The Human Condition

I was recently asked to manage some of the outsourcing for an ecommerce site. I'd been meaning to sign up on a couple of freelance sites anyway to try and fill some open time and what I see on these sites is a real eye opener on the human condition.

Now I fully understand the global economy and I'm not about to start ranting about wage differences but people working for 50 cents an hour? I don't know what a computer and a copy of MS Office, Photoshop or whatever costs in some third world ghetto but I'd be willing to bet 50 cents won't cover it.

Far worse than those who are apparently working with pirateware are the unscrupulous employers on these sites. These are just some of the things I've seen in a couple of days time looking at the other jobs posted:

• Looking for someone to post false reviews
• Looking for someone to modify copywrited material to make it their own
• Looking for someone to copy someone elses web site
• Looking for someone to manage someone who’s not getting the work done on time
• Looking for someone to remove bad publicity on the internet (ripoff report, etc.)
• "I don’t know how to do it but it should only take you a few minutes" (this one's always my favorite)
• "You need to make as many revisions as it takes at no charge until I say it's right"
• Ridiculous time frames

Probably the worst of all are employers trying to get out of paying $2.00 hr when a deadline is missed. I'd be willing to bet the instructions were all of two sentences or the deadline was unrealistic.

All this just goes to show there are untold masses ready and willing to fill Hitlers shoes given the opportunity.

Pushing The Air Plane

A man is driving near Seattle when he comes upon another man who is pushing an air plane down the road.

"What's wrong with the plane?"the man in the car asks. "Nothing" replies the man pushing the plane.

"Where are you pushing the plane to?"

"Montana"

"Why don't you fly it?"

"I don't know how to fly a plane."

"Why don't you learn to fly or hire a pilot?"

"Pilots cost money and I don't have time to learn to fly. Now if you'll excuse me I have to get back to pushing this plane."

How many time have you heard (or said) “We don’t have time to do it right”? Yet it seems there’s always time to take the long road or worse yet, do it twice.

Every company has air plane pushers. In the haste to accomplish some goal, people fail to develop the methods and document the procedures for the task. They concentrate on getting it done rather than how to get it done properly and efficiently. This is the single biggest squandering of company resources there is, replacing efficient methods with more humans and the associated labor costs. That’s great if you're a philanthropist, not so great if you're trying to maximize profits.

Failing to plan causes a hemorrhage of money. Instead of letting out a bit of blood and suturing the wound with nice clean stitches, a dirty band aid is put on the gash and it’s allowed to bleed slightly forever, with all the puss and infection that come with such methods. Eventually this leads to a hospital visit and possibly an amputation.

Technophobia, ego, narrow mindedness, failure to adapt, and simple lack of education or experience all play a part in creating air plane pushers. Often it starts innocently enough. A quick solution is needed for some information requirement, then as the company grows these Git'er Done fixes remain, detaching information from the one version of the truth and causing employees to back-track, re-research, guess and duplicate effort.

How many air planes are being pushed around your company?

Gotta run - I’m off to flight school then my shrink to work on that fear of heights