Importing Excel Data Files
As the administrator of your Intuvo account, you can upload additional external excel file types from other data sources. The following instructions will guide you through preparing the excel data file before uploading it to Intuvo. Not following these instructions can cause data to become corrupt or import incorrectly. If you at any point have questions - please contact your account manager.
Initial Review and Clean Up of Data Being Imported
Before you begin the work to prepare the data set for import, take the time to look through the data and ensure that you would like all data present to be imported into your Intuvo system. Your Intuvo system will do its part to catch any duplicates upon import, but note that there is a chance duplicate records can be created if the incoming data points do not meet the necessary threshold to flag a potential duplicate.
Required Steps in Preparing your Excel Data File for Import
1. A column containing a 'Unique Identifier' for each row in your spread sheet - Column Header: pointLog_fileName
A unique ID must be present in each row of the column named pointLog_fileName. Some software exports provide a GUID upon export like ACT and Encompass. If your export already has a unique identifier you do not need to add this ID - but simply rename the column to pointLog_fileName.
The Technical: When an excel list is imported - Intuvo breaks down each row into its own importable file. Each of these files must look unique so that Intuvo does not reject the import. Once Intuvo determines it is unique - it will then try to match to other contacts within your Intuvo database. Based first on emails address, then by names that seem similar within your database, addresses, loan numbers etc. TLDR - each row must have a unique identifying column called pointLog_fileName.
If you do not already have a unique filename, you can generate a GUID: Globally Unique ID or you can use a file name like loan number.brw either way the filename must ultimately be completely unique.
Excel Help: The following formula can be used to create a GUID:
=CONCATENATE(DEC2HEX(RANDBETWEEN(0,4294967295),10),"-",+TODAY())
1. Create a new column, and copy + paste the formula into the top cell.
2. Drag the formula box down to create a GUID for all rows. (pro tip: highlight the top cell containing the formula and double clicking on the bottom right corner of the cell will send the GUID to the bottom of your data set.)
3. Copy the entire column, right click and then paste special > value (see screenshot below). This step is required, as it will remove the formula from the text.
2. A column for 'File Date' - Column Header: pointLog_fileDate
A file date (column name pointLog_fileDate) must be selected for the incoming data based on how you want it to combine with the data current in your Intuvo database. If you backdate your incoming file it will not overwrite the current contact data in Intuvo - it will only add new data such as subject properties, previous loans, and any missing contact data. If you use today's date for the incoming file - Intuvo will then use your excel list as the most current and correct data and it will overwrite any fields in Intuvo with the newer data from this excel import. Keep in mind that if you are connected to your Loan Originator Software and receive regular updates - any current transactions will always become the most recent data.
3. A column for 'File Owner' - Column Header: pointLog_importFileOwnerID
Create a Column and make the column header pointLog_importFileOwnerID - this column should contain the name of the Intuvo user that this import should belong to.
NOTE: Make sure that the owning user of the contacts being imported has been a) created as an Intuvo user, and b) is mapped within your Intuvo administration.
4. A "Contact Group" (if appliable) to identify each contact in reporting, quickly move a group of contacts onto a campaign, or send them a OneTouch e-mail.
5. Ensure There is a Correct Column Header for Each Subsequent Column of Data being Imported
For Contact 2 or Spouses - Enter 'Contacts2_' before the column name you are adding. (Sample: contacts2_contacts_birthdate)
1- Column Name is what your header in your spreadsheet needs to be labeled.
2- Description is a friendly description of the field and its contents.
3- Look Up Options are fields in Intuvo with preset data values - you will see your options provided. You must make your data match the selections in Intuvo - otherwise, Intuvo will create new data selections based on your imported data. You should also try to match your incoming data to Intuvo - and only when necessary - create new data selections.
**Please note that only one value (like contact group) can be imported by the excel import**
4- Data Type tells you the type of field that it is in Intuvo.
Text- Free type text field.
Date- Date field, your importing dates must be mm/dd/yyyy to import correctly.
Look Up & Multi Look Up- is preset data values - your data should only be a value that is within this list.
Phone- Phone number format. All Phone numbers should be formatted like this 555-555-5555 - Extensions go in their own column.
Link -This is a very special type of data to be used to link up records. You will notice that when it comes to Agent, Escrow, Insurance or other vendor types of data - that you are provided with another entire set of column headers for linked records. You must use the appropriate column headers in order to merge in the linked contact. Example: Escrow Company Name = loans_escrwOfcrCID.contacts_company.
When In doubt - contact your Account Manager or [email protected]
Once all the above steps are completed.
Do a Final Review of your entire spreadsheet. Below is just a general checklist of areas that have the most common problems.
- Borrower Names must be split into separate columns for First Names and Last Names.
- Phone Numbers must be - 555-555-5555
- Dates must be - mm/dd/yyyy
- Multiple Look Ups Must be a value provided in the standard import.