Migrating a Spreadsheet to a Form

This article describes how to import data from a spreadsheet to an ActivityInfo Form for Database Administrators.

Records are imported Form by Form. Therefore, as a Database Administrator, you first need to design the Form in ActivityInfo with the Fields corresponding to the type of data you wish to import. To find out what these corresponding Fields are, check the headings and the type of the data in each spreadsheet column you wish to import. When the Form and the Fields are ready, you can import the Records from the spreadsheet.

Example data ready for import from spreadsheet
Example data ready for import from spreadsheet

Supported spreadsheet formats

You can import data from any source that allows you to copy your data to the clipboard as a table. This includes:

  • Excel
  • Open office
  • Libre office
  • Numbers

Design your form

Before you can import your spreadsheet into ActivityInfo, you must add a Form that matches the structure of your data. To create a suitable Form for Records in ActivityInfo, follow these steps:

  1. Navigate to the Databases.
  2. Select the Database in which you want to create the Form.
    If you do not yet have a Database, create a new Database from our Template library, for example a Who's doing what and where (3W) Database. (If you choose to work from the template, then you can skip ahead to step 5.)
  3. Click Add Form.
  4. Name the Form, and add the Fields you need. In this example: Reference field for the “State/Region” column, Text fields and a Geographic Point field.

Import your spreadsheet into the Form

After you set up the Form in ActivityInfo, follow these steps to import data from a spreadsheet:

  1. Navigate to the Form you created.
  2. Select the “Import” tool from the Toolbar.
  1. In a separate browser window or tab, go to the spreadsheet that contains the data to be imported.
  2. Ensure that the data in the spreadsheet is meeting the formatting guidelines described in Preparing the Records for import (in the spreadsheet).
  3. Select the cells with data and copy (Ctrl + C) it.
  4. Navigate back to the ActivityInfo Form.
  1. Paste (Ctrl + V) the contents of the spreadsheet that you copied in step 8.
  1. Click Continue.
  1. Time to match and validate the Fields.
    In this example, one of the columns from the spreadsheet was not added to the ActivityInfo Form prior to the data import. The import process cannot continue until the P-CODE column (highlighted in yellow) is not resolved, even though all ActivityInfo columns are matched to a spreadsheet column.
  1. Click on an unmatched column (highlighted in yellow). In this example you cannot match the columns of the xlsx to the ActivityInfo columns, as you have not yet created the necessary Fields for that. Therefore click “Ignore this column” (resulting in partial data import) or click “Cancel” to start over.
Unmatched columns highlighted in yellow
Unmatched columns highlighted in yellow
Ignored columns are excluded from the import process
Ignored columns are excluded from the import process
  1. After resolving the inconsistencies, you can now click “Continue” in the upper right corner.
  1. Review the “Import summary” overview:
  • If everything is correct, click “Done.”
  • To go back to the matching of columns and validation of Fields part of the process, click “Back.”
  • Click “Cancel” to abort the import process, if necessary.

In this example, the Records are partially imported (as the 1 unmatched column was ignored in step 14), and duplicate Records are ignored. You can fix these after the import, for now you can click “Done” in the upper right corner.

Note: If you wanted to add new Records to the Form, but the Import summary shows that the Records are going to be updated rather than added as new, that means that the Records with the same Key field value(s) already existed in the Form. The system uses Key fields defined in the Form to recognize which Records are getting updated and which Records are added as new.

You can use the Importer feature and Key fields to update multiple Records in the same instance. For more information, see Edit multiple Records.

  1. Fix any outstanding issues.
    In this example, this would mean:
  • Importing the unmatched and thus ignored column of data (P-Codes), by going back to Form settings and add the corresponding Field type. Then going through the import process again.
  • Run the Duplicate scanner to resolve any possibly duplicated data.

If you encounter other issues, use the Troubleshooting importer-related issues article as a guide.

Check out the following articles for related information regarding:

Next item
Translation