Python - adjusting sims report output

[siuko]

Hi,

 

I am trying to create a php webpage from the Sims report csv output.

 

Initially I was thinking of using python to clean up my data for easier import into mysql.

 

Below is an example of the content I can get out of Sims.

 

"Adno","Forename","Surname","Year","Class","Room","Period"
"033514","Joe","Bloggs","Year 10","10A/Gw1","S21","2Th:1"
"033514","Joe","Bloggs","Year 10","10A/Gw1","S21","2F:5"
"033514","Joe","Bloggs","Year 10","10A/Gw1","S27","1M:1"
"033514","Joe","Bloggs","Year 10","10A/Gw1","S27","1F:5"
"033514","Joe","Bloggs","Year 10","10A/Gw1","S27","2M:1"
"033514","Joe","Bloggs","Year 10","10B/Fr1","N13","2W:2"
"033514","Joe","Bloggs","Year 10","10B/Fr1","N13","1Th:1"
"033514","Joe","Bloggs","Year 10","10B/Fr1","N14","1M:2"
"033514","Joe","Bloggs","Year 10","10B/Fr1","N14","1W:2"
"033514","Joe","Bloggs","Year 10","10B/Fr1","N14","2M:2"
"033514","Joe","Bloggs","Year 10","10C/Bs1","1MS","1T:1"
"033514","Joe","Bloggs","Year 10","10C/Bs1","1MS","1T:2"
"033514","Joe","Bloggs","Year 10","10C/Bs1","1MS","2T:1"
"033514","Joe","Bloggs","Year 10","10C/Bs1","1MS","2T:2"
"033514","Joe","Bloggs","Year 10","10D/Dr1","DRA","1W:1"
"033514","Joe","Bloggs","Year 10","10D/Dr1","DRA","1Th:2"
"033514","Joe","Bloggs","Year 10","10D/Dr1","DRA","2W:1"
"033514","Joe","Bloggs","Year 10","10D/Dr1","DRA","2Th:2"
"033514","Joe","Bloggs","Year 10","10Y1/En","N13","2M:4"
"033514","Joe","Bloggs","Year 10","10Y1/En","S11","1W:4"
"033514","Joe","Bloggs","Year 10","10Y1/En","S11","1W:5"
"033514","Joe","Bloggs","Year 10","10Y1/En","S11","1Th:3"
"033514","Joe","Bloggs","Year 10","10Y1/En","S11","2W:4"
"033514","Joe","Bloggs","Year 10","10Y1/En","S11","2W:5"
"033514","Joe","Bloggs","Year 10","10Y1/En","S11","2Th:3"
"033514","Joe","Bloggs","Year 10","10Y1/En","S12","1M:5"
"033514","Joe","Bloggs","Year 10","10Y1/Ma","N22","1M:4"
"033514","Joe","Bloggs","Year 10","10Y1/Ma","N22","1T:5"
"033514","Joe","Bloggs","Year 10","10Y1/Ma","N22","1F:1"
"033514","Joe","Bloggs","Year 10","10Y1/Ma","N22","1F:2"
"033514","Joe","Bloggs","Year 10","10Y1/Ma","N22","2T:5"
"033514","Joe","Bloggs","Year 10","10Y1/Ma","N22","2F:1"
"033514","Joe","Bloggs","Year 10","10Y1/Ma","N22","2F:2"
"033514","Joe","Bloggs","Year 10","10YMIX2/Pe","NC","1F:3"
"033514","Joe","Bloggs","Year 10","10YMIX2/Pe","NC","2F:3"
"033514","Joe","Bloggs","Year 10","10YMIX2/Pe","NC","1W:3"
"033514","Joe","Bloggs","Year 10","10YMIX2/Pe","NC","2W:3"
"033514","Joe","Bloggs","Year 10","10YT1/Gw","N28","1Th:4"
"033514","Joe","Bloggs","Year 10","10YT1/Gw","S21","1T:3"
"033514","Joe","Bloggs","Year 10","10YT1/Gw","S21","1F:4"
"033514","Joe","Bloggs","Year 10","10YT1/Gw","S21","2M:3"
"033514","Joe","Bloggs","Year 10","10YT1/Gw","S21","2T:3"
"033514","Joe","Bloggs","Year 10","10YT1/Gw","S21","2F:4"
"033514","Joe","Bloggs","Year 10","10YT1/Gw","S27","1M:3"
"033514","Joe","Bloggs","Year 10","10YT1/Gw","S27","1Th:5"
"033514","Joe","Bloggs","Year 10","10YT1/Gw","S28","2Th:4"
"033514","Joe","Bloggs","Year 10","10YT1/Gw","S28","2Th:5"
"033514","Joe","Bloggs","Year 10","10YT1/Pc","N26","1T:4"
"033514","Joe","Bloggs","Year 10","10YT1/Pc","N26","2T:4"
"033514","Joe","Bloggs","Year 10","CLS D1", , 
"033514","Joe","Bloggs","Year 10","HCL Dovedale", , 
"034143","Tim","Another","Year  8","8B/Da","DAN","1W:4"
"034143","Tim","Another","Year  8","8B/Da","DAN","2W:4"
"034143","Tim","Another","Year  8","8B/Dr","DRA","1F:5"
"034143","Tim","Another","Year  8","8B/Dr","DRA","2F:5"
"034143","Tim","Another","Year  8","8B/En","S13","1M:1"
"034143","Tim","Another","Year  8","8B/En","S13","1Th:4"
"034143","Tim","Another","Year  8","8B/En","S13","1F:1"
"034143","Tim","Another","Year  8","8B/En","S13","2M:1"
"034143","Tim","Another","Year  8","8B/En","S13","2W:2"
"034143","Tim","Another","Year  8","8B/En","S13","2F:1"
"034143","Tim","Another","Year  8","8B/Gg","N14","1F:2"
"034143","Tim","Another","Year  8","8B/Gg","N14","2F:2"
"034143","Tim","Another","Year  8","8B/Hi","N01","1Th:5"
"034143","Tim","Another","Year  8","8B/Hi","N01","2Th:5"
"034143","Tim","Another","Year  8","8B/Pc","N12","1W:3"
"034143","Tim","Another","Year  8","8B/Pc","N12","2Th:4"
"034143","Tim","Another","Year  8","8B/Re","N01","1F:3"
"034143","Tim","Another","Year  8","8B/Re","N01","2F:3"
"034143","Tim","Another","Year  8","8B/Sc","S23","1M:4"
"034143","Tim","Another","Year  8","8B/Sc","S23","1Th:2"
"034143","Tim","Another","Year  8","8B/Sc","S23","2M:4"
"034143","Tim","Another","Year  8","8B/Sc","S23","2T:3"
"034143","Tim","Another","Year  8","8B/Sc","S24","2Th:2"
"034143","Tim","Another","Year  8","8B/Sc","S26","1W:2"
"034143","Tim","Another","Year  8","8X3/Ar","1A2","1T:5"
"034143","Tim","Another","Year  8","8X3/Ar","1A2","2W:3"
"034143","Tim","Another","Year  8","8X3/It","2LM","1W:1"
"034143","Tim","Another","Year  8","8X3/Mu","1MS","1M:5"
"034143","Tim","Another","Year  8","8X3/Mu","1MS","2M:2"
"034143","Tim","Another","Year  8","8X3/Tc","S01","1M:2"
"034143","Tim","Another","Year  8","8X3/Tc","S02","1T:3"
"034143","Tim","Another","Year  8","8X3/Tc","S02","2W:1"
"034143","Tim","Another","Year  8","8X3/Tc","S02","2T:5"
"034143","Tim","Another","Year  8","8X1/Fr","N11","1T:4"
"034143","Tim","Another","Year  8","8X1/Fr","N11","2T:4"
"034143","Tim","Another","Year  8","8X1/Fr","N14","1M:3"
"034143","Tim","Another","Year  8","8X1/Fr","N14","2M:3"
"034143","Tim","Another","Year  8","8X1/Fr","N14","1F:4"
"034143","Tim","Another","Year  8","8X1/Fr","N14","2F:4"
"034143","Tim","Another","Year  8","8X3/Ma","N24","1T:1"
"034143","Tim","Another","Year  8","8X3/Ma","N24","1W:5"
"034143","Tim","Another","Year  8","8X3/Ma","N24","1Th:1"
"034143","Tim","Another","Year  8","8X3/Ma","N24","2T:1"
"034143","Tim","Another","Year  8","8X3/Ma","N24","2W:5"
"034143","Tim","Another","Year  8","8X3/Ma","N24","2Th:1"
"034143","Tim","Another","Year  8","8XG1/Pe","GYM","1T:2"
"034143","Tim","Another","Year  8","8XG1/Pe","GYM","1Th:3"
"034143","Tim","Another","Year  8","8XG1/Pe","GYM","2T:2"
"034143","Tim","Another","Year  8","8XG1/Pe","GYM","2Th:3"
"034143","Tim","Another","Year  8","CLS W8", , 
"034143","Tim","Another","Year  8","HCL Wyedale", , 

 

I am wanting to do the following in python (if anyone can help as I'm not good with programming)

 

1) Use python to remove the rows that have no room and period - such as the 2 below.

 

"033514","Joe","Bloggs","Year 10","CLS D1", , 
"033514","Joe","Bloggs","Year 10","HCL Dovedale", , 

 

2) Collate all the data for a student with the same admission number onto one line.

 

It would need to be able to read each line with a matching Admission number - and write out one line that had the students information contained.

 

It would also be good if I could get it to read the period in order (1M:1, 1:M2 etc) and place the room for that period in the correct order.

 

All of this would then make it simple to import the one line into mysql.

 

Any help would be very appreciated.

[OB1]

Disclaimer: I'm not a php coder.

Personally, instead of using python and adding another tool into the chain, I'd do this with fgetcsv().

After that you can determine which rows don't have a room and discard them by checking the appropriate element of the array.

 

If you really want to do it in python, you'll want to use the csv module.

 

Re: sorting your days correctly, I think you'll have to change the representation somehow.

[howartp]

I do this sort of thing with a macro in Excel - every half term we export the IT classes from SIMS for import into MRBS.

 

For example, the macro does a series of Replace commands to replace AMon:1, AMon:2, BTue:4 etc with a sortable key.

 

Peter

[SovietRussia]

The way I would do it would be (and currently do)

 

Get the report to generate it as XML then use XML Parser in PHP to pass the data to MySQL.