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()