Format of Address book CSV
lemel_man
I've just tried exporting my TB address book as a CSV so that it could
be imported into my Gmail account. Unfortunately, although it imports
with no apparent errors, it appears as mostly garbage in Gmail.
Any ideas please?
--
Regards, Gary Wooding
(To reply by email, change gug to goog in my address)
gabor
If you look into the exported file with Excel or some
other spreadsheet viewer you'll see column headers
followed by the data. It's possible that Gmail needs
the CSV to have the columns in a particular order. You
might try exporting Gmail addresses to CSV if that is
possible to see how different the formats are.
HTH,
Gabor
Mike Easter
> TB 2.0.0.22, XP Pro+SP3
> I've just tried exporting my TB address book as a CSV so that it could
> be imported into my Gmail account. Unfortunately, although it imports
> with no apparent errors, it appears as mostly garbage in Gmail.
> Any ideas please?
Tbird's csv export is filled with junk, too many separated value
categories for gmail to digest.
Gmail's .csv export is also filled with junk, also too many separated
value categories.
You would need to import the .csv into a real spreadsheet editor,
similar to Excel or OO Calc, and get rid of most of that junk so that it
becomes 'digestible'.
See this message about Tbird's & gmail's dozens of AB fields:
From: "Mike Easter"
Newsgroups: mozilla.support.thunderbird
Subject: Re: Import TB address book to Google
Date: Sat, 1 Aug 2009 05:30:56 -0700
Message-ID: <CMydnRIVXePmqOnX...@mozilla.org>
I think the tbird export function should permit the user to reduce the
number of fields to export; down to lastname, firstname, email or so.
--
Mike Easter
Craig
> lemel_man wrote:
>> TB 2.0.0.22, XP Pro+SP3
>> I've just tried exporting my TB address book as a CSV so that it could
>> be imported into my Gmail account. Unfortunately, although it imports
>> with no apparent errors, it appears as mostly garbage in Gmail.
>> Any ideas please?
>
> You would need to import the .csv into a real spreadsheet editor,
> similar to Excel or OO Calc, and get rid of most of that junk so that it
> becomes 'digestible'.
That was my experience. I haven't exported to Google, rather, I just
wanted to vet all the data in my addy book. The only comfortable way
for me to do it was editing TBird's exported CSV in OOo Calc.
Not bad... not too tedious. My one suggestion is to make sure the first
row is locked so the field titles are always visible.
>
> I think the tbird export function should permit the user to reduce the
> number of fields to export; down to lastname, firstname, email or so.
Yea, like PalmOS' address export function. Users could add/remove and
arrange all the fields prior to export.
hth,
-Craig
lemel_man
I rather suspected that was the reason. I like the idea of using a
spreadsheet to make adjustments.
Miles
> TB 2.0.0.22, XP Pro+SP3
> I've just tried exporting my TB address book as a CSV so that it could
> be imported into my Gmail account. Unfortunately, although it imports
> with no apparent errors, it appears as mostly garbage in Gmail.
> Any ideas please?
>
Last week I resolved the problem exporting from TB as an .ldif and
importing that file to Yahoo. Then exported it as a yahoo.csv.
Opened it in Excel, deleted several unused columns and changed "first"
to "last" and "last" to "first" in order that it read last name first.
Then imported to Google Voice (which is will be the same file as
Google Mail).
Recommendation is you correct as many errors as possible while in TB
because it's extremely time consuming to do such on-line in Gmail or
GV. Such as ensuring you have the needed data such as phone numbers,
email addresses, the names of the parties (a opposed to their email
address being in that field), etc., etc.
Miles
lemel_man
because I lost my way in the swapping, so, I then wrote a little program
to do the job for me.
Although the columns were then in the correct order, it still didn't work.
After a bit of head-scratching I figured that the column headings were
NOT just names, the import process actually used them. When I used the
OE names instead of the re-arranged TB ones it worked pretty well. But
not well enough.
Further head-scratching revealed that TB surrounds fields that contain
commas with double quotes. IE. an address line of '17 My Road, My
Estate' (without the quotes) is recorded as "17 My Road, My Estate" with
the double quotes to show that the embedded comma is NOT a CSV comma.
This totally throws Gmail. (It could have been my program, but I didn't
pursue it further).
I then changed the embedded commas to some other character (I chose a
period) and then it worked fine.
I just thought somebody might be interested.
David Pyles
a function of Excel.
Dave Pyles
lemel_man
No Dave, its not Excel, it's TB's Export CSV tool.
If a field contains one or more commas, TB surrounds the field with
double-quotes when it exports as a CSV.
The field inside the address book doesn't have the quotes, it gets them
when it's exported as a CSV.
David Pyles
carefully, and I didn't see it when I exported my addressbook as a test
because I don't ever put commas in the fields.
Dave Pyles
gNeandr
>> David Pyles wrote:
>>> On 9/1/2009 10:42 AM, lemel_man wrote:
> You're right about fields containing commas. I wasn't reading too
> carefully, and I didn't see it when I exported my addressbook as a
> test because I don't ever put commas in the fields.
> Dave Pyles
aren't exported well.
Because of those difficulties I'm using the LDIF format for export.
lemel_man
> How about new lines in Notes field of the contact??! I remember they
> aren't exported well.
> Because of those difficulties I'm using the LDIF format for export.
>
I tried the LDIF format for transferring to Gmail, but it didn't work
for me. That's why I took the trouble to investigate rearranging the fields.
Both TB and Gmail (read OE format) have a single 'Notes' field, but you
can create several lines in either. TB also has 4 'Custom' fields, but
Gmail has none.
As far as I can tell without even more investigation, any field can
contain commas to separate sub-fields.
The TB Address Book contains 36 fields, compared to OE's 29. In case
anybody is interested, here is a complete list.
TB Field TB Equiv OE Field
HdrTB.1 = First Name 1 HdrOE.1 = First Name
HdrTB.2 = Last Name 2 HdrOE.2 = Last Name
HdrTB.3 = Display Name * HdrOE.3 = Middle Name
HdrTB.4 = Nickname 3 HdrOE.4 = Name
HdrTB.5 = Primary Email 4 HdrOE.5 = Nickname
HdrTB.6 = Secondary Email 5 HdrOE.6 = E-mail Address
HdrTB.7 = Work Phone 12 HdrOE.7 = Home Street
HdrTB.8 = Home Phone 14 HdrOE.8 = Home City
HdrTB.9 = Fax Number 16 HdrOE.9 = Home Postal Code
HdrTB.10 = Pager Number 15 HdrOE.10 = Home State
HdrTB.11 = Mobile Number 17 HdrOE.11 = Home Country/Region
HdrTB.12 = Home Address 8 HdrOE.12 = Home Phone
HdrTB.13 = Home Address 2 9 HdrOE.13 = Home Fax
HdrTB.14 = Home City 11 HdrOE.14 = Mobile Phone
HdrTB.15 = Home County 27 HdrOE.15 = Personal Web Page
HdrTB.16 = Home Post Code 18 HdrOE.16 = Business Street
HdrTB.17 = Home Country 20 HdrOE.17 = Business City
HdrTB.18 = Work Address 22 HdrOE.18 = Business Postal Code
HdrTB.19 = Work Address 2 21 HdrOE.19 = Business State
HdrTB.20 = Work City 23 HdrOE.20 = Business Country/Region
HdrTB.21 = Work County 28 HdrOE.21 = Business Web Page
HdrTB.22 = Work Post Code 7 HdrOE.22 = Business Phone
HdrTB.23 = Work Country * HdrOE.23 = Business Fax
HdrTB.24 = Job Title 10 HdrOE.24 = Pager
HdrTB.25 = Department 26 HdrOE.25 = Company
HdrTB.26 = Organisation 24 HdrOE.26 = Job Title
HdrTB.27 = Web Page 1 25 HdrOE.27 = Department
HdrTB.28 = Web Page 2 19 HdrOE.28 = Office Location
HdrTB.29 = Birth Year 36 HdrOE.29 = Notes
HdrTB.30 = Birth Month
HdrTB.31 = Birth Day
HdrTB.32 = Custom 1
HdrTB.33 = Custom 2
HdrTB.34 = Custom 3
HdrTB.35 = Custom 4
HdrTB.36 = Notes
Terry R.
whim, lemel_man pounded out on the keyboard:
> TB 2.0.0.22, XP Pro+SP3
> I've just tried exporting my TB address book as a CSV so that it could
> be imported into my Gmail account. Unfortunately, although it imports
> with no apparent errors, it appears as mostly garbage in Gmail.
> Any ideas please?
>
Were you able to assign a field name to import multiple phone numbers?
I was importing yesterday and found that by just exporting a CSV in TB
would dump all the info into "Notes" in Gmail Contacts. So I named the
fields as they are named "Name" "Email" "Phone" and removed unneeded
ones. But I was unable to find a way to get multiple phone numbers
entered correctly. I tried "Phone Mobile" like the drop down selector
uses, but that didn't work.
Terry R.
--
Anti-spam measures are included in my email address.
Delete NOSPAM from the email address after clicking Reply.
Wolf K
[...]
> The TB Address Book contains 36 fields, compared to OE's 29. In case
> anybody is interested, here is a complete list.
Another candidate for an industry standard. There's no reason, apart
from bloodymindedness, for address book formats to vary between
programs. It's always possible to have n/a fields.
Cheers,
wolf k.
lemel_man
>
>
> Were you able to assign a field name to import multiple phone numbers?
>
> I was importing yesterday and found that by just exporting a CSV in TB
> would dump all the info into "Notes" in Gmail Contacts. So I named the
> fields as they are named "Name" "Email" "Phone" and removed unneeded
> ones. But I was unable to find a way to get multiple phone numbers
> entered correctly. I tried "Phone Mobile" like the drop down selector
> uses, but that didn't work.
Take a look at earlier postings in this thread.
Terry R.
Daylight Time) , and on a whim, lemel_man pounded out on the keyboard:
> Terry R. wrote:
>>
>> Were you able to assign a field name to import multiple phone numbers?
>>
>> I was importing yesterday and found that by just exporting a CSV in TB
>> would dump all the info into "Notes" in Gmail Contacts. So I named the
>> fields as they are named "Name" "Email" "Phone" and removed unneeded
>> ones. But I was unable to find a way to get multiple phone numbers
>> entered correctly. I tried "Phone Mobile" like the drop down selector
>> uses, but that didn't work.
>
> Take a look at earlier postings in this thread.
>
They don't answer my question.
lemel_man
>>>
>>> Were you able to assign a field name to import multiple phone numbers?
I didn't try; I tried only the things I mentioned.
>>>
>>> I was importing yesterday and found that by just exporting a CSV in
>>> TB would dump all the info into "Notes" in Gmail Contacts. So I
>>> named the fields as they are named "Name" "Email" "Phone" and removed
>>> unneeded ones. But I was unable to find a way to get multiple phone
>>> numbers entered correctly. I tried "Phone Mobile" like the drop down
>>> selector uses, but that didn't work.
>>
I said that just re-ordering the TB fields was not sufficient, I had to
use the OE names as well. Also, because TB had more fields than OE, some
fields had to be removed.
>> Take a look at earlier postings in this thread.
>>
>
> They don't answer my question.
What more can I say?
Terry R.
> Terry R. wrote:
I guess you could have said first off, "I didn't try". I was just
wondering if anyone had imported multiple phone numbers successfully. I
had already tried renaming field names and like the default export,
anything not recognized is just thrown into the "Notes" section.
lemel_man
> ...snip...
> I was just
> wondering if anyone had imported multiple phone numbers successfully. I
> had already tried renaming field names and like the default export,
> anything not recognized is just thrown into the "Notes" section.
>
>
Phone, Mobile Phone, and Business Phone. Any or all can accept multiple
sub-fields separated by commas, but they don't get individual names.
Terry R.
> Terry R. wrote:
>> ...snip...
> > I was just
>> wondering if anyone had imported multiple phone numbers successfully. I
>> had already tried renaming field names and like the default export,
>> anything not recognized is just thrown into the "Notes" section.
>>
>>
> As stated in an earlier post, the OE format has 3 phone fields: Home
> Phone, Mobile Phone, and Business Phone. Any or all can accept multiple
> sub-fields separated by commas, but they don't get individual names.
>
I had already tried using
Phone Work
Phone Home
Phone Mobile
as fields and the import just put them in "Notes". My reasoning was the
field name in the "Contact" form was PHONE followed by the drop down
dialogs Home Work Mobile, etc.
I tried the OE field names
Business Phone
Home Phone
Mobile Phone
and those imported properly.
I then tried TB's
Home Phone
Work Phone
Fax Number
Mobile Number
and only "Home Phone" imported properly, since it matches OE. The other
3 were placed in "Notes".
So it appears Gmail understands the OE field names but not TB's. Why am
I not surprised?
Thanks,
lemel_man
My post dated 01/09/2009 actually stated just that, re:
> I tried column swapping in Excel, but it didn't work. I thought it was because I lost my way in the swapping, so, I then wrote a little program to do the job for me.
> Although the columns were then in the correct order, it still didn't work.
> After a bit of head-scratching I figured that the column headings were NOT just names, the import process actually used them. When I used the OE names instead of the re-arranged TB ones it worked pretty well. But not well enough.
The OE field NAMES are actually used by Gmail.
You MUST use the OE names, and put them in the correct order. (Actually,
I'm not totally certain about having to put them in the correct order,
but I don't have the time to try it out.)
Terry R. wrote:
> ...snip...
>
> I had already tried using
> Phone Work
> Phone Home
> Phone Mobile
> as fields and the import just put them in "Notes". My reasoning was the
> field name in the "Contact" form was PHONE followed by the drop down
> dialogs Home Work Mobile, etc.
>
> I tried the OE field names
> Business Phone
> Home Phone
> Mobile Phone
> and those imported properly.
>
> I then tried TB's
> Home Phone
> Work Phone
> Fax Number
> Mobile Number
> and only "Home Phone" imported properly, since it matches OE. The other
> 3 were placed in "Notes".
>
> So it appears Gmail understands the OE field names but not TB's. Why am
> I not surprised?
>
Ron Hunter
new ISP, and also to Thunderbird. Other than having to move the columns
around in Excel to accommodate them to how Thunderbird handles them
(they came out with the email address farther down in the list than TB
would handle, they came over pretty easily.
TB addressbook import should be expanded to handle OE/Outlook/MSN field
names.
Terry R.
> Terry,
I placed one "card" out of order to test. The "First Name" field
inserted properly even though it was placed between phone numbers. All
the rest of the fields were placed into the "Notes" section with their
data. So it's hard to tell what order matters and what doesn't unless
tested with more variables.
lemel_man
>
>
> I placed one "card" out of order to test. The "First Name" field
> inserted properly even though it was placed between phone numbers. All
> the rest of the fields were placed into the "Notes" section with their
> data. So it's hard to tell what order matters and what doesn't unless
> tested with more variables.
>
>
The table I gave in my post of 02/09/2009 gives the order of the fields
in both TB and OE, and I also give the TB field numbers that correspond
with the OE ones.
For example, TB field 5 is called 'Primary Email' (without the quotes),
and is the same as OE field 6 which is called 'E-mail Address'. If you
call OE field 6 'Primary Email' it won't work.
As I wrote earlier, these actually work, but you must ensure that any
field value that contains a comma is enclosed in quotes.
nilton
CSV exports of address books:
TB_address_book
OE_address_book
GMail_address_book
GMail_address_book_for_Outlook
address_books_compared.txt is a TAB delimited file exported from Excel.
It may not be complete and some fields may be associated in a different
way, but it is a pretty good starting point.
Just replace the column header row with the new name from this table and
the data shows up correctly in the new address book.
The order of the fields is not significant. The field names are. And
these field names are in the first row.
There is no need to change anything else in the CSV, except if the CSV
files were created with different Regional Settings, then they may need
some editing.
Thanks
Nilton
Terry R.
a whim, nilton pounded out on the keyboard:
Those don't line up too well.