An Update existing import changes records that are already in Optio, for example the residence of a group of employees or the external ID on a set of transactions. Each row in your file is matched to one record through an identifying column. The import then writes the columns you matched and leaves every other field as it is.
You start an update from the Imports directory, on the card for the type of record you want to change. This article explains how to change only what you mean to change.
⚠️ The one rule to remember: every column you match is written for every row in your file. If a cell in a matched column is empty, the stored value for that record is cleared. To leave a field unchanged, do not match its column: ignore it in Match columns, or remove the column from your file before you upload it.
Before you start
- Read How to import data for the basics of the flow.
- Know which fields you want to change, and for which records.
- Have the identifying value for each record in your file, for example the company email for each employee.
How records are matched
Each update import has one identifying column. The template marks it with a note saying the column must be matched. If a row's identifying value does not match a record in your company, that row is not updated.
| Import | Identifying column | Good to know |
|---|---|---|
| Employee, Update existing | Company email | The company email identifies the employee, so an update cannot change it. |
| Transaction, Update existing | Transaction ID | All other columns are optional in an update. Match only the ones you are changing. |
| Other update imports | See the template | See the template |
In an update, a column marked Required only needs a value in every row if you match it. The only column you must match is the identifying column.
Update one or two fields for many records
This is the most common case, for example adding an external ID to a set of transactions.
- Prepare a file with two kinds of columns: the identifying column, and the columns you want to change. You can start from the template or from an export, and delete every other column.
- In the Imports directory, select Update existing on the card you need, for example Transaction. Your file browser opens.
- Select your file.
- In Match columns, match the identifying column and the columns you are changing. Ignore every other column. If your file has many columns, ignore them all first and then match the few you need.
- In the review step, check that the only columns shown are the ones you mean to change, and fix any marked cells.
- Select Finish and confirm.
Example. You want to add an external ID to 40 transactions. Your file has three columns: Transaction ID, External ID and Comment. You match Transaction ID and External ID, and you ignore Comment or remove the column before upload. The 40 transactions get their external ID, and their comments stay as they were.
Update different fields for different records
Sometimes one employee needs a new address, another a new bank account and a third a corrected tax ID. Because every matched column is written for every row, a file where each row fills in different columns would clear the empty cells.
There are two safe ways to handle this:
- Split the file. Run one update per group of fields, each with only the rows and columns that change together.
- Start from current values. Export the records first, so every row already holds the current value for every column. Edit only the cells that should change, then import the whole file. Unchanged cells are written back with the value they already had. Clear a value on purpose
To remove a value, for example a mailing address that no longer applies, match its column and leave the cell empty for that record. Leave the column unmatched for every record you do not want to clear.
Check what changed
When the import finishes, the import details page shows how many records were updated, failed or ignored. To see every value the import changed, select Download and then Download changes. The file shows the previous values next to the new ones. See How to review an import and download its changes.
Frequently asked questions
Q: Can I undo an update?
A: No. Download the changes from the import details, put the previous values back into a file with the identifying column, and run the same update again with only those columns matched.
Q: Why did a field I did not mean to change become empty?
A: Its column was matched and the cell was empty. Download the changes to find the previous value, then run a correcting update.
Q: Do I need to fill in every required column to update one field?
A: No. Match the identifying column and the fields you are changing, and ignore or delete the columns you do not need before uploading.
Related articles
- How to import data: the import flow from start to finish.