1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Development Arg! How to Survey 3,000 People?

Discussion in 'Software' started by Shadow_101, 22 Nov 2007.

  1. Shadow_101

    Shadow_101 Mudkips.

    Joined:
    12 Feb 2004
    Posts:
    1,116
    Likes Received:
    4
    Arg! never let on to people in work that your a dab hand at IT!

    I’ve been asked to come up with a solution for getting a survey out across 3,000 people. my idea was some sort of web page / ASP connected to mySQL database or something similar to record the results.

    i wouldn’t know where to start however implementing this?
    (I’ve done databases connected though .NET applications but not web interfaces)

    Any better, ideally simpler solutions? (the chap that will need to use the results isnt tech savvy)

    Thanks for any help guys.
     
  2. Hugo

    Hugo Ex-TrustedReviews Staff

    Joined:
    25 Dec 2006
    Posts:
    1,384
    Likes Received:
    19
  3. steveo_mcg

    steveo_mcg What's a Dremel?

    Joined:
    26 May 2005
    Posts:
    5,841
    Likes Received:
    80
    I think your first solution is on the money, but i'd probably try to use php over ASP buts that just personal preference. Once you have the results build an interrogation tool for the non tech guy to get the results from the database, i'd probably put an access front end on your mysql server to prevent scaring the guy but still give you plenty of options if the guy needs more complicated queries.
     
  4. mk-donald

    mk-donald bits and bobs

    Joined:
    2 Aug 2007
    Posts:
    109
    Likes Received:
    1
    Some past personal experience for you...

    I had a similar project back when I worked in the city and had 7000 out of 9500 staff doing a survey on our intranet (that was designed in a couple of hours using ColdFusion onto an Access database!)

    Fundamental to place yourself in a 'business overview' and remember that every 10 seconds you save in the time it takes each person to do the survey is, over 3000 staff, ONE MAN DAY of work saved/not-wasted (ie 10s x 3,000 = 30,000s = 500m = 8.3h).

    I suggest two things:
    a. If you don't have the inhouse capability, then providing the data isn't confidential, just use one of the web-based services - shouldnt cost more than £150 for 3000 surveys ie 5p/person compared to what cost of their time ?
    b. SPEND as much of the budget as possible on making the questions/answers as TUNED as possible

    By TUNED I mean from both consumer and inputter ends:
    - purposeful in that whoever is going to use the information from them is getting USEFUL data
    - easy to complete, without ambiguity, coping will all possible answers and fundamentally ATTRACTIVE to do

    The real issues we had were:
    1. How to validate WHO people are - in our context we asked for their staff number (but didnt validate it in any way) and their email address - and sent that email address a copy of the data completed.
    2. How, if at all, to cope with PARTIAL completion of the survey (we chose NOT to allow partial completion, but just to say that until they pressed final submit they had to start again)
    3. Remember to cope with people who cant/wont do online and have a printed/PDF version of the questions/survey ready for some, and with someone geared up to key the info in.

    Hope thats helpful
     
  5. Shadow_101

    Shadow_101 Mudkips.

    Joined:
    12 Feb 2004
    Posts:
    1,116
    Likes Received:
    4
    Thanks for your input guys, went for a more simple solution in the end (the budget for this is zero) used a modified vb script, courtesy of a chap on hexus. incase anyone else needs something similar, the script bellow will pull any information you require from all worksheets contained the same folder as the script. the output file results.txt is csv

    Code:
    'batch process all .xls files in the same directory where
    'this script is located, "results.txt" and "errors.txt"
    option explicit
    
    call main()
    
    private sub main()
    	dim filePath
    	dim fileNames
    	dim resultsFile, errorsFile
    	
    	call checkengine()
    	
    	filepath = trim(replace(wscript.scriptFullName, wscript.scriptName, ""))
    	if filepath = "" then
    		call wscript.echo("ERROR: Failed to determine path")
    		exit sub
    	end if
    	
    	if not getFiles(fileNames, filePath, resultsFile, errorsFile) then exit sub
    	call wscript.echo("Found " & ubound(fileNames) + 1 & " file(s) to process")
    	
    	call processBooks(filePath, fileNames, resultsFile, errorsFile)
    	
    	call resultsFile.close()
    	call errorsFile.close()
    	
    	set resultsFile = nothing
    	set errorsFile  = nothing
    	
    	call wscript.echo("")
    	call wscript.echo("Press RETURN to continue...")
    	call WScript.StdIn.Read(1)
    end sub
    
    private sub checkengine()
    	dim pcengine, wshshell
    	
    	pcengine = LCase(Mid(WScript.FullName, InstrRev(WScript.FullName,"\")+1))
    	If Not pcengine="cscript.exe" Then
    	Set WshShell = CreateObject("WScript.Shell")
    	WshShell.Run "CSCRIPT.EXE """ & WScript.ScriptFullName & """"
    	WScript.Quit
    	End If
    end sub
    
    private function getFiles(byref fileNames, byval filePath, byref resultsFile, byref errorsFile)
    	dim fso, folder, files, file
    	
    	getFiles = false
    	
    	set fso    = wscript.createObject("scripting.fileSystemObject")
    	set folder = fso.getFolder(filePath)
    	set files  = folder.files
    	
    	for each file in files
    		if right(file.name, 4) = ".xls" then
    			if isEmpty(fileNames) then
    				redim fileNames(0)
    				fileNames(0) = file.name
    			else
    				redim preserve fileNames(ubound(fileNames) + 1)
    				fileNames(ubound(fileNames)) = file.name
    			end if
    		end if
    	next
    	
    	set resultsFile = fso.openTextFile(filePath & "results.txt", 2, true)
    	set errorsFile  = fso.openTextFile(filePath & "errors.txt", 2, true)
    	
    	getFiles = true
    	
    	set files  = nothing
    	set folder = nothing
    	set fso    = nothing
    end function
    
    private sub processBooks(byref filePath, byref fileNames, byref resultsFile, byref errorsFile)
    	dim excel, excelsheet
    	dim i, j
    	dim str
    	
    	set excel = wscript.createObject("Excel.Application")
    	excel.DisplayAlerts = 0 ' don't display any messages about documents needing to be converted from  old Excel file formats
    	
    	str = "Original File Name,Sheet Number,Cell A1,"
    	str = str & "Cell A2,"
    	str = str & "Cell A3,"
    	str = str & "Cell A4,"
    	str = str & "Cell A5,"
    	str = str & "Cell A6,"
    	str = str & "Cell A7,"
    	str = str & "Cell A8,"
    	str = str & "Cell A9,"
    	str = str & "Cell A10,"
    
    	call resultsFile.writeLine(str)
    	
    	str = "Workbook,Worksheet,Error"
    	call errorsFile.writeLine(str)
    	
    	for i = 0 to ubound(fileNames)
    		call wscript.echo("Processing workbook: " & fileNames(i))
    		call excel.workbooks.open(filePath & fileNames(i), false, true) ' open the excel document as read-only open (path, confirmconversions, readonly)
    		
    		for j = 1 to  excel.worksheets.count
    			call wscript.echo("Processing worksheet: " & j & " of " & excel.worksheets.count)
    			set excelsheet = excel.activeWorkbook.worksheets(j)
    			
    			str = fileNames(i) & "," & excelsheet.name & ","
    			
    			
    				str = fileNames(i) & "," & excelsheet.name & ","
    				str = str & excelsheet.range("A1").value & ","
    				str = str & excelsheet.range("A2").value & ","
    				str = str & excelsheet.range("A3").value & ","
    				str = str & excelsheet.range("A4").value & ","
    				str = str & excelsheet.range("A5").value & ","
    				str = str & excelsheet.range("A6").value & ","
    				str = str & excelsheet.range("A7").value & ","
    				str = str & excelsheet.range("A8").value & ","
    				str = str & excelsheet.range("A9").value & ","
    				str = str & excelsheet.range("A10").value & ","
    				
    				call resultsFile.writeLine(str)
    		
    			
    			set excelsheet = nothing
    		next
    		
    		call excel.workbooks(1).close
    	next
    	
    	
    	call excel.quit()
    	set excel = nothing
    end sub 
          
    
    
     

Share This Page