Limesurvey: How to import responses from a modified deactivated table

limesurvey_table_code A good number of my consulting clients use the very useful and powerful survey tool Limesurvey. Unfortunately, since version 1.92+, it seems impossible to reimport deactivated responses tables into new response tables if the survey was modified. I’m sure this doesn’t matter for long form surveys and mainly static surveys, but some of my clients use this platform as a dynamic form engine. In that case, forms can and will change over the duration of a project.

To resolve this problem and enable the importation of old responses tables, I’ve written a quick Python script. It uses MySQLdb, but that library should be installed by default on most Linux boxes. The script also requires a MySQL database backend but it should be easily adaptable to other database engines.

Before running the script, four variables must be configured:

dbaddr='127.0.0.1' #localhost
dbuser='username' #user name used to access the limesurvey tables
dbpass='password' #password used to access the limesurvey tables
dbdb='limesurveyDB' #limesurvey's database name

After configuring these settings, you should be able to launch the script. Launching the script with the –help switch will print out the usage help:

python importOldAnswers.py --help
usage: importOldAnswers.py [-h] [--list SID] [--copy TABLENAME SID]

Import old results in current answer table

optional arguments:
  -h, --help            show this help message and exit
  --list SID            list old result table for a given SID
  --copy TABLENAME SID  copy old table to a new result table

Using the script with the –list SID argument will list all the old result tables for a given survey id (SID):

python importOldAnswers.py --list 15946
lime_old_survey_15946_20120124220544
lime_old_survey_15946_20120130222036
lime_old_survey_15946_20120304201731
lime_old_survey_15946_20120703192749
lime_old_survey_15946_20130304201731

From there, you can choose an old result source table to import into an existing response table:

python importOldAnswers.py --copy lime_old_survey_15946_20130304201731 15946
Copying table lime_old_survey_15946_20130304201731 to lime_survey_15946
Retrieve headers
Number of columns in source table: 228
Number of columns in target table: 229
Number of columns in merged table: 228
Number of answers in source table: 2
Done copying

Now for the caveats: the script will not convert from a data type to another, it will not truncate data and it will not do any form of sanity check before inserting data. Never use on a production database, always work with a copy. If you find a bug, please let me know. I’ve released the code with a GPL license, but if that causes a problem let me know. If you know me in real life and you find that script useful, you can always buy me a beer! :-)

You can download the code here, or copy and paste the following listing:

__author__ = 'Olivier H. Beauchesne'
__license__ = 'GPL'
__version__ = '0.1'
__email__ = 'olivier@olihb.com'

import sys
import MySQLdb as mysql
import argparse

#database settings
dbaddr='127.0.0.1' #localhost
dbuser='username' #user name used to access the limesurvey tables
dbpass='password' #password used to access the limesurvey tables
dbdb='limesurveyDB' #limesurvey's database name

#this should not change if using limesurvey's defaults
TablePrefix='lime_survey_'
OldTablePrefix="lime_old_survey_"

def getHeaders(table):
    cursor = db.cursor()
    sql = "SELECT * FROM %s" % table
    cursor.execute(sql)
    desc = cursor.description
    header = []
    for i in desc:
        header.append(i[0])
    return header

def buildResultMap(table, headers):
    cursor = db.cursor()
    sql = "SELECT * FROM %s" % table
    cursor.execute(sql)
    data = cursor.fetchall()
    m = dict()
    for h in headers:
        m[h]=[]
    for d in data:
        for i in range(0,len(d)):
            header = headers[i]
            m[header].append(d[i])
    return m

def getOldTables(sid):
    cursor = db.cursor()
    sql = "SHOW TABLES"
    cursor.execute(sql)
    data = cursor.fetchall()
    tableList = map(lambda x:x[0],data)
    return filter(lambda x: x.count(str(sid)) and x!=TablePrefix+str(sid),tableList)

def mergeHeaders(table1,table2):
    return filter(lambda x: x in table2,table1)

def insertNewData(header, results,tableName):
    numItems = len(results['id'])
    print "Number of answers in source table: "+str(numItems)
    header.remove('id')
    cursor = db.cursor()
    columns = ",".join(header)
    for i in range(0,numItems):
        columnsValues =  map(lambda x: results[x][i],header)
        format_strings = ','.join(['%s'] * len(header))
        cursor.execute("INSERT INTO "+tableName+" ("+columns+") VALUES (%s)" % format_strings, tuple(columnsValues))

def main():
    global db

    #argument parsing
    parser = argparse.ArgumentParser(description='Import old results in current answer table')
    parser.add_argument('--list',type=int, help="list old result table for a given SID",nargs=1,metavar='SID')
    parser.add_argument('--copy',type=str, help="copy old table to a new result table", nargs=2, metavar=('TABLENAME','SID'))

    #exit if empty
    args=parser.parse_args()
    if len(sys.argv)==1:
        parser.print_help()
        sys.exit(1)

    #connect to db
    db=mysql.connect(host=dbaddr,user=dbuser,passwd=dbpass,db=dbdb)

    #show list
    if args.list:
        tables = getOldTables(args.list[0])
        for t in tables:
            print t

    #copy table
    if args.copy:

        #format table name
        sourceTable = args.copy[0]
        targetTable = TablePrefix+str(args.copy[1])
        print "Copying table "+sourceTable+" to "+targetTable

        #get headers
        print "Retrieve headers"
        headersSource = getHeaders(sourceTable)
        headersTarget = getHeaders(targetTable)
        mergedHeaders = mergeHeaders(headersSource,headersTarget)
        print "Number of columns in source table: "+str(len(headersSource))
        print "Number of columns in target table: "+str(len(headersTarget))
        print "Number of columns in merged table: "+str(len(mergedHeaders))

        #merge results
        resultSource = buildResultMap(sourceTable, headersSource)
        insertNewData(mergedHeaders,resultSource,targetTable)
        print "Done copying"

#entry point
if __name__ == '__main__':
    main()

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Post Navigation