Subscribe
Subscribe to the GIS Tools mailing list, one of several low volume mailing lists alerting you to changes to this site.

Your email address:


 More Info
Related Links
For a full discussion of using Perl with AML, read:
Supercharge your AMLs with Embedded Perl Code

The Perl Resource Page contains more information about getting and using Perl.

See GIS Tools for more GIS related items.
Downloads
Download the AML
Home Page | GIS Tools | Resume | Travel | Site Map
gis tools

List2CSV

List2CSV (CSV for short) is an easy-to-use freeware ArcInfo textfile formatting utility for converting ArcInfo listfiles into standard comma-separated-value format suitable for input into other software.

For example, if you wanted to create an Excel file with the names of all the schools within a 5KM radius of a certain point, you could simply make your selection in ArcPlot, list the attributes you wanted to send to Excel, and run CSV to create an Excel input file.

Note that for CSV to run properly, Perl 5 needs to be installed on your system. (Don't worry: it's easy. For information on how to get Perl, please see the Perl Resource Page.)

>>> Download CSV.AML <<<


Now that I have the AML, how do I run it?

Easy. If you have Perl (version 5 or above) installed, just copy csv.aml into any directory on your &amlpath and type:

&r csv <listfile>

Note: By putting the file into your &atool directory, you can omit the &r.

CSV then converts the listfile into a comma-separated-value file, which it calls listfile.csv, and prints a message stating that the operation has been completed successfully.

Perl is required because CSV is essentially a Perl program wrapped in an AML wrapper. (For more information on this technique, see Supercharge your AMLs with Embedded Perl Code.) If you don't have Perl, don't worry -- it's easy to get it. Please see the Perl Resource Page.

Hint: The easiest way to tell if Perl is already installed is to type "perl" at a system prompt. If it looks like the machine is waiting for further input, then Perl is installed. If you get an error, it isn't.

Options

CSV has three command-line options that modify its default behavior:

  • noheader - Do not print the column headers
  • norecord - Omit the left Record column that ArcInfo adds to all list command output
  • quiet - Do not print a message on completion

These options can be strung together on the command line in any order. For example:

&r csv mylistfile quiet noheader

An example

Before CSV will do anything productive, you must first create a list file. There are several ways to do this, but the simplest is to use the ArcPlot command listout (short for listouput) in conjunction with the list command.

For example:

AP: listout example init
AP: list sample poly
AP: listout screen
AP: &r csv example
Converted example into a CSV called example.csv
AP:

The first listout command tells Arcplot to send upcoming outputs of the list command to an ASCII file called example. The init option tells the system to clear the file if it already exists and start writing at the top.

The list command lists the polygon attributes of the coverage sample.

The second listout command restores the output for future list commands to the screen. Finally, the &r csv command runs CSV on the file example, which converts it into a comma-separated-file called example.csv.

If your list output looks like this:

Record     AREA    PERIMETER LOTS# NAME
     1  183.445    84818.000   5   Lot 131
     2  773.004   166500.000   6   Lot 132

CSV will produce this:

Record,AREA,PERIMETER,LOTS#,NAME
1,183.445,84818.000,5,Lot 131
2,773.004,166500.000,6,Lot 132

Warning: If your item definitions are not set properly for your data, when you list the information, you will get a field that looks like ************. CSV will not be able to correct this, and your output file will retain the stars.

Can I run CSV from other Arcinfo modules?

Yes. CSV is designed to run equally well from the ArcEdit, ArcPlot, Tables, GRID, and Arc prompts.

If you are in ArcEdit, you can access the listout command by prefixing it with apc. The example above could be run from ArcEdit like this:

Arcedit: edit sample poly 
Arcedit: select all
Arcedit: apc listout example init
Arcedit: list
Arcedit: apc listout screen
Arcedit: &r csv example
Converted example into a CSV called example.csv
Arcedit:

There are other ways to produce a comma-separated-value format file in ArcInfo. For example, you could use the tables unload command, which works fine for bulk data conversion. However, while you can use the basic select commands to control which records are output, Tables does not have the rich selection commands that ArcEdit and ArcPlot do. For example, it is impossible to select features using spatial relationships in Tables.

You can use CSV to overcome this limitation. For example, suppose you are editing an arc coverage in ArcEdit, and you want to export a list of the lengths of all the arcs shown on the screen. You could do it like this:

Arcedit: ef arc
Arcedit: select screen passthrough
Arcedit: apc listout example init
Arcedit: list length
Arcedit: apc listout screen
Arcedit: &r csv example
Converted example into a CSV called example.csv
Arcedit:

So what's the big deal?

When I first set out to write a utility to convert list output into a more standard format, I thought the task would be easy. In the past I had written a number of mission-specific list output conversion programs, which were simple enough. But writing a general program that could handle all cases proved more difficult.

ArcInfo has two separate output formats for the list command, which I refer to as Row and Column formats. ArcInfo will use column format unless the output lines would be too long for the display, in which case it will switch to row format. There is no way to direct ArcInfo to use one or the other -- the program chooses. These two formats look quite different from one another:

Row format:

              1
AREA                       =            0.00000
PERIMETER                  =            0.00000
VENUS#                     =     1
VENUS-ID                   =     1
ELEVATION                  =  2124
              2
AREA                       =            0.00000
PERIMETER                  =            0.00000
VENUS#                     =     2
VENUS-ID                   =     2
ELEVATION                  =  2124

Column format:

Record  ELEVATION COLOR
     1   2125     Light Blue
     2   2128     Red

Though less compact, row format is much easier to work with, because it follows a simple pattern:

FIELD  =  value

Column format, however, is essentially a spaced-delineated format, except that the values themselves can also contain spaces (as in the example above). As a result, the only way to reliably untangle the fields is by trying to calculate the position of each column. This process, however, is made more challenging by the fact that the column headers can be either left, center, or right-justified depending on the whim of ArcInfo, which makes it impossible to calculate where the column boundaries fall without analyzing each field in each row.

Luckily, CSV handles all these variations for you, and is able to detect and convert all the formats that ArcInfo can produce. Whatever your item definitions and listings look like, CSV will generate a consistent format for you to use with your other software.

Top of page
Green Tea

Comments or questions to: