Importing survey data and other structured data from Excel spreadsheets
With MAXQDA you can import structured documents from an Excel spreadsheet in XLS/S format, during which individual table cells will be automatically coded. In addition, variable values can be assigned to the individual texts. This is particularly useful when importing (online) surveys with standardized and open answers.
How should the Excel table be structured?
When imported into MAXQDA, each row of the table becomes a new document, wherein the contents of the cells form the document content and are coded with the respective column header. The structure of the Excel table corresponds to the principle of a data matrix of a standardized survey. This logic is illustrated in the table below:
|Document Group||Document Name||Question 1||Question 2||Variable|
|Document Group A||Person 1||Answer to question 1||Answer to question 2||Variable value|
|Document Group A||Person 2||Answer to question 1||Answer to question 2||Variable value|
|Document Group B||Person 3||Answer to question 1||Answer to question 2||Variable value|
|Document Group B||Person 4||Answer to question 1||Answer to question 2||Variable value|
Each row of the table contains a case, and the columns “Question 1” and “Question 2” contain the respondent’s responses to the respective questions. The column “Variable” contains additional variable values for each case. Of particular importance are the first two columns “Document group” and “Document Name”, which assign the individual rows of the table to documents in MAXQDA’s “Document System”.
Starting the import process
To import Survey data from an Excel file, go to the Import tab and select the Survey data> Import data from Excel spreadsheet function. You can also import documents from an Excel spreadsheet on the Import tab from Excel spreadsheets. In principle, however, both calls lead to the same result.
Selecting import settings
After calling up the function, the desired Excel file must be selected from the file dialog box. This opens a window where you can enter the settings for the import.
The two top settings determine which columns contain the Document Group and the Document Name. If the names “Document group” and “Document Name” are used in your table as column headings, MAXQDA will automatically select them, but this choice can be changed at any time.
If you select [Create new document group] for the entry “Document group”, MAXQDA will automatically import all documents into a new document group. MAXQDA automatically selects this option, if your table doesn’t contain a column with the heading “Document Group”.
In the middle section, you can select the columns to be imported as coded text or as variables.
If both “Code” and “Variable” are selected, MAXQDA will import the contents of this column as both as coded text and as a variable. This may be useful, for example, you do not wish to view the variable information of a document each time as a tooltip over a document name or in the Data Editor for document variables, but rather view, for example, how old a respondent is and whether he has children, directly in the text.
If neither “Code” nor “Variable” are selected, MAXQDA will ignore the column during the import process.
In the lower section, further options are available:
Code empty cells: When this option is selected, MAXQDA will import and code cells without content as empty paragraphs; these cells would otherwise not be coded.
You can also decide how MAXQDA should handle documents that were already included in the project before the import. MAXQDA considers that a document already exists in the project if the document name and document group are identical.
Import: When this option is selected, existing documents will be included in the import, and therefore may appear twice in the respective document group.
Ignore for import: Select this option if documents that already exist in the project should not be taken into account during the import.
Add text to existing documents: It is also possible to add text to existing documents. For example, data from different points in time in a longitudinal study can be added to the original document. Existing variable entries in existing documents will be updated. Empty variable values in the Excel table are ignored when importing.
Selecting other settings for importing variables
If you choose to import variables, a second window will open after you click OK.
Source: By selecting this option, you can again decide which variables will be imported.
Target: If there is already a document variable with the same name in the project, MAXQDA assigns the variable to it automatically and no other choice is available. If there is no variable with the same name in the project, you can specify the type of variable. Options include: Boolean (true/false), date/time, floating-point number, integer, and text.
Preview data type: This column displays a preview of how an entry will appear in the appropriate column in the data editor.
A final report window confirms the import of documents from the table and lists how many texts, codes, and variables were imported, as well as how many documents may have been ignored during the import.
Imported texts in MAXQDA
After importing the table from the example into an empty MAXQDA project, it will appear as follows:
When importing, MAXQDA proceeds as follows:
- Document groups that do not yet exist will be created.
- The headings of the code columns will run from left to right and will be inserted from top to bottom in the code system as new code names, as long as they do not yet exist. All text segments from the code columns will be coded with the respective column headings.
- If a variable does not yet exist, it will be created. Each document will be assigned the variable value from the variable column.