![Thunderbird logo](https://assets-prod.sumo.prod.webservices.mozgcp.net/media/uploads/products/2024-02-12-07-36-53-599028_1.png)
how do i properly import a csv file?
i tried to import a csv file from outlook.com to thunderbird, and all the values are in the wrong fields. E-mail addresses and names of every contact are in the wrong fields. For instance, if I have 100 contacts, I have to edit them individually in Thunderbird after importing the file. This is not helpful. Is there a way to convert the file so I don't have to do this? Please advise.
All Replies (20)
The problem is probably the first row in your csv file. I suggest editing with a simple text editor. If the first row identifies the columns, then they are not in sync with Thunderbird layout. I suggest removing that title row and then import. At that time, you will be presented with a pane that lets you specify each field and how to map it.
Can you tell me what you mean? Identifies after edits or prior? The imported file seemes to show the contacts last names from the file, and many of them have their e-mail addresses listed as a phone number. I would like it to show their first and last names in the new contact cards. I noticed on one, that if I went to edit it in Thunderbird, it still has the first name, but it is showing the contact by the "display name," and that shows that the last name is the in the display name field. I have seen in some articles or posts that there are also options for a different type of contact file format.
Is there a way to get first name and last name imported so they will display correctly without removing the first name from the csv file?
It is displaying in the csv like so: first name, middle name, last name, title, suffix, nickname, given name, email 1, 2, 3, 2 different home phone fileds, then 2 business phone fields, mobile phone, car phone, other phone, fax, pager. it is ironic that i don't recall seeing these all in outlook.com. It does seem that it is there by looking though.
But thunderbird has a different layout.
The first row identifies the layout. My suggestion is to remove it, allowing you to specify each entry during the import process. Once you remove line one, restart the import (assuming you deleted the prior attempt).
would i need to remove the imported contacts that are messed up and those i adjusted already somehow? what do i do in the text editor? i am not familiar with editing a contact list in something like notepad.
A csv file (first row) can also be edited in Excel or similar spreadsheet. My suggestion is to remove the current entries, as when you do it again, you would be otherwise duplicating many entries. Anyway, that's how I would do it.
it didn't work to remove the first row. i don't know if i need to do a step differently. I noticed also if i check or uncheck "first row contains field names," it doesn't make a difference. Do you have any other suggestions? It would be appreciated.
I don't know what you mean by 'it didn't work' -
it basically still had last names displayed as the display names, and it e-mail addresses as phone numbers. I saw the majority of the contacts listed without names.
if you might have any other advice, it would be appreciated.
This is a topic almost as old as Computing. CSV files are not all created equal, and in some cases like exports from some versions of Microsoft office need to be converted to Unicode text.
A comma separated values (CSV) file is such a simple thing, and like most simple things it is actually very complex under the hood with lots of gotchas and getting it wrong is very very easy.
Lets start with making sure the file is actually laid out correctly. David has tried to lead you through this part, but I suggest you have a look at my blog posting from 2013 as I have some images that might help there. https://thunderbirdtweaks.blogspot.com/2013/03/importing-csv-files.html
Note that the file should be opened in Windows notepad and then use the file save as and ensure the "encoding" is set to utf-8. Then save. This can be a particular problem with older Office application which still used ANSI until a year or two ago and this simple set can make mangled data not so mangled.
Then we have the discussion of the import fields. This is where most folk fall down in the gotcha department. Thunderbird will use identical field names to it's internal fields, but manual mapping is required when the fields are off.
You will see from the image of a CSV import above that the first two fields are good to go. but the third is mapping the company name to Display name and after that it just gets messy in the extreme.
In my blog post I walk through changing the heading in a spread sheet program to match what Thunderbird expects. I do not recommend Excel for that unless you have the latest version. If you do not do that field naming in a spreadsheet, then you have to manually map those fields in the import process.
So in my example image (from a random email address book CSV off the internet) you would need to click the down arrow on the Display name and change that to Organisation, then the nickname and change that to either home address or work address. This process has to continue until you run out of fields. Some will be correct. Most will not be and require manual intervention.
This process with CSV files was called massaging the data by the finance staff I worked with in the 20th century. They spent days at a time on it, fixing field mapping and manually removing misplaced punctuation that lead to things simply being misaligned when the CSV was imported to a spreadsheet.
Exactly what you will need to correct in your case will depend entirely on the file you have to start with.
i don't have the newest version of excel, but i have libre office. so i have excel 2007, and libre calc.
will excel 2007 not work with it?
also, do i need to delete every field and create the display name field?
some contacts have something like city and maybe a business name. Some have a phone number. Not all of them. Most of them just have a name and e-mail address.
Libre natively uses Unicode. Excel 2007 does not, hence me not recommending it. My example images were all done in LibreOffice. The less hurdles you have to jump through the better.
The older version of Excel helpfully offers to "import" Unicode and it just is not helpful at all. Generally messing up the layout in the process. I don't suggest you delete all the display name fields, just that you use the names Thunderbird uses to make the manual process go faster.
When I export an address book from Thunderbird it uses the names below as the field names.
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 State,Home ZipCode,Home Country,Work Address,Work Address 2,Work City,Work State,Work ZipCode,Work Country,Job Title,Department,Organization,Web Page 1,Web Page 2,Birth Year,Birth Month,Birth Day,Custom 1,Custom 2,Custom 3,Custom 4,Notes
What I am saying is that if your file has a field for the zipCode, then using the same name as Thunderbird makes it easier so renaming it to "Home ZipCode" or "Work ZipCode" which ever is appropriate is the thing to do. If your import file does not have data for a field, say Department, then omit the field name from your file. You do not have to use all the names, just those relevant to the data you have to import.
i understand it partially. it says utf-8, open in notepad, and save in libre office as odf or whatever. However, how do i do those things, but open it in libre calc, then do what is in the picture above? The blog post talks about inserting a column, which should be like doing so in excel. deleting a row is easy, and renaming a cell is easy.
However, I did not export from Thunderbird. I don't know if somehow it needs to be like this: [article]
Outlook.com csv files are different than those exported from Thunderbird. I believe that there are minimal fields in Thunderbird addressbooks, but then in outlook, there can be hundreds of them.
I think the image above might be something in Thunderbird, but I don't remember that. I am hoping i don't have to do that mapping/matching for every contact, but rather can just adjust the file, and it can be more simple, so it is neither like some kind of coding, nor like fixing a program conflict.
Gewysig op
well, apparently my reply was deleted when i tried to edit it. I am confused about how to edit it in notepad. How do i save it to the libre notes file and how is that separated from their version of excel?
I understand how to remove their column labels in the first row, and renaming it, but it is not the same as exporting from thunderbird as exporting from exporting from outlook.com due to the number of columns.
I hope that matching fields is not going to be for every contact as I do not want this to be like trying to code or application conflicts.
I found this, but I don't know if it is the same: [post] I really don't know how to edit that kind of thing.
Oh just email me the file and I will do it for you. I have spent probably 4 times as long on this topic already as it would take to actually do it.
I have personal information in that file. I do not understand all these processes since I do not use this program normally. I do think that I did before temporarily (many years ago), then I switched back to another program since I didn't like it. It was like I was probably testing Outlook 2007 and Thunderbird, and Windows Live Mail.
I do not recall clearly. I cannot really connect the dots between the different steps provided. You see, you and the other guy provided some steps, but I do not know how to connect different aspects of these explanations since I am not familiar with these types of things. There is different formatting and software programs. I am familiar with basic cells in Excel and moving and deleting them, and that there is punctuation and text in the notepad file. I understand that there are steps provided, yet it seems like there might be something connecting between steps (which I have asked prior). It seems possible that these cells are created by exporting some programming from different email clients, yet the language is also formatted by text and commas, but i don't completely understand it. That is why I have asked questions.
I am wondering if you can tell me by the questions that I asked instead of sending the file?
I might be able to send the CSV file, but it has private contacts. It may also help me learn better and also help anyone who did not see an answer that was clear in previous posts.
The fields available in the Thunderbird import program are very limited, so it is not possible to select a field corresponding to the source field (can't find my screen shot). How can I select an appropriate destination field?
@wblynn If the information you see here is insufficient for our needs, I suggest you refrain from spamming those on this topic and stick to the two identical questions you have posted on this forum today on this topic. (Will be closing the orphaned one. One question per topic please.)
i notice that my last reply did not get a reply here. i got a notification of the other person recently.
Gewysig op
JoshuaCM said
i notice that my last reply did not get a reply here. i got a notification of the other person recently.
Nope, because my time is limited and I would rather just do it in two minutes than spend days trying to leverage your skill set to an appropriate level.
It is well beyond the scope of this forum to even try to explain why Excel (Microsoft office) was still using ANSI characters until very very recently (still is for desktop I think). UNICODE came with Windows XP. It made it to office in about the latest release some 20 years later. So I say use calc as it starts with the unicode text and does not try stupid things with it like "importing" it and turning something simple into a dogs breakfast before you get started and then exporting something incompatible at the end. How to use Calc is beyond the scope of this forum, just as how to use Excel is.
I see nothing complex in opening a file in notepad and making sure the correct format is included in the save as dialog. It is a necessary step and ANSI text is not compatible with Thunderbird or the web. Only Microsoft office really. The source of the CSV file in question.
Then there is the issue that Microsoft export from their mail clients in some cases hundreds of columns of useless address book fields, but Thunderbird is limited to those it shows in the address book import. How you prune this stuff to fit is entirely up to you. But the import will only import matched field names. be they automatically matched because they are the same in both things (as I suggest doing in Calc) or manually and quite tediously in the import, only to be repeated if you get something wrong.
I have been messing around with CSV imports since some time in the early 1990s with dBaseIII and Cobol mainframes. I just can not really transfer 20+ years of experience in a few paragraphs. It is like asking a car transmission mechanic to tell you how to fix yours. The steps are simple and the process rather boring. But experience is required to get it right each time, otherwise you sort of fix it. (been there done that and it was a failure the first twice)
If you want more detail of CSV formatted files, I suggest you start at wikipedia where they state "Comma-separated values is a data format that predates personal computers by more than a decade: the IBM Fortran (level H extended) compiler under OS/360 supported CSV in 1972." The wikipedia article goes on to say under the heading standardization "the term "CSV" is widely used to refer to a large family of formats that differ in many ways." add to that you are dealing with Microsoft implementation of a vague standard and you may start to understand my reluctance. Even though it is styles comma separated, other delimiters are used, including tabs and colons. Other variants use a stricks use of quotation marks to delineate start and end of a text string. Other files simply do not have any delimiter relying instead solely on the tab or comma or whatever is used. The room for confusion of document layout and data is immense.