Importing records from a spreadsheet into a form: CSV step by step

Most organisations do not start from nothing. Before the online form there was a spreadsheet: a list of members, last year's registrations, a register kept by a colleague. Typing it all in again would take days and introduce mistakes. A better route is to bring the list in as records, so that it joins everything collected afterwards in one place, with the same fields, the same rules and the same analysis.

This article walks through importing a CSV file into a Pulseform form, what is checked on the way in, and what to do with rows that do not pass.

Why CSV

CSV (comma-separated values) is the plain-text form of a table. Every spreadsheet program can save one: in Excel, choose Save As and pick a CSV format. It carries only the data, with no formatting, formulas or several sheets, which is what makes it safe to exchange between systems. Pulseform's importer takes a file with the .csv extension up to 10 MB.

If you work in Arabic, one thing is worth knowing. Excel's "CSV" is not always saved in the same text encoding: on an Arabic Windows machine it is often Windows-1256, and "Unicode text" is another format again. Read the wrong way, Arabic answers turn into unreadable characters with no warning. Pulseform's importer is built to recognise those common encodings and convert them, so an Arabic list should arrive as Arabic. Even so, open the first records after your first import and check that the Arabic reads correctly.

Step by step

1. Open the form's records page. Under Export & Import, choose Import Submissions (CSV).
2. Download the template. It has the right column names for this form. This is the step that saves the most trouble: the importer matches your data to the form's fields by these names.
3. Fill it in from your list. Copy your data into the template's columns, one record per row, and keep the column names exactly as they are.
4. Save the file as CSV and upload it.

What is checked

When you upload the file, the dialog shows a tick box, Apply Data Validation (Recommended), switched on by default. Leave it on. With it on, each row is checked against your form's fields before it is saved, and a row that breaks the form's rules is skipped. A row can fail for reasons you already know from the form itself:

• a required field is empty,
• a number is outside the range you set,
• a date is outside the allowed dates,
• an answer does not fit a pattern, for example a phone number with a missing digit.

Unticking the box skips these checks. That is meant for very large files where speed matters, and the price is that bad rows come in with the good ones. For an ordinary list, leave it ticked.

The import is also a good moment to find the bad data hiding in an old spreadsheet: the phone numbers with nine digits, the ages of 200.

The report, and fixing the rows that failed

The import runs in the background, so a big file does not hold up your screen, and you are notified when it finishes. The result tells you how many records were imported, how many rows were skipped, and lists the first problems with the reason for each, so you do not have to hunt for them.

For each skipped row, the reason tells you what to correct: fill in the empty required cell, fix the number, correct the phone number. Then take care over one thing: import only the corrected rows, in a new file. The rows that passed are already saved, so uploading the whole file again would add them a second time.

A sensible habit is to try the import with a handful of rows first. Read the result, fix whatever it shows, and only then upload the full list.

Before you import

• Clean the list first. Consistent spellings matter: if a column of regions says "North", "north" and "North " it will be three values to anything that counts them. Choose one spelling for each.
• One header row, no empty rows, and no extra sheets or notes in the file.
• Keep the template's column names unchanged. If you rename a column, the importer cannot match it to a field.
• Check your limits. Imported records count against your limits like any others, including the 100-response allowance of the free trial.

Things to know

• If the form uses verified participation with locked records, importing is disabled once the first answer arrives. Import before the first answer, or choose another way.
• The form's rules are what check each row, so set them before you import, not after.

After the import

The imported records sit beside the ones collected by link or by your field team. You can filter them, see the statistics, and compare one field with another in Comparative Analysis, exactly as you would for any record. And you can export the combined set whenever you need it elsewhere.

In Pulseform

See view and export form records in the guide for the import steps, and building a form and its field rules for the rules that each row is checked against.