March 2017 edit: I removed chunks of code now that I’m freelancing – if you’re interested in an automated, fast, reliable way of getting LOTS of DCM data into one place, contact me! I can do it for you or provide software to do it. Stata and R are both options and then data can go into SPSS or whatever, or it can be a stand-alone executable program.
I’ve recently been involved in three projects using the Dementia Care Mapping data collection tool. This is a neat way of getting really detailed longitudinal observations on the activity, mood, interactions and wellbeing of people with dementia in group settings. The makers of the DCM provide a spreadsheet which looks like this:
that is, there is a row of BCC behaviour codes and a row of ME quality of life codes for each person, and they are captured typically every five minutes. Date and time and other stuff that might be important are floating above the main body of the data. Subsequent worksheets provide descriptive tables and graphs, but we will ignore those as we want to extract data into Stata for more detailed analysis. (But let me, in passing, point out this work in Bradford, which is moving towards electronic collection.)
The good news is that Stata versions 12 and 13 have improved import commands, with specific support for Excel spreadsheets. You can specify that you want to take only particular cells, so that allows us to pull in the stuff at the top like data and time, and then go back and get the bulk of the data.
In the most recent project, there were several care homes, and each was visited several times, and within each visit there was at least a before, during, and after spreadsheet. Thankfully, my colleague who did the collection had very consistently filed everything away so the file and folder structure was very consistent, and that is crucial if you want to automate the process of extracting and compiling the data.