Datafile Manager Lite or simply DM Lite, is an Excel Spreadsheet that I created and develop for the company Appliances Connection. I started developing it since April 2017 and was named Datafile Manager back then. It is built with formulas, visual basic scripts, and macros. This Excel Sheet, as the name, is designed to manage datafiles (worksheets from vendors that contain their product lists) of various formats. It is built to fix garbled data and patch missing critical information as well as automate a lot of tasks. It can also produce a format ideal for Appliances Connection’s system.
Datafile Manager Lite’s Features:
Datafile Manager Lite has the capability to automate a lot of tasks, including fixing product names, auto-generate prices, automatically categorize items, auto-generate shipping method, auto-concatenate image urls and attachments, generate partial items, and generate a system-compatible standardized datafile using a macro. The original spreadsheet (Datafile Manager) had a Table Specs Sheet HTML Builder, but this was later removed, following the company’s decision to remove Table Specs Sheets from its Items.
Datafile Manager Lite’s Structure:
Datafile Manager Lite has many different worksheets that does specific jobs to handle the data. It has the main worksheet — Datafile Management (Datafile) worksheet — where product information can be entered. This worksheet have formulas that gather data from other DM Lite worksheets. This is also the worksheet where a standardized datafile will be generated from. Among the functions it does are automatically entering the sale price for the items (if it is missing), automatically enter shipping method, fix the product name, automatically generate shipping costs, automatically create category ID, and automatically decide if an item should be hidden or not based on the rules in the setting.
Another worksheet is the Tools, where users can add symbols or a unit of measure into numbers, excellent for adding product attributes. There is also the Prices Worksheet that gathers the info for the costs (wholesale price), MSRP (Minimum Suggested Retail Price), MAP (Minimum Advertising Price), and performs calculations for the sale price. The worksheet allows the formula for the sale price to be change thereby making it flexible. Below is an example of such function, wherein the credit card fee and profit margin percentage is shown to help generate the selling price. There is also an option in the sheet that allows the user to create custom formulas to get sale price.
Another part of DM Lite is the Real Shipping Worksheet that controls the Real Shipping Price for the item. There is a table here where you can set the weight and the prices for that weight. This will be use by DM Lite’s Datafile Worksheet to calculate the Real Shipping Cost.
There is also the Settings worksheet where you can configure DM Lite’s settings to manipulate its functions, formulas, and behavior. This worksheet is connected to various Worksheets in DM Lite. Technically, to almost all worksheets.
Next is the Images worksheet that handles the Images for the Datafile Worksheet. You can enter the images’ url here, or if the vendors use only a filename, you can set a path URL to add it into the filename. You can also attach the extension name to the filename, but this has to be set in the Settings Worksheet.
One of the most interesting features of Datafile Manager Lite is its ability to automatically find an item’s category base on its Product Name, this is handled by the Categories Worksheet. I set this one up to match the category IDs in Appliances Connection’s database. It does a search for a keyword in an Item’s Product Name and looks it up in its built-in predefined Lookup Tables, and if it finds a match, it returns the category ID and the Category Name for that item. This Category ID will then be use by the main worksheet — Datafile Worksheet. If it can’t find a match in its Table, it will return a default category.
The Categories worksheet also has a Category Suggestion Tool that suggests a possible Category ID if it finds multiple keywords in the item’s Product Name. The results are based from a customizable Lookup Table within the Worksheet. The function was made possible by a Visual Basic Script UDF (User Defined Function) that returns multiple search results. A function which cannot be normally handled by Excel’s standard formulas.
Another important feature of Datafile Manager Lite is its ability to automatically build Product Names. This feature is included in the Product Name Worksheet (aka Product Name Builder). In this worksheet, DM Lite will attempt to correct the misspelled words and try to reorganize the order of the words in the product name to make it more presentable and understandable. Below is an example:
As to how Datafile Manager Lite fixes the names, it first try to get the keyword in the Product Name then separates the non-keyword from it. Finally, it will merge the two but the keyword will be now assigned in the right place. The same happens when there are colors involve. For example, in a Product Name “Red Sofa Table”, it will be rewritten as “Sofa Table in Red”. Another example is if it encounters a garbled Product Name such as “Accent Table Kensington Black”, it will automatically be rewritten as “Kensington Accent Table in Black”.
A user may also add additional attributes and features into the Product Name; these will be automatically concatenated into the name by DM Lite’s formulas. A user may also choose to rename the Product manually using the override fields found in the worksheet.
The Product Name worksheet is also installed with a customizable keyword lookup table, this allows users to enter their own keywords and the replacement words for that keyword. DM Lite will incorporate this table into the formula and override its own built-in Lookup Table. This is useful in case the Product Names’ keywords are not found in DM Lite’s Lookup Tables.
The last worksheet I would like to mention is the Box Partials worksheet. This worksheet was introduced as per the Manager’s request. This was made to address a Box Partial issue wherein it took the company’s data entry team a great amount of time to build thousands of partials for its numerous items. Normally, it would take them days to months to produce the partial items but with this worksheet, it would take them only a couple of hours. Partial items by the way, are components of an item. As some items come in two or more boxed pieces, these has to be specified in the product page as Box Partials.
The Box Partials worksheet has a Box Partial generator. It gets the SKU and adds the box numbers into it. It also adds Box numbers into the Product name, then retrieve the box dimensions from its dimensions table, as well as the category ID and the SKU where it should be attached. The end result are box partial items, which can then be easily added into the main “Datafile” worksheet as partial items.
Other Features:
DM Lite also has a Paste as Values shortcut key (CTRL+M), which allows users to copy and paste data as Values. This prevents users from copying and pasting formulas.
Datafile Manager Lite’s Complexity
Finally, I would like to showcase Datafile Manager Lite’s complexity. It is not just any ordinary Excel Sheet that you can find in the Internet. Actually, it’s the only one of its kind, made solely for the company, Appliances Connection — a multi-million dollar company in Brooklyn NY that sells Appliances and Furniture online. The formulas are complex and they are actually connected to the many different Worksheets in DM Lite. These formulas connect harmoniously, enabling all the Worksheets to interact and function as one. It is governed by so-many conditional statements and arguments that link to one another, replicating a function of a program.
Idea and Conception
As Appliances Connection grows bigger, the tasks increase in number and the demand of work rises along with it. Before, product items can be easily managed by the Data Entry Team, which does manual updates and uploads of such items. This eventually became insufficient, so the Bulk System was created and was managed by a handful of experienced ITs. The Bulk System is designed to import and update items en masse. However, managing the bulk system is no easy task, and only few have the skills to properly utilize it. On top of that, the datafiles that are used on this system sometimes need fixing and thus, it also requires exceptional analysis skills in order to repair and input the data accurately. This task requires an adequate amount of time, perhaps, a day or two to just correct one datafile. The delay of course has its consequences, because not only does the company lags behind schedule, they are also outsold by their competitors. To meet with the growing demand, the company has to hire more people with skills, which spells more expense. But with DM Lite, most of the tasks can be automated so it kind of lessens the need for more manpower.
There was a time I was task to handle the bulk system and bulk upload items using a garbage version of a datafile, which was bought by Appliances Connection from a scraper company. Their datafile is a total mess and required several days to fix. Lasting for more than a week. The demand of work was too much and the bosses are demanding that it should be completed as soon as possible. It’s impossible to fix it immediately, and there are many datafiles piling up on my list so I figured I needed a way to fix these datafiles in a more practical way. And as I worked through the datafiles, I began to spot similar patterns in my work. I noticed that the work I am doing was redundant so I thought that if I create a program that could eliminate this redundancy then I would be able to save more time and catch up. This is where the idea began. Overtime, I worked on this idea, and this is how Datafile Manager was conceptualized. I started with a worksheet, arranged the columns and created a standard format then add auto-concatenating functions into it so that all I have to do is copy/paste while the formulas does the job of arranging them.
Before, Appliances Connection’s website uses a Table Specs Sheet. Unfortunately, it requires HTML (Hypertext Markup Language) knowledge and not everyone in the company knows HTML. The Data Entry Team has to build this Table Manually using the tools provided by the system. While there is a way to create this Table in bulk, not everyone is aware of the formula. To build this table, I incorporated my knowledge of HTML into Excel, creating an HTML code that can be attached into the Products’ Specifications. This enabled Excel to generate an HTML code for the Table Specs Sheet and thereby create Table Specs Sheet for all of the items in bulk. This became one of the core functions of DM.
As time goes by, more and more functions were added into the spreadsheet including a macro that automatically builds a standard datafile (courtesy of Eslam Zaki, who advised and taught me how to create a macro). And as I became more adept in writing conditional statements and arguments, auto-correcting functions were added into DM Lite, making it smarter. The worksheet undergo several revisions, it’s form also changed from a file comprising of only one worksheet to having multiple worksheets. When the company decides to remove the Table Specs Sheet from the items, a lighter version of Datafile Manager was created. It was called Datafile Manager Lite or DM Lite, it has all the functions of the original except for the HTML Code Builder. Moreover, several Worksheets and functions has been added. At the time of this writing, the current version of DM Lite is now at 2.0. The file format also change from .xlsx, .xlsm (macro-supporting excel file), and now .xlsb (macro-supporting binary excel file).
Where to Download?
Datafile Manager Lite is made exclusively for Appliances Connection and is not available for download. However, I could design a similar spreadsheet if requested. Please contact me.
Follow me at:
