How to Import an Esri ASCII Grid into Microsoft Access

Using Esri data in an Access Database

Importing huge amounts of data from an Esri ASCII Grid (Raster) is achievable through VBA code in Microsoft Access. An Esri ASCII Grid can contain tens of thousands of data points that mean little to the human eye. By importing all of this data into Access, you can control and manipulate it to see the information in a way that is useful to you.

Below we present an example database that does just this. The function of the Access database is to create a table containing information on the coordinates referred to in the ASCII file, and the values of the main variable at each point. If you already have a database and are just interested in the VBA code, see the section further down the page.

Click here to download the free Access Esri import database.

About the Esri ASCII Grid

The Esri ASCII grid is a data format used to transfer information from GIS software into other programs. It is used when your dataset contains information on the value of a variable at each point in a geographic area (e.g. height above sea level).

Viewed as text, the data is a space delimited list of data points organised into rows and columns and preceded by a ‘header’ (highlighted below). This header contains information on the grid to which the data pertains. (All images in this article can be clicked to open in full size in a new window.)

The data following this header represents the magnitude of some variable at different coordinate points. The first column of the last row is the origin (bottom left of the grid), with the x coordinate growing to the right, and the y coordinate growing upwards.

To get the real world coordinates represented by any given data point you can use the Esri conversion formulae:

x = xll + cellsize * (column - 1)

y = yll + cellsize * (nrows - row)

The terms 'column' and 'row' are the column and row numbers of the data point in question. The terms cellsize, xll and yll are in the header of the file. You'll see we have used such formulae within the code in our sample database.

Esri Data in Raster Format:

Esri Data in an Access Table:

Stand-alone Code

If you need to implement the importing routine in an already existing database, you can use this stand-alone code and set your database up to use it as explained below.

Click here to open the Esri import VBA code in a new window.

To use the stand-alone code you will first need to set up a table in Access to receive the information. There are four things we’ll be able to import from the file: Point Number, an x coordinate, a y coordinate, and the actual data value. Create a table with four such fields; the point number can be a Long, while the rest should be Doubles to maximise accuracy.

In the example below, we’ve set up a table to import the Esri data on the levels of solar irradiance at different coordinate points.

In our code we have recorded the location of the file we’re going to import in the properties of the Access database. We have called the property ‘EsriFile’, and are importing this property in the code on the line commented as such.

If you wish to source your file path in the same way, add the file path as a property by going to (Access 2010): File, View and Edit Database Properties (it’s on the far right of the screen), Custom, and then fill out the Name and Value fields and click on the Add button (pick ‘Text’ for the Type). Your new property will appear in the list below the inputs. Make sure to use the full file path including the file itself and its extension, e.g. C:\Documents and Settings\All Users\Documents\My Documents\Downloads\G_opt_classic_latlon_year.asc

Once ready, bring the code we have provided into a module in your database. You’ll need to edit the code for things such as your table name and field names to match however you have set your table up. Once you have customised the code to suit your means, you are ready to start importing Esri format data! Take note of the commentary in the code if you wish to know more about how the import is working.

Important Note: Running this import code can take a long time. An Esri raster file can have any amount of data points, perhaps in the millions. As a rough guide you should allow half an hour of time for each 100,000 data points in your file. During this time, you should not try to use Access for any other purpose. It is likely that Access will show up as 'Not Responding' while busy importing the data. It will return to normal once the import is done.

If you enjoyed this article or found it useful, why not tell others about it? Tweet