DMS Scripting

JustCode : Delete cases from the input file of a dms script

by

This script shows you how to delete a record in your input file. The delete is done in the OnNextCase

' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 4th August 2009
' Delete cases from the input file of a dms script.
' ****************************************

InputDatasource(Input, "")
    ConnectionString = "Provider=mrOleDB.Provider.2; _
        Data Source=mrDataFileDsc; _
        Location=C:\temp\short_drinks.ddf; _
        MR Init MDM Access=1; _
        Initial Catalog=C:\temp\short_drinks.mdd"
    SelectQuery = "SELECT * FROM VDATA"
End InputDatasource

OutputDatasource(Output, "")
    UseInputAsOutput = "True"
End OutputDatasource

Logging(MyLog, "")
    Group = "DMA"
    Alias = "datamgmt"
    FileSize = 500
    Path = "c:\temp"
End Logging

Event(OnNextCase, "")

    If Respondent.Serial > 10 Then
       dmgrJob.DropCurrentCase()
       dmgrLog.LogError_2(Ctext(Respondent.Serial) + " - Case Deleted")
    End If

End Event

JustCode : Strip out html from metadata context

by

This code will show you how to strip out html or unwanted characters from a specified context in your mdd file Via a DMS script.

Event(OnBeforeJobStart, "")

    Dim oMDM
    ' Create the MDM object and open the Short Drinks .mdd file in read-write mode
    Set oMDM = CreateObject("MDM.Document")
        oMDM.Open("C:\survey.mdd", ,2)
        oMDM.Contexts.Current = "Question"

    Dim oVar,oElement
    'Using the StripHTML funciton
    
    For Each oVar in oMDM.Variables
        if oVar.IsSystem = False then
            oVar.Label = StripHTML ( oVar.Label )
            For each oElement in oVar.Elements.Elements
                oElement.Label = StripHTML ( oElement.Label )
            Next
        end if
    Next

    'Using the StripOut function
    For Each oVar in oMDM.Variables
        if oVar.IsSystem = False then
            oVar.Label = StripOut ( oVar.Label )
            For each oElement in oVar.Elements.Elements
                oElement.Label = StripOut ( oElement.Label )
            Next
        end if
    Next

    oMDM.Save("C:\survey.mdd")
    Set oMDM = null

    Function StripHTML ( sText )
        Dim objRegExp, strOutput
        Set objRegExp = CreateObject("VBScript.RegExp")
            objRegExp.IgnoreCase = True
            objRegExp.Global = True
            objRegExp.Pattern = "< (.|n)+?>"

            'Replace all HTML tag matches with the empty string
            strOutput = objRegExp.Replace(sText, "")

            'Replace all < and > with < and >
            strOutput = Replace(strOutput, "< ", "<")
            strOutput = Replace(strOutput, ">", ">")

            StripHTML = strOutput    'Return the value of strOutput

        Set objRegExp = null
    End Function

    Function StripOut ( sText )
    ' You can use this function to hard code specific things you want to clean out the text
    Dim sAnswer
        sAnswer = sText
        ' Just repeat this line with the items you want to remove
        sAnswer = Replace(sAnswer,"[WHAT_ARE_YOU_LOOKING FOR]","")
        StripOut = sAnswer

    End Function

End Event

InputDatasource(Input, "")
    ConnectionString = "Provider=mrOleDB.Provider.2;Data Source=mrDataFileDsc;Location="C:\survey.ddf;Initial Catalog="C:\survey.mdd"
    SelectQuery = "SELECT * FROM VDATA"
End InputDatasource

OutputDatasource(Output, "")
    ConnectionString = "Provider=mrOleDB.Provider.2;Data Source=mrSavDsc;Location="C:\survey_out.sav"
    MetaDataOutputName = "C:\survey_OUT.mdd"
End OutputDatasource

JustCode : Strip out new lines from text questions

by

This code shows us how to strip out new lines from the text questions in our survey via a DMS script.

 
' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 30th July 2009
' Delete new lines from text questions
' ****************************************

Event(OnNextCase, "Next")
Dim iQuestionCount
    For iQuestionCount = 0 To dmgrJob.Questions.Count - 1
        If dmgrJob.Questions[iQuestionCount].response.DataType = 2 then
            dmgrJob.Questions[iQuestionCount].response.Value = dmgrJob.Questions[iQuestionCount].Replace(mr.Cr," ")
            dmgrJob.Questions[iQuestionCount].response.Value = dmgrJob.Questions[iQuestionCount].Replace(mr.lf," ")
        End If
    Next
End Event

JustCode : Delete cases from the output file of a dms script

by

This script shows you how to delete a case in the OnNextCase Event in the output data file.

' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 4th August 2009
' Delete cases from the output file of a dms script.
' ****************************************
InputDatasource(Input, "")
    ConnectionString = "Provider=mrOleDB.Provider.2; _
        Data Source=mrDataFileDsc; _
        Location=C:tempshort_drinks.ddf; _
        Initial Catalog=C:\temp\short_drinks.mdd"
    SelectQuery = "SELECT * FROM VDATA"
End InputDatasource

OutputDatasource(Output, "")
    ConnectionString = "Provider=mrOleDB.Provider.2; _
        Data Source=mrSAVDsc; _
        Location=C:\temp\Output.sav; _
        MR Init MDM DataSource Use=2; _
        MR Init Overwrite=1"
       MetaDataOutputName = "C:\temp\Output.MDD"
End OutputDatasource

Logging(MyLog, "")
    Group = "DMA"
    Alias = "datamgmt"
    FileSize = 500
    Path = "c:\temp"
End Logging

Event(OnNextCase, "")

    If Respondent.Serial > 10 Then
        dmgrJob.DropCurrentCase()
        dmgrLog.LogError_2(Ctext(Respondent.Serial) + " - Case Deleted")
    End If

End Event

JustCode : Delete Empty columns of data

by

This mrs script shows you how you can use mrs files to make and run a dms file that will strip out any columns that do not have any data in.

Step 1: Open the VQ file and make an MDD FILE.

Dim mrDSCs, mrMdsc, oDoc,sVQ

sVQ = "C:\temp\FILE"
    Set mrMdsc = createobject("mrdscreg.components")
    Set mrMDSC = mrMdsc["mrScDsc"]

    Set oDoc = mrMdsc.Metadata.Open(sVQ + ".vq")
        oDoc.Save(sVQ + ".mdd")
        oDoc.Close()

Set oDoc = Null

Step 2: Take the MDD file and loop the variable instances


Dim oMDM, oVar
Dim sSQL,oConnection,oRecordset

' Create the MDM object
Set oMDM = CreateObject("MDM.Document")
    oMDM.Open("C:\temp\FILE.mdd", , 1)

    For Each oVar in oMDM.Variables
        'if oVar.IsSystem = False then
            debug.Log("Question Name : " + oVar.FullName)

            ' Start code to check if the field has data in it.
            Set oConnection = CreateObject("ADODB.Connection")
                oConnection.ConnectionString = "Provider=mrOleDB.Provider.2;Data Source=mrScDSC;Location=C:\temp\FILE.VQ;Initial Catalog=C:\temp\FILE.mdd"
                oConnection.Open()

            If ( oConnection.State = 1 ) Then
                Set oRecordset = CreateObject("ADODB.Recordset")
                    oRecordset.Open("SELECT count(respondent.serial) FROM VDATA WHERE " + oVar.FullName + " is not null" ,oConnection,3,1)
                    If ( oRecordset.EOF = true and oRecordset.BOF = true ) Then
                        sSQL = sSQL
                    ELSE
                        sSQL = sSQL + oVar.FullName + ","
                    End if
                Set oRecordset = Null

            End If

            oConnection.Close()
            Set oConnection = Null
    ' end if
Next

oMDM.Close()
sSQL = Left(sSQL,len(sSQL)-1)
debug.Log(sSQL)

Step 3: Create the dms file with the select statement in it.


Dim oFSO, oFile

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set oFile = oFSO.CreateTextFile("C:\temp\FILE.dms", True)


oFile.WriteLine("InputDatasource(Input, """")")
oFile.WriteLine("    ConnectionString = ""Provider=mrOleDB.Provider.2;Data Source=mrScDSC;Location=C:\temp\FILE.vq;Initial Catalog=C:\temp\FILE.mdd"" ")
oFile.WriteLine("    SelectQuery = ""SELECT " + sSQL + " FROM VDATA""")
oFile.WriteLine("End InputDatasource")
oFile.WriteLine("")
oFile.WriteLine("OutputDatasource(Output, """")")
oFile.WriteLine("    ConnectionString = ""Provider=mrOleDB.Provider.2;Data Source=mrSavDsc;Location=C:\temp\Final.sav""")
oFile.WriteLine("    MetaDataOutputName = ""C:\temp\Final.mdd""")
oFile.WriteLine("End OutputDatasource")

oFile.Close()

Set oFSO = Null

Step 4: Run the file


Dim oDMOMJob

Set oDMOMJob = CreateObject("DMOM.Job")

oDMOMJob.Load("C:\temp\FILE.dms", null)

oDMOMJob.Run()

Add Context , Populate & Strip HTML out

by

You would have recently seen in LinkedIn that Barb asked the following question. “When you add html code into your survey, it displays beautifully but then it displays as code in your tabs. Is there a way in a DMS to globally eliminate any HTML code?” and I pointed him to a post that had been written awhile ago. Also in my comment I mentioned that I had a script that would add a context to an MDD and then strip out the HTML and put just the text in it. This article shows you the code for that script.

Read more

De-dupe your data files

by

In this article I will show you one way that you could use to de-dupe your data collection datafiles. I am not sure how you would get duplicates in it in the first place , but let’s just assume you do have them. This example will use the Museum.ddf file in the DDL. This file & the ddf can be found here “.DDLDataData Collection File”

Read more

Q & A : Comments in your scripting

by

Recently we where asked how do you put comments in your scripts. This quick post shows us that you can have two types of comments. The first is the block comment. To create a block comment place a ‘! at the start of the bit you want to comment out and then a !’ at the end and you will see that you have commented out that section.

Routing(Web)
'!
    * Survey Description : Comments 
    * Last Updated       : 16th June 2011

!'

Q1.ask()

' **** End of Survey
End:

End Routing

The next type of comment you can have is a single line comment. To create a single line comment place a ‘ at the front of the line that you want to comment out.