Summary
Ensure you have the correct data model selected in the top right of the initial import form when you are uploading a spreadsheet (or downloading a template)
Make sure the "Match records to Companies" section in the form (if showing) is correctly mapped for the ID(s) you are using
Review and revise the "Map Source Columns to [Data Model] fields" section as necessary
Tooltip messages and error messages give you an indication of issues (or potential issues), so you can understand the situation and adjust as appropriate
Who is this article for?
Planhat users who are importing data via spreadsheet
Series
Importing data via Excel spreadsheet - troubleshooting ⬅️ You are here
Article contents
Introduction
This is a technical deep-dive article
Read on if you'd like to learn more about troubleshooting when importing data via Excel spreadsheets.
If you'd just like an introduction to this process, please refer to our main article for an overview of the key points.
The process of bulk-importing data by uploading an Excel spreadsheet should be very straightforward, but you may occasionally encounter an issue. Not to fear: here we will take you through what to check, tips, Q&As (questions and answers), and some worked examples, to help solve common queries.
Quick troubleshooting checklist
Does the spreadsheet you are importing include all the necessary data (including IDs and required fields)?
Is the correct data model selected in the import form (modal)?
Is the "Match records to Companies" section (if showing) mapped correctly?
Think about which column(s) in your spreadsheet contain a reference to the relevant Company, and map to the relevant field(s) as appropriate
Note that if the default mapping of "Company Id" column to "Id" (i.e. Planhat ID) is not being used, you should to clear it from here
If you are importing End Users and are matching records to Companies via email domains (rather than one of the three ID types), have you ensured that the email domains are listed in the "Related Domains" field of the relevant Companies?
Have you correctly configured the "Map Source Columns to [Data Model] fields" section?
Planhat automatically maps columns to fields where possible, but you should still review this and edit it if necessary
For each column, you can either exclude it from the import ("Don't import"), or map it to a Planhat field - you can even create new fields from here if necessary
If the "Import" button is grayed out, have you put your mouse over the button to view an explanation in the tooltip message?
Example shown below
Additional tips
Use the templates where possible
When you go to import a spreadsheet, after ensuring the correct data model is selected in the top right, you can click "Download template"
Although it's not obligatory to use these templates - you can use your own spreadsheets, e.g. created from exporting data from Planhat - we recommend that you use the templates to maximize your chances of success
Make use of the interactive "drill down" error messages
If you see an error message where some records were not imported because they were unmatched, you can click into the number (example below) to see the details of which records had the issue. This is particularly useful if you had some records fail and some records succeed
Click the image to view it enlarged
Make use of tooltips
If you see a symbol you're unsure about, or a button is grayed out (not clickable), you can put your mouse over the icon or button to see a tooltip explanation (examples below)
Questions and answers (Q&As)
Q: If a record fails to import part-way through the spreadsheet, will the remaining records also be attempted or will the import stop?
A: If a record (row) fails part-way through the spreadsheet, it won't stop the remaining records (rows) in the spreadsheet from being processed - e.g. see example screenshots below (all from the same test)
Click the image to view it enlarged
Q: I am seeing an error message like the following text - what does this mean and how can I fix it?
You have some (non critical) issues with the uploaded file. The file can still be processed but may result in some rows being ignored.All 3 record(s) could not be matched to a company and were skipped. Please check your mapping configuration.A: The wording "could not be matched to a company" indicates that the Company ID matching, configured at the top of the import modal (form), was set up incorrectly. Try again, but ensure that you have correctly mapped the relevant spreadsheet column(s) to the appropriate ID(s) in Planhat (or cleared it if you are using email domain instead). To learn more about Company ID mapping, see here and here
Q: When setting up the Company matching (Company mapping), do I need to use prefixes ("extid-" and "srcid-") to specify different types of IDs in the "Company Id" column?
A: No, this is no longer needed. Although using the prefixes won't stop it from working, now that we have the import UI with Company ID matching where you can specify pairs of spreadsheet columns and IDs, you can simply state the actual IDs (Source IDs and External IDs) in spreadsheet columns
Q: Why is there an orange exclamation mark in a triangle next to a column in the "Map Source Columns to [Model] fields" section when importing?
A: Combined with "Don't import" in the field dropdown, this simply means that Planhat was not able to automatically identify a field to pair with that column - you can mouse over the symbol to see a tooltip explanation to confirm. You can leave this as "Don't import", or select your choice of field from the dropdown menu
Q: Why can't I click on the "Import" button? Why is it gray?
A: Put your mouse over the "Import" button to see a tooltip explaining the issue - using this information, you should be able to solve the button and enable the "Import" button. For example, in the screenshot below, the issue is that multiple spreadsheet columns are currently mapped to the same field - you would just change or remove one of these mappings to fix the problem
Further details
Selecting the correct data model in the top right
When you open up the import modal (form) via the Federated Search box (the main Planhat search box in the top middle of your Planhat tenant), it will have a data model (e.g. Companies or End Users or Deals etc.) preselected in the top-right corner.
Before you download a template (if you will be using one), and before choose a spreadsheet file to upload (via "Choose a file..."), you should always take a moment to review whether the currently selected data model is the model whose records you want to import this time - if not, then use the dropdown menu to change it:
If you have the wrong data model selected in the top-right corner of this import form, then depending on the combination of model selected v. model uploaded, and also how you set up the column/field mapping, you will either:
find that the data uploads but to the wrong place (e.g. you could accidentally create Assets out of your End User data),
or the import will fail.
Ensuring Company ID matching has been configured correctly for your uploaded spreadsheet; examples using Related Domains
When you are importing records of data models other than Company, at the top of the import data mapping screen you will see a section for Company ID mapping:
The reason for this is that Company is at the top of the Planhat data model hierarchy, and so records of most other models need to link to a Company - e.g. each End User and Asset and Campaign etc. needs to be associated with a Company.
When you're creating new records, e.g. new Assets in the example screenshot above, you need to specify which Company each record should be linked to.
In the Company matching section as pictured above, you can choose to map between spreadsheet columns (e.g. "Company Id") and any of Planhat ID ("Id"), Source ID ("SourceId (CRM)") and External ID ("ExtId") - or you can clear the mapping and not specify Company matching here.
Circumstances where you don't need a Company ID mapped here:
If you are just updating existing records, rather than creating new records, then you don't need to include the Company reference, because the records being updated (identified by their own IDs) are already associated with Companies
If you are creating new End Users, you can actually map these records to Companies via email domains rather than needing to use one of the three ID types
If you are not using this Company mapping, it's important that you clear it, and don't leave it mapped as "Company Id column to Planhat Id field" as is loaded by default (like in the example screenshot above) - or the import will likely fail. Similarly, if you are using Company mapping but it's slightly different than the pre-loaded default (e.g. you are using the External ID rather than the Planhat ID in the "Company Id" column) adjust it to match your mapping, or it will fail.
Next we will go through a couple of examples of mapping End Users via email domains, showing potential issues and how to resolve them.
Worked example 1
Let's say we want to create new End Users via a spreadsheet upload, and we want to map those End Users to Companies via their email domains ("Related Domains" field).
We download the End User import template, and add in our End Users like so:
If we go to upload this in Planhat, the "Match records to Companies" section is pre-loaded like this as default:
If we don't adjust anything here, and we simply click the "Import" button in the bottom-right of the modal, the import will fail like so:
You have some (non critical) issues with the uploaded file. The file can still be processed but may result in some rows being ignored.
All 3 record(s) could not be matched to a company and were skipped. Please check your mapping configuration.
We can actually click on the number of "Unmatched" records (3 in this case) to see the details of which records failed and what the problem is. In this case, the records could not be matched to a Company.
Click the image to view it enlarged
The reason for this failure is that we left the default "Company Id" mapping in place, and so Planhat is looking in the "Company Id" column in the spreadsheet to try to map its values to Planhat ID to identify the relevant Companies, but in our spreadsheet that column has been left blank (as you can see in a previous screenshot).
The solution here is to remove the mapping for "Company Id". We can either click on its name and clear the cell ...
... or we can delete that whole line (screenshot below).
Now, when we import the exact same spreadsheet, it succeeds:
Worked example 2
It's important to note that if you are importing new End Users and mapping them to Companies via email domains rather than one of the three types of IDs, then the Companies need to have the email domains saved in the "Related Domains" field in Planhat in order for this mapping to work.
For example, let's say that this is our spreadsheet that we want to upload. We want to create one End User for the Company "Wrangler", and one for the Company "Tripactions".
We proceed with importing this spreadsheet, making sure that the "Company Id" column (which we are not using in this case) is not mapped in the import modal (form), as discussed in the previous example.
But in this particular case, when we attempt the import, we see an error message:
There are errors in the uploaded file, causing some or all rows to be ignored. Please check the import summary for errors, and re-import the rows after fixing the problems.
Error at row 3: companyId - Required Field.
The error in this case indicates that the End User in row 3 (the second End User) has had a problem.
We can see in the "Summary" (in the screenshot above) that actually the other End User was successfully created - and if we go to that Company (Wrangler) we can confirm that that End User is now there:
Although the error message points towards Company Id, we are not using Company Id specifically in this case - but the issue is indeed that the other End User could not be successfully matched to the right Company.
If we take a look at the Related Domains field for Tripactions, we can see that it was populated incorrectly, and so the email address phil.wang@tripactions.com could not be mapped to that Company via the email domain.
If we add the correct domain (tripactions.com) to the Related Domains for this Company in Planhat, and then try importing the same spreadsheet again (again ensuring that Company Id is not showing in the Company matching section at the top), this time the import succeeds:
(The "Not updated" record is the End User that was already successfully created.)
