Overview
The Import Templates tool in Accounting Seed can be used to import multiple records for low volume imports. The Import Templates tool provides the ability to import both the header and lines at the same time, using a single file.
The Import Templates tool allows you to import any of the standard Salesforce objects, any standard Accounting Seed objects, as well as any custom objects.
For each type of object you would like to import records, the main steps include performing a one-time setup and mapping process. Once a file has been imported and mapped to the related fields on the object, the file can be selected and reused, as needed, without having to re-map fields. If the mapping changes or if columns need to be added or removed, you have the option to clone or create a new import template record, re-map the columns, and then import.
Only .CSV files can be imported into the Import Templates tool.
Best Practice: If you have Excel 2019 or higher, to avoid issues with special characters, save your file import as a .CSV file. This will automatically save the file as a .CSV UTF-8 file. For more information, refer to the following Microsoft Community article.
Import files with up to 200 lines of data can be imported using the Import Templates tool.
Important: For large volume data imports with more than 200 lines, use the Salesforce Data Loader app or the web-based Data Import Wizard tool. For more information, refer to the Import Templates using the Data Loader App and Data Import Wizard article.
Important: Before you begin the import process, verify that the object for which you want to import a template is already included in the Import Objects Picklist Value Set. To review the existing values and/or to add new picklist values, refer to the Add Import Templates Picklist Values section of the Winter “23 Configuration Changes article.
The Import Templates process includes the following:
- General CSV File Creation Guidelines
- Import Template Record
- Upload your File (Setup)
- Map your Fields and Preview your Mappings
- Upload your File (Import)
- Review Errors
- Update Records using Import Templates
Do you want to:
- Import Header and Line Records simultaneously
- Import Lines for Existing Header Records
- Import Header Records or Records for an Object without Lines
- Clone an Import Template
General CSV File Creation Guidelines
The Import Templates tool includes a Setup button, which is used to map columns and rows of data to appropriate fields on a record. Once the mapping process is complete, you can use the Import button to actually import the data.
Columns are field names on an object:
- Ensure that you have column headers in the .CSV file for all required fields on the object for which you want to import records.
- Add any optional column headers (field names) that you want to include.
Best Practice: Although column headers are not required, to help simplify the mapping process, a best practice is to add the column headers/field names (labels) as they appear in Salesforce. - If your .CSV file contains both header and line records:
- Be sure to add all required fields for both the header object and the line object. We recommend that you add the header columns first and then, the line columns.
- The .CSV file should also contain a column for which the records can be grouped by. For example, you might import a .CSV file with 20 Billings. Using this example, the Billing Name can be included as a column header for which the records can be grouped by.
Rows are records that will get imported
The following rules apply to the fields below when entered data on a row:
- Lookup values, such as Customer/Account, GL Account, Product, etc can be added using the Salesforce ID number or the full name as it appears in the org (organization).
Best Practice: Instead of using actual names, which might create import errors due to typos, missing spaces, duplicate values, etc., we recommend using the Salesforce ID numbers for Lookups. - To locate the Salesforce ID for a record, select the record to open the detail page. The URL in the browser will include the 15-18 digit alphanumeric identifier towards the end of the URL. For example, a Salesforce ID might look like this: a0K3b00000NQZ0uEAH. For more information, refer to the Locate the Unique ID of a Record in Salesforce article.
- Dates must be entered using the YYYY-MM-DD format.
- Checkbox values should be entered using True or False.
The Import Template record is where the object details and mapping information are stored. Once this information is created using the Setup button, you can use the same template record over and over to import the same kind of data for that object.
Import Template Record
Once the .CSV file has been created, if you do not already have an Import Template record created, you can create a new one using the New button on the Import Templates list view. When creating a new Import Template record, the following information should be included:
- Name - the import template name. This can be any name of your choosing.
- Detail Object - this is a drop-down list to select the object for which you want to import data.
Note: Remember that if your object is not listed in the drop-down list, you can add it to the Import Objects Picklist Value Set. For more information, refer to the Add Import Templates Picklist Values section of the Winter “23 Configuration Changes article. - Parent Object - if a Parent Object exists, select it from this drop-down list. For example, Billing Line might have been selected as the Detail Object. Therefore, Billing would be selected as the Parent Object. If no Parent Object exists, leave this field blank.
- Parent Relationship - if a Parent Object was selected, enter the API Name of the Parent. If no Parent Object was selected, leave this field blank.
Note: To locate the API Name, click the Gear icon and select Setup. Then, click the Object Manager tab. Search for the appropriate object. The API Name for each object displays in the second column. You can copy/paste the API Name into the Parent Relationship field. - File Has Headers - if the file that you are importing has both header and lines, click this checkbox.
After saving the Import Template record, you can now upload your .CSV file.
Upload your File (Setup)
Important: If you have questions/concerns about your .CSV file, before uploading the file, refer to the General CSV File Creation Guidelines.
Upon clicking the Setup button on the Import Template record, the Upload your File dialog box displays for uploading your .CSV file. As with other file imports in the application, you can click the Upload Files button and navigate to the file location. Or, you can drag and drop your file into the Upload your File dialog box. From this point, click Next to upload the file. The upload may take a moment to complete. Once the file is uploaded, you are ready to Map your Fields.
Map your Fields and Preview your Mappings
The Map your Fields dialog box allows you to map the header/index for the Target Object to the Target Field. If Lookups are included, the Lookup Type and Lookup Field can be selected. If you included a header record in the .CSV file, the Group By radio button will need to be selected. The Group By radio button displays to the right as the last column on the May your Fields dialog box.
The mapping process can be performed from one of the following two locations:
- The Import Templates tool, when you have completed the Setup and clicked Next.
- The Advanced Line Manager when you clicked Save & Map Fields.
Mapping from within the Import Templates Tool
Best Practice: Remember that although column headers are not required, to help simplify the mapping process, a best practice is to add the column headers/field names (labels) as they appear in Salesforce.
Any column header can be used to Group By, such as a name or number field. The column header can also be any name that is meaningful to you.
When selecting the Target Object, if a master-detail relationship exists, you want to be sure to select the appropriate object for the Header/Index fields. For example, in the sample screen below, the Billing header includes the Billing Number, the Billing Date, the Accounting Period, and the Customer. Therefore, the Billing object was selected as the Target Object for these fields. All other fields in the sample screen below are included in the on the Billing Line object.
In the sample screen below:
- Billing Number was selected as the Group By field
- Accounting Period is a Name field Lookup
- The Salesforce ID was used in the Customer field in the .CSV file for each record. Therefore, the Lookup Type is ID.
Note: If you used actual names in the .CSV file for the Customer column header, when mapping, the Lookup Type would be the Name field. - Date, Quantity, and Unit Price, which are not Lookups were selected
- Revenue GL Account and GL Variable 1 were selected with Lookups to the Name field
Note: Click here to view the above image in full screen.
At this point you can Preview your Mappings. Otherwise, click Create Mapping to map your data. When the mapping is complete, a message will display to indicate that your changes have been saved. Continue with the Upload your File (Import) section below.
Note: The Import Template record now includes your mapping. This template can be reused, as necessary to import data without having to re-map the information.
Mapping from the Advanced Line Manager
If you are using the Advanced Line Manager to import new lines, before importing, ensure that your .CSV file contains a column that represents the record header. This can be the record name or the Salesforce ID number of the record. Additionally, in the mapping screen, Group By must be selected for the related object column header.
Since you are importing lines into an existing record, the Target Object defaults (as in the sample screen below). This should not be changed. The columns from your .CSV file are listed as rows in the first Header/Index column on the mapping screen.
- Regardless of whether you use a record name or the Salesforce ID number in your .CSV file to represent the header values, the header for the record must be mapped to the header field on the line. For example, if the Recurring Payable Line displays as the Target Object on the mapping screen, the Target Field for the header row should be selected as Recurring Payable (AcctSeed_Recurring_Account_Payable_c).
- If your .CSV file has the Salesforce ID number to represent the header, select ID as the Lookup Type.
- If your .CSV file has the record name as the header, select Name Field as the Lookup Type and Name as the Lookup Field.
Important: When mapping the fields, the Group By radio button must be selected for the header row (as in the sample screen below).
Note: Click here to view the above image in full screen.
At this point you can Preview your Mappings. Otherwise, click Create Mapping to map your data. When the mapping process completes, the new template with the mapping information is now available from the Select/Create Template drop-down list.
Note: This object import template now includes your mapping. This template can be reused, as necessary to import data without having to re-map the information.
Note: The mapping process is complete, but your data has not been imported until you click Import CSV.
Click Import CSV to import the new lines. The lines will automatically display in the Advanced Line Manager as new lines (the sample screen below is on a Recurring Payable).
If you receive an error message, refer to the Review Errors section in this article.
Note: Click here to view the above image in full screen.
Preview your Mappings
At any time during the mapping process, you can click the Preview button to preview what has been mapped. Then, click Back to return to the mapping screen. A sample Preview screen displays below.
Note: The first 5 rows from your .CSV file will display in the Preview your Mappings window.
Note: Click here to view the above image in full screen.
Upload your File (Import)
Upon clicking the Import button on the Import Template record, the Upload your File dialog box displays for importing your data into Accounting Seed. You will be uploading the file that includes the mapping. As with other file imports in the application, you can click the Upload Files button and navigate to the file location. Or, you can drag and drop your file into the Upload your File dialog box. From this point, click Next to upload the file. The upload may take a moment to complete. A message will display to indicate that your data has been imported.
This Upload your File dialog box looks similar to the one for Upload your File (Setup), however, it is not the same one. As mentioned, this one is opened from the Import button. It is used to import records for a template that has already had the mapping process completed. If you have not completed the mapping process, this will need to be done before continuing.
Best Practice: If you need to add columns after completing the Setup and the mapping process, we recommend cloning the Import Template record. Then, complete the Setup and Import process.
Note: If you received an error message, refer to the Review Errors section.
At this point, you can access the imported records to review and continue normal processing. In the sample screens below, you can see the two Billings that were created (one with three lines and one with two lines just as in the .CSV file). Also, you can review fields, such as the Unit Price, which is the same as was on the .CSV file for each Billing Line. Review other fields on the Billing header and lines that were included in the .CSV, as necessary. Additionally, the system auto-generated the Billing Number and Billing Line numbers.
Note: Click here to view the above image in full screen.
Note: Click here to view the above image in full screen.
Review Errors
After completing the mapping process, if you receive an error or error messages when attempting to import, review the information in the table below to troubleshoot the error(s):
Note: If the Review Errors popup box displays, you might need to scroll to the right to review the error messages.
Error | Reason | Solution |
Cannot deserialize instance of date from VALUE_STRING | In the .CSV file, one or more record rows has the date value in an incorrect format. | Change the date to the YYYY-MM-DD format in the.CSV file, then re-import the file. |
Cannot deserialize instance of currency from VALUE_STRING | In the .CSV file, one or more record rows has the currency value (like Amount, Unit Price or Unit Cost) in an incorrect format. | Check the .CSV file to correct currency values so that they have amounts in the right format, then re-import the file. |
Cannot deserialize instance of double from VALUE_STRING | In the .CSV file, one or more record rows has a numeric value (like Quantity) in an incorrect format. | Check the .CSV file to correct numeric values so that they have numbers in the right format, then re-import the file. |
Field name with name <name in csv> does not exist.” For example: “AcctSeed__Account__c with name Expess Logistics and Transport does not exist. | Typos in name Lookups in the .CSV file. | For any Lookup values in the .CSV file that are saved as names, ensure they are exactly as in Salesforce, then re-import the file. Best Practice: Instead of using actual names, which might create import errors due to typos, missing spaces, duplicate values, etc., we recommend using the Salesforce ID numbers for Lookups. |
Id in upsert is not valid | There is a typo in an ID value in the .CSV file. | Ensure the Salesforce IDs in the .CSV file are exactly as in Salesforce, then re-import the file. |
Make sure your import template's mapping contains a field as 'Group By.' |
The ‘Group By’ radio button was not selected during the setup of the .CSV file or the 'Group By' radio button was selected for a field with the Target Field value set to ‘None.’ | From the Import Templates screen, click Setup again and upload the .CSV file. Then, select the ‘Group By’ radio button for a field that has been mapped to a Target Field (except with ‘None’ value) and then re-import the file. |
Required Fields are Missing. [<field name>] For example: Required Fields are Missing. [Next Billing Date] |
The .CSV file is missing a column(s) for a required field(s) on the Target Object. | Update the .CSV file to add the necessary columns for each required field, then re-import the file. |
id value of incorrect type For example: Recurring Billing: id value of incorrect type: Rec Bill. |
The selected ‘Lookup Type’ for the field during mapping does not match with the field type in the .CSV file. Example: the Recurring Billing field type is 'Name' in the .CSV file, but during mapping the 'Id' type is selected for this field. |
From the Import Templates screen, click Setup again and upload the same .CSV file. Then, change the ‘Lookup Type’ to the correct one and re-import the file. |
Update Records using Import Templates
The Import Templates tool can be used to update existing records.
Note: When performing an update to protected fields on existing records, the source documents must be unposted.
Refer to the General CSV File Creation Guidelines to create a new .CSV file. Otherwise, if an existing file was used to import the records, create a copy of the .CSV file. Include a column that specifies the Record ID (for example, the Cash Receipt numbers or the Salesforce ID) of the records you would like to update. In the sample screen below, the Cash Receipt records need to have the Receipt Date updated.
The required columns are the Record ID and any fields that you would like to update.
Once you have the .CSV file, if the records were initially imported using an Import Template, create a clone of the Import Template record. Otherwise, refer to one of the three options below to import the file based upon the type of update you would like to perform.
- Import Header and Line Records simultaneously
- Import Lines for Existing Header Records
- Import Header Records or Records for an Object without Lines
You will need to click the Setup button to create the mappings, during which you will need to map the ID column to the relevant Record ID. Then, click the Import button to perform the update.
Important: If the .CSV is not mapped correctly in the Setup, new records will be inserted instead of updated ones.
The sample screen below includes the updated dates.
Return to top
Import Header and Line Records simultaneously
- Create a .CSV file using the General CSV File Creation Guidelines section.
- In the .CSV file, repeat the header record values for each row/line that belongs to the same header. Additionally, make certain that one of the header columns is the one for which the records will be grouped by. In the sample screen below, Column A (Billing Number) will be the Group By field.
In the sample .CSV file below, columns A - D represent fields that are on the Billing header and the remaining columns E - I represent fields that are on the Billing Lines.
There are two Billings in this .CSV file, which are denoted by a simple grouping number (Billing Number 1 has three lines and Billing Number 5 has two lines).
Note: Once imported, the system will replace the Billing Number in the .CSV file with an auto-generated Billing Number.
Note: Click here to view the above image in full screen. - Save the file as a .CSV UTF-8 file.
Best Practice: If you have Excel 2019 or higher, to avoid issues with special characters, save your file import as a .CSV file. This will automatically save the file as a .CSV UTF-8 file. For more information, refer to the following Microsoft Community article. - Once the file has been saved, create an Import Template record.
- Navigate to Accounting Home and click the Setup tab. Then, under the Utilities menu, click Import Templates. The Import Templates list view displays.
- Click New. The New Import Template dialog box displays.
- Enter a name for the new template.
- Click the Detail Object drop-down list and select the appropriate object.
- Click the Parent Object drop-down list to select the related parent.
- Enter the API Name for the parent object.
Note: To locate the API Name, click the Gear icon and select Setup. Then, click the Object Manager tab. Search for the appropriate object. The API Name for each object displays in the second column. You can copy/paste the API Name in the Parent Relationship field. - If your .CSV file contains a header record, select the File has Headers checkbox.
- Click Save. The Import Template record displays with the detail page open.
- Click Setup. The Upload your File dialog box displays.
- Click Upload Files. Otherwise, drag and drop the file into the dialog box.
- Click Next. The Map your Fields dialog box displays.
- Map your fields based upon the data that is in your .CSV file. For example, select the Target Object based upon the field names that are in the header and line rows. In the sample screen below, the first four field names are Billing header fields and the remaining fields are Billing Line fields.
Since this file contains both header and lines, be sure to select the Group By radio button appropriate field name. For example, in the sample screen below, the Group By radio button is selected for the Billing Number.
Note: You can select any column header to Group By, such as a name or number field. The column header can also be any name that is meaningful to you.
If you have Lookup values in the name format, select the Name Field in the Lookup Type and Name in the Lookup Field column. If your file has Salesforce ID numbers, select ID as the Lookup Type.
Note: Use the horizontal scroll to scroll to the right to view all column headers.
Note: Click here to view the above image in full screen. - Click Preview to preview the mapping information.
Note: Click here to view the above image in full screen. - Click Back to return to the Map your Fields dialog box.
- Click Create Mapping. When the mapping is complete, a message will display to indicate that your changes have been saved.
Note: The Import Template record now includes your mapping. This template can be reused, as necessary to import data without having to re-map the information. - From the Import Template record, click Import.
- Import the file that includes your mapping. Then, click Next.
Best Practice: If you need to add columns after completing the Setup and the mapping process, we recommend cloning the Import Template record. Then, complete the Setup and Import process.
Note: If you received an error message, refer to the Review Errors section.
- At this point, you can access the imported records to review and continue normal processing.
Import Lines for Existing Header Records
- Create a .CSV file using the General CSV File Creation Guidelines section.
- When importing only the line records, set the first column to identify the header for importing the lines. For example, the sample screen below identifies two existing Billing headers (00000238 and 00000239). The first column (Billing Number) indicates the Billing for which the lines should be imported. The remaining columns are the Billing Line fields.
Note: The first column (header) is the field that will be used when you select the Group By column. You can use the Salesforce ID as the header or an auto-generated number field, if one already exists.
- Save the file as a .CSV UTF-8 file.
Best Practice: If you have Excel 2019 or higher, to avoid issues with special characters, save your file import as a .CSV file. This will automatically save the file as a .CSV UTF-8 file. For more information, refer to the following Microsoft Community article. - Once the file has been saved, create an Import Template record.
- Navigate to Accounting Home and click the Setup tab. Then, under the Utilities menu, click Import Templates. The Import Templates list view displays.
- Click New. The New Import Template dialog box displays.
- Enter a name for the new template.
- Click the Detail Object drop-down list and select the appropriate object.
- Click the Parent Object drop-down list to select the related parent.
- Enter the API Name for the parent object.
Note: To locate the API Name, click the Gear icon and select Setup. Then, click the Object Manager tab. Search for the appropriate object. The API Name for each object displays in the second column. You can copy/paste the API Name in the Parent Relationship field. - If your .CSV file contains a header record, select the File has Headers checkbox.
- Click Save. The Import Template record displays with the detail page open.
- Click Setup. The Upload your File dialog box displays.
- Click Upload Files. Otherwise, drag and drop the file into the dialog box.
- Click Next. The Map your Fields dialog box displays.
- Map your fields based upon the data that is in your .CSV file.
Since this file contains both header and lines, be sure to select the Group By radio button appropriate field name. For example, in the sample screen below, the Group By radio button is selected for the Billing Number.
Note: You can select any column header to Group By, such as a name or number field. The column header can also be any name that is meaningful to you.
If you have Lookup values in the name format, select the Name Field in the Lookup Type and Name in the Lookup Field column. If your file has Salesforce ID numbers, select ID as the Lookup Type.
Note: Use the horizontal scroll to scroll to the right to view all column headers.
Note: Click here to view the above image in full screen. - Click Preview to preview the mapping information.
Note: Click here to view the above image in full screen. - Click Back to return to the Map your Fields dialog box.
- Click Create Mapping. When the mapping is complete, a message will display to indicate that your changes have been saved.
Note: The Import Template record now includes your mapping. This template can be reused, as necessary to import data without having to re-map the information. - From the Import Template record, click Import.
- Import the file that includes your mapping. Then, click Next.
Best Practice: If you need to add columns after completing the Setup and the mapping process, we recommend cloning the Import Template record. Then, complete the Setup and Import process.
Note: If you received an error message, refer to the Review Errors section.
- At this point, you can access the imported records to review and continue normal processing.
In the sample screens below, the new lines were added to two existing Billing header records.
Note: Click here to view the above image in full screen.
Note: Click here to view the above image in full screen.
Import Header Records or Records for an Object without Lines
- Create a .CSV file using the General CSV File Creation Guidelines section.
Below is a sample file with only the header record.
- Save the file as a .CSV UTF-8 file.
Best Practice: If you have Excel 2019 or higher, to avoid issues with special characters, save your file import as a .CSV file. This will automatically save the file as a .CSV UTF-8 file. For more information, refer to the following Microsoft Community article. - Once the file has been saved, create an Import Template record.
- Navigate to Accounting Home and click the Setup tab. Then, under the Utilities menu, click Import Templates. The Import Templates list view displays.
- Click New. The New Import Template dialog box displays.
- Enter a name for the new template.
- Click the Detail Object drop-down list and select the appropriate object.
- Leave the Parent Object and the Parent Relationship fields none/blank.
- If your .CSV file contains a header record, select the File has Headers checkbox.
- Click Save. The Import Template record displays with the detail page open.
- Click Setup. The Upload your File dialog box displays.
- Click Upload Files. Otherwise, drag and drop the file into the dialog box.
- Click Next. The Map your Fields dialog box displays.
- Map your fields based upon the data that is in your .CSV file.
The Group By radio button does not need to be selected for this type of import.
If you have Lookup values in the name format, select the Name Field in the Lookup Type and Name in the Lookup Field column. If your file has Salesforce ID numbers, select ID as the Lookup Type.
Note: Use the horizontal scroll to scroll to the right to view all column headers.
Note: Click here to view the above image in full screen. - Click Preview to preview the mapping information.
Note: Click here to view the above image in full screen. - Click Back to return to the Map your Fields dialog box.
- Click Create Mapping. When the mapping is complete, a message will display to indicate that your changes have been saved.
Note: The Import Template record now includes your mapping. This template can be reused, as necessary to import data without having to re-map the information. - From the Import Template record, click Import.
- Import the file that includes your mapping. Then, click Next.
Best Practice: If you need to add columns after completing the Setup and the mapping process, we recommend cloning the Import Template record. Then, complete the Setup and Import process.
Note: If you received an error message, refer to the Review Errors section.
- At this point, you can access the imported records to review and continue normal processing.
Clone an Import Template
- Navigate to Accounting Home and click the Setup tab. Then, under the Utilities menu, click Import Templates. The Import Templates list view displays.
- Select a list view other than the Recently Viewed list view.
- Select the Import Template that you want to clone.
- Click Clone. The fields default from the existing record.
- Rename the cloned Import Template.
- Click Save.
- Complete the mapping process for the newly cloned template.
Comments
0 comments
Please sign in to leave a comment.