Consolidate CSV Price List Import/Export

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.