Editing Contact Info in External Software
It’s often desirable to edit certain data from a large number of contacts together in a separate list, for example in Excel. This can be handy to change the addresses of a set of people, to add custom data, to correct labels etc. You can export contacts from Yesplan, edit the exported file in external software (for example in Excel), then import the edited file back into Yesplan where the contacts will be updated.
It isn’t possible to edit people, organizations and links in the same file. If you want to edit both people and organizations, you will have to do this via two files—one for people and one for organizations.
Step 1 – Export Contacts from Yesplan§
Per contact type that you want to edit—people, organizations or links—you will need a file in the correct format so you can import it back into Yesplan later. You can produce such a file in the general search function in Yesplan, where you can export the results of a query.
If you perform this export as a normal user, the file may contain columns with the value “---” because you can’t see the custom data field in question in Yesplan.
For that reason, we advise to always export resources as an administrator.
To request contacts of a certain type, use a query that returns contacts of that certain type.
A few examples:
- To export all people from Belgium we use the query
- To export all organizations we use the query
- To export all contact links from the ‘Yesplan’ organization we use the query
Read more about the options in the Query Language Manual.
When you have the desired results, select the proper encoding (UTF-8, MS-DOS or Mac, depending on your platform) under the search results, click “Export…” and then select “Export all contacts”. Yesplan will prepare a CSV file that contains the chosen contacts. When this is ready (it takes a few seconds) you can download the file. When this is ready (it takes a few seconds) you can download the file.
Step 2 – Edit Contacts§
Open the CSV file that you downloaded in step 1 in an application that can read files of this type. You can do this in Excel for example. Make the desired changes or additions to the contact info and then save the edited file:
- The first row of the CSV file contains the names of the file’s various fields, the column headers. It’s important to leave the table headers untouched, otherwise Yesplan won’t be able to interpret the file.
- All other rows in the file contain contact info. There is one row per contact.
- The CSV file from step 1 also contains fields with the table headers “per_id”, “org_id” and “lnk_id”. These columns contain the contact’s identification numbers in Yesplan. It’s important to leave the values of these fields untouched, otherwise Yesplan won’t be able to interpret the file correctly.
New in Yesplan 29 In Yesplan 29, you can set the date format of your Yesplan installation. Date fields in the CSV file must be in line with the date format of your Yesplan installation, which can be set in “System Settings” > “System Preferences”:
- “Day Month Year”:
- “Month Day Year”:
- “Year Month Day”:
Before you can update the contacts in Yesplan, you must delete all columns that aren’t related to the contact type that you’re editing.
When opening a CSV file, Excel will automatically try to interpret the data. A field that only contains digits will be regarded as a number, and Excel deletes zeros at the beginning of that number. That can be a problem for telephone numbers (e.g. ‘0456…’ becomes ‘456…’).
You can avoid this by importing the CSV file into Excel rather than opening it with Excel. Open a new file in Excel and choose import from the “File” menu. Follow the wizard and choose the type “Text” for those particular columns under the step where you can set the data type per column. That way, combinations of digits aren’t regarded as numbers and they will retain possible zeros at the beginning.
Step 3 – Delete Unnecessary Columns§
The export of contact info from Yesplan contains a few additional fields to give extra context to the information that you receive. For example, for contact links it’s very useful to receive information about the linked person and organization. However, this means that the exported CSV file will also contain fields that aren’t part of the basic information for the type of contact (people, organizations or links) that you requested with the query.
For example, the result of the query
person:country:BE is a list of people who meet the conditions, but the exported CSV file will also contain fields for each of these people with information about the default link and the organization of this link (if a default link exists).
These additional fields (columns) are useful in an export file, but they are unnecessary when importing the information back into Yesplan. Before we can update the contacts in Yesplan, the unnecessary columns will have to be removed manually. If you don’t do this, the file will be rejected during the import into Yesplan.
Per type, you must delete the following fields:
- Remove all columns with a table header that starts with “org_” or “lnk_” (including “org_id” and “lnk_id”).
- Please note: leave the column “HR” as is.
- Remove all columns with a table header that starts with “per_” or “lnk_” (including “per_id” and “lnk_id”).
- Remove the column “HR”.
Editing contact links:
- Remove all columns with a table header that starts with “per_” or “org_” (including “per_id” and “org_id”).
- Remove the column “HR”.
Save the file—with your changes and without the unnecessary columns—as a CSV file.
Yesplan expects a CSV file when importing, not an Excel file. First you must export the worksheet with the contacts to the CSV format. You do this in Excel by clicking “Save As…” in the “File” menu and then selecting the “CSV” format.
Step 4 – Edit Contacts in Yesplan§
You can edit contacts in Yesplan in “System Settings” > “Imports” > “Contacts”. You’ll find the form for uploading the CSV file there. You can enter the following parameters there:
- Import Contacts: From this drop-down menu, choose the type of contacts you want to update: “Update people”, “Update organizations” or “Update Contact Links”.
- Encoding: The character encoding (character encoding or text encoding) of the CSV files that serves as an input. Yesplan supports three encodings: “CSV (UTF-8)”, “MS-DOS CSV (Latin1)” and “Macintosh CSV (MacRoman)”. The correct choice of encoding is important to ensure that special characters (like é, è, ç, ø.) are read correctly. If these characters appear incorrectly after updating in Yesplan, try the import again with the correct encoding.
- Importing as User: The Yesplan user who will own the imported contacts.
- Select File: Choose the CSV file with edited contacts from its location on your computer.
Yesplan will import the contacts in the background. You won’t see the result of the update immediately.
When the contacts are updated, the result will appear in a list (in the column on the right). This list has the following options:
- View Results: View the details and any alerts or error messages regarding the import.
- Undo: Undo all changes to the contacts. Only the changes that you made in the CSV file will be undone and restored to the value that they had before. Since this can have a big impact, you will have to confirm the action.
- Delete from History List: Delete the row from the list of imports. This is only possible for imports where no contacts were added. Please note that Yesplan automatically deletes old imports from the list at regular intervals.
Yesplan will automatically carry out a detection of duplicates for imported contacts. Since this calculation can be rather intensive, it will occur at night so as not to hinder the optimal operation of Yesplan. For imports where it still has to take place, the “Results” column will state that “Duplicates will be calculated tonight”. If the duplicates detection was carried out, you won’t see this statement.
If the import was successful, the number of imported contacts will appear together with any warnings. The alerts show which information couldn’t be imported due to an error in the import file. If necessary, you can adapt the file and import it again. Be sure to click “Undo” in the history next to the failed import to start with a clean slate.
Some errors prevent the execution of the import (for instance because required fields are missing). Consult the list of warnings and adapt the file where necessary.