Importing from spreadsheets / CSV

Importing from spreadsheets / CSV

agileBase allows data to be imported from CSV (Comma Separated Variable) files. Excel, OpenOffice and other spreadsheets can save data in this format.

You’ll need MANAGE privileges on the table you wish to import into. Select that table from the ‘Build’ section, scroll to the bottom of the data and click the import link to start.

Data can be imported in two ways.

• insert all new records
• update existing records

Inserting new records will create a new record in the database for every line in the CSV file. Updating existing records will attempt to match each line in the file with an existing record and update all field values from the CSV. This can be useful when merging in data from an external source, or exporting, altering with a third party system and re-importing. Another use can be updating one field based on another – you can export, update the fields as necessary and re-import.

Updating existing records is particularly useful when records in other tables depend on the data in the table being imported into, so you can’t delete the data.

In either case, the CSV file must contain fields in the same order as they are in the table being imported into. The column titles don’t need to be the same though, it’s the order which is important.

Updating

When updating records, a key field must be selected to match records up. This can be any field that has the ‘unique’ property set. Alternatively the internal agileBase rowID can be used. This field is included as the first field in any data export.

Sometimes, admins will want to do an export -> modify spreadsheet -> re-import workflow. This is straightforward:

1. Export from the admin interface by clicking the table containing the data you want to modify (under ‘Build’) and clicking the export button in the top toolbar
2. Open the spreadsheet, making any modifications you need. The first column will be the internal ID for each record, keep this, it will be needed when re-importing
3. Blank out (remove content from) any columns which you haven’t updated. Otherwise if people have updated them since your export, you will overwrite their changes
4. Re-import using an ‘update’ type import as above. Select ‘use internal agileBase row IDs to identify records’

Notes

For a file field, please leave an empty column in the spreadsheet in that position. If you wish to do a bulk upload of many images, please contact support.

For separator and cross-referenced fields, don’t include a column as they contain no data.