SIMS Bulk Import

[matt40k]

What type is the UDF field in SIMS? Is the serial number just numbers or does it have letters? Is it separated such as 123-12312 ?

[jred]

It's just letters and numbers. like this for example: SDMRJG4F7F185. It is a single line text UDF with 100 character limit. I tried a few different things, can't see a way to import in to any other type of UDF as it doesn't pick up our TRUE/FALSE ones in the list to choose from.

[matt40k]

Sorry for the delay, I've replicated it. I've added it to my to-do list which seems to be growing.

 

Also re: UDF - it's filtered to only single line text, it'll ignore true\false - currently.

[TheScarfedOne]

Had this app on my todo list for ages, about time I got round to using it! Cheers Matt

[matt40k]

@jred - I found a workaround for your issue - SIMS Bulk Import - View Issue #1531: Unable to import UDFs. Just be aware that I've found another issue - where it's making it lower case - SIMS Bulk Import - View Issue #1549: UDF - Case change.

 

Both will be fixed in the next release.

[Ephelyon]

Hi Matt, is there any way this could be expanded to use the UPN as well as / instead of the Admission Number please? We currently have our pupil UPNs in AD anyway so our Parental Portal knows who they are, and now I need to bulk-import their Exchange e-mail address as defined in AD into SIMS. It would be very handy to use the UPN as the common reference point as I can then dsget the UPN and e-mail address from AD and use it with SIMS Bulk Import to do the lot all at once.

[matt40k]

The issue is it uses the SIMS Business Objects and they don't currently allow you to find a students (PersonID) using only the UPN. If you go into SIMS and try searching for a student - you can't find them by UPN. The ideal solution is to log a CR with Capita and ask them to add it to SIMS - assuming it doesn't already exist. Maybe @PhilNeal can find out for us

 

The only workaround I can think of is to pull out all the students just to get a list of UPN numbers!!! Which is rather messy to say the least. There was a discussion somewhere as what reference number to use for sync'ing with SIMS - should it be the UPN, AdmissionNo, PersonID or whatever. I know I've had historically issues with UPN as the school generates a new UPN only for the LA to find out that, that student already has UPN from a previous school or they've entered the UPN incorrectly.

[limawhiskey]

I exported an list of admission numbers and UPNs and used a lookup to make the CSV for import. I agree, potentially messy solution, but it worked for me.

[Ephelyon]

Okay, thanks.

 

How do you mean "used a lookup"? In AD, SIMS or the Import Tool?

[vikpaw]

I'm guessing it was an Excel lookup.

as for using UPNs the Capita patch for importing student emails does use UPNs. it has two options, either provide a list of students by UPN and email address, or escape any specified students and apply a mask to all. worked really well for me.

it's a bit of a faff having to apply the patch. then apply another patch to remove the tools / logs, but it worked.

[Ephelyon]

Right, I see, cheers for that info. Do you know how I might go about looking for that?

 

I can get a list of UPNs/e-mails from AD with: dsquery user "OU=Pupils,OU=*** Users,DC=**********,DC=local" -limit 0 | dsget user -pager -email> "%UserProfile%\Desktop\Pupils.txt"

 

So that would work once I clean the text file up a little.

[vikpaw]

If you want the patches from Capita, you need to request them from your support unit. It's called the bulk email import utility. patches 16799,16800.

But, you might as well use Matt's tool.

Export a list of admission numbers and UPNs from SIMS into Excel. open your text document in Excel and make sure the columns are separated. Then stick in a lookup formula, you'll want a VLOOKUP, with the first column containing UPN and second column containing the admission number. Make sure the fourth parameter is FALSE. And use $A$3:$C$50 format to specify the table so when you copy down the reference doesn't change.

Edited March 6, 2013 by vikpaw

[matt40k]

How could you @vikpaw!! Those patches are foul!!

@Ephelyon - what limawhiskey is saying it to export the UPN AND the Admission no to AD, then you can use the admission no to import back into SIMS.

[Ephelyon]

Right, thanks for that!

[vikpaw]

How could you @vikpaw!! Those patches are foul!!

@Ephelyon - what limawhiskey is saying it to export the UPN AND the Admission no to AD, then you can use the admission no to import back into SIMS.

worked for me - foul or not. luckily, i just want them all to fit a strict mask, so no need to supply a list.

[halljkl]

Hi Matt,

 

When I run this it all matches up but then when the HTML file opens it says "Import Failed" with nothing in the notes.

 

Any idea what this is?

 

Thanks

Lewis

[matt40k]

Going to have a wild stab in the dark and ask, is it UDF you are importing?

[halljkl]

Thanks for the speedy reply! No its importing emails using the admission number.

Lewis

[matt40k]

Weird, can you ping over the log file - matt [at] matt40k.co.uk please

[halljkl]

Will do. Thank you!

[Ephelyon]

Now doing the same thing for staff in relation to AlfieCloud, got a CSV containing Surname, Forename, Email, but nothing comes up in any of the drop-down boxes to match the fields to. Using the stable version for now.

[chrisateen]

Thanks for that. Wished I saw this a few days ago though as it would have saved me some work

[matt40k]

@Ephelyon old version work ok?

[Ephelyon]

This is 1.7.21.0 that I'm using.

[Ephelyon]

Right, problem with the CSV file originally (not all fields filled in). Exported properly now, down to 40-odd people in the right format, can select the right fields to match, but then it gets about halfway through finding people and always crashes out. Tried both stable and preview versions about 20 times each.