This project is based on a Real- World scenario

Subject:Computer SciencePrice: Bought3

Share With

This project is based on a Real- World scenario. A customer gets an updated price sheet from their vendor, Gardner Denver (https://www.gardnerdenver.com / https://www.gardnerdenver.com/en-us/industrials/products/compressors). This updated price sheet comes in the form of an Excel spreadsheet with three (3) columns - Material (aka part number), Description, Price.

The current software they use for managing their inventory, pricing, quotes, invoices, and other such fun things in business requires that their price update is in the form of a CSV file. But, not just any old CSV file, and not in the current format the pricing sheet comes in. And there are going to be three (3) files. One for the main warehouse, one for warehouse 00 and one for warehouse 01. The CSV files must meet the following criteria:

1. The three columns that are needed are:
   1. Part number
   2. Cost
   3. Price
2. The file must be comma-delimited
3. The file must NOT contain any quotation marks
4. The numeric fields (Cost and Price) must have two decimal places (with trailing zeros)
5. The cost field is calculated
   1. Cost = Price * 0.6
6. Each warehouse file must be separate
   1. For the main warehouse, the part number does not change
   2. For warehouse 00, you must append -00 to the part number
   3. For warehouse 01, you must append -01 to the part number

Here is a sample of the result from each warehouse file.

Main Warehouse

00001143 SP,136.41,227.35

00001223 SP,48.66,81.10

00001236 SP,149.72,249.53

0001-0003-00,100.95,168.25

00015172 W,85.32,142.20

Warehouse-00

00001143 SP-00,136.41,227.35

00001223 SP-00,48.66,81.10

00001236 SP-00,149.72,249.53

0001-0003-00-00,100.95,168.25

00015172 W-00,85.32,142.20

Warehouse-01

00001143 SP-01,136.41,227.35

00001223 SP-01,48.66,81.10

00001236 SP-01,149.72,249.53

0001-0003-00-01,100.95,168.25

00015172 W-01,85.32,142.20

Objectives / Deliverables

What you must do is develop a PowerShell script that converts this Excel file into the three separate CSV files with the above criteria. You CANNOT manipulate the Excel file OUTSIDE of PowerShell. This MUST be done completely within your PowerShell script.

This means that you must either have Excel installed on the machine or you will need to get and use the ImportExcel module to manipulate the Excel file. You do NOT need to use functions, but you can if you wish to.

1. The PowerShell script MUST be documented
   1. Include a "header"
      1. Your name
      2. Class

• Title of project

1. Description of what the script is going to do
2. Comment your code so someone else can follow along and understand what is happening in each area of your code
3. You must use the GD2018Price.xlsx  Download GD2018Price.xlsx file included with the project
   1. Remember, you cannot modify the Excel file outside of the PowerShell script
4. You must either of the following:
   1. Use Excel COM calls to work with the Excel file
   2. ImportExcel Module by Doug Finke
5. I want you have a timer that starts at the beginning of the script and stops at the end. The timer should then display the elapsed time to do all of the processing and converting.
6. The cost field MUST be calculated within the PowerShell script
   1. Cost = Price * 0.6
7. There must be three (3) separate final pricing files for import - one per warehouse. They will be identical except for the part numbers (see above).
   1. CSV
   2. CSV
   3. CSV