Mini-course Combining files from multiple data sources to create one combined file

1. Mini-course Combining files from multiple data sources to create one combined file

2. 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

3. Example

4. Example 2

5. Getting a complete district NeSA file out of DRS All students in one file. At the student level By year

6. Link to DRC https://ne.drcedirect.com/default.aspx Or there is also a link on the NDE Assessment Page to eDirect

9. 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”

10. To download the file click to “Save” but don’t open to do this click on the green arrow

11. Save the file to the computer as a .csvfile 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

12. 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

13. How to open a .csv file in Excel It is crucial to follow these steps or date formatting and leading zeros will be lost And the file will not pull into Access properly

14. First, open a blank copy of Excel, click “Data”, and then select “From Text”

15. Locate the file, click in the file name you wish to view and then select “Open” to import the file

16. An Import Wizard will appear Select “Delimited” And “Next”

17. Check “Comma” And “Next”

18. 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” 2. 1. Click “Finish”

19. Click “Ok” on this screen

20. Unwanted columns of data can be eliminated by right clicking on the column (s), highlighting the area, and deleting

21. This will now yield a large raw file that can be used for analysis

22. Fileto become familiar with the file structure of the NeSA This was sent in an email earlier today

23. From both files include at a minimum Student ID School Code First Name Last Name Subject Scale Score Performance level Suggested to also add Gender Race Frl Sped Grade Year

24. 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

25. Save the file as a .csv (comma delimited) Find the “Save As” function

26. From the pull down menu locate the CSV option as shown below and select that type

27. There may be two warnings such as the one shown below. On each warning select “Yes” or “Ok”

28. Steps to combining files in Microsoft Access Or FileMaker Pro

29. Creating a new database Select “New” and then “Create”

30. This screen will appear

31. Select “External Data” or “Import External Data” and “Text File” The exact wording will depend on the version of Office

32. Locate the file to be imported by browsing, usually a .csv file Select “Import the Source Data into a new table……..”

33. Select “Delimited”

34. Select “Comma” (or other delimiter if some other format) and “First Row Contains Field Names” if the file has field headings then click “Next”

35. 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

36. Method 1

37. 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 2 1

38. Method 2

39. 1. Choose “Advanced” and a window will appear on top like the one shown 2. Place the cursor in the box showing the data type and click. A pull down menu will appear 3. Select “Text” from your choices This 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” 4 2,3 1 5

40. It is important to scroll through the file once more just to be sure all fields are saved as text

41. Select “No Primary Key” and “Next”

42. Name the table something meaningful to you Remember, you may have multiple tables so label clearly (with year tag) Select “Finish” Name the table

43. Select “Close”

44. Repeat the process with other tables that are wanted in the end data set In today’s case the other NeSA file

45. 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 source The 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

46. In this example there are two NeSA files to be joined (2010 and 2011 NeSA files). A Query will be created to do this. 2. “Query Design” 1. “Create” Click “Create” and then “Query Design”

47. 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

48. To designate the tables to be worked with Click on the name of the table Click “Add” and the table will appear in upper part of the screen as shown by the blue arrow 1 2

49. When matching fields from different tables into one data set (query) Both tables must have a common field In most cases this will be the unique student identifier 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 set That is the field that will join the two tables

50. From both files include at a minimum Student ID School Code First Name Last Name Subject Scale Score Performance level Suggested to also add Gender Race Frl Sped Grade Year