gmail help excel backup restore android contacts list lost preceding zero

[abisdad]

Hi,

Had the problem of my contacts disappearing on my Samsung Galaxy S i9000 four times, and got fed up of doing restores, so read up and found that if I used G-mail, they would always be backed up....

Problem was that my contacts were on my Android device, not my sim or anything else.

So I backed up to a CSV file (backedup-contacts.csv) via the cable and the Samsung app "Kies", then imported into my G-mail, by logging into G-mail, clicking on the "G-mail" drop down list on top LHS of screen and selecting Contacts. Then within Contacts, clicking the "more" button at the top, and selecting "import", choose file, and selecting my "backedup-contacts.csv", and imported.

This didn't work, as only some of the field came down! I then undid that within G-mail (It lets you roll back really easily!), and then did a backup to a .CSV file (google.csv) and looked at the format of the file - BINGO! It showed me all the file field names, and then was just a matter of copying the data in MS Excel (sorry LibreOffice - I'm more confident in Excel for stuff like this!) from my backedup-contacts.csv to google.csv, making sure it was all in line - (I added a contact called "a a" and filled every field with "aaa" or "111" to make sure they were all in line). Also make sure that your number columns are wide enough to display the numbers, otherwise Excel converts it to Scientific Notation, which you don't want.

THE BRAVE BIT!!! - MAKE SURE YOU HAVE A GOOD AND TESTED BACKUP BEFORE YOU DO THIS BIT!

I then deleted all my G-mail contacts on my computer and let it sync to my phone!

I then deleted all my contacts on my phone too!

I waited until the sync had finished, then switched my phone off.

<GULP>

I then restored from "google.csv" to my G-mail account, and switched my phone back on again. It then synced, the 550 contacts took a minute or so via my house's wifi.

SUCCESS! All my contacts are there, BUT I've lost all the leading zeros... :hmpf:

This was because Excel was trying to be clever, and treated them as numbers, which should not have preceding zeros!

So the billion dollar question is how do I get them back, as there are various length numbers, some start with a "+" for international numbers and it's very very frustrating!

I've Googled this, and lots of people have this problem, but most assume that their numbers are of a uniform length, so any suggestions....

Many thanks,

Abisdad.

 

[abisdad]

Hi all - back again, with what worked for me!

This a four part solution, and quite manual - sorry.

Part 1:

I backed up my G-mail contacts list again to "google.csv", then created two new columns after each "Phone value" field - remembering to keep the column width's wide enough to see all the numbers...

Part 2:

In the first new column I created this formula, and copied it down for all the contacts (Ctrl+C, then highlight the destination cells, then Ctrl+V will do it):

=IF(AE2="","",IF(LEFT(AE2,1)="+",AE2,"0"&AE2))

What this formula does is check the cell containing the phone number (AE2), the cell it is in is (AF2):

• if it's blank, then leave the new cell it blank
• if the number is an international one preceded by a "+", then just copy the number as is
• else add a "0" before the number.

Part 3:

Highlight the all the cells with the formula in, which should be displaying your corrected numbers now, and copy (Ctrl+C)

Select the next cell to the right of your first contact formula (AF2), and do a right click, select "Paste Special", then click on "Paste values - Number & Formatting", which will paste all the new values, not just the formulas.

Part 4:

The last part is just to move the field label "Phone 1 - Value" over 2 cells from AE1 to AG1. Then delete the two columns AE & AF.

Do this for each of the Phone value fields, save as a CSV and close.

Then delete all your G-mail contacts on your computer or phone, let them sync, and do a restore from google.csv.

All done!

 

[hfinger]

What happens if, in Excel, you set the telephone number column to Text, i.e. not General or Numbers? Does this save the leading zeros to CSV format files?