Marksheet to Database Import

[CAM]

OK, sitting down and working out how to import marksheet results from SIMS into a database. After splitting it into separate tables I realised I will need to have it in the following format:

Name | Grade | Subject | Season

 

The problem I have is SIMS exports marksheet data as follows:

Name | English Autumn | English Spring | Maths Summer| Maths Spring

 

I'd need to design the database to have subjects as table headings which screams bad practice at me!

 

Any ideas or is this the best solution?

[theriver]

If this was me, I'd:

 

- create a temp table with the name, grade, subject, season as text fields

 

- have a procedure that goes through the SIMS export line by line:

 

For each row:

 

insert into mytemptable (name, grade, subject, season)

select name, english autumn, 'english', 'autumn'

 

insert into mytemptable (name, grade, subject, season)

select name, english spring, 'english', 'spring'

 

insert into mytemptable (name, grade, subject, season)

select name, maths summer, 'maths', 'summer'

 

insert into mytemptable (name, grade, subject, season)

select name, maths spring, 'maths', 'spring'

 

 

 

which would give something that looks a bit like

 

 

name   grade   subject   season
CAM    A       english   autumn
CAM    A       english   spring
CAM    A       maths     summer
CAM    A       maths     spring

 

 

Once it's in the temp table I'd do whatever other transformations are required and then insert the new rows into the real table.

Edited April 22, 2015 by theriver
formatting

[LosOjos]

I do this, but the marksheets don't come in to it on my side, they're just for data entry.

 

I then set up reports to export assessment results to a CSV (I created a category "DB_EXPORT" and put all the aspects I wanted exporting in to it - actually there are multiple categories now but you get the idea )

 

The CSV is then imported in to my SQL DB using a couple of scripts; all this done as a scheduled task. Unfortunately, I found SIMS to be inconsistent in the way it exports CSVs, enclosing some fields in quotes and not others, which SQL didn't like, so I wrote a wrapper script that cleans them up.

 

Just make sure that the column order of your SIMS report matches that of your table and it'll save you a lot of extra manipulation - I use the Aspect name to identify the subject (I have a mapping table for user friendly names when displaying the results elsewhere, but you could just as easily run this import to a temp table then do the aspect->subject mapping while transferring the data in to your live table).

 

Oh and test it on a small subset of your data to begin with - running the SIMS report on a couple of hundred aspects and about half a dozen result sets takes a good half hour here!

 

An example script set (and the BAT that runs as a scheduled task) are below:

 

REM update_results.bat - this is setup as a scheduled task to pull results from SIMS in to my SQL DB each night

commandreporter /user:username /password:password /report:ZZZ_EXPORT_RESULTS /output:results.csv
cscript CleanCSV.vbs "%CD%\results.csv"
sqlcmd -E -S SERVER\SQLEXPRESS -I -i "UpdateResults.sql"
echo Y | DEL results.csv

 

'==========================================================================
'
' NAME: CleanCSV.vbs
'
' COMMENT: Uses Excel to strip unwated quotes from CSV data
' HOW TO USE: CLI - cscript cleancsv Path/To/File
'==========================================================================

'Check arguments
If WScript.Arguments.Length <> 1 Then
WScript.Echo "Usage: CleanCSV Path/To/CSV"
WScript.Quit
End If 

Set myExcel = CreateObject("Excel.Application")
myExcel.Visible = False
myExcel.Application.DisplayAlerts = False

Set myWorkbook = myExcel.Workbooks.Add()
Set mySheet = myWorkbook.Sheets.Add()

Dim arr(99)
For x = 0 to 99
arr(x) = 2
Next

Set QT = mySheet.QueryTables.Add("TEXT;" + WSCript.Arguments(0), mySheet.Range("$A$1"))
With QT
.TextFileCommaDelimiter = True
.TextFileColumnDataTypes = arr
.Refresh
End With

myWorkbook.SaveAs WScript.Arguments(0), 6
myWorkbook.Close
myExcel.Application.Quit

 

/*
UpdateResults.sql

Takes processed CSV from SIMS and
imports it in to SQL DB
*/

USE DatabaseName;

DELETE FROM Results;

BULK INSERT Results
FROM 'results.csv'
WITH (
FIELDTERMINATOR=',',
ROWTERMINATOR='\n',
FIRSTROW=2
);

Edited April 22, 2015 by LosOjos

[CAM]

This could be more complicated then anticipated...

@LosOjos Is it a query similar to SELECT Grade, Subject FROM simsdb WHERE AspectCategory = "db_export";

[LosOjos]

This could be more complicated then anticipated...

@LosOjos Is it a query similar to SELECT Grade, Subject FROM simsdb WHERE AspectCategory = "db_export";

 

I don't run any queires against the SIMS DB - that would get me a wrist slapping!

 

The scripts above are a set, but you can see what's going on easiest from the first BAT script - I create a report as normal in SIMS.NET, then use CommandReporter to run this report and save it as a CSV. I then do some tidying up of the CSV with the second script (CleanCSV.vbs) because SIMS is incosistent with it's quotes aroundfields, and finally I run a SQL query against my own SQL server instance to import the data in to it where I'm free to do what I like without fear of borking SIMS.

 

The reason I mentioned the aspect category is I found it a really easy way to mark the aspects I want to export to my own DB; the SIMS report can then just be filtered to only include aspects in that category

[CAM]

Ohhh I know not to do that. It was pseudo-pseudocode.

 

I just tried this with one year group and it worked! Trouble is I can't get the subject to appear unless I completely dismantle my categories as I have multiple subjects rolled into one data collection (which I am thinking of scrapping next year anyway). I think the way Assessment Manager has been used over the past years is a bit too messy for export.

[CAM]

I fixed the subject problem by using Assessment Manager Categories. It's a hack, but it works.

 

I also got side-tracked by XML after discovering I can use reporting to output a decent XML file. I think. I am following a tutorial and it ran the following commands.

[xml]$rep = Get-Content IMExport.xml
$rep.SuperStarReport.Record

 

This loads the contents of the exported XML file into memory and displays everything in the file on the screen (which I have to CTRL-C because it's so big). But if I run this:

 

$rep.SuperStarReport.Record.Surname

 

It should display just the surname field. But it displays nothing at all making me wonder if something has gone wrong. The file exported from SIMS looks like the following (sensitive data changed):

 

60795
ADAMA
WILLIAM
Year 12
T
WBRI
12GAL
M
Year 12 autumn
English

 

Forgive the awful identing, I can't work out how to do it on the forum as tab won't work.

 

Tutorial: http://blogs.technet.com/b/heyscriptingguy/archive/2012/09/13/use-powershell-to-simplify-access-to-xml-data.aspx

Edited April 28, 2015 by CAM

[LosOjos]

Just tested your PowerShell code against a SIMS report with the same fields here and it worked fine - double check you have spelt each XML element exactly right (though it looks fine in your example above) and that PowerShell actually has access to the file you're loading.

 

XML can be handy, but it can also be a real pain to work with. I find the best editor to be one by a company I'd never heard of called firstobject, it's the only editor I found that doesn't crash when trying to load large XML files: Free XML editor download

 

Also, you probably won't need this as I'm sure you can achieve the same results more quickly in Powershell, but back before I had used Powershell, I regularly ran large reports from SIMS overnight then used XPath queries to extract the parts I needed for various systems the next day, and I wrote a little CLI utility to help: XPathX - Geek Josh

 

EDIT: I just noticed the geek reference, well played sir, well played

Edited April 29, 2015 by LosOjos