Set-up, Import Client & Other Address Set Data
WARNING! If you wish to import data from a copy of your data on a notebook then DO NOT USE the utility described below. Instead you must refer to “Set-up, Import Notebooks”.
This utility is designed to allow you to…
- Initially set-up your client, or other address sets by importing data from a CSV file (i.e. comma separated variables) from Microsoft Outlook, or some other system
- 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 client 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 resaved 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 “Individual 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 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 “Individual Clients” (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
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 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 the section on “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”)
For example, Microsoft’s Outlook 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”