FAQ: How to Export Large Shapefiles to Excel

Published on Wednesday, October 25, 2017 by Milos Gregor

Occasionally I receive a question: How many shapefile features can be exported to a spreadsheet at once? Users have this problem with the third-party products, where the maximum number of exported spatial features is strictly limited.

In general, it is possible to say that the maximum number of shapefile features that you can use in the GIS.XL add-in is not limited. An example will be shown in the following use case. We import a shapefile into the add-in Map Panel. This shapefile contains line features, which describes all digitized relief lines in Slovakia – in total > 120,000 lines.

The first limitation you may encounter is the maximum size of the shapefile that you can work with. Imported shapefiles are processed in the computer’s memory, so here we are limited only by the amount of available memory. In the GIS.XL add-in I have tested shapefiles that includes > 1,000,000 features (points) and program process them smoothly. However, if you need to work with such large files frequently, I recommend using another tool that is directly optimized to work with such large datasets.

Tip: If you want to try directly your large shapefiles in the GIS.XL add-in, you can do it also in the DEMO version. Shapefile import into the Map Panel is not limited in any way.

For the data export from shapefile to Excel spreadsheet (shp to xls), click on the function GIS to Excel – Export Map Data.

A help dialog appears, where you can setup the data that you want to export. More about export options can be found in documentation, where is this functionality described in detail. In displayed dialog, we export values from the attribute table (TYPRELL column), together with spatial information about individual line features – feature ID and coordinates of line centroid for each line.

After the export definition, just press the Apply button and the program will begin with the export (selected spatial and attribute data from shapefile). If your shapefile contains large number of features, or you export more complex information (e.g. feature coordinates in GeoJSON format) this dialog can freeze for a short time.

About the export completing you are informed via a Message Box.

The exported data is stored in the clipboard. Then click on the selected cell in spreadsheet and press the CTRL+V keyboard shortcut to insert the data. The following figure shows the result of our example. We generated a table in Excel containing 4 columns and 110,211 rows.

Tip: If you already have table created in the clipboard, you can even create a CSV file. Just open any text editor (such as Notepad) and paste your data (CRTL+V). After saving you have a CSV file where individual columns are separated by the tab-space.

Overall, the GIS.XL add-in does not limit you at work with large shapefiles. Your limits are the computer memory size and limits of Excel Spreadsheets (e.g. max number of inserted / stored cells).