Reformatting the Data
Once you have all the data in a new spreadsheet, this is how to format it so that it can be imported into webCDI
Last updated
Once you have all the data in a new spreadsheet, this is how to format it so that it can be imported into webCDI
Last updated
Now we are actually going to move stuff around!
Now you can close the June 15, 2018. csv and the template file
In the csv you have created, the first thing you should do is copy and paste the "Subject_id" column from the Qualtrics data section to line up below the "name_of_child" column from the template data section (this should be column D). Then, delete the letter in front of each of the subject id's so that only the numbers are left.
Now, we will go back and fill in the three columns to the left.
The first column (column A) which is filled with CDI_Words_GESTURES_fillable_XXXX.pdf in the template version will be left blank.
The second column labeled "From" and filled with "anonymous user" will be the same for your reformatted data. To do this, type in "anonymous user" in the first row of your reformatted data, then hover over the bottom right hand corner of that cell until you see a black cross. When you see the cross, click and drag down to the last row of your reformatted data to fill all of the rows with "anonymous user."
In column C, labeled "Received Date," in the template data, copy and paste the RecordedDate column from the Qualtrics data section.
Next, for columns E, F, and G line up "gender," "birthdate," and date_today" correspond to "Q36," "Q36_2" and "EndDate" respectively. Copy and paste these columns in the correct places.
As you will see, the data in the two gender columns is entered slightly differently. To fix this, highlight the "Q36" column, go to the "Edit" menu, click "Replace..." and a window like the one pictured below should pop up.
In the "Find what:" box type "Female" and in the "Replace with:" box type "f" and then click "Replace All." Then do the same thing for male to replace "Male" with "m."
The data in the "EndDate" column also looks slightly different. To fix this, highlight the EndDate column and in the top toolbar, where it says "Number" select "Date" in the drop down menu. This should reformat the date with just MM/DD/YY to match the template.
Now, the remaining columns should be in the right order! YAY!!!!
Highlight the Qualtrics data starting with the column titled "Q5_1" until the third to last column titled "Q32_15" and copy and paste this data in the columns to the right of the "EndDate" column in your reformatted section. At this point, the rightmost column of the template data should line up with the rightmost column of your reformatted data. If not, something is wrong :(
Once you have confirmed that you have the same number of columns in the template data as in your reformatted data you can delete the original data that you copied over from the Qualtrics spreadsheet.
Now its time for more replacing!!!
Highlight columns "Q6_1" through "Q6_28" using the replace tool that you used before, replace "Understands" with "Yes" and replace " " (blank cells, to do this you can just leave the "Find what:" box completely blank) with "Off."
Then, highlight columns "Q9_1" through "Q26_8" and replace "Understands and Says" with "understands and says", "Understands" with "understands", and " " (blank) with "Off."
Now, you can delete the rows of template data (BUT NOT THE COLUMN HEADINGS) and the column headings from the Qualtrics data. Your spreadsheet should now look like the image below. SAVE THE SPREADSHEET!!