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.
#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
