desc : Add or update record fields from an Excel document
Winmore Administrators can access Data Imports in the Creation Studio. Use this feature to import Excel spreadsheets containing new or updated records such as Accounts and Contacts with fields such as Names, Phone Numbers, and Financial Values. For very large or advanced import functions such as importing hyperlink websites, activities, or documents, please contact your Winmore Customer Engagement Manager for assistance.
You’ll see a list of any data imports you’ve run previously, along with their status and results. To re-download any previously imported file, just click on the blue link. To create a new import, click on the green Create Import button.
Imports are run for one existing Record Type at a time. Select whether you would like to Insert or Update the records. These processes differ slightly so see your applicable section below.
Insert: Creates new records
To create new records with Insert, your file must have two columns:
- The record name, and
- The Record Owner’s owner_id.
The owner ID is a 7 digit number attached to the user. There are several ways to find the owner ID.
Finding Owner ID
Method 1: The owner ID can be found in a user’s profile page in the URL or next to their avatar.
Method 2: Create an Insights view (See this article) that includes the owners with their name and owner id from the system table. For larger data sets, this type of table would be ideal to export and reference to automatically have the owner IDs added.
Creating the import file
Once you ensure you have the two required fields, you can prepare your data table for import. This can include any other fields you wish to populate during the import.
In the example import file above, we’ve used the seven digit owner ID, the contacts’ names and columns for email and role. The fields must be included on the record type prior to uploading in order to populate. If a field does not exist (for example, role is not a field in our database) the information will simply not be mapped and will not import. If you would like a field that doesn’t exist to be added, talk to your company’s Winmore administrator
A Few Uploading Notes:
- If you’re uploading to a dropdown field, you will need to use the api names found in that field. For example, a field called Region with the options of North America, South America and Europe will likely have the api names: north_america, south_america, and europe. The system will reject any rows with “North America” and “South America” since this does not match the api name exactly.
- The header for each column in your import sheet is the API name of a field. This may not match the Display name. The API name is listed under the Field details.
- Due to security concerns for imported values, no field value (cell value in Excel) may start with -, *, +, or @
For columns which reference fields containing other Record Types (for example, the name of the Account on the associated Contact), the value for each cell in your import sheet must be the Lanetix ID (8 digit reference number) of the record you’re referencing.
Uploading the Prepared File
Once the file is prepped, choose the Record Type, the operation to Insert (if these are brand new records) and then “Upload File” and choose your Excel Workbook file with the extension .xlsx to continue.
When the system has loaded the file, it will appear at the top of your uploads list with the option to Resume or Discard. To map and upload the file, click Resume.
The next screen allows you to double check details and match any fields from your import file to your new records which aren’t automatically detected. Check to make sure that the time zone for any date information you’re importing is correct.
At the bottom of the screen, you will find a section for mapping your imported fields. Each column header in your imported Excel document is displayed as a value under the SOURCE column. In each row you can see a sample of the data in your document, and under the FIELD SELECT column, the Winmore field to which this data will be mapped.
In our example below, the “name” column from the import sheet has a Sample Value of Owen Grady, and that’s mapped to the Contact record field “name”. Review this mapping for each field. If it’s incorrect or not automatically mapped, click the magnifying glass to the right and select the correct field. When everything looks correctly mapped, click the green Review Import button.
When everything is mapped to the correct field, click the green Review Import button.
A confirmation screen appears with information about your pending import. Check that the details are correct, and if you’d like an email sent to you to confirm the upload. Some larger imports can take some time to process, so you may want to let it run and wait for the email confirmation in a few minutes time.
Now you’re taken back to the initial screen and your import will be listed at the top with its results to the right. Green checks for uploaded files, red flags for upload failures.
If there are any failures in the import, a link will appear here which you can click to view details.
Any errors will be detailed with an explanation of why they weren’t processed correctly.
Click the green Download Correction Sheet button to get an Excel doc which contains only the unprocessed rows for correction. Make these corrections and restart the import from the beginning.
Importing Special Fields
For special fields such as multi-select dropdowns and links using URLs, you’ll need to create an extra sheet in your import Excel workbook. Name this second sheet with the API name of the field you want to populate with your import.
Inserts work the same way, except there is no lanetix.id yet for the records you’re creating upon import. For this reason, another “key” value must be selected. Make sure that one column of unique identifiers is the same in the first sheet and any subsequent sheets, then select this column as the “key” in all sheets when mapping the import.
For URL fields, create a second sheet in your import file as in the example above. Name the new sheet with the API name of your imported field for automatic mapping. Use the lanetix.id of each record (or a unique key value from sheet 1) for each row. Then under the header labels “text” and “url” enter your link text and destination URL.