Common problems when importing or exporting respondents ADVANCED

This FAQ applies to surveys where you have chosen to enable survey access control and create a list of respondents.

Note:  Files larger than 10Mb cannot be uploaded to online surveys.  If your respondent list is very large, please split it into two or more files before attempting to import it, so that each file is less than 10Mb in size.

A note on CSV files

The respondent list is imported into and exported out of online surveys as a CSV (comma-separated values) file.

In simple terms, a CSV file separates every piece of information in the file with a comma.  A program that reads a CSV file knows that when it sees a comma, the next piece of information it encounters should be treated separately from the piece of information before the comma.

Online surveys knows that a comma means that it should move on to the next field in the respondent list, so when it reads a file with the following values:

mj1234,Pa55w0rd,mary-jane@poppleton.ac.uk

it knows that “mj1234” should go in the first field, “Pa55w0rd” should go in the second field, and “mary-jane@poppleton.ac.uk” should go in the third.

A CSV file only contains basic information separated by commas.  It can be created in a text editor (e.g. Notepad) but this can be a labour-intensive way of creating a file if there are to be thousands of records.  It is common to use a spreadsheet application such as Excel to create a CSV file.  This can make the initial process easier than using a text editor, but applications such as Excel can add extra information or change the formatting of certain fields when converting the file to and from the CSV format, which can have unexpected results.

We cannot offer support on the use of different software packages to create and manipulate CSV files.  We offer some guidance on this page in relation to common issues relating to the import or export of respondents.  This includes some general guidance on the use of CSV files, but this guidance is provided without warranty and with the proviso that it may not be suitable for all circumstances or all versions of the relevant software.

If you are not confident working with CSV files we recommend you contact your institution’s IT support team for guidance.

We are not responsible for the content of external websites or the availability of linked pages.

Common problems when importing or exporting a respondent list:

Import error message:  Invalid email

There are two common reasons for this error message:

1. There is a header row in your respondent list file.  This generally returns the message “Invalid email Email”.  Remove the header row and try again.
2. One or more email addresses is not in a valid format.  The error message will provide the first incorrectly formatted email address that online surveys has found (e.g. “Invalid email mary-jane@poppleton-com”).  A common error is the use of hyphens instead of dots.  Correct the incorrect email addresses and try again.

Import error message:  Line 1 has too many columns (xx) max is 15

Remove any information after the 14th column in your respondent list file (Column N) and try again.

If you receive the same message again, create a new, blank file and copy and paste the first 14 columns from the original respondent list file into the new file.  Save the new file as a CSV and try again.

Import error message:  Input format invalid.  No credentials supplied on line xx

This indicates that there is a blank row at the end of your respondent list.  Open your respondent list file, delete the row immediately after the last respondent record, save the file and try again.

If this does not work, copy the respondent list into a new file as follows:

1. Create a new, blank file in the program you used to create your original respondent list file.
2. Open the original respondent list file and select and copy only the rows that contain respondent information.  Do not copy the entire sheet, and do not copy any blank rows at the end of the list.
3. Paste these rows into the new file.
4. Save the new file as a CSV and try again.

Import error message:  Invalid username

This usually indicates that there is an error in the format of your CSV file.  If you are confident working with CSV files, you can use the following website to give you a better idea of what is wrong with your file:

http://csvlint.io

If you are not confident working with CSV files, we recommend you ask your institution’s IT support team for help.

Any other import error message, or import does not work but no error message is displayed

This can indicate that:

• One or more of your respondent list fields contains too many characters and/or contains invalid characters.  Please see our Help page on the components of a respondent list for further information on the limits that apply to each field in a respondent list.
• There is an error in the format of your CSV file.  Please either check your CSV file at the website http://csvlint.io/ or ask your institution’s IT support team for help.
• Your respondent list file is too large.  Files larger than 10Mb cannot be uploaded to online surveys.  If your respondent list is very large, please split it into two or more files so that each file is less than 10Mb in size.
• Your respondent list file is large and the system is extremely busy.  During busy times, uploading a large respondent list can take several minutes and it may appear as if nothing is happening.  Under normal circumstances the system will be processing your respondent list and the list will usually upload successfully if you leave the process to complete and do not make multiple attempts to upload your respondent list file in a short period of time.  Please wait up to ten minutes and then refresh the Distribute page to check whether the file has uploaded.

Foreign language characters are missing or corrupted

This is not usually a problem with online surveys.  This can happen when Excel converts the file to CSV or when Excel opens a CSV file and displays it for you.

The website below (at the time this article was posted) provides step-by-step instructions for using Excel and Notepad to create a CSV file that retains foreign language characters:

https://www.ablebits.com/office-addins-blog/2014/04/24/convert-excel-csv/#export-csv-utf8

Alternatively, try using Google Sheets, OpenOffice, LibreOffice or another application to work with your CSV file.

Numbers have lost their leading zeros or have turned into scientific notation/error text (e.g. 1.10581E+11)

This is not usually a problem with online surveys.  Some spreadsheet applications may corrupt long numbers and drop leading zeros when opening a CSV file.  Excel may also truncate numbers with more than 15 digits.

To resolve this:

1. Do not save the file if you have opened it and have found the numbers do not display as expected.  It is possible that the numbers still exist in their original state – if you save the file you may lose the original numbers.
2. Either:
   • Use a different application to work with your CSV file; or
   • Follow the steps below to import the file into Excel.  NOTE:  These instructions may not apply to all versions of Excel.
     1. Open a blank workbook in Excel.
     2. Go to the Data tab and choose Get External Data – From Text.
     3. Find your CSV file and click Import.
     4. Choose Delimited, then click Next.
     5. Choose Comma, then click Next.
     6. Select the column containing the numbers and choose Text.
     7. Repeat step 6 for all columns containing numbers, then click Finish.
     8. Click OK.  This should import the numbers ‘as is’, including any leading zeros and without any error text.
     9. Ensure that you follow this process every time you need to open the CSV file in Excel.