In this article we are going to create the final DMS script that we use to recode the concurrents.csv file created in the previous article into something that is richer and usable for tabulation. The final output file will be in a DataCollection Data file ( ddf ) format. This article assumes that you have some basic knowledge of DMS and VBA scripting.
In this script we are going to need the following Events or Sections.
Metadata(en-AU, Analysis, Label, SystemVariables = false) End Metadata InputDatasource(Input, "") ConnectionString = "" SelectQuery = "" End InputDatasource Event(OnNextCase, "") End Event OutputDatasource(Output, "") ConnectionString = "" MetaDataOutputName = "" End OutputDatasource
In this script we have another Metadata section that we will used to create some variables to hold the year, month and Day of the interview. ( The full code can be found at the link at the end of this article )
iDay "Day of Interview"
categorical [1..1]
{
_1 "1",
_2 "2",
_3 "3",
_4 "4",
_5 "5",
_6 "6",
_7 "7",
_8 "8",
_9 "9",
_10 "10",
_11 "11",
_12 "12",
_13 "13",
_14 "14",
_15 "15",
_16 "16",
_17 "17",
_18 "18",
_19 "19",
_20 "20",
_21 "21",
_22 "22",
_23 "23",
_24 "24",
_25 "25",
_26 "26",
_27 "27",
_28 "28",
_29 "29",
_30 "30",
_31 "31"
};
iMonth "Month of Interview"
categorical [1..1]
{
_1 "January",
_2 "February",
_3 "March",
_4 "April",
_5 "May",
_6 "June",
_7 "July",
_8 "August",
_9 "September",
_10 "October",
_11 "November",
_12 "December"
};
iYear "Year of Interview"
categorical [1..1]
{
_2008 "2008",
_2009 "2009",
_2010 "2010",
_2011 "2011",
_2012 "2012",
_2013 "2013",
_2014 "2014",
_2015 "2015",
_2016 "2016",
_2017 "2017",
_2018 "2018"
};
DOY "Day Of Year" categorical [1]
{
_1 "January",
_2 ".",
_3 ".",
_4 ".",
_5 ".",
_6 ".",
_7 ".",
_8 ".",
_9 ".",
_10 "10th",
........
_355 ".",
_356 ".",
_357 ".",
_358 ".",
_359 ".",
_360 ".",
_361 ".",
_362 ".",
_363 ".",
_364 ".",
_365 "."
}
}
Next we have a basic InputDatasource section that uses the Csv dsc to connect to the csv and mdd files that we created in the last article.
ConnectionString = "Provider=mrOleDB.Provider.2;Data Source=mrCsvDsc;
Location=C:templogsConcurrents.csv;
Initial Catalog=C:templogsConcurrents.mdd"
SelectQuery = "SELECT * FROM VDATA"
You should notice that this section looks at all the records and not just a subset of them. Next we have the OnNextCase Event that does all the hard work for us. Again , due to the number of lines the full text can be found here.
Dim i,sLine,otime,sValue
Dim iTimes
oTime = dateadd(StartTime,"s",-second(StartTime))
If ( Duration > 1440 ) Then
iTimes = 1440
Else
If ( Duration = 0 ) Then
iTimes = 1
else
iTimes = Duration
End If
End if
for i = 1 to iTimes
sValue = Ctext(timeonly(dateadd(oTime,"n",1*i)))
sLine = sLine + "_" + right(sValue,11) + ","
Next
sLine = left( sline,len(sLine)-1)
sLine = "{" + replace(replace(sLine," ","_"),":","_") + "}"
TimeSlot = sLine
DOY = "_" + ctext(datepart(otime,"y"))
iYear = "_" + ctext(Year(StartTime))
iMonth = "_" + ctext(Month(StartTime))
iDay = "_" + ctext(Day(StartTime))
So what is this section doing , well again as always we create some variables and then we take the StartTime question and remove the seconds portion away from it. You might ask what do we mean and why , well what we mean is that the times we have at present are like “15/09/2009 6:23:49 PM” and we are converting them to “15/09/2009 6:23:00 PM”. We are doing this because there is no real point worrying about the seconds and to be honest you could not make a MDD file that had all the seconds in it for an entire day, it would just be to big ( 864,000 items ) . So if we work in mins , then we only have 14400 items to work with.
Ok so we have out Start time without the seconds , next we do a check on Duration and if it is 0 we set the value of iTimes to be 1 otherwise iTimes takes the value of Duration. We Also do a check on Duration and say that if it is over 14400 then set iTimes to 14400. In English what this means is that if a survey started on one day lets say the 1st of January and somehow stayed active for more than a day we will assume this is an error and just say the survey lasted a day. We think it is pretty safe to assume that a survey would not be active for more than a day.
Now what we do is to code in the survey length. To do this we have our for loop that will loop around iTimes and each time we will take the starttime and add ( 1 min * iTimes ) to it , returning the time only. What this means is that we end up with the following if we have a start time of “15/09/2009 6:23:49 PM” with a duration of 3,
6:23:00 PM,6:24:00 PM,6:25:00 PM
because we are doing this a time at a time we do some string manipulation and convert the time to look like this ( at the end of the loop )
"_6_23_00_PM,6_24_00_PM,6_25_00_PM"
We then store this string into the TimeSlot Question. While all this is going on we actually try and code these into some specific timeslots , but at this stage this bit it work in progress. And then finally we code in the Year, Month and Day of the interview. And Last but not least we have the Output Section
ConnectionString = "Provider=mrOleDB.Provider.2;
Data Source=mrDataFileDsc;Location=C:TempLogsTimeSlots.ddf"
MetaDataOutputName = "C:TempLogsTimeSlots.mdd"
In this example we are outputting to a DDF file as we will be doing our reporting in Reports for Surveys. So now we have a script that when run it will take our csv file and re-code and convert it into a ddf file that we can use in Reports for surveys. In the next article we will show you how you might use the files created.
The full code for this article can be found in :
http://CodeCorner.SmaterDimensions.Com @ Convert concurrents.csv to DDF & MDD