XLS and CSV modification
Possible Languages:
• C (.NET)
• Visual Basic
• Python
• MS Access (2003/2007)
• MSSQL
Overview
We have an ecommerce site (future [login to view URL]) that requires daily price/product updates. We need a program or script (i.e. Excel or Access script/macro, etc) that will import data from CSV/XLS, consolidate the data, and export one table as CSV and another table as XLS (Excel 2003) format.
This script/macro/program needs to be run once daily (manual or scheduled)
File Formats
Import – Files to import will be in either CSV or XLS (Excel 2003) format.
Export – Data to export will be formatted in both CSV and XLS (Excel 2003) format.
Details
Import / Export
• Product/price lists (CSV and XLS) will be received daily and imported/consolidated into a master list.
• Imported Price lists will have different table headings and each supplier's list will need to be appropriately mapped into the master list
• All code must be documented to allow for minor changes (e.g. allow for addition of new suppliers with different data mapping)
• Export a copy of the master list as CSV, with custom table headings/format (so we can use our import tool to update MS Dynamics CRM product list)
• Export a copy of the master list as XLS (2003), with custom table headings/format (so we can upload/import to update online ecommerce product list)
Data Management
• Key index for the consolidated master list needs to be auto-generated, as we will have some overlap of the same product coming from different suppliers with different prices.
• We need the script to run a quick if/else switch so the export for the ecommerce database will update only one entry per product code as "active", the other entries of the same product as "inactive" - so that customers won't be confused when they search up a product and find many of the same item for different prices.
• The script must add a simple counter (can be table column) to keep track of number of days out of stock. Given that a product has seen 0 stock in over a given period (can be adjusted), to change the status of the product to "not active" so the ecommerce website will no longer display the product on the web site.
• We require an area where we can manually set the % markup rules (i.e. markupPrice= ceil(costPrice * 1.10, 1) thus a 10% mark up, minimum of 1 dollar. These rules need to be set up by category
• Exchange rate calculation. We require an area where we can manually input the exchange rate. Also to denote the currency for each supplier we work with.