Plan: Not Available: Intermediate Available: Pro or Elite
Users: Not Available: All users Available: FYI Admins or User Group permissions
Practice Management Source: Available: All sources
The CSV Data Import Automation Filter is available when creating Custom Automation Processes, allowing practices to configure one or more CSV files and import the data from the file into FYI. For example, practices can use the import to bulk create or update clients, disbursements, or jobs.
FYI Admins or users with access to Automations will be able to configure the filter to map each column in the CSV Import file to a specific Type, making it available as a Merge Field when adding steps to the Automation Process.
For practices on the Pro plan, it's important to consider the impact of Xero Practice Manager Rate Limits when using this feature.
This feature is currently in beta for the FYI Pro Plan. Learn more about beta registrations.
Preparing for the Import
Before data can be imported into FYI, you will need to complete the preparation steps below.
These steps will need to be repeated when importing different types of data, for example, creating different files when creating clients compared to updated jobs.
When creating the import file, there are several requirements as to how the file must be created to ensure that FYI can import the data successfully.
If these requirements are not met, data may not be available to be imported, or it will not be matched to existing clients or jobs.
| Requirement | Details |
| File Format | Must be saved using .CSV format |
| Row Limits |
There is a limit of 10,000 rows that can be imported at once. If there are multiple CSV files to be imported, the row total for both files combined must be under 10,000. Otherwise, users will need to re-run the automation to import all rows. If a single file contains more than 10,000 rows, the file will not be imported. |
| Column Headings |
The first row is assumed to be column headings, and displayed in FYI as:
|
| Columns for matching clients | When configuring the Automation Filter in FYI, the Match Clients During Validation option determines whether the imported data updates existing clients or creates new ones. To enable matching, at least one relevant column must be included to identify the client in FYI. Refer to Step 2 - Columns required to match clients below. |
| Job Number for matching existing Jobs | If data is to be imported for existing jobs in FYI and the Match Clients During Validation option is enabled in the Automation Filter, the Job Number column must be added to the file. Refer to Step 3 below. |
To enable client matching during import, your CSV Import file must include at least one of the columns below. When configuring and importing the data, you will be able to select which columns should be used to identify existing clients in FYI.
| Type | Notes |
| FYI Client ID | The unique identifier generated by FYI. This number cannot be edited. |
| Client Code | |
| Client Name | |
| Client Export Code | |
| Company Number | |
| Business Number | |
| NZBN | |
| Job Number | Matched with the client assigned to the job in FYI, and not the client specified in the CSV Import file. |
| Tax Number IRD Number VAT Number |
A match will only be found where the relevant tax number has been stored in full in FYI. For practices integrated with Xero Practice Manager, Tax Numbers synced to FYI will only display the last three numbers due to security reasons imposed by XPM, and will not be matched. |
To enable job matching during import, your CSV Import file must include the Job Number column.
The Job Number is displayed on the Job - Summary tab in FYI, within the Job Details section.
A Document View must be selected when configuring the CSV Import Filter in the Automation Process.
All files in the view will be processed, so additional filters may be required to limit the displayed documents. For example:
- Consider filtering by the Name to create a separate view for each type of import, e.g. Clients, Jobs, Disbursements.
- Filtering by Tags allows documents to be manually tagged to include or exclude the file for importing.
- Filtering by Date to import files for a specific date range, for example, when importing Disbursements for the current month.
To create a CSV Import View:
- Navigate to the Documents List.
- Update the Type Filter to select "CSV" only. This will ensure your view includes only CSV files.
- Add any additional filters as required. When running the Automation Process, all files in the view will be processed.
Note: When setting up the CSV Data Import Automation Filter, the Run Once Per Row option controls whether previously imported rows from the selected filter will be included in future imports.
-
Save the view using a descriptive name. You will need to select this View when creating the Automation process.
CSV Import Files must first be uploaded to FYI, to be displayed in the CSV View and become available to select when testing the Custom Automation process. There are a variety of methods available, depending on whether the file should be filed against a client or not.
-
Client-specific import files - use either Drag and Drop, File Import, or save a CSV file to FYI using the Office Add-in.
- Internal files - create a separate Knowledge Cabinet, for example, "Import files", and import the file using Drag and Drop.
CSV Data Import Process Filter
Once the CSV file has been added to FYI, the data can be imported using a Custom Automation process. For detailed instructions on creating a Custom Automation, refer to Setting up Automation Processes and Setting up Custom Processes.
To use the CSV Data Import Filter:
- From the Process - Details tab, click the edit icon next to Filter.
- Select the Filter Type of CSV Data Import.
- Select the Document View created in Step 4 above.
- Click the Sample Document dropdown to select the CSV Import File.
Note: If the document is not displayed, confirm the file has been saved in the CSV format and is displayed in the CSV Document View created earlier.
- Click Reset from CSV to display a preview of the data.
Note: If editing an existing Automation Process where the filter has been configured, clicking Reset from CSV will reset all fields back to default, including Run Once Per Row, and the Client and Job Matching from Validation fields. - Configure the fields as required. Refer to Filter Fields below for detailed information. If matching clients and jobs, ensure that at least one column has been mapped to the relevant Type for matching.
- Ensure the Run Only Once Per Row option is selected to avoid creating duplicate data. It's strongly recommended that this option be selected to ensure previously imported CSV file rows are not duplicated.
Note: A maximum of 10,000 rows across all files in the selected Document View will be processed by the automation. Ensure this option is selected before re-running the automation multiple times to import the remaining rows.
-
The Match Clients During Validation and Match Jobs During Validation options are used to indicate whether the imported data should be applied to existing data in FYI. Tick each option as required, ensuring at least one column has a relevant Type selected.
Note: If a valid Type has not been selected to enable matching, an error will be displayed, and the filter's Save button will be disabled.Matching ClientsWhen enabling the Match Clients During Validation option, one of the columns below must be included in the CSV Import file, and the relevant Type selected in the Filter.
If more than one column has been added to the CSV Import file, the remaining columns can be mapped to the String Type.
Note: If multiple columns have been mapped to the columns below, the data for each column in the row must match the same client; otherwise a match will not be made, and the row will not be imported into CSV.
Type Notes FYI Client ID The unique identifier generated by FYI. This number cannot be edited. Client Code Client Name Client Export Code Company Number Business Number NZBN Job Number Used to match with the client assigned to the job in FYI, and not the client specified in the CSV Import file. Tax Number
IRD Number
VAT NumberA match will only be found where the relevant tax number has been stored in full in FYI. For practices integrated with Xero Practice Manager, Tax Numbers synced to FYI will only display the last three numbers due to security reasons imposed by XPM, and will not be matched. Matching JobsWhen enabling the Match Jobs During Validation option:
- The Job Number column must be included in the CSV Import file.
- The Job Number column must be mapped to the Job Number Type in the Automation Filter.
- Both Match Clients During Validation and Match Jobs During Validation must be enabled in the Automation Filter.
The Job Number will be used for validating both the job, and the client assigned to the job, in FYI. Client columns in the CSV Import file can be mapped to the "String" Type in the Automation Filter, and will not be validated.
If a second validation is required to match the client in the CSV Import file, ensure the relevant client column has been mapped as per the Matching Clients section above. The data for both the client and the job in the row must match the same client; otherwise a match will not be made, and the row will not be imported into CSV.
- Click Save to save the configuration and return to the Automation Details to begin adding Automation Steps. Refer to Working with the CSV Import Filter below.
CSV Data Import Filter Fields
Filter Fields
| Field | Details |
| Filter Type | The filter used by the Automation Process. Select CSV Data Import from the drop-down. |
| Document View | The document view containing the CSV files to be imported. To be displayed, the "Type" column in the view must be filtered to CSV only. |
| Sample Document | Displays a list of CSV files in the selected view. Once selected, you will be able to display the columns in the file and map the data type to the column. |
| Skip Rows | Used to exclude a specific number of rows from the import. For example, if the file contains instructions in the first three rows, enter a value of 3. The first three rows will not be imported. |
| Reset from CSV | Used to display a preview of the data in the first row against the columns displayed below, so that the appropriate Type can be selected for each column. |
| Include | Tick to include the data in the column in the import, or untick if the data is not required. |
| Key | Displays column headings from the CSV Import file. These names will be used as Merge Fields when selecting the imported data in an Automation step. |
| Type |
Used to indicate the format of the data in that column, for example, Date. FYI will try to match the most appropriate Type, but users should review and update where required. Refer to Filter Column Types below for detailed information on each Type. If an incorrect Type is selected, a preview of the data will not be displayed when running the import, and the data will not be imported. For example, columns with a "Numeric" type selected cannot contain symbols, e.g. $3000. |
| Sample | Displays a preview of the first row from the CSV Import file, so the appropriate Type can be selected. |
| Run Only Once Per Row |
Controls whether the process will run only once per row in the file to avoid creating duplicates. It's strongly recommended to keep this option ticked for the CSV import. Note: A maximum of 10,000 rows across all files in the selected Document View will be processed by the automation. Ensure this option is selected before re-running the automation multiple times to import the remaining rows. |
| Match Clients During Validation |
Used to manage whether the import is matched against clients already in FYI, or new clients will be created by the import. This option is selected by default. When selected, at least one column in the CSV Import file must have a column Type used to identify the client in FYI, e.g. Client Name. If a relevant column has not been selected, an error will be displayed, and the Filter settings cannot be saved. To create new clients, this option should be unticked. |
| Match Jobs During Validation |
Used to manage whether the import is matched against clients already in FYI, or new clients will be created by the import. This option is selected by default. When selected, the Job Number column Type must be selected for one of the columns in the CSV Import file. If the Job Number Type has not been selected, an error will be displayed, and the Filter settings cannot be saved. |
Filter Column Types
When updating the Automation Filter, you will be prompted to select a Type for each column, used to indicate to FYI what kind of data is in the CSV Import file.
For information on selecting Types to match existing Clients and Jobs, refer to CSV Data Import Process Filter above.
| Type | Details |
| Business Number | Imported or matched to the Business Number or NZBN field for a client. |
| Client Code | Imported or matched to the Client Code field for a client. |
| Client Export Code | Imported or matched to the Export Code field for a client. |
| Client Name | Imported or matched against the client name in FYI. |
| Client Tax Number |
Imported or matched to one of the appropriate client fields, depending on the location of the practice: Tax Number |
| Company Number | Imported or matched to the Company Number field for a client. |
| Date | Data in the CSV file must be formatted using dd/mm/yyyy. |
| FYI Client ID |
Data is matched against the FYI Client ID for a client. The FYI Client ID is system-generated by FYI when the client is created, and cannot be imported or edited. |
| Job Category |
Matched against a Job Category created in FYI. The Category must have an Active status in FYI. |
| Job Number | Matched against the Job Number field in FYI, and the client assigned to the job. |
| Job State |
Matched against a Job State in FYI. New States cannot be created during Import. The Job State must be active in FYI. |
| Job Template |
Matched against a Job Template in FYI. The Template must have an Active status and the same Job Type. For example, you cannot select a Billing Job Template for a Workflow Job. |
| Numeric |
Numbers only. Decimals will be rounded to the next whole number, except when importing Disbursement Rates, e.g. 5.4 will be rounded to 5. Ensure the value does not contain currency symbols or commas e.g. $3,000 should be entered as 3000. Note: Credit Disbursements (eg -5.00 values) are not supported. |
| String | Used when entering numbers and text, for example, a Client Name or a Business Address. |
| Toggle |
Used for True/False or Yes/No drop-down fields, where data can be inserted using a Merge Field, for example, Custom Fields set to "Checkbox" or "Drop-Down". The value in the import file must be set to True, False, Yes, or No. |
| User |
Select a user in FYI. Must match the name of an active user in FYI. |
Working with the CSV Import Filter
Using Merge Fields to select CSV Import file columns
When configuring steps in a Custom Automation Process, Merge Fields let you control which FYI fields the imported data is inserted into. The Trigger Data section updates based on the Type selected in the Automation Filter for each column. For example, when configuring a Number field, only columns with a Type of Number will be available to select.
For detailed examples of using imported data in Automation Steps, refer to Custom Process Examples for CSV Imports, and Process Step Details for detailed information on all available Automation Steps.
Previewing the CSV Import Data
Data will only be imported into FYI when the Automation Process is tested (a single entry only) or when the process is run (all CSV files in the selected Document View, up to a total of 10,000 rows).
A preview of the import will be available when testing and running the Automation process. If any of the rows contain invalid data, click the link to download the Error Report. Update the CSV file to correct the data as required before running the import.
Troubleshooting Filter Error Messages
Click on the headings below for troubleshooting information on error messages that may be displayed when configuring the CSV Data Import Automation Filter.
Issue
When trying to import the CSV file, the Issue Report CSV file reports Invalid cells.
Cause
Invalid cells can be caused by:
-
Data format - The Data in the selected Column is not in the correct format. For example, selecting a Numerical Type column for a Date field, or a Date field being entered as "August".
- The Client has been archived - The client must be restored before they can be updated using the Automation process.
Solution
The data must be corrected in the original file or the Issue Report CSV before importing the file.
- If using the original file, remove rows that were successfully imported to avoid creating duplicates during the next import.
- If using the Issue Report file, for practices using the Tag functionality, you will need to remove the tag "Exclude-from-import" to ensure the Issue Report file can be used to re-import data. Files with this tag will not be able to be used.
Issue
When importing data from a CSV file, the user receives the following error message:
"Error: Unable to update CSV import mapping: fields still referenced in merge fields in steps would be removed."
Cause
This issue is caused by the column headers in the CSV import file(s) in the Filter not matching the column headers in the previously imported CSV file.
Solution
To resolve the error, practices can either:
- Update the CSV file column headers to reflect the same headers as the previously imported file, or
- Update the Automation Filter items by selecting Reset from CSV, to reflect the new column headings.