Import (Names and Addresses)
This utility is designed to allow you to…
- Initially set-up your client, insurer and other address sets by importing data from an XLS or CSV file (i.e. a document containing data fields separated by commas) from some other system or Microsoft Outlook
- Re-import client, or other address set data, having previously exported it from Durell to an Excel spreadsheet for modification (e.g. for group scheme data updates)
- Merge all of the data held by two Durell databases (e.g. to merge the Durell systems used by different branches of a single organisation)
Before You Start – Tips Regarding CSV & XLS Files
CSV files can be loaded into Excel and then re-saved as XLS ones, so you can use this technique to switch between the two file formats. Importing from an Excel file (i.e. the “.xls” type) is far more reliable than a “.csv” one. Furthermore, to import reliably from an Excel spreadsheet you should always ensure that…
- The spreadsheet has been re-saved as an Excel spreadsheet (i.e. with an “.xls” extension after the file name)
- There are no rows below the main data containing totals
- There is just a single row at the top with column headings
Importing from Outlook
This is described below, but refer to Diary, Import from Outlook to see how to get the data out of Outlook in the first place.
Import from a Excel Spreadsheet (XLS) or Comma Separated Variables (CSV) File
To load a file, for example, into the “Business Clients” address set as shown below, starting from Durell’s “Main Menu”…
- Click the “Set-up” button
- Double-click “Import” in the menu list on the right of the Set-up screen
- Click the option to “Import data from a quote comma delimited file or Excel spreadsheet”
- Click the “Browse” button, then on the “Import” dialogue ensure the “Files of type” dropdown is set to “Excel (*.xls)”
- Locate the XLS (or CSV) file to be imported
- Click the “Connect” button to see whether Durell can read the located file
- Tick or clear the box “First row has field names”, accordingly
- Use the “…to address set” drop-down to locate the target address set, such as “Clients, Business” (or use “New” to create a new address set)
- Use the first yellow drop-down in the “Preview” window to select where the first field should go in Durell
- Repeat this until all data has been designated a recipient field in Durell
- Click the “Import” button to load all the data from the XLS file into Durell
- Optionally click the “Save setup” button if you’d like to retain the field designation for future use (i.e. with the same import file)
- Click the “Close” button
Importing Advisers and Leads - and Setting a Default Adviser and Lead for Each Client
This importing is done in just the same way as clients, above, but note...
1. When importing Advisers and Leads you should pass their reference codes, comprising 4 characters for advisers and six for leads, to the Durell field called "Initials" (e.g. Adviser Frank Smith might have a ref code of FRSM, or FSMI, etc, which should go to the "Initials" field. Do not put this code into either the field called "Adviser" or the one called "Lead".
2. When importing Clients you should pass their default Adviser and Lead's reference code (if they exist) to the Durell fields called "Adviser" and "Lead", where these codes should match Adviser and Lead ref codes in your Durell database (n.b. it's better to leave these fields blank than to pass incorrect ref codes). Do not use the Durell field called "Initials" for clients (see Advisers & Leads above), as this field is only for setting-up Advisers and Leads.
Re-importing Client Data
In order to re-import data you must first use the facility in Durell to export to a spreadsheet (see Documents, Spreadsheet, Output), ensuring that you include the unique client identification field, called “Record” in the “Client Details” table, as shown below at the top of the “Selected” fields. Then after modifying the exported records in Excel, to re-import the data you simply use the standard facility to import from an Excel or CSV file, described above, ensuring that you include the “Record” field so that it gets correctly re-located.
Import Data from Another Durell Solutions Database (i.e. Merge two Durell Systems)
Use the “Import data from another Durell Solutions database” option to load names and addresses from, for example, another Durell user’s system…
- Click the “Browse” button to find the location of the other system’s database (bound to be called “imw-data.mdb”)
- Use the “…from address set” dropdown to select the source address set in the other system
- Use the “…to address set” dropdown to select the target address set in your own system
- Tick the box to "Import ALL client and policy data", or leave it clear for just client names and addresses
- Click “Import”
- Click “Close” when all required address sets have been loaded
The above routine should only be used once for each set of data. If the same data is imported more than once then every record will be re-created in the recipient database with a new record number. So for example, if you were to import a database that contained a “BLOGGS F” three times then the recipient database would end up with three “BLOGGS F” clients. To re-import data correctly see “Re-importing Client Data” above.
Changing Imported Mailshot Data (e.g. From “M” to “Male”)
When you import data from spreadsheets it may not be in the form you require. For example the user-define mailshot field for “Gender” might contain the values “M” and “F” instead of “Male” and “Female”. To change any such data held in the user-definable mailshot fields see Mail Shot, Bulk Change Mailshot Data.
Editing Excel Data Fields (e.g. Create “Mr R White” from “Mr”, “Robert” & “White”)
Microsoft’s Outlook, for example, stores “Title” (e.g. Mr), “First Name” (e.g. Robert) and “Last Name” (e.g. “White”) as separate fields, where Durell requires a combination of these to create the first line of each postal address (i.e. Durell’s field called “Address line 1”, which typically will be something like “Mr R White”). Provided you have a copy of Excel you can edit the Outlook export file to include such a combined field before you import it into Durell, as follows…
- Load the exported file (e.g. C:\contacts.xls) into Excel
- Click on the letter “A” heading of the first column (the whole column should then be highlighted), as shown below
- On the menu line, click “Insert / Columns” to create a new blank first column
- Enter a title for the column, such as “Name for Address”
- Click the first field below this heading and enter the formula, as shown below (i.e. =B2 & “[space]” & LEFT(C2) & “[space]” & E2 )
- A name should then appear, formed from the Title in B2, a space, the leftmost character of the First Name in C2, another space, and then the Last Name from E2
To repeat this for the whole of column “A”…
- Right-click the first data cell and select “Copy” (i.e. click cell A2 in the illustration above, containing the formula that created “Mr R White"
- Click the first blank cell below it (i.e. cell A3 in the illustration above)
- Hold down the “Shift” key and then use your mouse to select the entire first column, as shown below
- Right-click anywhere on the highlighted area and select “Paste”