Looking at data in Microsoft Excel
Exporting directly to .xlsx format
If you plan on looking at your form data in Microsoft Excel, SurveyCTO allows you to export directly to .xlsx format. This will help you avoid all the potential issues you may have when importing .csv data into Excel (see below). In addition, exporting directly to .xlsx format offers other benefits for Excel users:
Values will be formatted using the proper data type. For example, responses from text fields will always be formatted as text, so if you're collecting phone numbers using the numbers_phone appearance, you don't have to worry about Excel automatically removing plus signs (+) or zeroes (0) from the beginning of a phone number. Please note: the format of select_one, select_multiple, calculate, and calculate_here fields will depend on the actual responses—if all response values for the field are numeric, the exported responses will be formatted as numbers, otherwise they will be formatted as text.
When exporting to LONG format, the responses from repeat groups will be exported as separate sheets within the same Excel workbook (instead of separate .csv files). See the topic on understanding the format of exported data for more details about LONG format.
To export to .xlsx instead of .csv in SurveyCTO Desktop, go into Export options (available as you configure an export, or via Workspace settings), and choose .xlsx (Excel) in the Export to the following file type section. If you're downloading data directly from your server console's Export tab, just select .xlsx (for use with Excel) as the file format before clicking Export files.
Importing .csv data into Excel
By default, SurveyCTO exports data into .csv files, in a comma-separated-value format supported by just about any spreadsheet, database, or statistical analysis software. Since this is the default setting, many people export to .csv and then attempt to open it in Microsoft Excel. You can certainly review, clean, or even analyze your data in Excel. However, please note the following:
If you double-click on a .csv file, most systems will default to opening the file in Excel. In this case, Excel will import the .csv file based on a series of assumptions, most of which will not alter your data. But it will assume that the characters in the file are Windows or Mac characters as opposed to Unicode characters, so it will mess up accents or foreign scripts.
The safer way to import a .csv file is to first open Excel and then choose either File...Import or Data...Get External Data...Import Text File. You will want to specify that it is a .csv file, that it is comma-delimited, and that its "file origin" or "encoding" is Unicode/UTF-8. You will then get the opportunity to preview the columns and override the cell formats used for each column. Finally, when it asks you where to put the data, there will be a Properties... button that you can click to see options for "refresh control": whether to re-import the data whenever you open the Excel workbook and whether to re-prompt for the source .csv file every time. Once you import this way, you can essentially re-import at any time by clicking Refresh on the Data tab.
Sadly, when you import data using Excel's import wizard, it gets confused when it sees line-breaks inside cells – which will be a problem for you if anybody ever presses Enter when entering a text response into one of your survey forms. Excel will end the row at the line-break and essentially break one row of data into multiple rows. To avoid this, there is an option in Desktop's settings to replace line-breaks in exported .csv files with some other character (like a single space).
Excel will sometimes convert things that aren't dates into dates. For example, say you have an ID number like "10-11-12": Excel will convert it into a date, encoding it internally (and saving it!) as the number of days between Jan. 1, 1980 and Oct. 11, 2012. Obviously, that is totally wrong. To prevent Excel from corrupting your data in this way, you need to take care when assigning the formatting of your columns on import: for ID numbers and other things that might be mistaken for dates, "General" is not a safe choice.
If you want to edit your data or make notes in new columns, you are better off configuring Desktop to directly merge incoming data into an existing Excel workbook, rather than exporting to .csv format and then re-importing into Excel yourself. See this help topic for more on merging directly into Excel.
