United States Department of Agriculture
Natural Resources Conservation Service		 Soils Go to Accessibility Information
Skip to Page Content




Using NASIS Exports

Introduction

NASIS 5.0 includes a new export referred to as the “New SSURGO Format”. The existing FOCS export produces data in the older soil data format that was originally established for the State Soil Survey Database (SSSD). The new SSURGO Export produces data in a format that reflects the SSURGO version 2 database structure and includes the NASIS fuzzy logic interpretations.

The new SSURGO format was developed in order to provide new NASIS data and NASIS interpretations as the tabular component of SSURGO. This new SSURGO format will be used by Customer Service Toolkit 3.0, as well as Soil Data Viewer 3.0. In order for these applications to use this data, the contents of a SSURGO Export must be imported into a Microsoft Access database. This document describes that Microsoft Access database and how to use it to import the contents of a SSURGO Export.

While a typical SSURGO Export includes all data for a single soil survey area, a SSURGO Export may contain data for more than one soil survey area, or it may contain only part of the data for a soil survey area. What data to include in a SSURGO Export is at the discretion of the person generating the export.

The tables and columns included in a SSURGO Export are fixed. The person producing the export cannot specify which tables and columns should be included. The person producing a SSURGO Export does have complete control over which legends, map units, components and interpretations are included in a SSURGO Export.

Downloading the Template Microsoft Access Database

A template Microsoft Access database is available for importing the contents of a SSURGO Export. This database includes a pre-built structure equivalent to the format produced by a SSURGO Export. This database also contains a macro for importing the data produced by a SSURGO Export. In order to use this database, you must have Microsoft Access installed on your PC.

Several versions of this template Microsoft Access database exist, one corresponding to Access 97 and another corresponding to Access 2000. You should download the version corresponding to the version of Microsoft access that you have installed on your PC. Downloading an older version of the template database and then converting it to a newer version of Microsoft Access is not guaranteed to work.

You may download a copy of the template Microsoft Access database from the main NASIS Downloads page at the following reference. Select the appropriate version of the template (Microsoft Access 97 or Microsoft Access 2000) from the section titled SSURGO Version 2 Microsoft Access Template Database (NASIS 5.0).

NASIS Downloads

Importing a SSURGO Export into the Template Microsoft Access Database

General Information

The template Access database can only import data produced by a SSURGO Export. The template Access database cannot import data produced by the FOCS Export.

You can only import the data for one export at a time.

You may import any number of SSURGO Exports into the same Access database, so long as none of the data that you are importing already resides in the database. In other words, there is currently no capability to update existing data in the Access database.

Preparing the Data for Import

During the SSURGO Export process, a separate ASCII delimited file is produced for each table that exists in the template Access database. At the end of the export process, all of these individual files are tar’ed into a single file which is then zipped to reduce its size. The name of this final file produced by the SSURGO Export process is “exportfilename.gz”. (In NASIS 4.1, the export file was always named “nasis.gz”. In NASIS 5.0, the export file name defaults to the soil survey area ID of the legend being exported, but the user may name the export file whatever they wish.) The file “exportfilename.gz” must be unzipped and extracted before the data it contains can be imported into the template Access database.

The easiest way to do this is to transfer “exportfilename.gz” to a PC and use WinZip to extract its contents. When you open “exportfilename.gz” with WinZip, WinZip displays:

Archive contains one file:

exportfilename.tar

Should WinZip decompress it to a temporary folder and open it?

Select the “Yes” button, and when the new screen comes up, select the “Extract” option, select the directory to which you wish to extract the files and then select the “Extract” button.

You may also extract the files under UNIX and then transfer the extracted files to your PC. This requires two commands executed in succession:

gunzip exportfilename.gz

tar -xf exportfilename

The first command results in a single file named “exportfilename”. The second command extracts the individual ASCII delimited files from the archive. Once the individual files have been extracted, those files can then be transferred to a PC using FTP. The DOS end-of-line convention is built into the each ASCII delimited file. Therefore, if you FTP the unzipped ASCII files from UNIX to Windows, always transfer the files in Binary mode so that the existing end-of-line convention will not be corrupted.

WARNING

You should not unzip the exportfilename.gz file into the same directory in which you previously unzipped some other exportfilename.gz file. The problem is that some unzip utilities, like WinZip, are not very smart when it comes to zero length files. When Winzip sees a zero length file in the archive, it does not even bother to create that file when it extracts from the archive. Therefore if that same file from a previous export resides in the extract target directory, it does not get overwritten by the zero length file, as it should. Then, when you attempt to import that data into the Microsoft Access database, the import macro attempts to import that old copy of that file. Of course it cannot do so because that data is from an entirely different export. Referential integrity constraints in the Microsoft Access database prevent this from happening, as you would hope.

To avoid this problem, make sure that the directory into which you unzip exportfilename.gz, is empty before you unzip.

The symptom of this problem is the appearance of a dialog box during the import process that says:

Microsoft Access was unable to append all the data to the table.

The contents of fields in 0 record(s) were deleted, and SOMENUMBER record(s) were lost due to key violations.

*If data was deleted, the data you pasted or imported doesn't match the field data types or the FieldSize property in the destination table.

*If records were lost, either the records you pasted contained primary key values that already exist in the destination table, or they violate referential integrity rules for a relationship defined between tables.

Do you want to proceed anyway?

If you get this message, you should abort the import process and re-extract exportfilename.gz into an empty directory before attempting to import that data into Microsoft Access.

How to Import Data

Before you can import the data produced by a SSURGO Export, the data must be extracted from the final zipped file produced by the SSURGO Export process. See the previous section for these instructions. You must also have a copy of the template Access database on your PC, and Access must be installed on your PC.

Once you have satisfied these requirements, use the following steps to import data into the template Access database:

  • Open the template Access database.
     
  • If the template database is currently empty, the Import Dialog form will automatically be displayed.
     
  • If any data has previously imported into this database, you will need to select the “Macros” tab in the Database Window, and then execute the macro named “Import”, either by double clicking it or by selected it and then clicking the “Run” button.
     
  • In the blank on the form titled “Import Legend”, fill in the fully qualified path of the directory that contains the extracted export data. Remember to include the drive letter reference as part of this path name. Enter only the name of the directory that contains the extracted export files. Do not enter the name of a particular file. Note that the extracted export data and the template Access database do not have to reside in the same directory.
     
  • Click the “OK” button.

Once the import process starts, the view is switched to the “Tables” view, and the highlight moves around as the data is being imported.

Errors During the Import Process

If there is an error during the import process, the import process terminates and a dialog box is displayed. Any errors that occur during the import process are logged to a file named “nasiserr.txt” in the root directory of the C drive. Please have the contents of this file available before calling the Soils Hotline.

An error will occur if you attempt to import legends, map units or components that already reside in the database.

An error will also occur under either of the following two conditions. One, the import process cannot find the file that identifies the version of the data you are attempting to import. Two, the import process finds that file, but the version recorded in that file does not match the version of the Access database into which you are attempting to import data. The name of this file is “version.txt”. This file is bundled together with the other ASCII delimited files that make up the complete set of export data.

About the Template Microsoft Access Database

Database Security

The template MS Access database is currently unconstrained by any known security measures. All code is visible. Tables may be altered and data may be edited. While nothing prevents you from altering or enhancing this database, please keep in mind that this template database is frequently enhanced, and if you download a new enhanced version, any changes you made to a previous version of the template database will be lost unless you migrate those changes to the updated version of the template database.

Database Structure and Metadata

The complete metadata describing the structure of the template Access database is available at the following URL:

NASIS Documents

From this page, select the link titled “SSURGO Version 2 Tabular Component”.

This metadata also resides in the template Access database itself. See the System Reports in the template Access database whose names begin with “Static Tabular Metadata”.

The SSURGO Version 2 data structure is not exactly the same structure that exists in the transactional NASIS database. The major differences are outlined below.

  • NASIS contains both aggregated (map units and components) and point data (sites and pedons). The template Access database contains only aggregated data.
     
  • Not all of the aggregated data that resides in NASIS is included in a SSURGO export. Data pertaining to the management of an ongoing soil survey is not included in the new SSURGO standard.
     
  • NASIS makes a distinction between “data mapunits” and “mapunits”. The new SSURGO standard does not include the concept of a “data mapunit”. The hierarchy in the new SSURGO standard is still Soil Survey Area - Mapunit - Component.
     
  • In NASIS, tables and columns may be referred to by either their logical or physical name. In the template Access database, only physical names exist. Although no logical names exist in the template Access database, the same column captions are used in both NASIS and the template Access database.
     
  • In the template Access database, integer codes have been translated into their corresponding ASCII string representation.
     
  • NASIS is implemented using Informix, which is case sensitive. Microsoft Access is not case sensitive. In some ways, this simplifies the process of querying the template Access database. It also means that the template Access database cannot enforce all of the unique constraints that are defined in NASIS. For example, the template Access database, cannot enforce a unique constraint on the combination of map unit symbol and map unit status, since Access cannot distinguish between “AHd” and “Ahd”.

Reports

The template database for NASIS 4.1 NASIS exports contained only a handful a rather primitive reports. The template database for NASIS 5.0 SSURGO Version 2 exports contains most of the critical manuscript reports, as well as additional reports. In addition to the standard soil reports, the template database also contains reports that display all of the SSURGO metadata.

In the SSURGO Version 2 version of the template database, reports must be invoked through the Report Interface. You should not run a report by selecting the “Reports” tab in the Database Window and then opening a specific report. The reason for this is that the Report Interface establishes which map units are included in a report, as well as any report parameters that may be required.

There are two Report Interface forms, “Soil Reports” and “System Reports”. Either of these forms may be opened by selecting the “Forms” tab in the Database Window and then opening the form by that name. All manuscript and other soil reports appear in the “Soil Reports” form. All metadata and other system related reports appear in the “System Reports” form.

The System Report “Template Database Information” is an important report. This report reveals the version of the template database that you are currently using. If you want to know if an updated version of the template database is available, run this report to determine the version of your current template database, and then visit the following web page to determine if a newer version is available:

NASIS Downloads

The version of the most current template database is displayed in this web page. The System Report “Template Database Information” also contains a detailed history of every change that has been made to the template database, from version to version.

Writing Your Own Reports and Queries

The template Access database has no provisions that prevent a user from developing their own queries and reports. All relationships that are defined between two related tables are built into the template Access database. This means that when a user selects two related tables into an Access query grid, the proper join between those two tables will automatically be made.

Again, please keep in mind that while nothing prevents you from altering or enhancing this template database, this template database is frequently enhanced, and if you download a new enhanced version, any changes you made to a previous version of the template database will be lost unless you migrate those changes to the updated version of the template database.

The Export Macro

The template Access database contains a macro that permits a user to export data from the template Access database. The name of this macro is “Export”. This macro permits a user to export any combination of soil data, dynamic metadata or static metadata (see Sample Reports). Data is exported to a set of ASCII pipe delimited files, where each file's structure is the same as the structure of its corresponding underlying table in the template Access database.

Use of this macro will most likely be very limited. It does provide a user a way to export data that was accumulated from multiple imports as a single cohesive set. It also provides a way to export current NASIS/SSURGO metadata for use in other applications.