Create, Collect, and Analyze Surveys and Web Forms

Survey Software from WISCO Computing
WISCO Survey - Convert Excel Files to CSV Data

convert excel to csv

Microsoft Excel Files

Survey responses are sometimes kept in Microsoft Excel spread sheets. This data must be converted so it can be imported into the WISCO Survey software. The Excel spread sheet may contain extra rows and columns with calculations that are not part of the original survey responses so they need to be removed from the imported information.

We have seen numerous examples of improperly formatted CSV files created by Microsoft Excel. Improperly formatted files can not be imported successfully into WISCO Survey. As one example, Microsoft Excel does not remove extra carriage returns when saving data as a CSV file. Properly formatted CSV files can not contain carriage returns in the middle of a data record. Only one carriage return is allowed per record, and that must be at the end of the record.

  1. Open the Microsoft Excel file. This file should be located in your project folder with your survey question file. This Microsoft Excel file has an .XLS filename extension.
  2. The Excel data will be displayed. The total number of rows and columns will be placed in the numerical spinners.
  3. Scroll the grid display to determine the last column and row that should be converted to the CSV file. Change the spinners to the respective numbers if necessary.
  4. Click the 'Modify Grid Size' button.
  5. Some proprietary data may use a value, for example -99, to indicate no response. These values representing no response need to be removed so proper calculations are made by WISCO Survey. Enter the value representing no response in the edit box next to the Find label. Leave the edit box next to replace empty. Click the 'Find and Replace Cell Contents' button.
  6. Repeat if necessary.
  7. Click the 'Create Converted CSV Data' button. The CSV data that is created can be viewed.
  8. Enter the name of the new converted CSV filename. It will be saved in project folder.
  9. Click the 'Save Converted CSV File' button.
  10. You're done.

Possible Problems

Survey Monkey adds non-printable characters in the first 8 or 9 cells of the second row - the ones with the question choices. Some CSV specific software refuses to read a CSV file saved by Excel. Change these cells in Excel to a space character, saving the Excel file with a different name without these null characters.

Resaving the Microsoft Excel file to a different name removes excess hidden Excel metadata in the original file. For example, one Excel file was originally 306 KB. Resaving with a new filename removed the Excel metadata, and shrank the file size to 282 KB.