Convert csv- or Excel-file xlsx to kml with custom markers

Question

I want to convert a CSV- or Excel(2010)-file to kml to be able to show it on google earth. The file holds addresses that are not geocoded. It holds a field with a URL for a custom marker. And it holds a field with some info that I want to show in an info window.

The file looks like this:

Name;Category;Address; Icon; Info
McFood;Fast Food;Street 1, zip, town, country; url-to-icon; Info
BurgerEmperor;Fast Food;Way 1, zip, town, country; url-to-icon; Info
BlueFrenchHorn;French;Street 12, zip, town, country; url-to-icon; Info
PetesPizza;Italian;whatever, zip, town, country; url-to-icon; Info
SubZero;Fast Food;Highway 6, zip, town, country; url-to-icon; Info

Answer 1

With some help from the community, I want to share my solution to this problem. It may be a little rough around the edges, but it works. I wrote two scripts (one for CSV-input and one for xlsx-input that only differ slightly).

CSV-to-KML:

import csv
import simplekml
import geocoder
from collections import defaultdict

mydict = defaultdict(list)

# the part of reading the file to a dictionary is taken from
# http://www.mysamplecode.com/2013/05/python-read-csv-file-list-dictionary.html
# and modified to have a list of dictionaries - solution from
# https://stackoverflow.com/questions/25891451/create-a-list-of-dictionaries-in-a-dictionary-from-csv-in-python/

with open ('food.csv', 'r') as csvfile:
    #sniff to find the format
    fileDialect = csv.Sniffer().sniff(csvfile.read(1024))
    csvfile.seek(0)
    #read the CSV file into a dictionary
    dictReader = csv.DictReader(csvfile, dialect=fileDialect)
    for row in dictReader:
        mycategory = row.pop("category")
        mydict[mycategory].append(row)  # Will put a list for not-existing key

mydict = dict(mydict) # Convert back to a normal dictionary (optional)

# Use SimpleKml to write kml-file
# category from csv-file used as folder
# Custom Markers taken from url in csv-file

kml =simplekml.Kml()
for ctgy in mydict:
    fol=kml.newfolder(name=ctgy)
    for entry in mydict[ctgy]:
        # geocode addresses
        g = geocoder.google(entry["Address"])
        pnt=fol.newpoint(name=entry["Name"], coords=[(g.lng,g.lat)], description=entry["Info"])
        if entry["Icon"] != "":
            pnt.style.iconstyle.icon.href=entry["Icon"]
kml.save("food.kml")

XLSX-to-KML:

It differs only slightly - to being able to use a DictReader with an excel-file I use xlsdictreader.py from the answer to this question: DictReader for Excel-Files

The differences are:

# import csv
from xlsdictreader import XLSDictReader

In file handling:

with open ('food.xlsx', 'r') as excelfile:
    dictReader = XLSDictReader(excelfile)