All,

love this forum, so I decided to do something back.

I own a HTC Touch, with a problem for the phonebook: it does not recognize the number properties (mobile, home, work, ...). I know it is possible to copy the Phonebook db from the Avic, edit it via SQLite (love the FireFox extension, btw) and then back to the Avic. But that is tedious. So I wrote a small Outlook macro to do this for me: it loops through all my outlook contacts (synced with Touch), and writes a SQL script. Optionally it also executes this script and writes the actual DB file in one go.

How does it work: for every contact, it looks for 3 phonenumbers: mobile, work and home. If it only finds one, it writes the record with dwType 0 (default), otherwise it sets the appropriate type. The output is sorted by FullName (works for me). Another gizmo is you can also assign a category (I chose "dont-export") for the contacts you do not want to show up in the phonebook.

Attached is the Class file, ready to be loaded into outlook. The macro writes into the temp folder (c:\temp), but that is cutomizable. For sqlite to write the DB, it is best to also put sqlite3.exe in the temp folder.

Of course, the file written does not contain your specific BlueTooth ID, so you will either have to rename it, or customize the target filename in the macro

have fun

weo

Apparently the forum did not like the CLS extension, so I added an extra ".txt"

Take that off, and you'll be fine

sorry about that

weo

as per below remark, I had to make some amendments to allow for Outlook2003.

Do not try to import the file, as this will not be "executable", go for Copy/Paste instead

Weo

export-phonebook.txt

have to correct myself, I made a mistake in the mobile and work index (dwType 3 = mobile!).

So to all who already downloaded this code, please update...

Thanks avic-weo for the macro post. I tried to load it into outlook via M Visual Basic (File... Import File...), but it does not seem to recognise the code. The code loads up in ThisOutlookSession, but cannot be compiled. I use XP and Outlook 2003 and VB 6.5. Any advice on how to load up the macro?

...How does it work: for every contact, it looks for 3 phonenumbers: mobile, work and home. If it only finds one, it writes the record with dwType 0 (default), otherwise it sets the appropriate type. The output is sorted by FullName (works for me)...

How did you find out/know the dwType? I scratched my head but didn't got it.

Thanks avic-weo for the macro post. I tried to load it into outlook via M Visual Basic (File... Import File...), but it does not seem to recognise the code. The code loads up in ThisOutlookSession, but cannot be compiled. I use XP and Outlook 2003 and VB 6.5. Any advice on how to load up the macro?

Hi MustangAvic, glad to see somebody is interested in this code.

I developed this using Outlook2007 and Vista, but there should be no reason why this does not work under XP/OL2003. A quick check on such a machine did reveal a quirk: importing the file creates another module, which then can not be "executed".

OL2007 also defined a generic Folder type, which in OL2003 needs to be replaced by MAPIFolder

So I suggest the following:

• 
  [*:2pzctsjy]start Outlook
  [*:2pzctsjy]open VBA editor by hitting Alt-F11
  [*:2pzctsjy]copy/paste the code from the cls file in the module ThisOutlookSession from Option Explicit onwards
  [*:2pzctsjy]return to Outlook (you can close the VBA editor)
  [*:2pzctsjy]run the macro via Tools->Macro, or by hitting Alt-F8

Afterwards, when you copy the generated file in the MiTACAP folder at the appropriate location, it gets copied automagically. Actually, this whole process is faster than uploading my contacts from my phone to the AVIC.

I have also updated the txt file with the above

have fun

Weo

How did you find out/know the dwType? I scratched my head but didn't got it.

credit goes to Maverickster: http://avic411.com/forum/viewtopic.php?p=132984#p132984

A BIG THANK YOU

This works great!

Thanks OP... what a time-saver!

I added additional functionality to select your Contacts folder (in case you have several as I do).

I also added a confirmation to ContactsToSQDB, asking if it should overwrite the contacts.sql file with a new one. If no, it will just skip the call to ContactsToSQL.

export-phonebook.cls.txt

After reading

http://www.avic411.com/forum/viewtopic.php?f=32&t=25921

i thought if the idea could be solved here.

Question:

Is it possible to export the adresses of contacts as POIs? Does it make sense in the AVIC?

I really wish we would have access to the source code of this phone application to improve it! They use a SQL database to store a name and a phone number as normal text and nothing more. There is much more potential in it like showing contact pictures instead of icons or set a contact as destination for navigation. Hellooo Pioneer!

Weo,

thank's so much for this perfectly working workaround!

The import of the contacts on my Nokia N71 didn't work well (first name/last name got in wrong direction) - and I was spending DAYS in trying different configurations without success.

With your macro it just took me 10 minutes to execute & replace the db-file on the unit - and now everything is the way I ever wanted it - I can distinguish between home, office and mobile - and everything is ordered as it should be (lastname, first name).

Thank you so much!

Bye,

Boby

you are very welcome

Hey everyone.

I am pretty much a newbie with this stuff, but have gotten everything to work to the point of now I have a file in my c:\temp folder "contacts.sql"

What do I need to do to get this file correctly on the AVIC? Is there anything else I need to do other than just place this file somewhere on the unit's HD?

Thank you very much, this is such a great forum.

Justin

The macro creates two files:

- One file containing the SQL code which is being executed afterwards and (=you don't need it for your device!)

- one file containing the data you need to copy to your unit (that's what you need to copy).

How to?

The easiest way:

1) Look at your unit, what's the Bluetooth-ID of your cell phone. You can find it out by looking into the folder "\My Flash Disk\USER\UserData\PHONEDATA". There you'll find a file called like "PhoneBookxxxxxxxxxx.db", where the x here stand for diffrent numbers and letters. That's the name the created file with the data has to have when you copy it there (otherwise your unit won't find it). It's important, that you've allready registred your cell phone initially on the unit (otherwise the directory will stay empty).

2) Open the Outlook macro and change the following line:

Const SQDB As String = "C:\temp\contacts.sqdb"

to: Const SQDB As String = "C:\temp\PhoneBookxxxxxxxxxx.db" (according to the name you discovered in step 1)

3) Start the macro

4) Copy the created .db-file from C:\temp to a SD card, boot your device in testmode and copy the file to the PHONEDATA directory, overwriting the exising file on your unit.

Regards,

Boby

Thanks!

Tried it out this morning and the contacts are very nicely done.

What a great site and helpful people.

Justin

cool, way to go, Boby!

This macro is outstanding. I have 2000+ contacts in Outlook/on my WM phone, and was just thinking "I wish there was a simple way of getting a few select contacts directly from Outlook to the AVIC." This thing worked perfectly. I used the FeloniusMonkey version.

By the way, the necessary sqlite3.exe file can be found here: http://sqlite.org/download.html

Awesome job.. Thanks..

I noticed some points..

1) non-English characters are not displayed correctly on the AVIC. I added a replace function for these characters

2) Mobile and Business phones were mixed on the AVIC. I changed them too..

I attached the file

export-phonebook.cls.txt

Hi Magna,

the character conversion is nice, but there might be a problem with the following lines:

     Call PrintSQLElement(myItem.FullName, myItem.HomeTelephoneNumber, numQual * 1)
     Call PrintSQLElement(myItem.FullName, myItem.MobileTelephoneNumber, numQual * 3)
     Call PrintSQLElement(myItem.FullName, myItem.BusinessTelephoneNumber, numQual * 2)
     Call PrintSQLElement(myItem.FullName, myItem.OtherTelephoneNumber, numQual * 0)

The AVIC only supports 3 dwTypes: 1, 2 and 3. Type 0 is used for no type. What I do with numQual is determine whether there is more than 1 phone number for a given contact. If there is, I use types 1, 2 and 3. If there isn't, type 0 is used. So adding a line with numQual *0 is dangerous in the sense that a contact appears with both his BusinessTelephoneNumber and his OtherTelephoneNumber, which is not what you want.

So you either have to take out the numQual altogether, or you will have to take out the last line.

Also, having numbers mixed up was a bug I thought I had corrected. Were you still working on an older version?

Weo

Weo,

I have the number mix up with the FeloniusMonkey version. I compared the code, and it looks like he used your revised version (Jul 22). Here is what happens:

What doesn't work:

(outlook contact=>avic contact)

Home+Mobile=>Home+Office

What works:

Home=>no label

Office=>no label

Mobile=>no label

Home+Office+Mobile=>Home+Office+Mobile

Office+Mobile=>Office+Mobile

I didn't test Home+Office

on edit: looks like the identifier for office and mobile is mixed up still.

This is where I got the info from:

For those who are interested, the dwType column controls multiple numbers for each contact. Specifically:

0 = General (only use this if you only have one number for that particular contact)

1 = Home

2 = Work

3 = Mobile

I will make sure the original file works that way. (Might be I am a little lysdectic???)

I did try out all combo's, and did not have any problem with them, but I do not really understand what you mean by

What doesn't work:

(outlook contact=>avic contact)

Home+Mobile=>Home+Office

Home+Mobile should result in Home+Mobile (that's what it does on my computer anyway )

Weo

I am having trouble making this work and I hope you can help. I have changed the file names to the following:

Const SQDB As String = "c:\temp\PhoneBookxxxxxxxxx.db"

Const SQLfile As String = "c:\temp\contacts.sql"

Const SQLITE As String = "c:\temp\sqlite3.exe"

The first file name is actually the file my nav unit created so I think I am OK there. The other file names I changed to c:/temp/*.* because I don't have a D drive.

I can get through loading the text into the macro editor and running the macro. I get two choices and select "ThisOutlookSession.ContactsToSQDB" and run it. Thae macro asks permission to overwrite C:\temp\contacts.sql if it exists and I select yes. The macro asks what folder and I select Contacts. The macro asks "OK to generate PhonebookDB" and I select yes. At this point it has created the contacts.sql in C:\temp. Otherwise, the folder is empty. Then I get the error "File Not Found" and it returns me to the editor.

Where am I screwed up?

So,

your third line in the macro looks like this:

Const SQLITE As String = "C:\temp\sqlite3.exe"

Question: Have you downloaded the SQLite programm from here (http://www.sqlite.org/sqlite-3_6_17.zip), unzipped & stored the file "sqlite3.exe" in the folder C:\temp ?

Excellent! That did the trick. I will try loading it up on the unit tomorrow. Most of my numbers have parens around the area code and hyphens between prefix and suffix so I am anticipating that I may have a problem there. We'll see.

Thanks again.

Worked like a charm, parentheses and all.