This script takes a Google Contacts CSV and imports it into a simple schema. This script is a Groovy Rule Script Rule for use in RuleChains.

ImportGoogleContactsCSVtoDatabase

sql.execute("""
CREATE TABLE IF NOT EXISTS `GoogleContacts` (
  `id` bigint(18) NOT NULL AUTO_INCREMENT,
  `Name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Given Name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Additional Name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Family Name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Yomi Name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Given Name Yomi` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Additional Name Yomi` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Family Name Yomi` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Name Prefix` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Name Suffix` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Initials` varchar(6) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Nickname` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Short Name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Maiden Name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Birthday` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Gender` varchar(6) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Location` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Billing Information` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Directory Server` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Mileage` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Occupation` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Hobby` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Sensitivity` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Priority` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Subject` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Notes` longtext COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
""")
sql.execute("""
CREATE TABLE IF NOT EXISTS `GoogleContactsEmail` (
  `contact_id` bigint(18) NOT NULL,
  `Type` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Value` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL,
  KEY `contact_id` (`contact_id`),
  CONSTRAINT `GoogleContactsEmail_ibfk_1` FOREIGN KEY (`contact_id`) REFERENCES `GoogleContacts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
""")
sql.execute("""
CREATE TABLE IF NOT EXISTS `GoogleContactsPhone` (
  `contact_id` bigint(18) NOT NULL,
  `Type` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Value` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  KEY `contact_id` (`contact_id`),
  CONSTRAINT `GoogleContactsEmail_ibfk_1` FOREIGN KEY (`contact_id`) REFERENCES `GoogleContacts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
""")
sql.execute("""
CREATE TABLE IF NOT EXISTS `GoogleContactsWebsite` (
  `contact_id` bigint(18) NOT NULL,
  `Type` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Value` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
  KEY `contact_id` (`contact_id`),
  CONSTRAINT `GoogleContactsEmail_ibfk_1` FOREIGN KEY (`contact_id`) REFERENCES `GoogleContacts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
""")
sql.execute("""
CREATE TABLE IF NOT EXISTS `GoogleContactsCustom` (
  `contact_id` bigint(18) NOT NULL,
  `Type` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Value` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  KEY `contact_id` (`contact_id`),
  CONSTRAINT `GoogleContactsEmail_ibfk_1` FOREIGN KEY (`contact_id`) REFERENCES `GoogleContacts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
""")
sql.execute("""
CREATE TABLE IF NOT EXISTS `GoogleContactsAddress` (
  `contact_id` bigint(18) NOT NULL,
  `Type` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Value` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  KEY `contact_id` (`contact_id`),
  CONSTRAINT `GoogleContactsEmail_ibfk_1` FOREIGN KEY (`contact_id`) REFERENCES `GoogleContacts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
""")
sql.execute("""
CREATE TABLE IF NOT EXISTS `GoogleContactsOrganization` (
  `contact_id` bigint(18) NOT NULL,
  `Type` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Value` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  KEY `contact_id` (`contact_id`),
  CONSTRAINT `GoogleContactsEmail_ibfk_1` FOREIGN KEY (`contact_id`) REFERENCES `GoogleContacts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
""")
sql.execute("""
CREATE TABLE IF NOT EXISTS `GoogleContactsIM` (
  `contact_id` bigint(18) NOT NULL,
  `Type` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Service` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Value` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  KEY `contact_id` (`contact_id`),
  CONSTRAINT `GoogleContactsEmail_ibfk_1` FOREIGN KEY (`contact_id`) REFERENCES `GoogleContacts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
""")
sql.execute("""
CREATE TABLE IF NOT EXISTS `GoogleContactsRelation` (
  `contact_id` bigint(18) NOT NULL,
  `Type` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Value` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  KEY `contact_id` (`contact_id`),
  CONSTRAINT `GoogleContactsEmail_ibfk_1` FOREIGN KEY (`contact_id`) REFERENCES `GoogleContacts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
""")
sql.execute("""
CREATE TABLE IF NOT EXISTS `GoogleContactsEvent` (
  `contact_id` bigint(18) NOT NULL,
  `Type` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Value` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  KEY `contact_id` (`contact_id`),
  CONSTRAINT `GoogleContactsEmail_ibfk_1` FOREIGN KEY (`contact_id`) REFERENCES `GoogleContacts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
""")
sql.execute("""
CREATE TABLE IF NOT EXISTS `GoogleContactsGroup` (
  `contact_id` bigint(18) NOT NULL,
  `Name` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL,
  KEY `contact_id` (`contact_id`),
  CONSTRAINT `GoogleContactsEmail_ibfk_1` FOREIGN KEY (`contact_id`) REFERENCES `GoogleContacts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
""")
sql.execute("TRUNCATE TABLE GoogleContactsEmail")
sql.execute("TRUNCATE TABLE GoogleContactsPhone")
sql.execute("TRUNCATE TABLE GoogleContactsWebsite")
sql.execute("TRUNCATE TABLE GoogleContactsCustom")
sql.execute("TRUNCATE TABLE GoogleContactsAddress")
sql.execute("TRUNCATE TABLE GoogleContactsOrganization")
sql.execute("TRUNCATE TABLE GoogleContactsIM")
sql.execute("TRUNCATE TABLE GoogleContactsRelation")
sql.execute("TRUNCATE TABLE GoogleContactsEvent")
sql.execute("TRUNCATE TABLE GoogleContactsGroup")
sql.execute("DELETE FROM GoogleContacts")


def toolkit = new groovy.util.CharsetToolkit(new File(System.getProperty("java.io.tmpdir"),"stpetegreensfull.csv"))
def guessedCharset = toolkit.getCharset()
def reader = new au.com.bytecode.opencsv.CSVReader(toolkit.getReader())
boolean headerRow = true
def header = []
def arr = []
def row = [:]
while ((arr = reader.readNext()) != null) {
	if(headerRow) {
		header = arr
		headerRow = false
		continue
	}
	header.eachWithIndex { h,i ->
		row[h] = arr[i]
	}
	java.util.regex.Matcher matcher
	java.util.regex.Pattern p
	String headerType
	String serviceType
	row = row.inject([:]) { m,k,v ->
		switch(k) {
			case { 
				p = java.util.regex.Pattern.compile("^E-mail (\\d+) - (\\w.*)")
				matcher = p.matcher(k)
				matcher.matches()
			}:
				if(!("emails" in m)) {
					m.emails = []
				}
				if(matcher.group(2).equalsIgnoreCase("Type")) {
					headerType = v?.toString()?.trim()
				} else {
					if(v?.toString()?.trim()) {						
						m.emails.push(["Type": "${headerType.replaceFirst(/^\\*/, '').trim()}", "Value": v?.trim()?.toString()?.toLowerCase()])
					}
				}
				break
			case { 
				p = java.util.regex.Pattern.compile("^Phone (\\d+) - (\\w.*)")
				matcher = p.matcher(k)
				matcher.matches()
			}:
				if(!("phones" in m)) {
					m.phones = []
				}
				if(matcher.group(2).equalsIgnoreCase("Type")) {
					headerType = v?.trim()
				} else {
					if(v?.toString()?.trim()) {
						m.phones.push(["Type": "${headerType.replaceFirst(/^\\*/, '').trim()}", "Value": v?.toString()?.trim()])
					}
				}
				break
			case { 
				p = java.util.regex.Pattern.compile("^Website (\\d+) - (\\w.*)")
				matcher = p.matcher(k)
				matcher.matches()
			}:
				if(!("websites" in m)) {
					m.websites = []
				}
				if(matcher.group(2).equalsIgnoreCase("Type")) {
					headerType = v?.toString()?.trim()
				} else {
					if(v?.toString()?.trim()) {
						m.websites.push(["Type": "${headerType.replaceFirst(/^\\*/, '').trim()}", "Value": v?.toString()?.trim()])
					}
				}
				break
			case { 
				p = java.util.regex.Pattern.compile("^Custom Field (\\d+) - (\\w.*)")
				matcher = p.matcher(k)
				matcher.matches()
			}:				
				if(!("customs" in m)) {
					m.customs = []
				}
				if(matcher.group(2).equalsIgnoreCase("Type")) {
					headerType = v?.toString()?.trim()
				} else {
					if(v?.toString()?.trim()) {
						m.customs.push(["Type": "${headerType.replaceFirst(/^\\*/, '').trim()}", "Value": v.toString().trim()])
					}					
				}
				break
			case { 
				p = java.util.regex.Pattern.compile("^Address (\\d+) - (\\w.*)")
				matcher = p.matcher(k)
				matcher.matches()                        
			}:
				if(!("addresses" in m)) {
					m.addresses = []
				}
				if(matcher.group(2).equalsIgnoreCase("Type")) {
					headerType = v?.toString()?.trim()
				} else if(matcher.group(2).equalsIgnoreCase("Formatted")) {
					if(v?.toString()?.trim()) {
						m.addresses.push(["Type": "${headerType.replaceFirst(/^\\*/, '').trim()}", "Value": v.toString().trim()])
					}
				}
				break
			case { 
				p = java.util.regex.Pattern.compile("^Organization (\\d+) - (\\w.*)")
				matcher = p.matcher(k)
				matcher.matches()                        
			}:
				if(!("organizations" in m)) {
					m.organizations = []
				}
				if(matcher.group(2).equalsIgnoreCase("Type")) {
					headerType = v?.toString()?.trim()
				} else if(matcher.group(2).equalsIgnoreCase("Name")) {
					if(v?.toString()?.trim()) {
						m.organizations.push(["Type": "${headerType.replaceFirst(/^\\*/, '').trim()}", "Value": v.toString().trim()])
					}
				}
				break
			case { 
				p = java.util.regex.Pattern.compile("^IM (\\d+) - (\\w.*)")
				matcher = p.matcher(k)
				matcher.matches()                        
			}:
				if(!("ims" in m)) {
					m.ims = []
				}
				if(matcher.group(2).equalsIgnoreCase("Type")) {
					headerType = v?.toString()?.trim()
				} else if(matcher.group(2).equalsIgnoreCase("Service")) {
					serviceType = v?.toString()?.trim()
				} else if(matcher.group(2).equalsIgnoreCase("Value")) {
					if(v?.toString()?.trim()) {
						m.ims.push(["Type": "${headerType.replaceFirst(/^\\*/, '').trim()}", "Service": serviceType, "Value": v.toString().trim() ])
					}
				}
				break
			case { 
				p = java.util.regex.Pattern.compile("^Relation (\\d+) - (\\w.*)")
				matcher = p.matcher(k)
				matcher.matches()
			}:				
				if(!("relations" in m)) {
					m.relations = []
				}
				if(matcher.group(2).equalsIgnoreCase("Type")) {
					headerType = v?.toString()?.trim()
				} else {
					if(v?.toString()?.trim()) {
						m.relations.push(["Type": "${headerType.replaceFirst(/^\\*/, '').trim()}","Value": v.toString().trim()])
					}					
				}
				break					
			case { 
				p = java.util.regex.Pattern.compile("^Event (\\d+) - (\\w.*)")
				matcher = p.matcher(k)
				matcher.matches()
			}:				
				if(!("events" in m)) {
					m.events = []
				}
				if(matcher.group(2).equalsIgnoreCase("Type")) {
					headerType = v?.toString()?.trim()
				} else {
					if(v?.toString()?.trim()) {
						m.events.push(["Type": "${headerType.replaceFirst(/^\\*/, '').trim()}", "Value": v.toString().trim()])
					}					
				}
				break					
			case "Group Membership":
				m.groupMemberships = v.toString().tokenize(':::').collect { g ->
					if(g?.toString()?.trim()) {
						return g.toString().trim().replaceFirst(/^\\*/, "").trim()
					}
				}
				break
			default:
				m[k] = v?.toString()?.trim()
				break
		}
		return m
	}	
	sql.execute("""
		INSERT INTO `GoogleContacts` (
			`Name`, 
			`Given Name`, 
			`Additional Name`, 
			`Family Name`, 
			`Yomi Name`, 
			`Given Name Yomi`, 
			`Additional Name Yomi`, 
			`Family Name Yomi`, 
			`Name Prefix`, 
			`Name Suffix`, 
			`Initials`, 
			`Nickname`, 
			`Short Name`, 
			`Maiden Name`, 
			`Birthday`, 
			`Gender`, 
			`Location`, 
			`Billing Information`, 
			`Directory Server`, 
			`Mileage`, 
			`Occupation`, 
			`Hobby`, 
			`Sensitivity`, 
			`Priority`, 
			`Subject`, 
			`Notes`
		) VALUES (
			:name, 
			:gname, 
			:aname, 
			:famname, 
			:yname, 
			:gnamey, 
			:anamey, 
			:famnamey, 
			:prefix, 
			:suffix, 
			:initials, 
			:nickname, 
			:sname, 
			:maiden, 
			:birthday, 
			:gender, 
			:location, 
			:billing, 
			:directory, 
			:mileage, 
			:occupation, 
			:hobby, 
			:sensitivity, 
			:priority, 
			:subject, 
			:notes
		)
	""",[
		"name": row["Name"],
		"gname": row["Given Name"],
		"aname": row["Additional Name"],
		"famname": row["Family Name"],
		"yname": row["Yomi Name"],
		"gnamey": row["Given Name Yomi"],
		"anamey": row["Additional Name Yomi"],
		"famnamey": row["Family Name Yomi"],
		"prefix": row["Name Prefix"],
		"suffix": row["Name Suffix"],
		"initials": row["Initials"],
		"nickname": row["Nickname"],
		"sname": row["Short Name"],
		"maiden": row["Maiden Name"],
		"birthday": row["Birthday"],
		"gender": row["Gender"],
		"location": row["Location"],
		"billing": row["Billing Information"],
		"directory": row["Directory Server"],
		"mileage": row["Mileage"],
		"occupation": row["Occupation"],
		"hobby": row["Hobby"],
		"sensitivity": row["Sensitivity"],
		"priority": row["Priority"],
		"subject": row["Subject"],
		"notes": row["Notes"]				
	])
	def contactId = sql.firstRow("SELECT MAX(id) as `contactId` FROM `GoogleContacts`")?.contactId
	row.emails.each { e -> 
		sql.execute("INSERT INTO `GoogleContactsEmail` (`contact_id`,`Type`,`Value`) VALUES (:id,:type,:val)",["id":contactId,"type":e["Type"].toString(),"val":e["Value"].toString()])
	}
	
	row.phones.each { e -> 
		sql.execute("INSERT INTO `GoogleContactsPhone` (`contact_id`,`Type`,`Value`) VALUES (:id,:type,:val)",["id":contactId,"type":e["Type"].toString(),"val":e["Value"].toString()])
	}
	row.websites.each { e -> 
		sql.execute("INSERT INTO `GoogleContactsWebsite` (`contact_id`,`Type`,`Value`) VALUES (:id,:type,:val)",["id":contactId,"type":e["Type"].toString(),"val":e["Value"].toString()])
	}
	row.customs.each { e -> 
		sql.execute("INSERT INTO `GoogleContactsCustom` (`contact_id`,`Type`,`Value`) VALUES (:id,:type,:val)",["id":contactId,"type":e["Type"].toString(),"val":e["Value"].toString()])
	}
	row.addresses.each { e -> 
		sql.execute("INSERT INTO `GoogleContactsAddress` (`contact_id`,`Type`,`Value`) VALUES (:id,:type,:val)",["id":contactId,"type":e["Type"].toString(),"val":e["Value"].toString()])
	}
	row.organizations.each { e -> 
		sql.execute("INSERT INTO `GoogleContactsOrganization` (`contact_id`,`Type`,`Value`) VALUES (:id,:type,:val)",["id":contactId,"type":e["Type"].toString(),"val":e["Value"].toString()])
	}
	row.ims.each { e -> 
		sql.execute("INSERT INTO `GoogleContactsIM` (`contact_id`,`Type`,`Service`,`Value`) VALUES (:id,:type,:service,:val)",["id":contactId,"type":e["Type"].toString(),"service":e["Service"].toString(),"val":e["Value"].toString()])
	}
	row.relations.each { e -> 
		sql.execute("INSERT INTO `GoogleContactsRelation` (`contact_id`,`Type`,`Value`) VALUES (:id,:type,:val)",["id":contactId,"type":e["Type"].toString(),"val":e["Value"].toString()])
	}
	row.events.each { e -> 
		sql.execute("INSERT INTO `GoogleContactsEvent` (`contact_id`,`Type`,`Value`) VALUES (:id,:type,:val)",["id":contactId,"type":e["Type"].toString(),"val":e["Value"].toString()])
	}
	row.groupMemberships.each { e -> 
		sql.execute("INSERT INTO `GoogleContactsGroup` (`contact_id`,`Name`) VALUES (:id,:name)",["id":contactId,"name":e.toString()])
	}	
	
	row.clear()
}
reader.close()