Sep 292011

Ok, well this is going to be a little boring (and seemingly pointless) for some, yet extremely useful for others. I’m writing it down for personal reference. As for me, this is extraordinarily useful stuff. Get ready for an excel / open office lesson. If you want to start an eCommerce business, the most important concept for you to understand is that you must have control of your data. I cannot stress enough the necessity to be able to manipulate your data. Chances are, if you are trying to learn how to start an ecommerce business, the tips outlined in this article (and in the next article on how to use VLookup) will seem confusing and/or pointless. Stick with me as there’s a method to my madness. I didn’t build an 1.2 million/year business from scratch without a few tricks! Follow along as I work on doing it again!

If you are operating a webstore, you will likely have thousands of products. The initial launch for my soon-to-be released site will contain approximately 2000-2500 products. The initial launch will only reflect a small number of the products that will be for sale in the future. Keeping track of everything you’ve got selling is going to be absolutely essential.

You can manually upload one by one, adjusting price one by one, adjusting everything manually one by one. It will work. And when your suppliers raise prices 5% good luck to you. When a collection is discontinued and you have to delete it all by hand, one by one good luck to you. We have computers now. Fucking use them.

Losing control of your data can be a terrible time drain. A prominent developer I used to work with would always say, if it can be automated, automate it. If it’s a repetitive task, automate it. No exceptions.

Ok, excel. And a little of open office. Why? Just because both have some advantages. Obviously most sites call for .csv files – In which case you really have to use excel. Open office is good too. Let’s discuss some common problems for eCommerce merchants.

Problem 1
You have a file full of images. You need those images to be listed in an excel spreadsheet so you can match them up with MSRP, items specifics, UPC, Cost, Sell Price, etc. This is absolutely essential if you hope to upload products to your website in bulk.

I have 2000 images. They are named Product.jpg, Product2.jpg, Product3.jpg and so on. How can I get this list into excel without typing each item individually?

Step one, prepare to dust off those ms dos skills you haven’t used since the 90s.

Go to file, run, and type “cmd” – This will pull up the dos menu.

Type “Dir c:\(your file location) > c:\(your file location)\filelist.txt

This creates a text file for you with a list of all the files in the folder.

Now, look in the (your file location) folder and you will see the filelist.txt file. Open it. Select All, Copy. Them go to Open Office and open a new spreadsheet and Paste in the contents.

A “Text Import” windo will pop-up. You will need to look at the “Separated by” section and make sure the “Space” box is check. Also, check the “other” box and add a “.” – This will make sure the .jpg from the filelist will be dropped into a separate column. Last, click the box next to “Merge delimiters” – this will set up the formatting nicely so you will have one column with just the product code.

Now that you have this list, you can combine it with some other powerful features – namely, VLookup. How to use VLookup will be the subject for the next post.

A brief bonus tip. You have a section on a bulk upload file calling for a link to the image location on your ftp. You have uploaded all your products as .jpgs in ftp:\(your location)\product1.jpg…product2.jpg…product3.jpg etc. Now you want a list in the spreadsheet.

Use the & function. In your spreadsheet, build the link. Have column A say: http://www.(your location) Column B will be the part number (or image names list – that we just learned how to extract!) Column C will be “.jpg” (which may or may not be necessary depending on what data you currently have). Last you need Column D for the formula. Type “=ColumnA&ColumnB&ColumnC” That’s it!

One last tip about spreadsheets. If you have created a list with a formula, and then you go to copy and paste it to another spreadsheet, you may lose the data. To circumvent this annoyance, copy and past first into a blank text file, then copy and paste again to the other spreadsheet. This will preserve the order.

AUTOMATION is key. If this doesn’t make sense to you feel free to ask. If you have encountered this sort of problem (like I do all the time), this info. could be quite helpful! I certainly hope it is! Next up, VLookup.

If you enjoyed this post, make sure you subscribe to my RSS feed!

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>



CommentLuv badge

Notify me of followup comments via e-mail. You can also subscribe without commenting.