mini-course combining files from multiple data sources to create one combined file
DESCRIPTION
Mini-course Combining files from multiple data sources to create one combined file. This course will use 2009-2010 and 2010-2011 NeSA reading as an example Or spring 2010 and spring 2011 as it is called in the DRC system - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/1.jpg)
Mini-course
Combining files from multiple data sources to create one
combined file
![Page 2: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/2.jpg)
This course will use 2009-2010 and 2010-2011 NeSA reading as an example
Or spring 2010 and spring 2011 as it is called in the DRC system
This process can be applied to any number of files as long as a text version can be
obtained
![Page 3: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/3.jpg)
Example
![Page 4: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/4.jpg)
Example 2
![Page 5: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/5.jpg)
Getting a complete district NeSA file out of DRS
All students in one file. At the student level
By year
![Page 6: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/6.jpg)
Link to DRChttps://ne.drcedirect.com/default.aspx
Or there is also a link on the NDE Assessment Page to eDirect
![Page 7: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/7.jpg)
![Page 8: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/8.jpg)
![Page 9: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/9.jpg)
For this example look for Spring 2010 , save the file and then find RMS Spring 2011 and save it.
Click the desired item and then click “View Reports”
![Page 10: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/10.jpg)
To download the file click to “Save” but don’t opento do this click on the green arrow
![Page 11: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/11.jpg)
Save the file to the computer as a .csv file but do not open it directly from DRC
When clicking on the green icon in the previous photo there will be an option to save.
In this case it will automatically be a .csv file as long as it is not opened first
![Page 12: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/12.jpg)
If you would like to simply look at the data in the file you may double click to
open it in Excel
If you do this, close the file when finished but do not
save it
![Page 13: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/13.jpg)
How to open a .csv file in Excel
It is crucial to follow these steps or date formatting and leading zeros will be lostAnd the file will not pull into Access properly
![Page 14: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/14.jpg)
First, open a blank copy of Excel, click “Data”, and then select “From Text”
![Page 15: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/15.jpg)
Locate the file, click in the file name you wish to view and then select “Open” to import the file
![Page 16: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/16.jpg)
Select “Delimited”And “Next”
An Import Wizard will appear
![Page 17: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/17.jpg)
Check “Comma”And “Next”
![Page 18: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/18.jpg)
In this step the entire row can be highlighted by clicking in the first column, scrolling across to the last column, putting the arrow in the last column, holding the shift key down while clicking the mouse. Then click “Text” and the headings should all change to “Text”
1.
2.
Click “Finish”
![Page 19: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/19.jpg)
Click “Ok” on this screen
![Page 20: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/20.jpg)
Unwanted columns of data can be eliminated by right clicking on the column (s), highlighting the area, and deleting
![Page 21: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/21.jpg)
This will now yield a large raw file that can be used for analysis
![Page 22: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/22.jpg)
File to become familiar with the file structure of the NeSA
This was sent in an email
earlier today
![Page 23: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/23.jpg)
From both files include at a minimumStudent ID
School CodeFirst NameLast Name
SubjectScale Score
Performance level
Suggested to also addGender
RaceFrl
SpedGradeYear
![Page 24: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/24.jpg)
Remember, after opening the file as a .csv or making changes in it you will need to save it as
a .csv when finished
The file will have to be saved as a .CSV and closed before it will pull into Access properly
![Page 25: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/25.jpg)
Save the file as a .csv (comma delimited)Find the “Save As” function
![Page 26: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/26.jpg)
From the pull down menu locate the CSV option as shown below and select that type
![Page 27: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/27.jpg)
There may be two warnings such as the one shown below. On each warning select “Yes” or “Ok”
![Page 28: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/28.jpg)
Steps to combining files in Microsoft AccessOr FileMaker Pro
![Page 29: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/29.jpg)
Creating a new databaseSelect “New” and then “Create”
![Page 30: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/30.jpg)
This screen will appear
![Page 31: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/31.jpg)
Select “External Data” or “Import External Data” and “Text File”The exact wording will depend on the version of Office
![Page 32: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/32.jpg)
Locate the file to be imported by browsing, usually a .csv fileSelect “Import the Source Data into a new table……..”
![Page 33: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/33.jpg)
Select “Delimited”
![Page 34: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/34.jpg)
Select “Comma” (or other delimiter if some other format) and “First Row Contains Field Names” if the file has field headings then click “Next”
![Page 35: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/35.jpg)
The next two slides show two methods of completing the same task
You will not need to do both but choose the method that you prefer
![Page 36: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/36.jpg)
Method 1
![Page 37: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/37.jpg)
Each field needs to be changed to text (much like in the.csv file earlier but in this case it is done field by field• 1. Highlight the field
• Select from the pulldown and choose “Text”Some will already be text Be sure to check each field before proceeding
1
2
![Page 38: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/38.jpg)
Method 2
![Page 39: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/39.jpg)
1. Choose “Advanced” and a window will appear on top like the one shown2. Place the cursor in the box showing the data type and click. A pull down menu will appear
3. Select “Text” from your choicesThis will still be done by working down field by field until all the fields are text
4. Select “Ok” when done (the overlay window will disappear) 5. Select “Next”
1
2,3
4
5
![Page 40: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/40.jpg)
It is important to scroll through the file once more just to be sure all fields are saved as
text
![Page 41: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/41.jpg)
Select “No Primary Key” and “Next”
![Page 42: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/42.jpg)
Name the table something meaningful to youRemember, you may have multiple tables so label clearly (with
year tag)Select “Finish”
Name the table
![Page 43: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/43.jpg)
Select “Close”
![Page 44: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/44.jpg)
Repeat the process with other tables that are wanted in the end data set
In today’s case the other NeSA file
![Page 45: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/45.jpg)
There are now two tables imported. These could be from any number of sources (MAP, NeSA, Demographics, Grades, local formative test files etc. or different years
from the same sourceThe task now is to join the two tables together into one table
You should now be able to see the two tables you pulled in
![Page 46: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/46.jpg)
In this example there are two NeSA files to be joined (2010 and 2011 NeSA files). A Query will be created to do this.
1. “Create” 2. “Query Design” Click “Create” and then “Query Design”
![Page 47: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/47.jpg)
There will be a window with the tables available in it.Select the table or tables that has the fields that are to be in the combined data set
![Page 48: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/48.jpg)
To designate the tables to be worked with1. Click on the name of the table
2. Click “Add” and the table will appear in upper part of the screen as shown by the blue arrow
1
2
![Page 49: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/49.jpg)
When matching fields from different tables into one data set (query)
1. Both tables must have a common field In most cases this will be the unique student identifier
2. Be certain when selecting fields for the data set (as shown in the next slide) that you select that common field from both tables to
be in the data set so that field appears twice in the data setThat is the field that will join the two tables
![Page 50: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/50.jpg)
From both files include at a minimumStudent ID
School CodeFirst NameLast Name
SubjectScale Score
Performance level
Suggested to also addGender
RaceFrl
SpedGradeYear
![Page 51: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/51.jpg)
Select the specific fields desired from the tables. The new data sheet will have the fields in the order they were selected from the tables in this step by
1. Double clicking on the field name or2. Dragging and dropping the field name in the lower table layout
Fields will appear from top view to the new data sheet grid as show by blue arrows
![Page 52: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/52.jpg)
Once the fields are selected and placed in the data set we will join the two
tables on the common field from both tables
Today that common field is student ID
![Page 53: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/53.jpg)
To Join the Tables1. Click on the field (student ID) in one table and hold the mouse button down
2. Drag the pointer to that same field on the second table and release the mouse button and a line will appear connecting the two tables
1
2
3
3. Right click on the line and then click on “Join Properties” Note: if you get a longer menu when you click on that option just click off and then right click on it again
![Page 54: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/54.jpg)
Once the steps in the previous slide have been completed and “Join Properties” has been clicked there will be a screen as show below to specify how the join is
to be done.Once the type of join is specified click “Ok”
1. Will only show records where both files have the same student ID in both files 2. Will only show all records that are in the 2011smallsample file and will have blank spots in the
2011smallsample2 file if that ID is not found in 2011smallsample2 3. This will show all records in the 2011smallsample2 file and will have blanks for the fields from the
2011smallsample file for those IDs not in 2011smallsample
12
3
![Page 55: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/55.jpg)
Notes on types of Joins
If looking at data over years (time) and the desire is to compare the same students over time join 1 would be the best to use. It is the one that would show all information
but only for students who were there all years
There will be times when the desire is to see who was there one year and not the next. In that case join 2 or 3
would be appropriate.
![Page 56: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/56.jpg)
Once the type of join has been specified click “RUN!”Cross your fingers and hold your breath
![Page 57: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/57.jpg)
The resulting file should have the fields chosen from each table in the order selected
![Page 58: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/58.jpg)
A .csv file that can be viewed and filtered in Excel can now
be exported
![Page 59: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/59.jpg)
method for exporting a .csv file that can be used in Excel
![Page 60: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/60.jpg)
1
2
3
1. Right click on the name of the file or query you wish to export which should highlight it2. Click or roll over “Export”
3. Roll over “Text File” and click on it
![Page 61: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/61.jpg)
Select where the file is to go.
Do not select this box or any of the others
Click “Ok”
![Page 62: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/62.jpg)
Select “Delimited” and “Next”
![Page 63: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/63.jpg)
Specify the type of file to be saved in most cases this will be “Comma”
Check box to include field names
![Page 64: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/64.jpg)
Select “Finish”
![Page 65: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/65.jpg)
Select “Close”The file should now be in the location you specified that it be saved
![Page 66: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/66.jpg)
Due Dates
You’ll let us
submit late
![Page 67: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/67.jpg)
Sample Fall CalendarData Collection/Form Due Date
Review Period Ending date – FIINAL NO CHANGES
Non Certificated Staff 31-Oct 15-Nov
Carl D. Perkins Career and Technical Education Act Report 10-Aug 15-Nov
Summer School Supplement 31-Aug 15-Nov
Elementary Class Size 15-Oct 15-Nov
Summer School Student Unit 15-Oct 15-Nov
Elementary Site Allowance 15-Oct 15-Nov
Assessed Valuation and Levies 15-Oct 15-Nov
Two-Year New School Adjustment Application 15-Oct 15-Nov
Student Growth Adjustment 15-Oct 15-NovInstructional Time 15-Oct 15-Nov
PK Instructional Program Hours/K Program 15-Oct 15-NovNSSRS Staff Data 31-Oct 15-Nov
Membership (last Friday in September) 31-Oct 15-NovSPED Child Count 31-Oct 15-NovEC Program Participation 31-Oct 15-Nov
Nonpublic Membership 31-Oct 15-Nov
SAMPLE
![Page 68: Mini-course Combining files from multiple data sources to create one combined file](https://reader036.vdocuments.us/reader036/viewer/2022070423/5681662f550346895dd99598/html5/thumbnails/68.jpg)