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

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).  

Top of Page

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.

Top of Page

Prepare an Excel spreadsheet for import

  1. Open the spreadsheet file that contains your data in Microsoft Excel.

  1. Ensure that the data is gathered into a single worksheet.

  1. 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.
  1. Close the spreadsheet. Keeping the file open may cause errors during the import operation.

 Top of Page

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:

  1. On the Data tab, in the Import group, click Dataset.

  1. Select the spreadsheet file that contains the data you want to import.

  2. Follow the instructions in the Import Dataset Assistant. For more information about using the Assistant, refer to Import Dataset Assistant steps.

Top of Page

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:

  1. Select the column by clicking the header in the preview area.

  2. Review the Field Options and make any changes.

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.

  • Codable Field  Contains text you want to analyze—for example, responses to open-ended survey questions.

  • Classifying Field  Contains information about the data—for example, the ID number, Sex. Age or Location of survey respondents. You cannot code, query or annotate content in classifying fields.

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  

  • For codable fields, the data type is always 'Text' and cannot be changed.

  • For classifying fields, the data type is preselected based on the values in the first 25 rows. If more than one data type can be used to store the values in the column, you can change the 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.

Top of Page

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:

Refer to Approaches to analyzing survey results for more detailed information.