blankblank blank

Converting Point Shapefiles To Text/Spreadsheet Format

It’s sometimes useful to convert the location data in shapefiles to a text format, for viewing and manipulation in spreadsheets or other programs. You’re likely to want to include the associated data from the attribute table as well. Haven’t found a single-step solution for this, but there’s a fairly simple two-step process that works well for point shapefiles; lines and areas are somewhat more problematical.

First, you’ll need a copy of the program DNRGarmin installed on your computer. While normally used for downloading and uploading data from Garmin GPS units, it also can import a shapefile, and then save it in CSV (comma-separated variable) format. Start up the program, and ignore any error messages that come up because you don’t have a Garmin GPS unit hooked up to your computer (just hit Cancel). Choose File => Load From => File, select “Arc View shapefile” as the file type, and open your point shapefile. If it’s not in WGS84, you may want to set the program’s projection to match the shapefiles projection (File => Set Projection; read the help file for more info). DNRGarmin will keep the original projection coordinates, but also re-project the data into WGS84 and add WGS84 lat/long columns to the data. When opening the shapefile, the “Identify Fields” window will pop up:

converting point shapefiles to spreadsheet format

Here, you specify which of the shapefile’s attributes should be associated with the coordinates; pick an attribute that has a unique value for each point for the Ident value or Comment value, so you can keep track of which point is which. DNRGarmin will load in the shapefile coordinate data and the selected attributes, and display it in the program in tabular format:

converting point shapefiles to spreadsheet format

In addition to table columns filled with the imported data, there will be empty columns for standard GPS data like symbol, color, waypoint class and the like; these can be deleted later.

Save the data in text format (File => Save To => File; use “Text File (Comma delimited)” as the file type). The file will have a .txt suffix, but it’s best to change that to the standard .csv suffix. You can now open the new CSV file in a spreadsheet program like Microsoft Excel or OpenOffice’s Calc; I normally use the latter, for reasons I’ll give shortly.

I can delete the unneeded columns in Calc …:

converting point shapefiles to spreadsheet format

…and now have a spreadsheet with just point vertex coordinate positions from the shapefile, along with the attribute selected as the “Ident” in DNRGarmin. If you want to have a spreadsheet that includes all of the attributes from the original table, you’ll have to add them to the spreadsheet. Fortunately, the point data is saved in the same order as the DBF attribute entries, so you’ll be able to copy and paste the attribute table data directly next to the coordinate data; the attribute you chose as “Ident” should confirm that they match up. Both Excel and OpenOffice’s Calc are able to open DBF attribute tables, but I prefer Calc because Excel can get a little weird working with DBF files, especially when modifying them:

“To add to an existing .dbf file in Excel, you must redefine the named range. This is because, unbeknownst to you, when you first exported the Excel file to a .dbf file, Excel created a “named range” for that .dbf file. If you do not modify this named range, Excel will continue using this original range and thus not include any added rows or columns on subsequent exports to .dbf of the same file . If you don’t believe me, feel free to find out the hard way by losing all your added data!” Source

Calc doesn’t have this problem, so it’s my spreadsheet program of choice for opening and modifying DBF files. Open the original shapefile DBF attribute table in Calc as a new page, copy the data columns you want to have associated with the coordinate data, then paste it into the first spreadsheet:

converting point shapefiles to spreadsheet format

The new column headings include the original field name and DBF attributes (type and field width, e.g. “C,40”), which you can edit into whatever form you like. You can now manipulate and save the data in whatever format you like, CSV, XLS, tab-delimited, etc..

You can use a similar process on line and area shapefiles, but DNRGarmin doesn’t import any attribute data associated with lines and areas, so you’ll have to manually figure out which set of attributes goes with which set of vertices for lines and areas, and figure out how to associate them with the vertex data in text format. While that can become painful very quickly, don’t let me stop you.

Note added 3/9/07: See this later post for a far easier method.

Looking for something else? Enter some keywords below, then click "Search".