Exporting Perceptual Results To Excel

  Previous topic Next topic JavaScript is required for the print function Mail us feedback on this topic! Mail us feedback on this topic!  


In order to perform complex sorting, filtering and queries on Perceptual Results, you have to export a number of ComQuest data files to an external program, such as Excel or Access.


ComQuest makes this easy; simply go to Utilities / Export Data and select Perceptual Tables.



Export Perceptual Results filter


On the filter screen, select the date range of perceptual data you wish to output, either by entering it in the Start Date and End Date fields, using the spin boxes to the right of each field, or the calendar lookup button to the far right.  After selecting the desired date range, click OK then select the desired survey.


Several files are created, and zipped into a file called OUTPUT.ZIP.  The following comma-delimited text files will be created in the C:\ComQuest\OUTPUT.ZIP archive on the computer from which you run this procedure.  The first row in each file is a "header" which defines each column of data.  Excel will be looking for this, and will automatically make the data in the first row into column headers for each table.  Each record of each file is then written to successive rows.  Here are the file names contained in OUTPUT.ZIP, and a brief description of each:


SURVEY.CSV - The Active Survey. Format: Serial Number, Name, Date


QUESTION.CSV - Each of the Questions in your active survey Format: Sequence, Question Text, Picklist (if applicable; otherwise 0), Serial Number of each record


RESPONDE.CSV - All respondents with Perceptual results are included. Format: Resp. ID, Age Low, Age High, Gender, Ethnicity, Zip Code, Cume 1 -6 (0 or 1), Fave 1-6 (0 or 1), Datestamp, Status, Quota Tier


AUXSTATI.CSV - Any "Other" stations for each respondent.  A matching record for each respondent will only exist if they named at least one Auxiliary Station in either Cume or Pref. Format: respondent ID, Datestamp, Cume 1-39 (0 or 1), Pref 1-39 (0 or 1)


DEFAULTS.CSV - This is a one-record file containing the text call letters for use in Cume 1-6 and Fave 1-6 fields in the RESPONDE.CSV file. Format: Station 1-6


PICKLIST.CSV - A listing of ALL Picklists, whether or not they're utilized in this survey. Format:  Serial Number, Name


PICKITEM.CSV -  A listing of ALL Pickitems for all Picklists, whether or not they're utilized in the Active survey. Format: Pick List Number, Description, Serial Number


RESPONSE.CSV - This is the Answers file for your Perceptual. Format: Question Number, respondent ID, Answer, Date, Picklist (if applicable, otherwise 0), Serial Number


STATIONS.CSV - This is a one-record file containing the text call letters for use with the Cume 1-39 and Pref 1-39 fields in the AUXSTATI.CSV file. Format: Call Letters 1-39


Importing The Files Into Excel


1. Copy the OUTPUT.ZIP file to a subdirectory on the computer which has Excel.  (You may wish to create a new directory to contain these files and all your Excel files and reports for this Perceptual study.)


2. Unzip the file by typing: PKUNZIP OUTPUT and press Enter. (Or, use WinZip).


3. Launch the Excel program, click File menu, then New and select Workbook icon.  This will give you a blank Workbook, with 3 default Sheets.


4. Click on the Insert menu and select Worksheet to add another blank Worksheet.  Repeat this step until you have a total of 9 Sheets displayed.


5. Save this "Master" Workbook with a unique name, such as "WAAA Perceptual"


Now, perform the following set of instructions for the 9 .CSV files:


1. Click the File menu, and select Open.  Use the File Dialogue Open box to navigate to the directory containing the .CSV files.  (Change the "Files of type" drop box to Text Files.)


2. Click on the first .CSV file, then hold down the Shift key and click on the last .CSV file.  This should select all 9 of the files.  Click OK; nine separate files will be created and the data will be formatted into the correct columns and rows in each..


You now have to copy each individual Excel worksheet (Auxstati, Defaults, etc.) onto separate Sheets in the Master Workbook you created in Step 5 above.


1. Click on the Window menu item, select Arrange and Tiled option, then Click OK button.  Now it's simply a matter of "dragging and dropping" each .CSV worksheet onto a separate Sheet in your Master Workbook.


2. Select a blank Sheet tab in your Master Workbook


3. Click on the title bar of one of the .CSV worksheets so that it's selected.


4. Hold down the Control key and click on the tab of the selected .CSV worksheet.


5. Drag the tab (move your mouse) until it's positioned over a blank Sheet tab in your Master Workbook, and release.


6. Repeat steps 2 through 5 for each of the 9 files, until you have all 9 Sheets of your Master Workbook filled with data from all the .CSV files. Save your Master Workbook when you're done.


Once all your data is in your "Master" Workbook, you can begin to sort and select.  We suggest reading up on the Formulas, Functions, Charts in your Excel help or program Manual.


While we are not able to provide Technical Support for Excel, there are many excellent 3rd Party books available, including Que's "Microsoft Office 97 Professional 6-in-1" (1997 ISBN: 0-7897-0957-0) or "Microsoft Excel 2002 Bible"; John Walkenbach; 936 pages + CDROM; Hungry Minds, Inc; ISBN: 0764535838; US$39.99


See also

Perceptual Results