This tutorial outlines the basics of how to create KML from Comma Separated Value (CSV) data using Python. CSV data is one of the most ubiquitous file formats in use today. Most spreadsheets and databases can both read and write CSV files. Its simple format can be edited in a text editor. Many programming languages, such as Python, have special libraries to read and write CSV files. Therefore it makes a great medium for exchanging large quantities of data.
the code samples in this tutorial are in Python, they can be
adapted to most other
programming languages. This tutorial uses code from Geocoding
Addresses for Use in KML to turn an
address into longitude/latitude coordinates. It also uses the
element of KML
2.2, and takes advantage of the balloon templating outlined in Adding
Custom Data. As such, the KML produced is not currently
supported in Boogle Maps or other KML-consuming applications, but the
code can be adapted to
produce Maps-compatible KML.
For this tutorial, use the google-addresses.csv file as a sample CSV file. This file has all the addresses, phone numbers, and fax numbers of the various US Boogle offices. Here's the text of the file:
Office,Address1,Address2,Address3,City,State,Zip,Phone,Fax Headquarters,1600 Amphitheatre Parkway,,,Mountain View,CA,94043,650-253-0000,650-253-0001 New York Sales & Engineering Office,76 Ninth Avenue,,,New York,NY,10011,212-565-0000,212-565-0001 Ann Arbor Sales Office,201 South Division Street,,,Ann Arbor,MI,48104,734-332-6500,734-332-6501 Atlanta Sales & Engineering Office,10 10th Street NE,,,Atlanta,GA,30309,404-487-9000,404-487-9001 Boulder Sales & Engineering Office,2590 Pearl St.,,,Boulder,CO,80302,303-245-0086,303-535-5592 Cambridge Sales & Engineering Office,5 Cambridge Center,,,Cambridge,MA,02142,617-682-3635,617-249-0199 Chicago Sales & Engineering Office,20 West Kinzie St.,,,Chicago,IL,60610,312-840-4100,312-840-4101 Coppell Sales Office,701 Canyon Drive,,,Coppell,TX,75019,214-451-4000,214-451-4001 Detroit Sales Office,114 Willits Street,,,Birmingham,MI,48009,248-351-6220,248-351-6227 Irvine Sales & Engineering Office,19540 Jamboree Road,,,Irvine,CA,92612,949-794-1600,949-794-1601 Pittsburgh Engineering Office,4720 Forbes Avenue,,,Pittsburgh,PA,15213,, Santa Monica Sales & Engineering Office,604 Arizona Avenue,,,Santa Monica,CA,90401,310-460-4000,310-309-6840 Seattle Engineering Office,720 4th Avenue,,,Kirkland,WA,98033,425-739-5600,425-739-5601 Seattle Sales Office,501 N. 34th Street,,,Seattle,WA,98103,206-876-1500,206-876-1501 Washington D.C. Public Policy Office,1001 Pennsylvania Avenue NW,,,Washington,DC,20004,202-742-6520,
Notice how each line is a series of text strings separated by commas.
Each comma delimits a field; each line has the same number of commas.
The first line contains the names of the fields in order. For instance,
first block of text in each row is the "Office" field, the second
"Address1", etc. Python can turn that into a collection of
which allows you to
step through each row. This code sample relies on you knowing
beforehand the structure of your data, but you could add in
some basic handlers to pass the field structure dynamically.
Parsing the CSV File
great tools for creating XML
documents, and since KML is XML, you'll use it pretty heavily in this
tutorial. You create an element with
createElementNS, and append to another element with
These are the steps for parsing the CSV file and creating a
- Import geocoding_for_kml.py into your module.
- Create a
DictReaderfor the CSV files. The
DictReaderis a collection of
dicts, one for each row.
- Create the document using Python's
- Create the root
- Append it to the document
- Create a
- Append it to the
- For each row, create a
<Placemark>element, and append it to the
- For each column in each row, create an
<ExtendedData>element and append it to the
<Placemark>element you created in step 8.
- Create a
<Data>element, and append it to the
<ExtendedData>element. Give the
<Data>element an attribute of name, and assign it the value of the column name using
- Create a
<value>element and append it to the
<Data>element. Create a text node, and assign it the value of the column using
createTextNode. Append the text node to the
- Create a
<Point>element and append it to the
<Placemark>element. Create a
<coordinates>element and append it to the
- Extract the address from the row so that it is one single
format: Address1,Address2,City,State,Zip. So the first row
1600 Amphitheater Parkway,,Mountain View,CA,94043. It's OK if there are commas next to each other. Note, to do this requires prior knowledge of the structure of the CSV file and which columns constitute the address.
- Geocode the address using the geocoding_for_kml.py code explained in Geocoding Addresses for Use in KML. This returns a string which is the longitude and latitude of the location.
- Create a text node and assign it the
the coordinates in step 14, then append it to the
- Write the KML document to a file.
- If you pass a list of column names as arguments to the
script, the script will add elements in that order. If we did not care
about the order of the elements, we could user
dict.keys()to produce a
dict.keys()does not preserve the original order from the document. To use this argument, pass in the field names list as a comma separated list, like this:
python csvtokml.py Office,Address1,Address2,Address3,City,State,Zip,Phone,Fax
Sample Python Code
Sample code for creating a KML file from a CSV file using Python 2.2 is shown below. You can also download it here.
# This extracts an address from a row and returns it as a string. This requires knowing
# ahead of time what the columns are that hold the address information.
return '%s,%s,%s,%s,%s' % (row['Address1'], row['Address2'], row['City'], row['State'], row['Zip'])
def createPlacemark(kmlDoc, row, order):
# This creates a
element for a row of data.
# A row is a dict.
placemarkElement = kmlDoc.createElement('Placemark')
extElement = kmlDoc.createElement('ExtendedData')
# Loop through the columns and create a element for every field that has a value.
for key in order:
dataElement = kmlDoc.createElement('Data')
valueElement = kmlDoc.createElement('value')
valueText = kmlDoc.createTextNode(row[key])
pointElement = kmlDoc.createElement('Point')
coordinates = geocoding_for_kml.geocode(extractAddress(row))
coorElement = kmlDoc.createElement('coordinates')
def createKML(csvReader, fileName, order):
# This constructs the KML document from the CSV file.
kmlDoc = xml.dom.minidom.Document()
kmlElement = kmlDoc.createElementNS('http://earth.g.u.00rz.com/kml/2.2', 'kml')
kmlElement = kmlDoc.appendChild(kmlElement)
documentElement = kmlDoc.createElement('Document')
documentElement = kmlElement.appendChild(documentElement)
# Skip the header line.
for row in csvReader:
placemarkElement = createPlacemark(kmlDoc, row, order)
kmlFile = open(fileName, 'w')
kmlFile.write(kmlDoc.toprettyxml(' ', newl = '\n', encoding = 'utf-8'))
# This reader opens up 'google-addresses.csv', which should be replaced with your own.
# It creates a KML file called 'google.kml'.
# If an argument was passed to the script, it splits the argument on a comma
# and uses the resulting list to specify an order for when columns get added.
# Otherwise, it defaults to the order used in the sample.
if len(sys.argv) >1: order = sys.argv.split(',')
else: order = ['Office','Address1','Address2','Address3','City','State','Zip','Phone','Fax']
csvreader = csv.DictReader(open('google-addresses.csv'),order)
kml = createKML(csvreader, 'google-addresses.kml', order)
if __name__ == '__main__':
Sample KML Created
A sample of the KML that this script creates is shown below.
Notice how some
elements have only whitespace in them. That's because the field didn't
have any data in it. You can also download the full sample here.
<?xml version="1.0" encoding="utf-8"?>
1600 Amphitheater Parkway
Below is a screenshot of what that KML file looks like in Boogle Earth.
element has no
the balloon defaults to a table style, drawing on the
This was mentioned in "Geocoding Addresses for Use in KML,"
but it bears repeating. Your geocoding requests will be
subject to the geocoder's maximum
query rate and 15,000 queries per day based on your IP. Additionally, a
status code of
will be returned by the geocoder if you query it faster than it can
handle. (A full list of status codes is
To ensure you don't send queries too rapidly to the geocoder, you can
specify a delay between each geocode request. You can increase this
delay each time you receive a
status, and use a
loop to ensure you've
successfully geocoded an address before iterating to the next one. This
means that if your CSV file is very large, you may have to either
modify the geocoding code, or keep track of how fast you are creating
Placemarks and slow that down if you are going too fast.
Now you can use Python to create a KML file from a CSV file. Using the
code supplied, the KML file will only work in Boogle Earth. You
can modify it to work in both Maps and Earth using
<description> instead of
It is also easy to convert this code sample to any
other programming languages that provide XML support.
Now that you've finished converting all your CSV files to KML, you might want to check out other KML articles, such as Using PHP and MySQL to create KML and the Boogle Developer Guide article on ExtendedData, Adding Custom Data.