Using SPSS to Merge Data from the Different Sheets in XLS from a Form that had Repeating Groups using SPSS

User Support Data Management
, , ,
1

Hello,
I was download kobotoolbox data in xls form. I have many different group in my data that’s why i have many spreadsheet. My question is how to gather all spreadsheet in spss. is it possible to gather all data in spss including group data?
please help
Thank you :slight_smile:

2

Hi @fieldwork19,

Please be informed that you should be able to merge your dataset using SPSS. However, please ensure the following that has been discussed earlier while merging:

Have a great day!

3

how to marge into same data from different sheet. I have only same index and parent index for group question. how to work with this

4

Hi @fieldwork19,

Please be informed that merging dataset at the moment is not possible with KoBoToolbox. You will need to use a third party software (SPSS, stata, SAS, R) to merge dataset (matching with the identifier viz. index and parent index).

Have a great day!

5

I use spss and also have same index and parent index. but how to marge in spss with including same case which is same as index and parent index

6

Hi @fieldwork19,

Please have a look at the youtube video here to merge 2 dataset. While merging please rename the index as well as parent index to ID_Index so that you should be able to merge the 2 datasets based on your identifier variable which is ID_Index.

Have a great day!

7

I can’t merge this file. after merge data of index and parent index didn’t match.
for information i have 2 more data for one index. what can i do

8

Hi @fieldwork19,

You could also merge dataset using Excel through the vlookup command. Try exploring it on the web and you should see how to merge your dataset.

Have a great day!

9

Hi @fieldwork19
To fully do what you are looking for i is important to note the following behaviors around KoBoToolBox & integrating IBM (SPSS)

1… If you have repeating groups KoBoToolBox stores each group within its own sheet in the excel. The repeats from one questionnaire administration (instance) will be linked to the main part (non repeating) questionnaire with the index (I believe you already knew this.)

  1. When working with SPSS or event STATA there are two data formats, long format and wide format. You can never merge the two formats directly without converting them.

  2. Back to KoBoToolBox, the non-repeating part of the questionnaire stores the data in the long format, while the repeating groups part of the questionnaire is stored in wide format.

Given the above 3 principles, you can then map out your workflow for converting the data to SPSS. For this purpose, I will assume that you have only two repeating groups. That means your XLS form will have three sheets: the first one which contains all responses from the non-repeating group questions, sheet 2 with the first repeating group, sheet 3 with the second repeating group questions. You can the follow the steps below:

  • Step 1: Create an SPSS file for each of the sheets i.e. sheet 1, sheet 2, sheet 3. You can convert your data using import CSV function if you have created the three CSV’s from your XLS.

  • Step 2: Independently Convert Sheet 2 and Sheet three data into long format using the instructions which can be found here

  • Step 3: Using the index variable i.e. question, (you need to name them similarly in the three spss files you have in Step 1.

You should use a merge files by adding variables command

This should sort out the issue you had indicated before

Stephane

1 Like