CAIS 201- Introduction to CAIS Assignment
Instructions
Download Employee.txt file exported from a Database. This text file includes the Comma Separated Values (csv) which you will work on.
Initiation
1. Open a new Excel file, save it with your name and assignment number. For example "GaziDuman_Assignment2"
2. 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.
Data Organizing
3. 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
4. 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.
5. 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.
6. 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.
Queries
6. 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.
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.
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.
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also includes a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.