paper snap excel sir access / spss viva las vegas or getting data from a to b john s. lemon

Paper SNAP Excel SiR Access / SPSS

Viva Las Vegasor

Getting data fromA to B

John 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

Getting data from a corrupt Access data base

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

paper snap excel sir access / spss viva las vegas or getting data from a to b john s. lemon

Documents

excel snap

excel files sir

use snap

snap scanning

outcome snap

snap forum

snap event

outcome sir