Import data from spreadsheets and text files
This topic is about creating new dataset sources in NVivo, by importing data from a Microsoft Excel spreadsheet. For example, you might want to import a spreadsheet containing survey responses.
In this release of NVivo for Mac, you cannot import data from text files.
What do you want to do?
- Gather your data
- Consider how you want to use your data in NVivo
- Prepare an Excel spreadsheet for import
- Import data and create a new dataset
- Import Dataset Assistant steps
- Work with your dataset
Gather your data
You cannot add additional records (rows) or fields (columns) to a dataset after import, so it is important to gather your data before you start the import operation.
For each dataset that you want to create, your data must be gathered into a Microsoft Excel spreadsheet (.xlsx or .xls).
You cannot select multiple worksheets (within a spreadsheet), so the data you want to import must be gathered into a single worksheet.
The maximum amount of data that can be imported into a single dataset is 255 fields (columns) and 100,000 records (rows).
Consider how you want to use your data in NVivo
You cannot change the analysis type (codable or classifying) of a field (column) after import, so you should decide how you want to use your data before you create a new dataset.
Fields that contain data that you intend to code and analyze should be stored as codable fields—for example, responses to open-ended survey questions, such as How do you think we can reduce our carbon emissions?
Fields that describe your data (metadata) should be stored as classifying fields—for example, the ID number, Age, Sex and Annual Income of your survey respondents. Values in classifying fields:
-
Provide context when you view coded dataset content in a node.
-
Can be used to build node structures that group your codable content—for example, by Age or Sex.
In this release, you cannot use information in classifying fields to automatically classify your case nodes, however you can import classification sheets.
When you choose to store a field as a classifying field, you may be able to choose from several possible data types—for example, date or text. You cannot change the data type of a classifying field after import.
If you want to create nodes that represent the subjects (cases) in your research—you will need a unique identifier for each subject in your data. If your data does not contain a unique identifier that you can use as a node name, you could add a unique ID number for each subject. This identifier should be imported as a classifying field.
Prepare an Excel spreadsheet for import
-
Open the spreadsheet file that contains your data in Microsoft Excel.
-
Ensure that the data is gathered into a single worksheet.
-
Review your data as described below:
| Element | Description |
| Blank rows | Remove any blank rows within the data. |
| Extraneous content | Your file should contain only the rows you want to import, and (optionally) a 1st row containing field labels. Any other content should be removed. |
| Data types | Make sure every value in a field contains the
same type of data. During the import operation, NVivo scans the
first 25 rows of your file to determine what data types are appropriate
for each field. If you choose to import a field using the date,
time, date/time, Boolean, integer or decimal data types, all the
rows in your text file must contain valid data for that data type.
If a row after the 25th line, contains invalid data, the import operation will terminate with errors. For more information about the values that can be store in a particular data type, refer to Valid data types. |
| Merged cells | Merged cells can cause errors during the import operation. We recommend that you do not import spreadsheets containing merged cells. |
| Calculated values | If a cell displays a calculated value, the displayed value (not the formula) is imported into the dataset. |
| Error values | If any of the cells in your worksheet display error values such as #NUM or #DIV, you should correct them before you start the import operation. Columns containing error values can only be imported as text fields—refer to Valid data types for more information. |
| Buttons and checkboxes | If any cells contain controls such as buttons or checkboxes the spreadsheet cannot be imported. You should remove these controls before import. |
-
Close the spreadsheet. Keeping the file open may cause errors during the import operation.
Import data and create a new dataset
NVivo provides an assistant that guides you through the import process. The assistant examines the data you are importing and helps you to ensure that the data is imported the way that you want.
To create a new dataset:
-
On the Data tab, in the Import group, click Dataset.
-
Select the spreadsheet file that contains the data you want to import.
-
Follow the instructions in the Import Dataset Assistant. For more information about using the Assistant, refer to Import Dataset Assistant steps.
Import Dataset Assistant steps
The information below provides a guide to using each step of the assistant.
| Assistant step | Description |
|
Select the worksheet to import |
This step is only displayed if your spreadsheet contains multiple worksheets—select the worksheet that contains the data you want to import. Check the preview to make sure you have selected the right worksheet. |
|
Specify how you want the data to be interpreted |
Check the preview to make sure that the dates, times and number in your data are correctly interpreted. You can use the options on this step of the Assistant to change the way the data is interpreted, and to indicate whether the first row of your data contains column headings (field names). Time Delimiter If the hours, minutes and seconds in your times are separated by a delimiter character—for example, a colon (:)—enter the delimiter. Date Delimiter If the days, months and years in your dates are separated by a delimiter character—for example, a slash (/)—enter the delimiter. If the days, months and years are not separated by a delimiter, clear this field. Date Format The order of day, month and year in your dates—for example, if your file contains the date 10/05/2005 and you select MDY, NVivo interprets the date as 'October 05, 2005'. If you select DMY, NVivo interprets the same date as 'May 10, 2005'. When the data is imported into NVivo, it will be displayed in the date format that is specified in your system preferences (Language & Text). Four digit years Select this option, if the years in your dates include the century—for example '1999' or '2010'. If the years in your dates do not include the century, clear this check box. Decimal Symbol The decimal separator that is used in your numbers. When the data is imported into NVivo, it will be displayed with the decimal separator that is specified in your system preferences (Language & Text). First row contains field names Select this check box when the first row of your spreadsheet contains the names of your columns. |
| Specify information about each of the fields you are importing |
On this screen you can choose which fields contain values that you want to code (codable fields) and which fields contain values that describe your data (classifying fields). By default, all fields (columns) are imported, and the analysis type (codable or classifying) is preselected based on the content in the first 25 rows of the data. The preview area shows the values in the first 25 rows and indicates how each field will be imported. Field Selection You can use these buttons to select or deselect all fields for import. For example, if you want to import only a few fields from a spreadsheet with many columns, you can deselect all fields, and then individually select the fields you want to import. Check the Field Options for each column because you cannot change this after the data has been imported. To view or change the field options for a column:
Field Name The field name that will be used for the currently selected field (column) when the new dataset is created. If the first row of your data does not contain column headings, the field names are set to Column A, Column B, Column C etc. You can change these field names to reflect the information in the column. Import Field Select this check box to import the field. If you clear this check box, the field is not imported. Analysis Type Choose whether this is a codable or classifying field.
You cannot change the analysis type after the data has been imported, so you should consider how you want to use your data before you choose the analysis type. Data Type
Refer to Valid data types for more information about data types. Decimal Places When the decimal data type is selected, you can choose the number of decimal places—if you reduce the number of decimal places, the data will be truncated. |
|
Enter a name and description for your new dataset |
You can change the name and optionally add a description. |
Work with your dataset
After importing a dataset, you can use a number of techniques to organize and analyze the data—for example, you could:
-
Run a Word Frequency query to get a feel for what respondents are saying.
-
Code the content—select the text in a codable field and code at new or existing nodes.
-
Auto code the dataset to make a node for each survey question and gather all the answers—a useful starting point for further exploration.
-
Auto code the dataset to make a case node for each respondent and gather all of a respondent's answers.
Refer to Approaches to analyzing survey results for more detailed information.