Import Spreadsheet Data

Online survey tools produce data in a spreadsheet format. This data can be imported into Transana for the analysis of open-ended questions.

Import Spreadsheet Data

Transana can import data from online survey tools that has been saved in spreadsheet formats. During the import process, Transana will create a separate text document for each participant in your survey and will create a separate Collection for each of your survey questions. You can even select categorical data fields from your survey for automatic coding of the data.

Preparing Your Data

Prior to import into Transana, you will likely need to prepare your data file. Transana can import data in two common structured formats.

Transana can import data in Tab Delimited Text format with UTF-16 character encoding. This format supports all languages, and is what Microsoft Excel will produce if you choose the Unicode Text (*.txt) file format to export your data.

Transana can also import data in Comma-separated values format with CP-1252 character encoding. This format supports languages based on the Latin character set, but can have encoding issues with characters that are not used supported by that character encoding format. This format is produced by Microsoft Excel if you choose the Comma Separated Values (*.csv) file format to export your data.

It is possible to import Tab Delimited Text and Comma Separated Values files with other character encodings, but it can be challenging to determine what character encoding is used with these formats for different programs capable of producing them.

Importing Your Data

To import properly formatted Spreadsheet Data into Transana, start by creating a new Library. Transana creates a Document in the Library for each survey participant, so it is best to start with an empty Library. Right-click the Library and choose Import Spreadsheet Data. This will start Transana’s Spreadsheet Data Import wizard.

The first page of the Spreadsheet Data Import wizard asks you to identify your data file and the file encoding used on that file. The file must be in Tab Delimited Text or Comma Separated Values format and you need to know the character encoding used by the program that created the file.

The second page of the Spreadsheet Data Import wizard asks you to identify whether your questionnaire prompts appear in the data file’s first row or the data file’s first column. In the example on the left, it is clear that the first row contains the questionnaire prompts and the first column contains a data value for each participant.

The third page of the Spreadsheet Data Import wizard allows you to select which data from your file to import. You may decide not to include some data items, such as purely quantitative responses.

Transana’s Spreadsheet Data Import wizard next gives you the opportunity to select some of your data items for auto-coding. You should only select items here that were closed-ended or extremely short-answer items.

Once all required information is entered, the wizard imports your data as requested. You will see the data presented in three sections.

Analyze Your Data

In your Library, the wizard will create one document per participant in your survey. The document will contain each of your question prompts and the participant’s response to that question.

The wizard will create a Collection with the same name as your Library. In that Collection, it will create a nested Collection for each question in your survey. Within that nested Collection, you will find a quote for each participant containing that participant’s response to that question. These quotes will be automatically coded for all of the items you selected on the Auto-code page.

Finally, you will see a new Keyword Group called Auto-code. WIthin that Keyword Group, you will find Keywords made up of the prompt name and the range of participant responses the wizard identified for those data items.

At this point, you should be set to begin analyzing participant responses from the nested Collections described above. You should find that the Spreadsheet Data Import wizard had prepared your data well for this task.