Period Attendance from NC WISE into Excel Extracting the Data File from NC WISE ·From the main start screen, click the Reports button on the top icon bar ·Click the Export/Ad Hoc File button ·Click Start Over ·Click Saved Exports ·Change the radio selector to All at the top of the screen ·Scroll through the list and click once on Period attendance file (created by DOT_LODGE on 09/19/2007 as a Public-Restricted extract). ·Click Restore Saved Extract ·Click OK – the extract should appear in the “Selected” column ·Click Create File ·Make sure that Special Delimiter is set to Tab, Fixed Length should be unchecked, Number of Columns to Order On is set to 1, Double Quotes Around Character Fields is set to No, and Export Field Titles First is checked. ·Click Save as . . . ·In the Save in drop-down at the top, choose a location to save your data or click on Save As to browse to a file location ·In the File name: field, type a name for your data extract (it is good to use a date in your file name to indicate when it was extracted:period att 11-8-08.txt)Always name the file with the .txt file extension. ·Click Save ·Click Goto Extract ·Click Clear Extract Criteria ·Click the Schools tab ·Click your school’s name on the left side then click Add>>. ·Click Run Extract. oIf you get an error message here about your school not being selected, go back to the Basic tab and choose your school from the drop-down (right above the check box for Include Cross Enrolled Students) – if you don’t get the error message, don’t worry about this step. ·Click Go Back ·Click the Exit Door ·Click Create Export ·Click OK at the FERPA warning oIf a data extract has never been run on your computer, you may possibly be asked to install some software at this step.If so, install it.It may make you log back in after it installs. ·The export will generate (you may get an hour-glass for a few minutes).Click OK when you get the message that the export is complete. ·Click the Exit Door – if asked to save, choose either Yes or No – it doesn’t matter. ·Continue clicking OK, the Exit Door, or Yes until you have exited from NC WISE.
Opening the Data in Excel—Directions for Excel 2007 are in bold italics ·Close any other windows that may be open. ·Open Excel (Start>Programs or All Programs>Microsoft Office>Microsoft Excel) ·Click the File(Office button) menu ·Choose Open ·From the Look in drop-down box at the top, choose the location that you saved your data ·At the bottom, change the Files of type drop-down to All Files ·Either double-click your file name or click your file and then click Open ·On the Text Import Wizard screen, choose Delimited and then click Next ·Be sure that Tab is checked and click Next ·Click Finish ·If some of your columns contain #######, double-click the divider separating that column and the next column to resize the column. ·Save the file using File(Office button) /Save as… Click on the up arrow beside Save as file type and choose Microsoft Office Excel Workbook. (Choose the version you use at school/home.)
Filtering Data for Only a Certain Reporting Period ·To filter data for only this school year, click the drop-down box for the Incident Date column.Choose Custom. ·Change the first drop-down box from equal to Greater Than or Equal To ·In the second drop-down box (beside the first drop-down), type the date of the first day of the reporting period (use the same format as was used on the data sheet – normally mm/dd/yyyy) or choose the first date from the drop down. ·If using Excel 2007: (Click the drop-down for Incident Date and click the + sign beside the year.Check each month that you DO NOT want to include.) ·Change the second drop-down box to Less Than or Equal To and choose the last date of the reporting period. ·Click OK ·Click the Insert menu and choose Worksheet. (Click on the tab beside the active worksheet at the bottom of your screen) ·This will create a new tab called Sheet2. Right-click the tab and choose Rename.Rename this tab with your date range (2nd 6 weeks). ·Click on your main data tab. ·Press Ctrl-A on the keyboard to select all of this data or click the blank box that falls just above row number 1 and just left of column A. ·Press Ctrl-C on the keyboard to copy this data (or Edit, Copy).Marching Ants will appear around the data. ·Click the tab of the worksheet you have added. ·Press Ctrl-V on the keyboard to paste the data (or Edit, Paste). ·Resize columns as needed to make the data more viewable.
Saving in the Correct Format ·If you do not save this file in Excel, you may lose some features.Please be sure to follow these steps and save your work ·Go to the File menu and choose Save As. ·Choose where you would like to save this worksheet by selecting a location from the Save in drop-down.At the bottom, File Name probably still shows the original name of the file that you selected when you built the file.You can change it or leave it the same.In the Save as Type drop-down, choose Microsoft Office Excel Workbook (very first item in the list). ·Click Save. Pivot Tables and the Data ·Pivot tables are based on data in a table.In order to create a pivot table you must first assure that the table of data that you want to use is selected before beginning this process. ·Click in cell A1 of the table for which you want to build a pivot table or chart. ·Click the Data menu and choose PivotTable and PivotChart Wizard (Click the Insert menu and click Pivot Table. ·Click Finish ·This will open a new worksheet, Sheet3.Right-click the tab and rename it with a title that will appropriately describe this pivot table. ·Now, to add items to the side (like names or course), click drag and drop that category into the area labeled Drop Row Fields Here. ·Drop Teacher Name, Course Code, Period, Legal Name, First Name into the Row Labels.You will have to right mouse click over each data field, choose Field Settings, and click None under Subtotals. ·Drop Absence Indicator into the Column Labels and into the Values or Data Area. ·Click the Drop-down for Absence Indicator, and uncheck L.
Making the Data available to Teachers
You probably don’t want to leave this in a pivot table for teachers, so you will need to copy this to a flat Excel file.
Open a new file.
In your original file, go to the top left corner of the spreadsheet, and click Ctrl-A, Ctrl-C to select and copy the sheet.
Click in the new file and click Ctrl-V to copy the data.
Save this file on the Teacher Share drive for teachers to use.
Extracting the Data File from NC WISE
· From the main start screen, click the Reports button on the top icon bar
· Click the Export/Ad Hoc File button
· Click Start Over
· Click Saved Exports
· Change the radio selector to All at the top of the screen
· Scroll through the list and click once on Period attendance file (created by DOT_LODGE on 09/19/2007 as a Public-Restricted extract).
· Click Restore Saved Extract
· Click OK – the extract should appear in the “Selected” column
· Click Create File
· Make sure that Special Delimiter is set to Tab, Fixed Length should be unchecked, Number of Columns to Order On is set to 1, Double Quotes Around Character Fields is set to No, and Export Field Titles First is checked.
· Click Save as . . .
· In the Save in drop-down at the top, choose a location to save your data or click on Save As to browse to a file location
· In the File name: field, type a name for your data extract (it is good to use a date in your file name to indicate when it was extracted: period att 11-8-08.txt) Always name the file with the .txt file extension.
· Click Save
· Click Goto Extract
· Click Clear Extract Criteria
· Click the Schools tab
· Click your school’s name on the left side then click Add>>.
· Click Run Extract.
o If you get an error message here about your school not being selected, go back to the Basic tab and choose your school from the drop-down (right above the check box for Include Cross Enrolled Students) – if you don’t get the error message, don’t worry about this step.
· Click Go Back
· Click the Exit Door
· Click Create Export
· Click OK at the FERPA warning
o If a data extract has never been run on your computer, you may possibly be asked to install some software at this step. If so, install it. It may make you log back in after it installs.
· The export will generate (you may get an hour-glass for a few minutes). Click OK when you get the message that the export is complete.
· Click the Exit Door – if asked to save, choose either Yes or No – it doesn’t matter.
· Continue clicking OK, the Exit Door, or Yes until you have exited from NC WISE.
Opening the Data in Excel—Directions for Excel 2007 are in bold italics
· Close any other windows that may be open.
· Open Excel (Start>Programs or All Programs>Microsoft Office>Microsoft Excel)
· Click the File (Office button) menu
· Choose Open
· From the Look in drop-down box at the top, choose the location that you saved your data
· At the bottom, change the Files of type drop-down to All Files
· Either double-click your file name or click your file and then click Open
· On the Text Import Wizard screen, choose Delimited and then click Next
· Be sure that Tab is checked and click Next
· Click Finish
· If some of your columns contain #######, double-click the divider separating that column and the next column to resize the column.
· Save the file using File(Office button) /Save as… Click on the up arrow beside Save as file type and choose Microsoft Office Excel Workbook. (Choose the version you use at school/home.)
Filtering Data for Only a Certain Reporting Period
· To filter data for only this school year, click the drop-down box for the Incident Date column. Choose Custom.
· Change the first drop-down box from equal to Greater Than or Equal To
· In the second drop-down box (beside the first drop-down), type the date of the first day of the reporting period (use the same format as was used on the data sheet – normally mm/dd/yyyy) or choose the first date from the drop down.
· If using Excel 2007: (Click the drop-down for Incident Date and click the + sign beside the year. Check each month that you DO NOT want to include.)
· Change the second drop-down box to Less Than or Equal To and choose the last date of the reporting period.
· Click OK
· Click the Insert menu and choose Worksheet. (Click on the tab beside the active worksheet at the bottom of your screen)
· This will create a new tab called Sheet2. Right-click the tab and choose Rename. Rename this tab with your date range (2nd 6 weeks).
· Click on your main data tab.
· Press Ctrl-A on the keyboard to select all of this data or click the blank box that falls just above row number 1 and just left of column A.
· Press Ctrl-C on the keyboard to copy this data (or Edit, Copy). Marching Ants will appear around the data.
· Click the tab of the worksheet you have added.
· Press Ctrl-V on the keyboard to paste the data (or Edit, Paste).
· Resize columns as needed to make the data more viewable.
Saving in the Correct Format
· If you do not save this file in Excel, you may lose some features. Please be sure to follow these steps and save your work
· Go to the File menu and choose Save As.
· Choose where you would like to save this worksheet by selecting a location from the Save in drop-down. At the bottom, File Name probably still shows the original name of the file that you selected when you built the file. You can change it or leave it the same. In the Save as Type drop-down, choose Microsoft Office Excel Workbook (very first item in the list).
· Click Save.
Pivot Tables and the Data
· Pivot tables are based on data in a table. In order to create a pivot table you must first assure that the table of data that you want to use is selected before beginning this process.
· Click in cell A1 of the table for which you want to build a pivot table or chart.
· Click the Data menu and choose PivotTable and PivotChart Wizard (Click the Insert menu and click Pivot Table.
· Click Finish
· This will open a new worksheet, Sheet3. Right-click the tab and rename it with a title that will appropriately describe this pivot table.
· Now, to add items to the side (like names or course), click drag and drop that category into the area labeled Drop Row Fields Here.
· Drop Teacher Name, Course Code, Period, Legal Name, First Name into the Row Labels. You will have to right mouse click over each data field, choose Field Settings, and click None under Subtotals.
· Drop Absence Indicator into the Column Labels and into the Values or Data Area.
· Click the Drop-down for Absence Indicator, and uncheck L.
Making the Data available to Teachers