Powershell Script to Compare Supplier CSV files and output as one Modified CSV
- or -
Post a project like this$
- Posted:
- Proposals: 6
- Remote
- #1247290
- Expired
✪Software Developer ✪Web Developer (Java,Python, Android, jee, JSP, JSF, Java Fx,Vue, Blue J, Nuxt) ✪ Processing ✪ Elastic Search ✪ Database
Saitama
6726430461807484100513410792051244165
Description
Experience Level: Entry
General information for the business: Computer Reseller with several suppliers who provide a csv feed several times a day
Description of requirements/functionality: Hopefully be quite a quick script for someone who knows the ins and outs of Powershell.
Several suppliers provide a CSV product feed hourly, with their latest product prices and stock levels in this. I am wanting a script that can be scheduled to run on a Windows PC that compares these feeds and combines them into one with some logic applied. Once the supplier feeds are combined then a profit margin needs to be applied to the products based on the category they are featured in and output into a new column.
I have already made each of the supplier CSV feeds into the same format of - SKU, ProductCat, Qty, Price, Supplier
SKU = Unique product part code, will be the same across the different suppliers. Some suppliers do have multiple products with the same SKU listed in their feed (which should not be possible, but is) so logic needs to be applied to make this a single line before comparing to other suppliers.
ProductCat = The category the product resides within, ultimately used to decide on the margin applied to the price. Currently use an index of all the supplier categories and then map them to a list of my unified categories which I apply my margin against.
Qty = The quantity of an item they have in stock. 0 Qty lines need to be ignored when compared against ones with quantity of 1 or greater. However 0s need to be compared against other 0 Qty products when they are not in stock at any supplier.
Price = Cost of item from the supplier, always looking to output the cheapest priced supplier that has stock.
Supplier = Just the name of the Supplier, so I can see who had the item when I need to place an order.
This information is currently fed into a Excel workbook with a set of VBA scripts that execute all the logic and provide an output, however this current process takes over an hour and I am looking to speed it up by using well thought out Powershell.
I need the script to do the following:
1)Get all the product feeds from a folder
2)Get the margins I have set / the categories they apply to from a CSV or set of CSVs in a folder
3)Check each supplier feed for duplicate SKUs. The basic logic here is that if one is in stock that is the line that is used, otherwise the highest priced item is the one that wants to be taken to reduce the chance of mispricing errors. All the other duplicates will then be removed.
4)Compare the suppliers together, outputting all the unique products and comparing the ones that appear from more than one supplier and outputting the one with stock and the lowest price, or just the lowest priced supplier if they all have no stock.
5)Apply a profit margin to this combined list, taking margins from a list that I have set and applying them to categories I have matched up with the supplier ones. Any supplier categories not set get a default margin applied as a failsafe.
6)Apply an individual product margin override, from another CSV that overwrites the category profit margin for one specifically entered for one SKU.
6)Export this information as a CSV file ready to upload to my website.
I have attached below examples of the supplier feeds and the Excel Workbook that currently runs the operation that I am looking to speed up / improve the reliability.
If you have any questions, as I am sure my explanation has not been as clear as it could be, please take a look at the excel workbook Macros to see what is happening, with the xFeedCaller being the macro that is executed triggering the others.
Specific technologies required: Powershell
OS requirements: Windows
Extra notes: The files are ~ delimited due to descriptions and contents of the csvs using other special characters earlier on / potentially in the SKUs.
Ideally need this script to execute as quickly as possible.
Description of requirements/functionality: Hopefully be quite a quick script for someone who knows the ins and outs of Powershell.
Several suppliers provide a CSV product feed hourly, with their latest product prices and stock levels in this. I am wanting a script that can be scheduled to run on a Windows PC that compares these feeds and combines them into one with some logic applied. Once the supplier feeds are combined then a profit margin needs to be applied to the products based on the category they are featured in and output into a new column.
I have already made each of the supplier CSV feeds into the same format of - SKU, ProductCat, Qty, Price, Supplier
SKU = Unique product part code, will be the same across the different suppliers. Some suppliers do have multiple products with the same SKU listed in their feed (which should not be possible, but is) so logic needs to be applied to make this a single line before comparing to other suppliers.
ProductCat = The category the product resides within, ultimately used to decide on the margin applied to the price. Currently use an index of all the supplier categories and then map them to a list of my unified categories which I apply my margin against.
Qty = The quantity of an item they have in stock. 0 Qty lines need to be ignored when compared against ones with quantity of 1 or greater. However 0s need to be compared against other 0 Qty products when they are not in stock at any supplier.
Price = Cost of item from the supplier, always looking to output the cheapest priced supplier that has stock.
Supplier = Just the name of the Supplier, so I can see who had the item when I need to place an order.
This information is currently fed into a Excel workbook with a set of VBA scripts that execute all the logic and provide an output, however this current process takes over an hour and I am looking to speed it up by using well thought out Powershell.
I need the script to do the following:
1)Get all the product feeds from a folder
2)Get the margins I have set / the categories they apply to from a CSV or set of CSVs in a folder
3)Check each supplier feed for duplicate SKUs. The basic logic here is that if one is in stock that is the line that is used, otherwise the highest priced item is the one that wants to be taken to reduce the chance of mispricing errors. All the other duplicates will then be removed.
4)Compare the suppliers together, outputting all the unique products and comparing the ones that appear from more than one supplier and outputting the one with stock and the lowest price, or just the lowest priced supplier if they all have no stock.
5)Apply a profit margin to this combined list, taking margins from a list that I have set and applying them to categories I have matched up with the supplier ones. Any supplier categories not set get a default margin applied as a failsafe.
6)Apply an individual product margin override, from another CSV that overwrites the category profit margin for one specifically entered for one SKU.
6)Export this information as a CSV file ready to upload to my website.
I have attached below examples of the supplier feeds and the Excel Workbook that currently runs the operation that I am looking to speed up / improve the reliability.
If you have any questions, as I am sure my explanation has not been as clear as it could be, please take a look at the excel workbook Macros to see what is happening, with the xFeedCaller being the macro that is executed triggering the others.
Specific technologies required: Powershell
OS requirements: Windows
Extra notes: The files are ~ delimited due to descriptions and contents of the csvs using other special characters earlier on / potentially in the SKUs.
Ideally need this script to execute as quickly as possible.
Thomas L.
100% (1)Projects Completed
2
Freelancers worked with
2
Projects awarded
67%
Last project
9 Feb 2016
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
Hi would you consider this as a .net application that can be scheduled just as easily?
do you pull the feeds or are they delivered to you?
if you pull them, would you want the application to pull for you or is this handled already?
105314
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies