GAB2OAB Google contacts to Outlook Contacts

[t0nZ]

I was in a hurry to convert 200+ address book from Google Contacts (G Suite, contacts exported in .CSV with Google Takeout, but the contact format is the same for every Google Gmail account)  to Outlook contacts, so I developed this quick and very dirty script.

Starting from a .CSV exported from Google, you can run the script and you have the contacts ported directly (no outlook import) on the Outlook Address book.

In my experience, the fields of Google contacts are not fixed in number  and maybe also in position, so I made the choice  to import only certain fields to Outlook, scanning every line.

These fields, exact google names:

        "Given Name"  
        "Family Name"  
        "E-mail 1 - Value"  
        "E-mail 2 - Value"  
        "Phone 1 - Value"  
        "Phone 2 - Value"  
        "Website 1 - Value" 
        "Notes"  
        "Name"  
        "Address 1 - Formatted"  

Also the lines of google contacts can be weird as CR and LF are not "regular".

I include my code, (worked with no errors on a lot of conversions) and also a very small fake adress book with examples of problems I encountered and resolved.

expandcollapse popup

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Icon=Icone\Win98-ico\script_file_teal.ico
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****

;GOA2OAB
;G.oogle A.ddress B.ook 2 (to) O.utlook A.ddress B.ook
;(C) NSC 2019
; convert a google (takeout) address book to an Outlook Address Book

#include <File.au3>
#include <Array.au3>
#include <_Gollog.au3>
#include <MsgBoxConstants.au3>
#include <FileConstants.au3>
#include <MsgBoxConstants.au3>
#include <WinAPIFiles.au3>
#include <String.au3>
#include <Math.au3>


; *****************************************************************************
#include <OutlookEX.au3> ; super perla pearl outlook create contacts !!!!!
; *****************************************************************************
Global $oItem
Global $oOutlook = _OL_Open()
If @error <> 0 Then MsgBox(16, "OutlookEX UDF", "Error creating a connection to Outlook. @error = " & @error & ", @extended = " & @extended)
; *****************************************************************************



Global $ver = "V.0.8192", $tempdir = "c:\GAB2OAB_temp", $File2convert, $aRetArray, $workfile = $tempdir & "\work-GAB2OAB.csv", $workfile2 = $tempdir & "\WORK2-gab2oab.csv", $FINALab = $tempdir & "\NEW-ab.csv", $A3clean[0], $A4clean[0], $A5clean[0]

; Gollog Autogenerated Gui
$mygui = "G.oogle A.ddress B.ook 2 (to) O.utlook A.ddress B.ook " & $ver & "           NSC 2019"
gollog_ini($mygui, 600, 200, 50, 50, "0x0033cc", "0xA8D9BD", "courier", 8, 600, @ScriptDir & "\GAB2OAB_log.txt") ; initialization with colors and font choice, more options see the UDF
Gollog(">>>>>>>> GAB2OAB " & $ver)
$File2convert = FileOpenDialog("Select Google Address Book File", @DesktopDir & "\", "CSV (*.csv)", BitOR($FD_FILEMUSTEXIST, $FD_MULTISELECT))
Gollog("selected " & $File2convert)

If Not FileExists($tempdir) Then
    Gollog("creating " & $tempdir)
    DirCreate($tempdir)
EndIf

If FileExists($workfile) Then
    Gollog("deleting " & $workfile)
    FileDelete($workfile)
EndIf
If FileExists($workfile2) Then
    Gollog("deleting " & $workfile2)
    FileDelete($workfile2)
EndIf
If FileExists($FINALab) Then
    Gollog("deleting " & $FINALab)
    FileDelete($FINALab)
EndIf

cleanfile()

readfile()

cleanfile2()

readfile2()

write2Outlook()


Exit

Func write2Outlook()

    Gollog("---> Assigning Fields...")
    Sleep(300)

    Local $FirstName = 4 ; initialize with Yomi name - always empty in tested companies, low class workaround
    Local $LastName = 4
    Local $Email1Address = 4
    Local $Email2Address = 4
    Local $MobileTelephoneNumber = 4
    Local $OtherTelephoneNumber = 4
    Local $Webpage = 4
    Local $Body = 4
    Local $Fullname = 4
    Local $BusinessAddress = 4


    For $z = 0 To UBound($A5clean, 2) - 1
        ;If $A5clean[2][$z] = "Additional Name" Then $MiddleName = $z
        ; limit @ 10
        If $A5clean[1][$z] = "Given Name" Then $FirstName = $z
        If $A5clean[1][$z] = "Family Name" Then $LastName = $z
        If $A5clean[1][$z] = "E-mail 1 - Value" Then $Email1Address = $z
        If $A5clean[1][$z] = "E-mail 2 - Value" Then $Email2Address = $z
        If $A5clean[1][$z] = "Phone 1 - Value" Then $MobileTelephoneNumber = $z
        If $A5clean[1][$z] = "Phone 2 - Value" Then $OtherTelephoneNumber = $z
        If $A5clean[1][$z] = "Website 1 - Value" Then $Webpage = $z
        If $A5clean[1][$z] = "Notes" Then $Body = $z
        If $A5clean[1][$z] = "Name" Then $Fullname = $z
        If $A5clean[1][$z] = "Address 1 - Formatted" Then $BusinessAddress = $z
    Next
    Gollog("Fields Found !")

    For $i = 2 To UBound($A5clean, 1) - 1


        Gollog("Contact 2 Outlook line " & $i)
        ;Gollog($A5clean[$i][1])
        ; Create a contact with first- and lastname
        ; *****************************************************************************
        $oItem = _OL_ItemCreate($oOutlook, $olContactItem, "", "", "FullName=" & $A5clean[$i][$Fullname], "BusinessAddress=" & $A5clean[$i][$BusinessAddress], "FirstName=" & $A5clean[$i][$FirstName], "LastName=" & $A5clean[$i][$LastName], "Email1Address=" & $A5clean[$i][$Email1Address], "Email2Address=" & $A5clean[$i][$Email2Address], "WebPage=" & $A5clean[$i][$Webpage], "MobileTelephoneNumber=" & $A5clean[$i][$MobileTelephoneNumber], "OtherTelephoneNumber=" & $A5clean[$i][$OtherTelephoneNumber], "Body=" & $A5clean[$i][$Body])
        If @error <> 0 Then Exit MsgBox(16, "OutlookEX UDF: _OL_ItemCreate ", "Error creating a contact'. @error = " & @error & ", @extended = " & @extended)
        ; Add a picture to the contact
        ;$oItem.AddPicture(@ScriptDir & "\The_Outlook.jpg")
        $oItem.Save()
        ; *****************************************************************************

    Next
    MsgBox(64, "OutlookEX UDF: the magic was made!", "All contacts successfully created ")

    _OL_Close($oOutlook) ; close the connection to Outlook
EndFunc   ;==>write2Outlook

Func cleanfile()

    Gollog('start check for unwanted LF... / final rogue "')
    Sleep(300)
    Local $A2Clean = FileReadToArray($File2convert)
    ;_ArrayDisplay($A2Clean, "original file", Default, 8)


    Local $dimLIST = UBound($A2Clean) - 1
    Local $string2write = ""
    Local $Astring1 = ""
    Local $Astring2 = ""

    Local $salvaAstring1 = ""
    Local $openLF = 0 ; we are between "LF" across lines

    For $i = 0 To $dimLIST
        Gollog('workin on line ' & $i)
        ;Gollog($A2Clean[$i])

        $Astring1 = StringToASCIIArray($A2Clean[$i])

        ;_ArrayDisplay($Astring1)
        Local $DimAstring1 = UBound($Astring1) - 1

        If $openLF = 1 Then
            ;check new line if start with "
            $Astring2 = StringToASCIIArray($A2Clean[$i])
            If $Astring2[0] = "34" Then

                ;       gollog('found initial " ')
                $openLF = 2
            Else
                Gollog('error new line not starting with "')
                MsgBox(48, "error", 'error new line not starting with "')
            EndIf
        EndIf

        If $Astring1[$DimAstring1] = "34" Then
            ;   gollog('First line -> Found final "')

            If $openLF <> 0 Then
                $salvaAstring1 = $salvaAstring1 & $A2Clean[$i]
            Else
                $salvaAstring1 = $A2Clean[$i]
            EndIf

        Else
            _ArrayAdd($A3clean, $salvaAstring1 & $A2Clean[$i])
            ;Gollog("adding to cleaned array state:" & $openLF)
            $openLF = 0
            $salvaAstring1 = ""
        EndIf

    Next
    Gollog('end clean from rogue "')
    ;_ArrayDisplay($A3clean, 'cleaned from rogue "')

    ;___________________________________P-PP-PP,P-------------------------------------------------
    Gollog('Start joining lines based on ODD " ')
    Sleep(300)
    ;_MathCheckDiv ( $iNum1 [, $iNum2 = 2] ) pari e dispari perla pearl



    Local $hFileOpen = FileOpen($workfile2, $FO_APPEND)
    If $hFileOpen = -1 Then
        MsgBox($MB_SYSTEMMODAL, "", "An error occurred whilst writing the file.")
        Return False
    EndIf

    Local $dimLIST = UBound($A3clean) - 1
    Local $Astring1 = ""
    Local $Astring2 = ""
    Local $dimSTRING = ""
    Local $AapiciINriga
    Local $numeroapiciINriga = 0
    Local $sommaNumeroapiciINrighe = 0
    Local $salvaAstring1 = ""
    Local $dispari = 0


    For $i = 0 To $dimLIST
        Gollog("workin' on line " & $i)
        $Astring1 = StringToASCIIArray($A3clean[$i])
        ;_ArrayDisplay($Astring1)
        ;Gollog($A3clean[$i])
        $AapiciINriga = _ArrayFindAll($Astring1, '34')

        If $AapiciINriga <> -1 Then
            $numeroapiciINriga = UBound($AapiciINriga)
            ;   Gollog("N° " & $numeroapiciINriga & "  apici in riga " & $i)
            $sommaNumeroapiciINrighe += $numeroapiciINriga
            If _MathCheckDiv($sommaNumeroapiciINrighe) = 1 Then
                $salvaAstring1 = $salvaAstring1 & $A3clean[$i]
                $dispari = 1
            Else
                Local $towrite = $salvaAstring1 & $A3clean[$i]
                ;       gollog("scrivo " & $towrite)

                FileWriteLine($hFileOpen, $towrite)
                $salvaAstring1 = ""
                $sommaNumeroapiciINrighe = 0
                $dispari = 0
            EndIf
        Else
            ;   Gollog("zero apici in riga " & $i)
            If $dispari = 1 Then ;linea vuota o senza apici ma " aperte dispari
                $salvaAstring1 = $salvaAstring1 & $A3clean[$i]
            Else
                Local $towrite = $salvaAstring1 & $A3clean[$i]
                ;       gollog("scrivo " & $towrite)

                FileWriteLine($hFileOpen, $towrite)
                $salvaAstring1 = ""
                $sommaNumeroapiciINrighe = 0
                $dispari = 0
            EndIf
        EndIf

    Next

    Gollog('END joined lines based on ODD "')
    ;_ArrayDisplay($A4clean, 'joined lines based on ODD "')
    FileClose($hFileOpen)

EndFunc   ;==>cleanfile


Func readfile()
    gollog("reading work file and creating multiD array")
    Sleep(300)
    $A4clean = FileReadToArray($workfile2)
    If @error = 1 Or @error = 2 Then
        gollog("error reading work2 file " & @error)

    Else
        ;       _ArrayDisplay($A4clean, "cleaned 1", Default, 8)
    EndIf
EndFunc   ;==>readfile


Func cleanfile2()

    Local $dimLIST = UBound($A4clean) - 1
    Local $Astring1 = ""
    Local $conta = 0
    Local $salvaconta = 0
    Local $dimSTRING = ""
    $salvalinea = ""
    Local $standardNumeroVirgole = 0
    Local $hFileOpen = FileOpen($FINALab, $FO_APPEND)
    If $hFileOpen = -1 Then
        MsgBox($MB_SYSTEMMODAL, "", "An error occurred whilst writing the file.")
        Return False
    EndIf


    Gollog('Checkin , number')
    For $i = 0 To $dimLIST
        ;Gollog('workin on line ' & $i)
        $Astring1 = StringToASCIIArray($A4clean[$i])
        ;_ArrayDisplay($Astring1)
        $dimSTRING = UBound($Astring1) - 1

        For $y = 0 To $dimSTRING
            If $Astring1[$y] = "44" Then ; secondo step ,
                $conta += 1
            EndIf
        Next

        If $i = 0 Then
            $standardNumeroVirgole = $conta
            gollog("----------numero virgole -> " & $standardNumeroVirgole)
        EndIf

        If $conta = $standardNumeroVirgole Then
            FileWriteLine($hFileOpen, $A4clean[$i])
        EndIf

        If $conta > $standardNumeroVirgole Then
            Gollog('>>> Line ' & $i & ' Virgole' & $conta)
            $toprecise = $A4clean[$i]
            $tofinal = ''

            While StringInStr($toprecise, '"') <> 0
                $tofinal = preciseremoval($toprecise) ;super smart
                $toprecise = $tofinal

            WEnd
            FileWriteLine($hFileOpen, $toprecise)
        EndIf

        If $conta < $standardNumeroVirgole Then
            Gollog('<<< Line ' & $i & ' Virgole' & $conta)
            $salvalinea = $salvalinea & $A4clean[$i]
            $salvaconta += $conta
            If $salvaconta = $standardNumeroVirgole Then
                $salvaconta = 0
                FileWriteLine($hFileOpen, $salvalinea)
                $salvalinea = 0
            EndIf
        EndIf

        $conta = 0
    Next

    FileClose($hFileOpen)

EndFunc   ;==>cleanfile2

Func writeWORKfile2()
    Gollog("writing work file")
    If _FileWriteFromArray($workfile, $A4clean) <> 1 Then
        gollog("error writing work file " & @error)
    EndIf
EndFunc   ;==>writeWORKfile2

Func readfile2()
    gollog("reading work file and creating multiD array")

    If _FileReadToArray($FINALab, $A5clean, Default, ",") = 0 Then
        MsgBox(48, "errore", @error)
    EndIf

    ;_ArrayDisplay($A5clean, "cleaned 2", Default, 8)

EndFunc   ;==>readfile2



Func preciseremoval($Pclean)
    ; llllllllllllllllllllllllll ---- pass 1 for ",,"
    $Astring1 = StringToASCIIArray($Pclean)
    ;_ArrayDisplay($Astring1)
    $dimSTRING = UBound($Astring1) - 1

    Local $salvadelete = ""
    Local $cond1 = 0
    For $y = 0 To $dimSTRING

        If $cond1 = 1 Then ; , removed so exit from loop
            If $Astring1[$y] = "34" Then ; terzo step " in chiusura
                ;   gollog('Found right "')
                If $salvadelete = "" Then
                    $salvadelete = String($y)
                Else
                    $salvadelete = $salvadelete & ";" & $y
                EndIf

                $cond1 = 0
                ExitLoop
            EndIf
        EndIf

        If $cond1 = 1 Then
            If $Astring1[$y] = "44" Then ; secondo step ,

                If $salvadelete = "" Then
                    $salvadelete = String($y)
                Else
                    $salvadelete = $salvadelete & ";" & $y
                EndIf
            EndIf
        EndIf

        If $Astring1[$y] = "34" And $cond1 = 0 Then ;PRimo step  per primo ->"
            $cond1 = 1
            ;gollog('Found left "')

            If $salvadelete = "" Then
                $salvadelete = String($y)
            Else
                $salvadelete = $salvadelete & ";" & $y
            EndIf
            ;Gollog("debug" & $salvaApici)
        EndIf

    Next

    ;   Gollog("apici e virgole da togliere: " & $salvadelete)
    ;gollog("virgole  togliere: " & $salvaVirgole)

    If _ArrayDelete($Astring1, $salvadelete) = -1 Then
        Gollog("error in arraydelete apici " & @error)
        gollog(StringFromASCIIArray($Astring1))
    Else
        gollog(' PASS 1 removed unwanted ",," from line ')
    EndIf


    $Astring2 = StringFromASCIIArray($Astring1)
    $Pclean = $Astring2

    Return $Pclean


EndFunc   ;==>preciseremoval

You will need the OutlookEX.au3 ,  look at this link and also my logging UDF  _Gollog.au3, attached.

Used on Outlook  from 2010 to 2016/365 with no problems.

Hope this script can help someone.

 

TEST1.csv _GOLLOG.au3

Edited June 18, 2019 by t0nZ

[argumentum]

Thanks for sharing @t0nZ   

PS: add the links to the UDFs to the 1st post  

Edited June 18, 2019 by argumentum
=)

[t0nZ]

Hi @argumentum, I added the link in the first post  to the fantastic OutlookEX UDF (credits and many thanks to @water) , and my log UDF (gollog) is attached in first post.

Yess you are right c:\temp may be in use , changed !

Thank you for the tips.

[water]

As soon as I find some spare time I will create a new function in OutlookTools to import contacts from a CSV file

[OSMMDAIJI]

Greate!

[water]

Done! Version 0.4.0.0 has a new function: _OLT_CSV_Import

[t0nZ]

Great,  I'll test IT ASAP !

Super @water !