CAIS 201–Introduction to CAIS
Due Date and Time: September26, Before 9AM.
All assignments must be typed inMS Excel and must be submitted via Canvas.
Please see the instructions posted in Canvas for online assignment submission
Go to Assignment 2 module and download Employee.txt file exported from a Database. This text file includes the Comma Separated Values (csv) which you will work on.
Please save your File after completing each step.
- Open a new Excel file, save it with your name and assignment number. For example “GaziDuman_Assignment2” (5 pts)
- Open Employee.txt file and select all the data with using “Ctrl+A” command, copy it with using “Ctrl+C” command and paste it on the very first cell (A1) of your Excel file on “Sheet 1” using “Ctrl+V” command. (5 pts)
Data Organizing (10 pts)
- Now all the data is placed on one column and separated with commas. In order to organize the data in Excel structure you need to separate the data placing them appropriately on cells instead of separating them with commas.
Note: Excel usually automatically recognizes the commas and places them on cells. However, sometimes you have to do it manually. If it doesn’t recognize on your file, go to DATA tab and you will see “Text to Columns” button. Click on it. On the box, pick the “Delimited” option and click “Next”. In the delimiters section, pick “comma” and click “Next”. You will see a preview on the box and click “Finish”. Then save your file.
Data Cleansing(40 pts)
- Some cells have extra spaces and need to be trimmed. In order to do that, open a new worksheet “Sheet 2” and click on the first cell (A1), begin trimming the values on “Sheet 1” starting from (A1). The function on Sheet 2 A1 should look like this =TRIM(Sheet1!A1). Then keep dragging the formula for the entire table. Then save your file. (10 pts)
- There are duplicate values to be cleaned up. Open a new work sheet “Sheet3” select all the values from “Sheet2”, copy them go to “Sheet3”, paste them using your mouse. On the paste menu select “only values” option. Otherwise you would paste them with the formulas. Then select the entire table on “Sheet3” and go to DATA tab click on “Remove Duplicates”. On the box, unpick Column A which is the Primary Key. Press OK and 6 rows should be removed automatically.Then save your file. (15 pts)
- TRIM treats the values in the cells as texts so it automatically removes “number format” from the numerical values and dates. You will see a green symbol on the cells and an exclamation mark when you click on the cells. You will need to convert these cells to numbers. For instance column A, EmployeeID, select all the numerical values starting from A2 and and click on the Exclamation Mark, then select Convert to Number. For HireDate and BirthDate columns, you need to convert them to numbers and then convert them to Date format. To do that you need to select all the values on those columns, right click on your mouse, go to Format Cells and Select Date.Then save your file. (15 pts)
- Open a new worksheet “Sheet4”. Copy the entire table from “Sheet3” and paste it on “Sheet4”. Select the first row (Headers) and on DATA tab select “Filter”. You can also pick a color for Headers from Home Tab to distinguish them from the rest of the Table.Then save your file. (10 pts)
7.On Sheet 4Display all the Supervisors who have the highest BaseRate. To do that, you need to select the filter on Title column, type Supervisor on the search field, click OK. Go to BaseRate Filter and select only the highest pay rate you see on the list. Then save your file. (10pts)
- 8. Open a new worksheet “Sheet5”, go to “Sheet3” select and copy the table, paste it on “Sheet5”. Follow the steps on 6.
Display all the employees in Engineering Department who have more than 21 SickLeaveHours and more than 4 VacationDays. To do that, go to Department Column, filter the Engineering only. Then go to SickLeaveHours column, go to Number Filters, select greater than option and type 21 in the related field. Follow the same steps for VacationDays Column. Then Save your file. (20 pts)
And submit your assignment.
If you don’t save your file after completion of each step, I can’t see that you did the work.