3 1. Introduction SPSS for Windows takes advantage of the Microsoft Windows (MS Windows) graphical environment. The aim of the product is to make statistics as straightforward and easy a task to accomplish on computer as possible. The menu system enables you to build up complex statistical commands quickly and easily using a point and click method, without involving detailed knowledge of the command language. 1.1 Aim of this Document This document is designed to demonstrate importing and exporting data and other objects. The document is intended for anyone wanting to use SPSS in the MS Windows environment. It is assumed that you already know how to login to the network, start MS Windows and perform basic MS Windows operations with the mouse. No prior knowledge of SPSS is assumed. Several SPSS data files are used in this introduction. Before you begin, download the zip file provided for use with this document. Open a web browser and go to the URL : Scroll down to locate the item titled Example file for Importing and Exporting With and right-click on the ZIP file. Select Save Link As (Save Target As in Internet Explorer). When the Save As dialog box appears, select a suitable directory and click Save. Go into Windows Explorer and double click the zip file. Click File / Extract All to unzip the data files. If you are using, or intending to use, SPSS for Windows on your own PC you will also find this document useful. If you want to obtain a copy of the software to run on your own PC you should contact the ISS IT Shop. The IT Shop sells copies of the software, provides details about installing the software and states the terms of the SPSS site licence. 2. Importing Data from Other Sources In addition to reading its own data files, SPSS has the facility to read text files and files created by many other applications e.g. Excel Microsoft Access SAS dbase 2.1 Reading Excel 5 or Later Files When reading Excel files, the following rules apply: Each column becomes a variable and the data type and width for each is determined by the data type and width in the Excel file. If the column contains a mixture of types (e.g. numeric and string), then the data type in SPSS is set to string and all values are read as valid string values. Information Systems Services Page 3 of 23

4 For numeric variables, blank cells are set to the system missing value (a full stop). For string variables, blanks cells are treated as valid string values. If the first row of the range of Excel data contains column headings, these become the names of the SPSS variables. Any column headings containing more than eight characters will be truncated to eight characters. If the first row does not contain variable names, default names will be allocated to the variables by SPSS. Information Systems Services Page 4 of 23

5 Task 1: Import Data from Excel Activity 1.1 Before importing the data, open the file students97.xls using Microsoft Excel, and observe the position of the data on this worksheet. Activity 1.2 Create a new SPSS data file by selecting New > Data from the File menu. Select Open > Data from the File menu and change the Files of Type box to Excel(*.xls). Select the file students97 and click Open. Activity 1.3 Complete the next dialog box as shown to specify the range, and position of variable names. Click OK. Figure 1 The Excel Data Source Dialog Box The file will be displayed in the SPSS Data Window. Figure 2 Imported Excel Data Notice that the width of the gender column needs changing to reflect the nature of the data. Either use the mouse to extend the width, or switch to Variable View and modify the Column property to 6. Save the file as an SPSS file (students.sav) in your folder. Information Systems Services Page 5 of 23

6 2.2 Using ODBC to read a MS Access Database File The process of reading records from a database table relies on the use of ODBC the Open Database Connectivity protocol. A data source consists of two essential pieces of information: the driver that will be used to access the data the location of the database that is to be accessed. In order to specify data sources, the appropriate drivers must be installed. The following ones can be installed from the SPSS CD ROM: SPSS Data Access Pack Microsoft Data Access Pack. Task 2: Read Data from an Access 2002 Database Comments This method may also be used to read an Excel spreadsheet. Activity 2.1 From the File menu select Open Database > New Query. The Database Wizard dialog box will be opened. Note that the currently defined data sources may be different from those shown. Select MS Access Database and click Next. Figure 3 SPSS Database Wizard Information Systems Services Page 6 of 23

7 Activity 2.2 At the ODBC login screen type the name and path of the required database file accstudents2002.mdb and then click O.K. (Your path will be different from the one shown.) Figure 4 ODBC Login Screen Activity 2.3 The Select Data dialog box now opens. To see the list of fields in the table, click the small plus sign to the left of the word tblstudents. Figure 5 Select Data Dialog Box Activity 2.4 At this point, individual fields could be selected. However, all fields are required for this example, so click the minus sign to the left of the word tblstudents to restore the screen to Figure 5 above. Information Systems Services Page 7 of 23

8 Activity 2.5 Use the mouse to drag the table over to the window on the right. Figure 6 Select Data Dialog Box Now we could click Next, and specify criteria for inclusion of each field. We could then modify the variable names. (Remember that in SPSS variable names are restricted to 8 characters and there are additional limiting constraints.) In this case, as all the fields are required with no filtering. Click Finish. The imported data set will be displayed. The Data View, shown below, shows that field widths require adjustment. The Variable View shows the current settings. Information Systems Services Page 8 of 23

9 Activity 2.6: Adjust the data layout. (i) Select Variable View. Modify the settings of Width and Columns to conform with the specifications indicated below. (ii) Return to Data View. The layout should now resemble the display below. Activity 2.7: Save the file as an SPSS system file called accstudents.sav N.B. This method may also be used to read Excel spreadsheet data. Information Systems Services Page 9 of 23

10 2.3 The Text Data Import Wizard The SPSS Text Wizard can read text data files in a variety of formats: Tab-delimited files Space delimited files Comma delimited files Fixed format files. Step 1 Step 2 Step 3 Step 4 Step 5 A predefined format can be applied, or the remaining steps in the Text Wizard can be followed. This requests information about the format of data which may be delimited (i.e. spaces, tabs, commas or other characters are used to separate variables), or fixed width, where each variable is recorded in the same column location of the record for each case in the file. The file may or may not contain variable names. Existing variable names which contain more than eight characters will be truncated. If no variable names are included SPSS will allocate default names. It is also necessary to describe the way cases are represented. Normally each line will represent a case and the first case will begin on line 1 if no variable names are supplied and line 2 otherwise. The number of cases to import may be specified so it is possible to import all cases, the first n cases, or a random sample. a) Delimited Files. This step will display the wizard s interpretation of the data file, and allows selection of the character or symbol used as a delimiter. b) Fixed Width Files. This step will display vertical lines on the file where the wizard thinks each variable begins. These can be moved if required. Allows control over the variable names, the data format that the Text Wizard will use to read each variable, and the variables which will be included in the data file. Values that contain invalid characters for the selected format will be treated as missing. In the final step, there is an opportunity to save the file specification for later use, or to paste the syntax. Information Systems Services Page 10 of 23

11 Task 3 Import Data (Fixed Format) Activity 3.1 From the menu select File > Read Text Data Change the Files of Type box to *.dat. Look in your folder for the file BCSurvival.dat, select it and then click Open. The Text Import Wizard dialogue window will be displayed. Figure 7: Text Import Wizard: Step 1 Specify No to the question Does your text file match a predefined format and click Next. Activity 3.2 In the next dialogue window,: (i) Choose Fixed Width to indicate how the variables are arranged. (ii) Choose No in answer to the question Are variable names included at the top of your file. The display should now resemble the window below. Figure 8: Text Import Wizard: Step 2 Information Systems Services Page 11 of 23

12 (iii) Click Next. Activity 3.3 Other required information includes the line number of the first case. In this example it is 1, but if the first row contained variable names the row number of the first case would be 2. In the current file, a case corresponds to a single record and so it occupies a single line. If a case contained several records it would be necessary to supply that information here. (i) Make the following selections: Figure 9 Text Import Wizard Step 3 (ii) Click Next. Activity 3.4 The next window allows alterations to be made to the arrangement of columns. Figure 10 Text Import Wizard Step 4 Information Systems Services Page 12 of 23

13 No modifications are necessary as the delimiter for this file is the space character so click Next. Activity 3.5 Since variable names were not read in with the data, SPSS has allocated the default names v1, v2 vn. Click on each variable name to select it, and then type the variable name. The first variable s details are shown below: Figure 11: Text Import Wizard Step 5 (i) Replace the dummy names V1, V2 etc. by the names id, age, pathsize, lnpos, histgrad, er, pr, status, pathscat, ln_yesno, time. To do this click on the column headed V1 and overtype the name V1 by id in the Variable name box. Then click on the next column and do the same for the next varable. (ii) When the all of the variable names have been changed, click Next. Activity 3.6 In the final dialogue window, shown below, click Finish. Figure 12: Text Import Wizard Step 6 Information Systems Services Page 13 of 23

14 The data should appear as follows: Figure 13 Imported Text File Activity 3.7 Save the file with the name bcsurvival. Notice that the file is now an SPSS data file which has the file extension sav. Clicking on the Variables button in the last dialogue window would allow deselection of variables. (in some situations not all the variables would be required). Activity 3.8: To view variable information select File > Display Data File Information > Working File Info from the menu. An output window will open in SPSS to show the information. A sample of this is shown below: Information Systems Services Page 14 of 23

15 Task 4 Import Data (Delimited) Activity 4.1 Select Read Text Data from the File menu. Find the file anxiety.txt in your folder, click on the file to select it and then click Open. Respond to the wizard s questions as follows: Step 1 Step 2 No (file does not match a predefined format). Choose Delimited and select Yes the variable names are included at the top of the file. Step 3 The first case of data begins on line 2. Each line represents a case. Import all of the cases. Step 4 Step 5 Step 6 Activity 4.2 Choose tab as the delimiter. All variables are to be imported, so just select Next. The file format is not required for later use, nor is the syntax required. Click Finish. The data will now appear in the data editor window. Save the file as an SPSS data file and call it anxiety1. If in Step 6 of the wizard, we had chosen to paste the syntax, the following code would have appeared in a Syntax Window: Figure 14 SPSS Syntax Window This code, which has been generated by the Text Data Import Wizard is written in the SPSS Command Language. By choosing the Run option in this window, the code would be executed and the data would be imported. In earlier versions of SPSS, it was necessary to understand this language in order to read in and analyse the data, and there are still types of analyses that can only be performed by writing SPSS code and running it from the syntax window. Information Systems Services Page 15 of 23

16 3. Exporting Results Pivot tables and text output from SPSS may be exported in HTML, text, Word/RTF, and Excel format. Charts may be saved in a variety of common formats used by other applications. Three options are available: Output Document. Exports any combination of pivot tables, text output, and charts. For HTML and text format, charts are exported in the currently selected chart export format. For HTML document format, charts are embedded by reference, and charts should be exported in a suitable format for inclusion in HTML documents. For text document format, a line is inserted in the text file for each chart, indicating the filename of the exported chart. For Word/RTF format, charts are exported in Windows metafile format and embedded in the Word document. Charts are not included in Excel documents. Output Document (No Charts). Exports pivot tables and text output. Any charts in the Viewer are ignored. Charts Only. Exports charts only. For HTML and text documents, export formats include: Windows metafile (WMF), Windows bitmap (BMP), encapsulated PostScript (EPS), JPEG, TIFF, PNG, and Macintosh PICT. For Word/RTF documents, charts are always exported in Windows metafile format. Information Systems Services Page 16 of 23

18 Task 6 Save all output to a Word document Activity 6.1 From within the OUTPUT window, select File Export. Activity 6.2 In the dialogue window displayed, enter C:\temp\alloutput in the field labelled File Name as shown below. Figure 17: Export Output (All output) Click OK. All the output objects will be saved in the named Word document. The alignment of columns within tables should be adjustable and charts should also be adjustable as picture objects. You are not forced to save the entire contents of the OUTPUT window. SPSS allows you to select an individual object, or perhaps a class of objects such as graphs, and to save selectively. The following examples illustrate these options Task 7 Save a selected table to an existing open Word file Activity 7.1 Select an individual table by clicking on it. (If you do not have any tables in your Output window run a Frequencies or Crosstabs analysis to create one). Activity 7.2 From the menu select Edit Copy. Information Systems Services Page 18 of 23

19 Activity 7.3 Move to the Word document and select Edit Paste Special Formatted Text (RTF). The table will be inserted directly into the document. Task 8 Save all tables A different method allows for all tables to be exported. Activity 8.1 From the menu within the OUTPUT window select Edit Select All Pivot Tables. All the tables within the OUTPUT window will be highlighted. Select File Export. Activity 8.2 (i) (ii) In the dialogue window displayed, specify Selected. In the field labelled File Name, enter c:\temp\alltables. The window should resemble the display below. Figure 18: Export Output (All tables) (iii) Click on OK. All tables will be saved in the named Word document. Information Systems Services Page 19 of 23

20 Task 9 Save a selected chart To save an individual chart directly into Word, the simplest method is to use Copy and Paste. This method has already been described (see Task 7 above). Alternatively, to save a chart into a file the Export facility can be used as follows. Activity 9.1 (i) (ii) (iii) (iv) Select an individual chart by clicking on it. Click on the right mouse and select Export. In the dialogue window displayed, specify Selected. In the field labelled Type (within the box labelled Document) change the Type to None (Graphics Only). (v) In the field labelled Type (within the box labelled Graphics) specify the Type as JPEG. (vi) In the field labelled File Name, enter c:\temp\graph. The window should resemble the display below. Figure 19: Export Output (Selected chart) (vii) Click on OK. The selected file will be saved in c:\temp\graph1.jpg. Note the addition of the digit 1 at the end of the filename. This is added by SPSS for reasons which will become clearer after the next example has been described. The chart may be inserted into a Word document using Insert Picture. Information Systems Services Page 20 of 23

21 Task 10 Save all charts A technique similar to that used to save all tables may be employed. Activity 10.1 From the menu within the OUTPUT window select Edit Select All Charts. All the charts within the OUTPUT window will be highlighted. Activity 10.2 Select File Export. (i) In the dialogue window displayed, specify Selected. (ii) Under the area labelled Document select Type as None (Graphics only) (iii) Under the area labelled Graphics, in the field labelled Root File Name, enter c:\temp\allgraphs. The window should resemble the display below. Figure 20: Export Output (All charts) (iv) Click on OK. The charts will be saved individually in the files shown in the Windows Explorer display below. Figure 21: Windows Explorer listing of files created Information Systems Services Page 21 of 23

22 The filenames are formed using the common prefix of allgraphs with the addition of a digit at the end. Note: Some care is needed in using Copy and Paste to move output into Word. For example, you could use Edit Select All Charts followed by Edit Copy and then use either Paste or Paste Special within Word to include the copied charts. The problem with this approach is that the collection of charts would be treated as one entity when inserted into Word. Any attempt to modify the attributes or alignment of one chart would cause the same changes to apply to all charts. For this reason, the use of the Export facility as illustrated above is the preferred method of moving multiple output objects into Information Systems Services Page 22 of 23

Getting Started Before you begin create a folder on your desktop called DreamweaverTraining This is where you will save your pages. Inside of the DreamweaverTraining folder, create another folder called

Pharmacy Affairs Branch Website Database Downloads PUBLIC ACCESS GUIDE From this site, you may download entity data, contracted pharmacy data or manufacturer data. The steps to download any of the three

Create a New Database in Access 2010 Table of Contents OVERVIEW... 1 CREATING A DATABASE... 1 ADDING TO A DATABASE... 2 CREATE A DATABASE BY USING A TEMPLATE... 2 CREATE A DATABASE WITHOUT USING A TEMPLATE...

Avery DesignPro for PC Frequently Asked Questions General Information Questions Q: What are the system requirements for DesignPro? A: The following is required to run DesignPro: Microsoft Windows VistaTM,

CONTENTS Arizona State University College of Health Solutions College of Nursing and Health Innovation Introduction to IBM SPSS Statistics Edward A. Greenberg, PhD Director, Data Lab PAGE About This Document

Guide 86 Version 3.0 SPSS for Windows importing and exporting data This document outlines the procedures to follow if you want to transfer data from a Windows application like Word 2002 (Office XP), Excel

Advanced Excel Data Validation Excel has a feature called Data Validation, which will allow you to control what kind of information is typed into cells. 1. Select the cell(s) you wish to control. 2. Click

Appendix A How to create a data-sharing lab Creating a lab involves completing five major steps: creating lists, then graphs, then the page for lab instructions, then adding forms to the lab instructions,

Using the TestTrack ODBC Driver The read-only driver can be used to query project data using ODBC-compatible products such as Crystal Reports or Microsoft Access. You cannot enter data using the ODBC driver;

Word 2010: Mail Merge to Email with Attachments Table of Contents TO SEE THE SECTION FOR MACROS, YOU MUST TURN ON THE DEVELOPER TAB:... 2 SET REFERENCE IN VISUAL BASIC:... 2 CREATE THE MACRO TO USE WITHIN

Basic Pivot Tables Pivot tables summarize data in a quick and easy way. In your job, you could use pivot tables to summarize actual expenses by fund type by object or total amounts. Make sure you do not

Querying the Data Warehouse Using Microsoft Access I. What is a Data Warehouse? A data warehouse stores data from current and previous years that have been extracted from the various operational databases

Page 1 of 14 Access 2010 Home > Access 2010 Help and How-to > Getting started Search help More on Office.com: images templates Access 2010: database tasks Here are some basic database tasks that you can

XMailer Reference Guide Version 7.00 Wizcon Systems SAS Information in this document is subject to change without notice. SyTech assumes no responsibility for any errors or omissions that may be in this

Software User's Guide Brother QL-series The contents of this guide and the specifications of this product are subject to change without notice. Brother reserves the right to make changes without notice

Getting Started Guide SAGE ACCPAC INTELLIGENCE Table of Contents Introduction... 1 What is Sage Accpac Intelligence?... 1 What are the benefits of using Sage Accpac Intelligence?... 1 System Requirements...

Importing TSM Data into Microsoft Excel using Microsoft Query An alternate way to report on TSM information is to use Microsoft Excel s import facilities using Microsoft Query to selectively import the

Opening a Database in Avery DesignPro 4.0 using ODBC What is ODBC? Why should you Open an External Database using ODBC? How to Open and Link a Database to a DesignPro 4.0 Project using ODBC Troubleshooting

Introduction Page 1 of 15 The Open Database Connectivity (ODBC) standard is a common application programming interface for accessing data files. In other words, ODBC allows you to move data back and forth

CREATING EXCEL PIVOT TABLES AND PIVOT CHARTS FOR LIBRARY QUESTIONNAIRE RESULTS An Excel Pivot Table is an interactive table that summarizes large amounts of data. It allows the user to view and manipulate

Help File Version 1.1.4.0 February, 2010 MetaDigger for PC How to Use the Sound Ideas MetaDigger for PC Program: The Sound Ideas MetaDigger for PC program will help you find and work with digital sound

FileMaker Pro and Microsoft Office Integration page Table of Contents Executive Summary...3 Introduction...3 Top Reasons to Read This Guide...3 Before You Get Started...4 Downloading the FileMaker Trial

MICROSOFT ACCESS 2007 BOOK 2 4.1 INTRODUCTION TO ACCESS FIRST ENCOUNTER WITH ACCESS 2007 P 205 Access is activated by means of Start, Programs, Microsoft Access or clicking on the icon. The window opened

Instruction manual testo easyheat Configuration and Analysis software en 2 General Information General Information This documentation includes important information about the features and application of

NDUS Training and Documentation What is a Mail Merge? A mail merge is generally used to personalize form letters, to produce mailing labels and for mass mailings. A mail merge can be very helpful if you

Intro to Excel spreadsheets What are the objectives of this document? The objectives of document are: 1. Familiarize you with what a spreadsheet is, how it works, and what its capabilities are; 2. Using

CHAPTER A Creating and Using Databases with Microsoft Access In this chapter, you will Use Access to explore a simple database Design and create a new database Create and use forms Create and use queries

HOW TO COLLECT AND USE DATA IN EXCEL Brendon Riggs Texas Juvenile Probation Commission Data Coordinators Conference 2008 Goals To be able to gather and organize information in Excel To be able to perform

Installing LearningBay Enterprise Part 2 Support Document Copyright 2012 Axiom. All Rights Reserved. Page 1 Please note that this document is one of three that details the process for installing LearningBay

MICROSOFT OFFICE 2007 MICROSOFT OFFICE ACCESS 2007 - NEW FEATURES Exploring Access Creating and Working with Tables Finding and Filtering Data Working with Queries and Recordsets Working with Forms Working

INTRODUCTION: You can extract data (i.e. the total cost report) directly from the Truck Tracker SQL Server database by using a 3 rd party data tools such as Excel or Crystal Reports. Basically any software

Creating a Participants Mailing and/or Contact List: The Limited Query function allows a staff member to retrieve (query) certain information from the Mediated Services system. This information is from

Appendix 2.1 Tabular and Graphical Methods Using Excel 1 Appendix 2.1 Tabular and Graphical Methods Using Excel The instructions in this section begin by describing the entry of data into an Excel spreadsheet.

osborne books Working with Spreadsheets UPDATE SUPPLEMENT 2015 The AAT has recently updated its Study and Assessment Guide for the Spreadsheet Software Unit with some minor additions and clarifications.

Chapter 8 Training Notes Bank Account 1 September 2015 BANK ACCOUNTS Bank Accounts, or Bank Records, are typically setup in PrintBoss after the application is installed and provide options to work with

Importing Contacts to Outlook 1. The first step is to create a file of your contacts from the National Chapter Database. 2. You create this file under Reporting, Multiple. You will follow steps 1 and 2

Charting LibQUAL+(TM) Data Jeff Stark Training & Development Services Texas A&M University Libraries Texas A&M University Revised March 2004 The directions in this handout are written to be used with SPSS

Intro to Web Development For this assignment you will be using the KompoZer program because it free to use, and we wanted to keep the costs of this course down. You may be familiar with other webpage editing

TestGen Tutorial Pearson Education Page 1 TestGen is a test generator program from Pearson Education designed to help you create paper handouts, quizzes or tests for your students. You can select questions

Secure Website and Reader Application User Guide February 2005 IMPORTANT NOTICE Copyright Medibank Private Limited All rights reserved. No part of this document (including its appendices and Schedules)

Creating and Viewing Task Dependencies between Multiple Projects using Microsoft Project Preliminary 1. You must have Microsoft Project 2003 or higher installed to complete these procedures. 2. If necessary,

How to Create and Send a Froogle Data Feed Welcome to Froogle! The quickest way to get your products on Froogle is to send a data feed. A data feed is a file that contains a listing of your products. Froogle

January 25, 2012 Importing from Tab-Delimited Files Tab-delimited text files are an easy way to import metadata for multiple files. (For more general information about using and troubleshooting tab-delimited

Creating a Distribution List from an Excel Spreadsheet Create the list of information in Excel Create an excel spreadsheet. The following sample file has the person s first name, last name and email address

Downloading RIT Account Analysis Reports into Excel In the last lesson you learned how to access the Account Analysis detail and export it to Excel through the Account Analysis function. Another way to

Guidelines for Completing the VDOT Form C 13CPM CONSTRUCTION DIVISION 1. OVERVIEW The VDOT Form C 13CPM is required to prepare and submit the Contractor s Progress Earnings Schedule as specified in the