I Give Up

[skippybangkok]

Office uses a different mail system, so export my contacts as a CSV file, and try to import to Outlook 2007. The import works fine, but my numbers are stored with + infront ( i.e. +66 ) so when I call from anywhere, no need to fiddle country specific dialing codes.

problem is a +66.... gets imported as just 66...........

tried every triuck in the book, exporting as excel 2003 version, access database etc etc............

advice aprpeciated.

[Forkinhades]

In excel you could do a macro script that changes all the 66 to +66

speaking from memory

[robblok]

In excel you could do a macro script that changes all the 66 to +66

speaking from memory

macro.. that is much to hard.. just find and replace much easier.

[TheWalkingMan]

Just put a "+" in the cell before the phone numbers. In the cell after the numbers, or a cell of your choice type "=+ (the cell with the plus sign)& cell with the phone number.

The cell should look like =c1&c4 which should give you the text +66.....

TheWalkingMan

[Crossy]

Use a custom cell format, for the cells with numbers in try +0 as a custom format (just modify one of the existing custom formats).

[skippybangkok]

Well this is where it gets weired......... there is a "+" infront of the number.

the only number it got right was +66 xxx yyyy ( with spaces - obviously recognizes it as text.) other numbers in spreadsheet which are lsited as i.e. '+66xxxyyyy get imported as 66xxxyyyy

[innerspace]

Had exactly this issue yesterday making a custom script to export a database.

Found that both php and excel were deleting my leading zero, the same as your plus.

The solution I developed was as you mentioned above - to put a space in the numbers. (I did it after 5 characters which displays nicely)

Unfortunately I did this in PHP rather than excel so don't have an exact solution for you but if you aim on these lines it should work.

The problem is that csv files don't store data types so excel instantly converts it based on a guess when opening and discards information that it deems irrelevent.

This is why even turning the data type to text fails to make a difference.

[GmB]

If your exported CSV is correct and contains the + when you import it into excel you can set the format of that column to text.

By the way what system are you using at work? It's odd there isn't a tool for the synchronization of contacts, calendar, notes etc.

[welo]

The problem is that the field is interpreted as number. Therefore the + marks a positive value/number and hence is redundant and is removed.

CSV usually encloses text values in quotes, e.g. "+66893435". When imported this will be interpreted as text and the plus will not be removed.

When adding one or more spaces in between (some of) the digits then this is no longer a 'number' in the technical term - numbers don't contain spaces or characters. The export will no longer detect the field as number and put it in quotes when writing to the csv files.

I think the Excel importer allows overriding the format during import.

EDIT: That does not seem to be part of the Import Wizard. But as GmB and Crossy already pointed out, if you can get the data into Excel with the plus, you can then mark the column as text (overriding the default detection)

You can then re-export the list, and the phone numbers should then be enclosed in quotes.

One more thing: Try opening the csv file in a text editor (Notepad or other) and replace +66 with "+66 (Use Find/Replace) - I think that the quotes don't have to be necessarily closed at the end to be recognized as text, but this depends on the software used.

welo

Edited July 1, 2010 by welo

[nikster]

Exactly... I believe when importing a CSV you can specify those fields as TEXT, not numbers. Then the + and leading 0 would get preserved. Perfectly obvious to a programmer

In case the importer doesn't allow it, it might just be the cell format - set it to text and the +s might just re-appear.

If that fails, I'd just do a search and replace with a text editor, replace all +66 with $PLUS$66, then once it's imported, replace all $PLUS$ with +.

Edited July 1, 2010 by nikster

[skippybangkok]

YES !!!!! It worked. Thanks Guys. Yipeeeeeeeeeeeeeeeeeee

company using lotus notes.....hate it, wont use it. Thus, export my address book out, and into outlook