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

0 votes

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
Sep 25 in Others by Kithuzzz
• 20,660 points
110 views

1 answer to this question.

0 votes

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)
answered Sep 26 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17 in Others by Edureka
• 13,640 points
112 views
0 votes
1 answer

Convert JSON file to Excel File

Hi To convert a file, Set options and ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
380 views
0 votes
1 answer

How to create a drop-down in excel with custom values

You can accomplish that using code rather ...READ MORE

answered Sep 25 in Others by narikkadan
• 37,660 points
72 views
0 votes
1 answer

Convert Excel to PDF issue with documents4j

MS Excel may not always be used ...READ MORE

answered Sep 26 in Others by narikkadan
• 37,660 points
198 views
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 3,149 views
0 votes
1 answer
0 votes
1 answer

Convert Word doc, docx and Excel xls, xlsx to PDF with PHP

After receiving a request, I'll put the ...READ MORE

answered Nov 20 in Others by narikkadan
• 37,660 points
64 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP