Viva Las Vegas or Getting data from A to B John S. Lemon

Viva Las VegasorGetting data fromA to BJohn S. Lemon

Setting the scene • Support number of items of software – major ones are: • SiR • SPSS - statistical package • SNAP - questionnaire / survey tool • Contribute to the ‘support forums’ for both SPSS and SNAP • Started with a message on SNAP forum

Setting the scene • Request for help on scanning paper forms • SNAP reference site on scanning as have used SNAP scanning from day 1 • Replied with ‘standard’ answer of “Do’s” and “Don’ts” • No idea where the other member was from

Setting the scene • Response to my E-mail inbox • “Can you provide a number for me to ring you at 1000 PDT ?” • First clue as to where user was !!! • Answered a number of technical questions • Then came the crunch one • “Can you come and run the operation ?”

Setting the scene • Next evening another call after Email saying “Yes – I can go” • Given details of flights and hotel – Las Vegas in December. • Also further details of the problem which had been glossed over !! • Including • Timescale • Potential numbers • They kept quiet until I agreed !!

Size of problem • 13,000 delegates • 12 themes / streams • 3 sub themes / streams • 36 different forms • Seven parallel sessions over three days • Optional response forms for each session attendee • Potential for 30,000 forms

Size of problem • Needed to track • Session • Stream • Sub-stream • Also link form to delegate • Timing • Last session closed 1830 • Results by 1200 next day

Size of problem • Form mainly tick boxes • OK • But – • Free text • Id number

Size of problem • SNAP designs forms and scans them extremely efficiently but • Only one licence so no simultaneous use • Temporary staff couldn’t use SNAP • No way to link via Id number in SNAP • Event was less than a month away • Needed lateral thinking

Lateral thoughts (?) • Temp staff know Excel • Snap creates Excel files • SiR can read Excel files • SiR is a database with PQL so • Can link Id number on form to extra stored data • Report generation is easy • Different data formats

Preparations • Create 36 forms • Letter vs. A4 paper size • Change the scanner as specifications of original too slow • Generate test forms • Create SiR schema • Start coding

More problems • Data files on attendees changed format almost daily • Requests for further information from final customer ( NAPA ) were added every other day • Problems when original printers had a fire • Work area in hotel too small

Almost there • Arrived in Las Vegas to find • Even more requests for reports & output • Data collection starting earlier so little time to recover from 18 hrs travel and arrival in Las Vegas at 0400 ‘Aberdeen time’ • However it meant • No time for slot machines • Chance to test out procedures before large scale data collection • Time for training temp staff

The outcome • SNAP scanned the forms with no problems • Transfer to Excel was fine • Except static blew two memory sticks • Temp staff corrected mis-scanned text and numbers

The outcome - SiR • Read corrected Excel files perfectly • Generated reports and output files • Coped with twice-daily changes in requirements including • Summary statistics from the different streams and sub-streams • Pattern of requests • Extra information from external sources

SiR XS – good points • Despite being time critical used SIR/XS • Like the ability to use longer names • Quickly realised that too many characters is almost as restrictive as just eight. • Interface is better • “Look and feel” of new dialog boxes is better • Function keys to run / edit are great

SiR XS – not so good points • Would love a “Find” and “Replace” facility in the editor • Better ‘typing’ of SPSS variables • Why • SPSS 14 procedures have restrictions based on type ( Series, Ordinal, Nominal ) • 15 is much more restrictive

Conclusions • Continuing theme of previous papers at SiR conferences • Use vPQL to manipulate different sources of data • Convert • Process • Re-format • Merge • Output

The power of a badge !! • Get to front of queues • Best seats for large scale ‘motivation’ sessions. • Apologies to people from States but … • Star spangled banner • Pledge of allegiance

Viva (?) Las Vegas • The ‘Eifle’ tower • Adverts on the TOP of trains

SoS(r)Save our Student (records)Getting data from corrupt Access data base John S. Lemon

The Prologue • Summer school for Access • Pre-cursor for direct entry to University • Collects own information • Created Access data base • Added ‘bells & whistles’ • Became ‘corrupt’ • Couldn’t find good backup • Couldn’t ‘rescue’ with Access

The Prologue • Couldn’t dump data into Excel • Couldn’t do much of anything • Try SiR and ODBC !!!

The problem • All looks OK

Phew ! • Still looks OK

Oh no !!! • What happened ?

Try and get data out • Simplest thing - EXPORT

Try and get data out • Simplest thing – EXPORT to Excel

The cycle begins ! • First message – what does it mean ?

The cycle continues • Another error

Another message • Have you ever got a reply ?

The cycle begins again ! • No clearer second time around

The cycle continues • Can get fed up with seeing this

Give up • Uncheck this – and you can ‘escape’

No progress though • Multiple copies !! • Each iteration gives another copy • All broken !!!

A Knight ( SiR ) to the rescue • Set up ODBC connection first of all – may be better ways – but - it works

The Import steps • In SiR select ‘Import Records’

The Import steps • Choose the data source

The Import steps • Now choose the table to Import

The Import steps • Chance to correct default data typing

The Import steps • Can save the code for repeated runs / editing

Success ? • The results from running the code

A check for re-assurance • Use Spreadsheet

A check for re-assurance • Looks OK – bad records at top & bottom

The Epilogue • It works • Needs some experimenting to get right • Again SiR saved the day

Viva Las Vegas or Getting data from A to B John S. Lemon