SIMS How To Create an Export File SiX Document Title/ Search Keyword(s): Create an export file from SIMS for purplemash usernames and passwords Subject: Create an export file from SIMS for purplemash usernames and passwords SiX Document ID: 26156 Service Group: e-Learning and Information Management Contact Details: Helpline 01823 355090 SWOICTHelpdesk@somerset.gov.uk Date of issue: December 2014 Produced by the Children and Young People's Directorate of Somerset County Council Whilst Somerset County Council makes every effort to ensure accuracy of content, it cannot accept liability for claim, loss or damage howsoever arising from the reliance on or use of this information. This document is available upon request in larger print sizes, Braille and other languages. Please use the contact details above for further information. ELIM/APT/SIMSToPurpleMash 23/09/2011 This document is designed to supplement the 2simple Locally Administered Simple Sign-On user guide , and explains how to extract pupil names from SIMS into Excel to produce the required CTF file for importing into Purplemash. These instructions require a login to SIMS with access to run reports on pupil basic information. ELIM Page 1 of 5 Version 1.01 Produced by the Educaton MIS Team as part of the Somerset County Council e-Learning & Information Management Blue Book Service. Steps 1. Open SIMS .net 2. Click on [Reports] | [Run Report] 3. Expand the Focus folder then click on the Student folder and locate and run the VLE Users Export – Students report. If the report cannot be found on your system please contact the helpline. When the file C:\ReportOutput.txt has been created Click [OK] 4. ELIM Open Excel 5. Click on [File] | [Open] or [Microsoft Office Button] | [Open] depending on the version of Office. Type (or copy and paste from this document) C:\ReportOutput.txt in the file name box and click [Open] 6. From the following dialogue boxes choose to open the file (with the default settings) as a Delimited file, with a Tab delimiter and “ as the text qualifier in the box on the right. Page 2 of 5 Version 1.01 Produced by the Educaton MIS Team as part of the Somerset County Council e-Learning & Information Management Blue Book Service. 7. Click on [Finish] to open the file. 8. Insert 2 new empty columns as Column A and B, to do this highlight the existing first two columns, then right click the selection and choose [Insert] 9. In Cell A1 type Username and in Cell B1 type Password 10. In Cell A2 type (or copy and paste from this document) =CONCATENATE(D2,RIGHT(ROW()+10,2)) 11. Copy Cell A2 down so that a username is generated for every pupil. The easiest way to do this is to click Cell A2, and move the cursor to the bottom right of the cell. When the cursor changes to a ‘+’ click and hold the left mouse button and drag down to the bottom pupil in the list, then release the mouse button. 12. To create passwords: For a simple password: In Column B enter the password to be allocated to each pupil. An example of passwords that could be used is included below, and can be copied and pasted from this document into the spreadsheet. Passwords do not need to be unique so the list can be pasted in several times to give all pupils a password, but when doing this make sure any extra passwords at the bottom of the list are deleted, so there are not more passwords than there are usernames. Cat12 Dog13 Fish14 Rabbit15 Kitten16 Puppy17 Cow18 Sheep19 Hen20 Frog21 Badger22 Eagle23 Robin24 Bat25 Toad26 Sparrow27 Hawk28 Goat29 Horse30 Pig31 Otter32 ELIM Page 3 of 5 Version 1.01 Produced by the Educaton MIS Team as part of the Somerset County Council e-Learning & Information Management Blue Book Service. Goose33 Hare34 Donkey35 Snake36 Hamster37 Heron38 Owl39 Dove40 Lion41 Tiger42 Deer43 Zebra44 Lamb45 13. Check and edit if necessary the usernames to ensure: a. All usernames are unique. If you have over 100 pupils there is a small possibility that pupils with the same first name may be given the same username. b. That no invalid characters are included. Invalid characters are any accented characters and any characters other than (A-Z),(0-9), (.), (-). 14. Select [File] | [Save As] or [Microsoft Office Button] | [Save As] to save this file as purplemash01 in My Documents in CSV (Comma delimited) file format. Click [Save]. A message will be displayed that purplemash01.csv may contain features that are not compatible with CSV (Comma delimited) Click [Yes] to continue to save the file in this format. ELIM 15. Close the spreadsheet 16. In Excel re-open the file purplemash01 from My Documents. Change the Files of type to All Files (*.*) to see the file to select 17. Delete Columns D to G by highlighting the columns and pressing the [Delete] key Page 4 of 5 Version 1.01 Produced by the Educaton MIS Team as part of the Somerset County Council e-Learning & Information Management Blue Book Service. ELIM 18. Add headings to columns as below 19. Add pupil under column D for each child by typing in pupil in the first row, highlight cell, clicking in bottom right corner and dragging down column. 20. Add y or n to shared folders column as per 19. Y will mean children can see shared folders to save their work. N will mean they will only be able to save work to their own online folder. 21. Add class name to column F, clicking in bottom right corner and dragging down column for each class. 22. Select [File] | [Save As] or [Microsoft Office Button] | [Save As] to save this file as purplemash02 in My Documents in CSV (Comma delimited) file format. Click [Save]. A message will be displayed that purplemash01.csv may contain features that are not compatible with CSV (Comma delimited) Click [Yes] to continue to save the file in this format. 23. This file, purplemash02.csv can be used to upload user names and passwords to purplemash, by following their instructions, while the purplemash01.csv can be used to help manage the distribution of usernames and passwords to teachers / pupils. 24. Once uploaded to PurpleMash you will be able to print out log in cards for your children. Page 5 of 5 Version 1.01 Produced by the Educaton MIS Team as part of the Somerset County Council e-Learning & Information Management Blue Book Service.