Importing an Address Book from Excel
Greetings, I am attempting to import a file with 90 contacts (garden group, volunteers) which were transferred to me as an Excel spreadsheet. Many posts on here have been helpful but the transfer is not yet successful. Firstly I expanded the spreadsheet with the set of matching Thunderbird column headings, and entering a '0' into all the blank data fields. The transfer partially worked using a number of save options from Excel - 'csv, UTF-8 unicode .csv, .txt etc. The common result was for the first part of the email address before the @ to show in the 'Name' column, with the full email in the Primary email column, and nothing else. One post on here suggested that something needs to be in the Display Name field, otherwise the auto transfer assumes it is a company name. I copied the first name column to the display name column, with a few entries altered to track the origin of data. That resulted in the display name entry appearing in the Name field, but no last name, with the primary email address still correctly showing. It's sort of workable now, except for double checking of people with common first names. Any suggestions on getting the First Name/Last Name combination to show? Thanks in advance, Brendan, Qld Australia.
All Replies (5)
Once your done messing in Excel save the CSV file. Open the file in Notepad and choose file save as and set the encoding to unicode/UTF.
Excel makes a right hash of anything but it's beloved ANSI. Obsolete for two decades (Windows XP no longer used it) everywhere except Microsoft office.
Thank you Matt,
No joy so far. I opened in Notepad - the save options are ANSI, UTF-16LE, UTF-16BE, UTF-8, UTF-8 with BOM. I selected UTF-8. Same result as before - the Name field has the Display Name only, no Last Name. Tried a few combinations of field tick boxes - unticking Display Name results in the earlier outcome where the Name is just the first part of the email address before @. I will try the other Notepad save options.
I open my .csv using OpenOffice Calc
character set: Unicode (UTF-8) Separated by: 'Tab' 'Comma' Text delimiter is "
Line 1 will have the following column headers in the order I've posted. First Name,Last Name,Display Name,Nickname,Primary Email,Secondary Email,Screen Name,Work Phone,Home Phone,Fax Number,Pager Number,Mobile Number,Home Address,Home Address 2,Home City,Home County,Home Post Code,Home Country,Work Address,Work Address 2,Work City,Work County,Work Post Code,Work Country,Job Title,Department,Organisation,Web Page 1,Web Page 2,Birth Year,Birth Month,Birth Day,Custom 1,Custom 2,Custom 3,Custom 4,Notes
Make sure all columns are in the order listed above and they must have identical titles.
Quick way to make sure you have them all in correct order - open .csv using Notepad
Clear all the column headers from the top part.
Then copy the above list and paste into Notepad.
Note there should not be an empty line between column headers and first contact details.
save file.
Line 2 will contain first contact with appropriate information per column header. For all the blank data fields - leave them empty. Line 3 will contain second contact....... Do not have any completely blank lines between each contact.
When importing: In Address Book
- Tools > Import
- select 'Address Books' and click on Next
- Select : Text file (LDIF, tab, csv, txt) and click on Next
- locate and select stored .csv file and click on Open
- then check all is mapped correctly
Thunderbird Address Book field on left side need to match the data address fields you are importing shown on the right side. So if on the right side you have:
- First Name
- Last Name
- Display Name
- Nickname
- Primary Email
But on the left side you have:
- First Name
- Last Name
- Display Name
- Primary Email
- Secondary Email
The 'Primary Email' field on left is going to align with your 'Nickname' field on right which is wrong. Scroll down to locate 'Nickname' on left side and select the checkbox. Then use the 'Move up' repeatedly until it aligns with 'Nickname' on the right side. Check all the entries starting at the top and correcting as you work your way down.
When all is correct - click on 'Import'.
Modified by Toad-Hall
Thank you, Toad Hall.
I don't have Open Office, have been saving from Excel to UTF-8 csv.
I followed all the above steps, all seemed okay. The Last Name and Screen Name arrive at the bottom of the list, but after using Move Up all the fields line up correctly. The only difference is that your list of headers has 'Postcode' in place of Zip Code.
I'm not sure which step has introduced a problem, but now the import doesn't work at all - there is an error message after pressing 'Import'. No detail, just "an error importing address book 'filename' ".
It's about time to abandon the attempt - maybe clear the head and have a fresh try later. From the earlier attempts I have the first names and email addresses, that will do for now.
Thanks for your efforts.
I had one more bright idea - copy and paste the 90 email addresses to a New List. No problem there. Then export to a .csv file, which looked good, it had all the column headers, which in theory must have the correct format. I then pasted the First name, Last Name, and Display name columns - all lined up correctly. Saved that to CSV RTF-8. Then opened in Notepad, and saved. No joy - importing resulted in the same "an error importing address book 'filename' ". Nothing will import now - even going back to the early version of the members list, which half worked originally - all display the same error. Have sent one bulk mail out to the members using just the New List entries, that worked fine. The plan now is to enter name details manually if required for emails to individual members. Thank you for the help attempts.