Importing and Exporting Records

Importing Records

If you have existing data that you would like to include in a form—whether for use in a data module, or for the continuation of a dataset from your old website—you can import form records from an Excel (.xls) file. The Excel file should be structured like the form records table, where each column is a field and each row is a record. Be sure to include the name of each field as its column's heading (in cell A1, B1, C1, etc.). 

Begin by clicking the "import" button, located between the records table and the filter panel. An upload area will open up, into which you can drop your .xls file. Alternatively, you can click "select...," and use a file browser to find your file.

After you add your file, you'll be taken to the import screen. Here you'll a table with the data from the first ten rows of your spreadsheet (for reference). The column headings, taken from the first cell of each column in your file (A1, B1, C1, etc.) will be used in the import screen as labels for each column.

Below each column label, you'll find a selection box with which you can determine how the import will treat each column and its data. 

Any fields that have been added to the form will be available in the selection box, labeled with the format, "field ID: field label [field type]," e.g., "127: Last Name [text]" (note: if a field has a short label specified, the short label will appear instead of the regular label). If the label of one of the form fields that's assigned to your form matches a column heading from your file (as found in cells A1, B1, C1, etc.), the selection box for that column will default to the matching form field, and when the data is imported, the column values in question will be assigned to that field. If the no match is found for a column heading, you can select one of the form fields that's been added to your form and use it as the field for that column's data, or you can select one of the other available options:

a. Do Not Import

Omits the column and all its data from the import.

b. Create New Field...

Creates a new field, adds it to your form, and assigns the column values to that field. When you select this option, you'll have to specify an element type (text field, choose one, etc.), a datatype (text, integer, etc.), and provide a name (likely the same as the column label taken from the first cell of the column).

c. Assign to Existing Field

Adds an existing shared form field to your form and assigns the column values to that field. Find the shared form field you're after by filtering the list of shared form fields by field type.

d. ID [read only]

Advanced feature. Uses the column values as the ID numbers for each record. If this option is not selected for any column, ID numbers are automatically generated.

Below the selection box you'll see a checkbox labeled Primary Key. This is an advanced feature. A primary key is a criterion or a set of criteria that identifies a record as unique. Marking one or more field as your primary key allows you set options for duplicate handling. 

Having trouble importing your Excel file? 

For best results, follow these best practices.

  1. Your Excel file must be reduced to 1 sheet (tabs at bottom of spreadsheet).
  2. The top row of your spreadsheet should contain column headings (field names in AR5).
  3. All other rows (records in AR5) should be formatted as plain text (not number, date, etc).
  4. Your Excel file should be save as .XLS (not .XLSX).

Exporting Records

Clicking the export button, located between the filter panel and the records table, will export form data to an Excel (.xls) file named, "form[module ID].xls." Only fields that have been selected in the filter panel will be included in the exported data, but all available records will be included (not just the subset specified by the "records per page" selection box).

Release Notes

Release Notes

3/6/2015 12:00:00 AM EST
9 years ago

AR5 Users,

The latest update to AR5 is now here, bringing with it a number of improvements to our suite of tools for front-end design. Themes have been expanded to include more types of page content, and you can now create and define custom module styles that you can apply, on the fly, to modules on the page. Let's take a closer look.

Video Tutorials

These short video tutorials provide an introduction to Agency of Record.