CSV (Comma Separated Values) Data can be imported into the WISCO Survey data file using an existing WISCO Survey question (SVP) file. If the WISCO question file does not exist, it must be created first before the survey responses can by imported. The survey question file contains many settings for each question, including the question type, column labels and text choices for the question, and the file space needed to save the response for each question. This is the reason why the WISCO Survey question file must exist before rows of CSV data can be magically inserted into a compatible WISCO Survey data file.

WISCO Computing also offers a service to convert your survey data to the WISCO Survey file formats.

The first few rows of a CSV data file contain information about the data. These rows are called header rows. The number of header rows, and the information they contain varies depending on the source used to create the CSV file. We have seen each header row contain question text, question text choices, column labels, and field names. We've also the question text and text choices in the same header row. There is no uniform standard for header rows in a CSV file.

Rows after the header rows are called data rows. Each of these rows is called a record. One record contains all responses for one survey. The total number of records (data rows) indicate the total number of surveys that were submitted. Some CSV files place additional identifying data at the beginning of each CSV record, before the response for the first survey question. Some CSV files place the additional identifying data at the end of the record. Each record is made of fields. Fields are separated by a comma. Each field contains one data item. There is no uniform way to represent data items.

WISCO Survey optionally stores this identification data (Extra Fields) at the end of each record in the DBF file. WISCO Survey uses one field to store responses for each survey question. Some WISCO Survey questions can have up to 25 parts, and all parts are stored together in one field. The total number of fields used by WISCO Survey equals the number of survey questions plus up to six optional fields. WISCO Survey will use six additional fields to keep identification data with the survey response data in the DBF file.

The industry standard DBF file has a limit of 127 fields in each record. The DBF limit of 127 fields per record is the reason for the limit of 100 questions per survey. Long surveys tend not to be completed, so we feel the 100 question limit is sufficient, especially with individual questions that can have up to 25 parts. The DBF file also has a limit of 4000 characters per record, Different question types reserve a specific number of characters to save a response. For example a response to a single choice question can be stored with one character, while a response for a text entry could go as high as 240 characters. You control how much space should be reserved for text responses. All other question types automatically determine the amount of space needed from the 4000 maximum characters. If you had a question asking for a Zip Code or Postal Code, it would make sense to limit the maximum entry to 10 characters (Zip+4 format). Canadian Postal Codes use a combination of 6 letters and numbers.

The first tabbed page 'Single Question Summary' shows your existing survey questions, and related question information.

The second tabbed page 'Preview CSV Data (Pro)' has 2 parts. If any data is already in this WISCO Survey DBF file, it is displayed at the top of the page. The top row indicates the Question numbers. The labels in the left column indicate the record number. The bottom half of this tabbed page is used to display the imported CSV file. This is filled in when you open a file by clicking the 'Open CSV Import File' button located on the button bar with the 'Help' and 'Close' buttons. The CSV files must use the .CSV extension.

The displayed CSV file must be formatted correctly for your import to be successful. Each line represents one survey record. All fields of each record must be on one line. We have seen numerous examples of CSV files saved with Microsoft Excel improperly formatted. 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. Sometimes Excel exports upper ASCII dash and apostrophes instead of regular characters. You may be required to edit your CSV file with a text editor like NotePad and correct any formatting problems. It may also be beneficial to work with a copy of your file instead of your original CSV file. If your original file was saved as a Microsoft Excel XLS file, the data must be resaved as a CSV file to remove a lot (but possibly not all) of the irrelevant metadata, carriage returns, etc. The CSV file should not have any empty rows.

Each line of the CSV file is either a header row or a data row. The entire contents of each line can usually not be displayed, even if you have the screen maximized. Marking the checkbox, will display the entire contents of the line the mouse if on top of in a yellow hint window. As you move the mouse to different lines, the CSV record in the window changes appropriately. If the check box is not marked, a hint window is not displayed.

The table on the left side is automatically filled with information from the WISCO Survey SVP question file. The table on the right is filled with information from the CSV file that you opened. The numbers in the left column 'CSV Col' indicate the field locations in the CSV records. These CSV Column numbers reference where the CSV data fields will be placed in the WISCO Survey data file.

The numerical spinners below the CSV Import Fields table provide information to coordinate where each data item in the CSV file should be placed with each WISCO Survey question number. CSV Header Rows contain information about the survey questions. They do not contain survey data. The numerical spinner should be set to the number of header rows. The number of header rows can be determined by viewing the CSV Import Fields table or the complete CSV file displayed on the previous tabbed page.

WISCO Survey uses the first field of each record to store data for the first survey question. Some CSV files place extra fields before the first question. The import process needs to know how many CSV fields Before Question 1 before processing the survey questions. Information from these extra CSV fields can be added to the end of each record in the WISCO Survey data file using the lower six numerical spinners. The CSV file may also contain CSV Fields After the Last Question that should not be added to a survey record. The CSV Hint Row spinner indicates the Header Row information to be displayed in the CSV File table on the fourth tabbed p[age. If you do not want to include the information from the extra fields, set the spinner to zero.

This button will not be enabled until a CSV file is opened after clicking the 'Open CSV Import File' button. Using the information from the numerical spinners, the CSV column numbers from the CSV Import Fields table are quickly placed in the Current WISCO Survey Fields table on the left side. Use this button instead of manually entering numbers in the WISCO Survey CSV column. If you do not want to import a field from the CSV file to the WISCO Survey data file, do not include the appropriate CSV Column number. Leave the appropriate cell empty.

The first table 'Choices from SVP File' includes question information from active compatible WISCO Survey question (SVP) file. The two table header rows indicate the WISCO Survey question number, and the topic phrases for each questions entered on the Design Menu - Edit Questions - Option/Values tab. The actual text choices for each question are displayed in the regular cells of each column.

The second table 'Choices from CSV File' has two header rows. The first header row includes the question number. The second header row displays a hint determined by the CSV Hint Row numerical spinner from the third tabbed page. This hint is filled in when the Get Choices from CSV File button is clicked. Both tables can be scrolled from left to right to show text choices for all survey questions.

When this button is clicked, WISCO Survey reads each record of the CSV file to determine possible choices that should be available for each survey question. These choices are placed in the second table 'Choices from CSV File'. below each question number. The survey question file is not changed by any text choices displayed in this table after this button is clicked. If all choices for a question are numerical, WISCO Survey will sort them in numerical order. This table is used to help diagnose possible import problems.

If the number of text choices for a survey question differs from the top and bottom tables, a warning label will be displayed, and all cells in the question column will be colored aqua for easy identification. Scroll the tables to find the particular survey questions with a potential problem for missing text choices. Having extra choices in the active survey file (top table) will cause no problems. The extra choices can not be inserted on this screen into the survey question file. If extra choices are found in the bottom table, they must be added to the regular survey questions on the Design Menu - Edit Questions selection. Then return to this screen. Numerical spinner settings are remembered. You will need to reload the CSV file, and click the 'Get Choices from CSV File' button again. These extra steps were included to make sure the WISCO Survey question file is not accidentally corrupted, and to allow possible question type changes that may occur when the total number of text choices changes.

The table at the bottom of this screen displays the column labels from the active WISCO Survey SVP question file. This information may be helpful when determining how to import data for questions with column labels.

The program used to create the CSV file should output the CSV data with a consistent method. The consistent method will vary from program to program. For example, a very simple survey question could ask 'What is your favorite color?' with choices A. red, B. blue, and C.yellow. The data could be output a number of valid ways. For example, if the chosen choice was blue, the data could be output as the letter choice 'B, the actual text 'blue', or the number 2 representing the second choice in the list. If the person did not make a choice, the data could be output as blank, represented by a space, represented by -1, or a letter out of the range of choices. There is no uniform standard for data content in a CSV file.

Examine the data in the CSV file by using the table on the third tabbed page or the display on the second tabbed page. For example, look how single choice survey question types are displayed, then select the most appropriate button. Examples are listed in the previous paragraph. WISCO Survey tries to make the best decisions based on the choices you make. Obviously, settings for question types that are not in your active survey question file will be ignored.

This button will not be enabled until a CSV file is opened after clicking the 'Open CSV Import File' button. When this button is clicked, WISCO Survey uses the information contained in the WISCO Survey question file, CSV file, and numerical spinners to quickly add the CSV data to the WISCO Survey DBF data file. A progress label is displayed below the button during the import process.

The displayed CSV file must be formatted correctly for your import to be successful. Each line represents one survey record. 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. You may be required to edit your CSV file with a text editor like Notepad and correct any formatting problems. It may also be beneficial to work with a copy of your CSV file instead of your original CSV file.

The CSV file can only be in use by one program at a time. If the file is being used by Microsoft Excel, and you attempt to open the file with WISCO Survey, you will get an error message I/O Error 32 indicating a sharing violation. Either the CSV file is opened by another application, or a previous application has not released its lock on the file. Obviously close the other software program to eliminate the problem.

The Microsoft Excel file can be converted to a CSV file by either WISCO Survey menu selection Convert Excel File to CSV, or saved as a CSV file by Excel. If you need to modify the CSV file further, you can use software that works exclusively with CSV files, or using Windows NotePad with Word Wrap turned off (Format Menu). The CSV file should only have header rows and data rows. Remove any formulas that may be located at the bottom of the CSV file. If a common negative value was used to indicate no response, place your curser at the start of the first Data Row, and perform a Global Replace of the common negative value with a single space character. Then save your modified CSV file.

1. Open the SVP Survey Question file. Question information is displayed on the 'Single Question Summary' tabbed page.
2. Click the 'Open CSV Import File' button. The file data is displayed at the bottom of the 'Preview CSV Data' tabbed page.
3. Click the 'Assign CSV Fileds' tab The left side displays information from your existing SVP question file. The right side displays information for the first 5 rows of the CSV file. The spinners need to set to associate the CSV data with the proper survey questions. After setting all CSV spinners, click the 'Auto-Fill CSV Column' button.
4. Click the 'Text Choices' tab. The text choices for each survey question are displayed. This information is from the SVP file.
5. Click the 'Import Field Types' tab. The 4 radio button selections determine how data from the CSV file will be imported into the DBF file.
6. Click the 'Save Converted CSV Data' button to perform the import of CSV data into the DBF file.