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 automatically to the individual texts (the cases). 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:
[Open Question 1]
[Open Question 2]
|Group A||Person 1||Great conference!||The catering was a bit tight||22||high|
|Group A||Person 2||Best keynote I have heard in a long time||25||medium|
|Group B||Person 3||I especially enjoyed the pre-conference workshop||none||21||medium|
|Group B||Person 4||Great job - keep going!||Please make workshops longer in the future||31||low|
Each row of the table contains a case, and the columns "Open Question 1" and "Open Question 2" contain the respondent’s responses to the respective questions. The "Variable" columns contains standardized information for each case in the form of variables. Of particular importance are the first two columns "Document Group" and "ID", which assign the individual rows of the table to documents in MAXQDA’s “Document System”.
1. It is not necessary to have a column for the “Document Group” as MAXQDA can also create a new document group, into which all documents are imported, during the import process. However, at least one column is required, which contains the document's names.
2. MAXQDA supports one sublevel for document groups. These can be created and addressed during import using the backslash, e.g. "Survey\New York".
3. The column headings should be short and concise, as this is much more practical for further work and MAXQDA only imports the first 63 characters of the headings. Only if the column headings differ in the first 63 characters, MAXQDA will be able to handle the columns as different.
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. On the Import tab, you’ll also find the Documents from Excel Spreadsheet function. In principle 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 pre-select them, but this choice can be changed at any time.
If you select [Create new document group] from the "Document Group" drop-down menu, 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 settings for importing the 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, decimal, 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. The complete heading of each column is recorded in the code memo.
- If a variable does not yet exist, it will be created. Each document will be assigned the variable value from the variable column.
In the section "Categorize Survey Data" you will find information on how to analyze the imported survey data in MAXQDA.