Converting shapefile to CSV including attributes and geometry

Question

I have a shapefile with 60k+ entries, all of which are polygons with corresponding attributes (acreage totals, landowner names, tax ID #s, etc.). What I ultimately need is a CSV file with all of these attributes and their corresponding geometry (in the KML compatible xyz format, that is, NOT the WKT format).

I know that I can open the .dbf file in Excel and get the attributes. I also know that I can open the shapefile in QGIS and copy the data into Excel, which gets me attributes and WKT geometry.

Is there a simple way to convert the shapefile to CSV (openable in Excel) with attribute and Google Earth friendly geometry?

Answer 1

Here's a simple script that uses the OGR python bindings:

import ogr,csv,sys

shpfile=r'C:\Temp\test.shp' #sys.argv[1]
csvfile=r'C:\Temp\test.csv' #sys.argv[2]

#Open files
csvfile=open(csvfile,'wb')
ds=ogr.Open(shpfile)
lyr=ds.GetLayer()

#Get field names
dfn=lyr.GetLayerDefn()
nfields=dfn.GetFieldCount()
fields=[]
for i in range(nfields):
    fields.append(dfn.GetFieldDefn(i).GetName())
fields.append('kmlgeometry')
csvwriter = csv.DictWriter(csvfile, fields)
try:csvwriter.writeheader() #python 2.7+
except:csvfile.write(','.join(fields)+'\n')

# Write attributes and kml out to csv
for feat in lyr:
    attributes=feat.items()
    geom=feat.GetGeometryRef()
    attributes['kmlgeometry']=geom.ExportToKML()
    csvwriter.writerow(attributes)

#clean up
del csvwriter,lyr,ds
csvfile.close()

EDIT: and another script to convert from your CSV to KML

import ogr,csv,sys,os
ogr.UseExceptions()

csvfile=r'C:\temp\test.csv' #sys.argv[1]
kmlfile=r'C:\temp\test.kml' #sys.argv[2]

csvreader=csv.reader(open(csvfile,'rb'))
headers=csvreader.next()

ds = ogr.GetDriverByName('KML').CreateDataSource(kmlfile)
lyr = ds.CreateLayer(os.path.splitext(os.path.basename(kmlfile))[0])

for field in headers[:-1]: #skip kmlgeometry
    field_def = ogr.FieldDefn(field)
    print lyr.CreateField(field_def)

for rec in csvreader:
    feat = ogr.Feature(lyr.GetLayerDefn())
    for i,field in enumerate(headers[:-1]): #skip kmlgeometry
        feat.SetField(field, rec[i])
    feat.SetGeometry(ogr.CreateGeometryFromGML(rec[-1]))
    lyr.CreateFeature(feat)

del lyr,ds

Answer 2

If you convert your shapefile to spatialite, you should be able to do the following:

1) Experiment with SQL to test the output:

ex.

SELECT col1, col2, col3, AsKml(geometry_column) FROM tab

2) Once you are satisfied with the result, you can export it to CSV format:

https://stackoverflow.com/questions/5776660/export-from-sqlite-to-csv-using-shell-script

For more information on Spatialite:

https://www.gaia-gis.it/fossil/libspatialite/index

Spatialite SQL functions:

http://www.gaia-gis.it/gaia-sins/spatialite-sql-3.0.0.html

Answer 3

If you are working on QGIS, you can instantly generate a CSV document by right clicking on the layer -> Save as -> CSV.

If you are working with ArcMap, then you can export the KML by using the tool Layer to KML (Go to Search option in the program). For some reasons it generates a KMZ instead of an KML (atleast that's what happened on my case).

In order to convert a KMZ to KML:

• Import your KMZ file to Google Earth and right click on your layer and save it as KML
• Open QGIS and drag and drop the KML file - it will automatically load the layer (>QGIS 2.10 PISA)
• Right click on the file and save it as CSV

This procedure is longer if you are working with ArcGIS but in QGIS it can be done in no time. You will have to install QGIS in any case.

Answer 4

At the command line you can use ogr2ogr, like this:

ogr2ogr -f CSV output.csv input.shp -lco GEOMETRY=AS_XYZ

lco is for “layer creation options”, i think. Other available options are documented here: http://www.gdal.org/drv_csv.html

Answer 5

there is a tool on the 'Geoprocessing Model and Script Tool Gallery' that does what you're looking for. it can export to excel or csv.

http://resources.arcgis.com/gallery/file/geoprocessing/details?entryID=95009B25-1422-2418-7FB5-B8638ECB2FA9

Answer 6

simply go to conversion tool in arc map. then go to table to excel. an excel file will made. convert that excel doc to .csv file