1 ;;; bbdb-csv-import.el --- import csv to bbdb version 3+
3 ;; Copyright (C) 2014 by Ian Kelling
5 ;; Maintainer: Ian Kelling <ian@iankelling.org>
6 ;; Author: Ian Kelling <ian@iankelling.org>
9 ;; Package-Requires: ((pcsv "1.3.3") (dash "2.5.0") (bbdb "20140412.1949"))
10 ;; Keywords: csv, util, bbdb
11 ;; Homepage: https://gitlab.com/iankelling/bbdb-csv-import
12 ;; Mailing-List: https://lists.iankelling.org/listinfo/bbdb-csv-import
14 ;; This program is free software; you can redistribute it and/or modify
15 ;; it under the terms of the GNU General Public License as published by
16 ;; the Free Software Foundation, either version 3 of the License, or
17 ;; (at your option) any later version.
19 ;; This program is distributed in the hope that it will be useful,
20 ;; but WITHOUT ANY WARRANTY; without even the implied warranty of
21 ;; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
22 ;; GNU General Public License for more details.
24 ;; You should have received a copy of the GNU General Public License
25 ;; along with this program. If not, see <http://www.gnu.org/licenses/>.
29 ;; Importer of csv (comma separated value) text into Emacs’s bbdb database,
30 ;; version 3+. Works out of the box with csv exported from Thunderbird, Gmail,
31 ;; Linkedin, Outlook.com/hotmail, and probably others.
32 ;; Easily extensible to handle new formats.
36 ;; If you installed this file with a package manager, just
38 ;; (require 'bbdb-csv-import)
40 ;; Else, note the min versions of dependencies above in "Package-Requires:",
41 ;; and load this file. The exact minimum bbdb version is unknown, something 3+.
45 ;; Back up bbdb by copying `bbdb-file' in case things go wrong.
47 ;; Simply M-x `bbdb-csv-import-buffer' or `bbdb-csv-import-file'.
48 ;; When called interactively, they prompt for file or buffer arguments.
50 ;; Then view your bbdb records: M-x bbdb .* RET
51 ;; If the import looks good save the bbdb database: C-x s (bbdb-save)
53 ;;; Advanced usage / notes:
55 ;; Tested to work with thunderbird, gmail, linkedin,
56 ;; outlook.com/hotmail.com. For those programs, if it's exporter has an option
57 ;; of what kind of csv format, choose it's own native format if available, if
58 ;; not, choose an outlook compatible format. If you're exporting from some other
59 ;; program and its csv exporter claims outlook compatibility, there is a good
60 ;; chance it will work out of the box. If it doesn't, you can try to fix it as
61 ;; described below, or the maintainer will be happy to help, just anonymize your
62 ;; csv data using the M-x bbdb-csv-anonymize-current-buffer (make sure csv
63 ;; buffer is the current one) and attach it to an email to the mailing list.
65 ;; Duplicate contacts (according to email address) are skipped if
66 ;; bbdb-allow-duplicates is nil (default). Any duplicates found are echoed at
67 ;; the end of the import.
69 ;;; Custom mapping of csv fields
71 ;; If a field is handled wrong or you want to extend the program to handle a new
72 ;; kind of csv format, you need to setup a custom field mapping variable. Use
73 ;; the existing tables as an example. By default, we use a combination of most
74 ;; predefined mappings, and look for all of their fields, but it is probably
75 ;; best to avoid that kind of table when setting up your own as it is an
76 ;; unnecessary complexity in that case. If you have a problem with data from a
77 ;; supported export program, start by testing its specific mapping table instead
78 ;; of the combined one. Here is a handy template to set each of the predefined
79 ;; mapping tables if you would rather avoid the configure interface:
81 ;; (setq bbdb-csv-import-mapping-table bbdb-csv-import-combined)
82 ;; (setq bbdb-csv-import-mapping-table bbdb-csv-import-thunderbird)
83 ;; (setq bbdb-csv-import-mapping-table bbdb-csv-import-gmail)
84 ;; (setq bbdb-csv-import-mapping-table bbdb-csv-import-gmail-typed-email)
85 ;; (setq bbdb-csv-import-mapping-table bbdb-csv-import-linkedin)
86 ;; (setq bbdb-csv-import-mapping-table bbdb-csv-import-outlook-web)
87 ;; (setq bbdb-csv-import-mapping-table bbdb-csv-import-outlook-typed-email)
89 ;; The doc string for `bbdb-create-internal' may also be useful when creating a
90 ;; mapping table. Please send any new tables to the maintainer listed in this
91 ;; file. The maintainer should be able to help with any issues and may create a
92 ;; new mapping table given sample data.
94 ;; Mapping table tips:
95 ;; * The repeat keyword expands numbered field names, based on the first
96 ;; subsequent field, as many times as they exist in the csv data.
97 ;; * All mapping fields are optional. A simple mapping table could be
98 ;; (setq bbdb-csv-import-mapping-table '((:mail "Primary Email")))
99 ;; * xfields uses the field name to create custom fields in bbdb. It downcases
100 ;; the field name, and replaces spaces with "-".
101 ;; * For example, if you had a csv data for bbdb's mail-alias, you could add to :xfields
102 ;; a csv field name would become "mail-alias", like "Mail Alias" or "Mail-alias"
104 ;;; Misc tips/troubleshooting:
106 ;; - ASynK looks promising for syncing bbdb/google/outlook.
107 ;; - The git repo contains a test folder with exactly tested version info and working
108 ;; test data. Software, and especially online services are prone to changing how they
109 ;; export. Please send feedback if you run into problems.
110 ;; - bbdb doesn't work if you delete the bbdb database file in
111 ;; the middle of an emacs session. If you want to empty the current bbdb database,
112 ;; do M-x bbdb then .* then C-u * d on the beginning of a record.
113 ;; - After changing a mapping table variable, don't forget to re-execute
114 ;; (setq bbdb-csv-import-mapping-table ...) so that it propagates.
115 ;; - :namelist is used instead of :name if 2 or more non-empty fields from :namelist are
116 ;; found in a record. If :name is empty, we try a single non-empty field from :namelist
117 ;; This sounds a bit strange, but it's to try and deal with Thunderbird idiosyncrasies.
119 ;;; Bugs, patches, discussion, feedback
121 ;; Patches and bugs are very welcome via https://gitlab.com/iankelling/bbdb-csv-import
123 ;; Questions, feedback, or anything is very welcome at to the bbdb-csv-import mailing list
124 ;; https://lists.iankelling.org/listinfo/bbdb-csv-import, no subscription needed to post via
125 ;; bbdb-csv-import@lists.iankelling.org
133 (eval-when-compile (require 'cl))
135 (defconst bbdb-csv-import-thunderbird
136 '((:namelist "First Name" "Last Name")
137 (:name "Display Name")
139 (:mail "Primary Email" "Secondary Email")
140 (:phone "Work Phone" "Home Phone" "Fax Number" "Pager Number" "Mobile Number")
143 (("Home Address" "Home Address 2")
144 "Home City" "Home State"
145 "Home ZipCode" "Home Country"))
147 (("Work Address" "Work Address 2")
148 "Work City" "Work State"
149 "Work ZipCode" "Work Country"))))
150 (:organization "Organization")
151 (:xfields "Web Page 1" "Web Page 2" "Birth Year" "Birth Month"
152 "Birth Day" "Department" "Custom 1" "Custom 2" "Custom 3"
153 "Custom 4" "Notes" "Job Title"))
154 "Thunderbird csv format")
156 (defconst bbdb-csv-import-linkedin
157 '((:namelist "First Name" "Middle Name" "Last Name")
159 (:mail "E-mail Address" "E-mail 2 Address" "E-mail 3 Address")
161 "Assistant's Phone" "Business Fax" "Business Phone"
162 "Business Phone 2" "Callback" "Car Phone"
163 "Company Main Phone" "Home Fax" "Home Phone"
164 "Home Phone 2" "ISDN" "Mobile Phone"
165 "Other Fax" "Other Phone" "Pager"
166 "Primary Phone" "Radio Phone" "TTY/TDD Phone" "Telex")
169 (("Business Street" "Business Street 2" "Business Street 3")
170 "Business City" "Business State"
171 "Business Postal Code" "Business Country"))
173 (("Home Street" "Home Street 2" "Home Street 3")
174 "Home City" "Home State"
175 "Home Postal Code" "Home Country"))
177 (("Other Street" "Other Street 2" "Other Street 3")
178 "Other City" "Other State"
179 "Other Postal Code" "Other Country"))))
180 (:organization "Company")
182 "Department" "Job Title" "Assistant's Name"
183 "Birthday" "Manager's Name" "Notes" "Other Address PO Box"
184 "Spouse" "Web Page" "Personal Web Page"))
185 "Linkedin export in the Outlook csv format.")
188 (defconst bbdb-csv-import-gmail
189 '((:namelist "Given Name" "Family Name")
191 (:affix "Name Prefix" "Name Suffix")
193 (:mail (repeat "E-mail 1 - Value"))
194 (:phone (repeat ("Phone 1 - Type" "Phone 1 - Value")))
196 (repeat (("Address 1 - Type")
197 (("Address 1 - Street" "Address 1 - PO Box" "Address 1 - Extended Address")
198 "Address 1 - City" "Address 1 - Region"
199 "Address 1 - Postal Code" "Address 1 - Country"))))
200 (:organization (repeat "Organization 1 - Name"))
202 "Additional Name" "Yomi Name" "Given Name Yomi"
203 "Additional Name Yomi" "Family Name Yomi"
204 "Initials" "Short Name" "Maiden Name" "Birthday"
205 "Gender" "Location" "Billing Information"
206 "Directory Server" "Mileage" "Occupation"
207 "Hobby" "Sensitivity" "Priority"
208 "Subject" "Notes" "Group Membership"
209 ;; Gmail wouldn't let me add more than 1 organization in its web interface,
210 ;; but no harm in looking for multiple since the field name implies the
213 "Organization 1 - Type" "Organization 1 - Yomi Name"
214 "Organization 1 - Title" "Organization 1 - Department"
215 "Organization 1 - Symbol" "Organization 1 - Location"
216 "Organization 1 - Job Description")
217 (repeat ("Relation 1 - Type" "Relation 1 - Value"))
218 (repeat ("Website 1 - Type" "Website 1 - Value"))
219 (repeat ("Event 1 - Type" "Event 1 - Value"))
220 (repeat ("Custom Field 1 - Type" "Custom Field 1 - Value"))))
221 "Gmail csv export format. Note some fields don't map perfectly,
222 feel free to modify them as you wish. \"PO Box\" and \"Extended
223 Address\" are added as additional address street lines if they
224 exist. Some special name fields are made custom instead of put in
225 name, which gets a single string. We map Gmail's \"Name Prefix\"
226 and \"Name Suffix\" to bbdb's affix (a list of strings). We lose
227 the prefix/suffix label, but those are usually obvious.")
230 (defconst bbdb-csv-import-gmail-typed-email
231 (append (car (last bbdb-csv-import-gmail)) '((repeat "E-mail 1 - Type")))
232 "Like the first Gmail mapping, but use custom fields to store
233 Gmail's email labels. This is separate because I assume most
234 people don't use those labels and using the default labels
235 would create useless custom fields.")
237 (defconst bbdb-csv-import-outlook-web
238 '((:namelist "First Name" "Middle Name" "Last Name")
239 (:mail "E-mail Address" "E-mail 2 Address" "E-mail 3 Address")
242 "Assistant's Phone" "Business Fax" "Business Phone"
243 "Business Phone 2" "Callback" "Car Phone"
244 "Company Main Phone" "Home Fax" "Home Phone"
245 "Home Phone 2" "ISDN" "Mobile Phone"
246 "Other Fax" "Other Phone" "Pager"
247 "Primary Phone" "Radio Phone" "TTY/TDD Phone" "Telex")
251 "Business City" "Business State"
252 "Business Postal Code" "Business Country"))
255 "Home City" "Home State"
256 "Home Postal Code" "Home Country"))
259 "Other City" "Other State"
260 "Other Postal Code" "Other Country"))))
261 (:organization "Company")
263 "Anniversary" "Family Name Yomi" "Given Name Yomi"
264 "Department" "Job Title" "Birthday" "Manager's Name" "Notes"
265 "Spouse" "Web Page"))
266 "Hotmail.com, outlook.com, live.com, etc.
267 Based on 'Export for outlook.com and other services',
268 not the export for Outlook 2010 and 2013.")
270 (defconst bbdb-csv-import-outlook-typed-email
271 (append (car (last bbdb-csv-import-outlook-web)) '((repeat "E-mail 1 - Type")))
272 "Like bbdb-csv-import-gmail-typed-email, but for outlook-web.
273 Adds email labels as custom fields.")
276 (defun bbdb-csv-import-flatten1 (list)
277 "Flatten LIST by 1 level."
278 (--reduce-from (if (consp it)
284 (defun bbdb-csv-import-merge-map (root)
285 "Combine two root mappings for making a combined mapping."
286 (bbdb-csv-import-flatten1
290 (cdr (assoc root bbdb-csv-import-thunderbird))
291 (cdr (assoc root bbdb-csv-import-linkedin))
292 (cdr (assoc root bbdb-csv-import-gmail))
293 (cdr (assoc root bbdb-csv-import-outlook-web)))))))
296 (defconst bbdb-csv-import-combined
298 ;; manually combined for proper ordering
299 '(:namelist "First Name" "Given Name" "Middle Name" "Last Name" "Family Name")
300 (bbdb-csv-import-merge-map :name)
301 (bbdb-csv-import-merge-map :affix)
302 (bbdb-csv-import-merge-map :aka)
303 (bbdb-csv-import-merge-map :mail)
304 (bbdb-csv-import-merge-map :phone)
305 ;; manually combined the addresses. Because it was easier.
307 (repeat (("Address 1 - Type")
308 (("Address 1 - Street" "Address 1 - PO Box" "Address 1 - Extended Address")
309 "Address 1 - City" "Address 1 - Region"
310 "Address 1 - Postal Code" "Address 1 - Country")))
312 (("Business Street" "Business Street 2" "Business Street 3")
313 "Business City" "Business State"
314 "Business Postal Code" "Business Country"))
316 (("Home Street" "Home Street 2" "Home Street 3"
317 "Home Address" "Home Address 2")
318 "Home City" "Home State"
319 "Home Postal Code" "Home ZipCode" "Home Country"))
321 (("Work Address" "Work Address 2")
322 "Work City" "Work State"
323 "Work ZipCode" "Work Country"))
325 (("Other Street" "Other Street 2" "Other Street 3")
326 "Other City" "Other State"
327 "Other Postal Code" "Other Country"))))
328 (bbdb-csv-import-merge-map :organization)
329 (bbdb-csv-import-merge-map :xfields)))
331 (defcustom bbdb-csv-import-mapping-table bbdb-csv-import-combined
332 "The table which maps bbdb fields to csv fields. The default should work for most cases.
333 See the commentary section of this file for more details."
334 :group 'bbdb-csv-import
338 (defun bbdb-csv-import-expand-repeats (csv-fields list)
339 "Return new list where elements from LIST in form (repeat elem1
340 ...) become ((elem1 ...) [(elem2 ...)] ...) for as many fields
341 exist in the csv fields. elem can be a string or a tree (a list
342 with lists inside it). We use the first element as a template,
343 and increase its number by one, and check if it exists, and then
344 increment any other elements from the repeat list which have
346 (cl-flet ((replace-num (num string)
347 ;; in STRING, replace all groups of numbers with NUM
348 (replace-regexp-in-string "[0-9]+"
349 (number-to-string num)
352 (if (not (and (consp it) (eq (car it) 'repeat)))
356 (first-field (car (-flatten it))))
357 (setq acc (cons it acc))
358 ;; use first-field to test if there is another repetition.
360 (replace-num (setq i (1+ i)) first-field)
362 (cl-labels ((fun (cell)
365 (replace-num i cell))))
366 (setq acc (cons (fun it) acc))))
370 (defun bbdb-csv-import-map-bbdb (csv-fields root)
371 "ROOT is a root element from bbdb-csv-import-mapping-table. Get
372 the csv-fields for root in the mapping format, including variably
373 repeated ones. Flatten by one because repeated fields are put in
374 sub-lists, but after expanding them, that extra depth is no
375 longer useful. Small trade off: address mappings without 'repeat need
376 to be grouped in a list because they contain sublists that we
377 don't want flattened."
378 (bbdb-csv-import-flatten1
379 (bbdb-csv-import-expand-repeats
381 (cdr (assoc root bbdb-csv-import-mapping-table)))))
384 (defun bbdb-csv-import-file (filename)
385 "Parse and import csv file FILENAME to bbdb.
386 The file will be saved to disk with blank lines and aberrant characters removed."
387 (interactive "fCSV file containg contact data: ")
388 (bbdb-csv-import-buffer (find-file-noselect filename)))
391 (defun bbdb-csv-import-buffer (&optional buffer-or-name)
392 "Parse and import csv buffer to bbdb. Interactively, it prompts for a buffer.
393 The buffer will be saved to disk with blank lines and aberrant characters removed.
394 BUFFER-OR-NAME is a buffer or name of a buffer, or the current buffer if nil."
395 (interactive "bBuffer containing CSV contact data: ")
396 (when (null bbdb-csv-import-mapping-table)
397 (error "error: `bbdb-csv-import-mapping-table' is nil. Please set it and rerun."))
398 (let* ((csv-buffer (get-buffer (or buffer-or-name (current-buffer))))
399 (csv-data (save-excursion
400 (set-buffer csv-buffer)
401 ;; deal with blank lines and ^M from linkedin
402 (flush-lines "^\\s-*$")
403 (goto-char (point-min))
404 ;; remove ^M aka ret characters
405 (while (re-search-forward (char-to-string 13) nil t)
408 (pcsv-parse-file buffer-file-name)))
409 (csv-fields (car csv-data))
410 (csv-data (cdr csv-data))
411 (allow-dupes bbdb-allow-duplicates)
412 csv-record rd assoc-plus map-bbdb dupes)
413 ;; convenient function names
414 (fset 'rd 'bbdb-csv-import-rd)
415 (fset 'assoc-plus 'bbdb-csv-import-assoc-plus)
416 (fset 'map-bbdb (-partial 'bbdb-csv-import-map-bbdb csv-fields))
417 ;; we handle duplicates ourselves
418 (setq bbdb-allow-duplicates t)
419 ;; loop over the csv records
420 (while (setq csv-record (map 'list 'cons csv-fields (pop csv-data)))
422 ((ca (key list) (cdr (assoc key list))) ;; utility function
424 ;; given ROOT, return a list of data, ignoring empty fields
425 (rd (lambda (elem) (assoc-plus elem csv-record)) (map-bbdb root)))
427 ;; E = data-field-name | (field-name-field data-field)
428 ;; get data from the csv-record and return (field-name data) or nil.
429 (let ((data-name (if (consp e) (ca (car e) csv-record) e))
430 (data (assoc-plus (if (consp e) (cadr e) e) csv-record)))
431 (if data (list data-name data)))))
432 ;; set the arguments to bbdb-create-internal, then call it, the end.
433 (let ((name (let ((namelist (rd-assoc :namelist))
434 (let-name (car (rd-assoc :name))))
435 ;; priority: 2 or more from :namelist, then non-empty :name, then
436 ;; any single element of :namelist
437 (cond ((>= (length namelist) 2)
438 (mapconcat 'identity namelist " "))
439 ((not (null let-name))
442 (mapconcat 'identity namelist " ")))))
443 (affix (rd-assoc :affix))
444 (aka (rd-assoc :aka))
445 (organization (rd-assoc :organization))
446 (mail (rd-assoc :mail))
447 (phone (rd 'vconcat (rd #'assoc-expand (map-bbdb :phone))))
448 (address (rd (lambda (e)
450 (let ((al (rd (lambda (elem) ;; al = address lines
451 (assoc-plus elem csv-record))
453 ;; to use bbdb-csv-import-combined, we can't mapcar
454 (address-data (--reduce-from (if (member it csv-fields)
455 (cons (ca it csv-record) acc)
459 (setq al (nreverse al))
460 (setq address-data (nreverse address-data))
461 ;; make it a list of at least 2 elements
463 (-repeat (- 2 (length al)) "")))
464 (when (consp elem-name)
465 (setq elem-name (ca (car elem-name) csv-record)))
467 ;; determine if non-nil and put together the minimum set
468 (when (or (not (--all? (zerop (length it)) address-data))
469 (not (--all? (zerop (length it)) al)))
470 (when (> 2 (length al))
471 (setcdr (max 2 (nthcdr (--find-last-index (not (null it))
474 (vconcat (list elem-name) (list al) address-data))))
475 (map-bbdb :address)))
476 (xfields (rd (lambda (list)
477 (let ((e (car list)))
478 (while (string-match " +" e)
479 (setq e (replace-match "-" nil nil e)))
480 (setq e (make-symbol (downcase e)))
481 (cons e (cadr list)))) ;; change from (a b) to (a . b)
482 (rd #'assoc-expand (map-bbdb :xfields)))))
483 ;; we copy and subvert bbdb's duplicate detection instead of catching
484 ;; errors so that we don't interfere with other errors, and can print
485 ;; them nicely at the end.
488 (when (bbdb-gethash elt '(mail))
490 (setq found-dupe t)))
491 (when (or allow-dupes (not found-dupe))
492 (bbdb-create-internal name affix aka organization mail phone address xfields t))))))
493 (when dupes (if allow-dupes
494 (message "Warning, contacts with duplicate email addresses were imported:\n%s" dupes)
495 (message "Skipped contacts with duplicate email addresses:\n%s" dupes)))
496 (setq bbdb-allow-duplicates allow-dupes)))
498 (defun bbdb-csv-import-rd (func list)
499 "like mapcar but don't build nil results into the resulting list"
500 (--reduce-from (let ((funcreturn (funcall func it)))
502 (cons funcreturn acc)
506 (defun bbdb-csv-import-assoc-plus (key list)
507 "Like (cdr assoc ...) but turn an empty string result to nil."
508 (let ((result (cdr (assoc key list))))
509 (when (not (string= "" result))
512 (defun bbdb-csv-anonymize-current-buffer ()
514 "Anonymize the current buffer which contains csv data.
515 The first line should contain header names."
517 (while (re-search-forward "\\w")
519 (insert (number-to-string (random 9)))))
522 (provide 'bbdb-csv-import)
524 ;;; bbdb-csv-import.el ends here