Data Connectors 

Get started with AMPL's data handlers to read and write data in the external formats of relational databases, spreadsheets, and CSV files.

Databases

Data handlers are accessed via AMPL’s table, read table, and write table statements, introduced in Chapter 10 of the AMPL Book. This chapter also introduces the use of table statements for data transfer. 

Spreadsheets

Data handlers introduced in chapter 10, although not specific to spreadsheets, provide examples that can be adapted to work with the new amplxl spreadsheet handler. Thus we recommend reading at least sections 10.1 though 10.4 if you have not used any AMPL spreadsheet interface previously.

CSV Files

Data handlers introduced in chapter 10, although not specific to CSV files, provides examples that can be adapted to work with the new amplcsv table handler. Thus we recommend reading at least sections 10.1 though 10.4 if you have not used any AMPL data table interface previously.

Data Input

For data input, AMPL also supports a plain text file format, and reads unformatted data. These options are introduced in Chapter 9.

Relational Databases

About

The structure of indexed data in AMPL has much in common with the structure of the relational tables widely used in data applications. The AMPL table declaration lets you take advantage of this similarity to define explicit connections between sets, parameters, variables, and expressions in AMPL, and relational tables maintained by other software. AMPL’s read table and write table commands subsequently use these connections to import data values into AMPL and to export data and solution values from AMPL.

The relational tables read and written by AMPL reside in files or databases whose names and locations you specify as part of the table declaration. To work with these files, AMPL relies on database handlers, which are add-ons that can be loaded as needed.

Getting started

To use AMPL’s features for relational table access, you must install a handler that is designed for your specific computer platform. This documentation page will provide links to database handlers for Microsoft Windows, Linux (all popular distributions), and macOS.

These handlers are based on the ODBC standard and have been tested with widely used database systems as indicated below. In the Windows section, we also describe an alternative for permitting data exchanges between 64-bit AMPL and 32-bit Microsoft Office applications. For database access on remote computers, see our notes and download instructions for the tableproxy table handler.

On Microsoft Windows systems, Excel spreadsheet data can also be treated as database tables. But for spreadsheets we recommend using table statements in conjunction with our direct spreadsheet interface instead.

Learn more

For more information on installation and documentation, see our documentation page on AMPL Documentation.

Featured Support

This interface features support for

  • spreadsheet software that recognizes .xlsx format files
  • Windows, Linux, and macOS
  • two-dimensional spreadsheet representations of multi-dimensional AMPL data

Learn more

Our extended spreadsheet interface works directly with .xlsx files for speed and reliability. It offers new, more convenient ways to identify and arrange data in sheets, while maintaining compatibility with existing AMPL table statements and interface conventions.

For more information on installation and documentation, see our documentation page on AMPL Documentation.

Comma-separated value files

Documentation

See our documentation page that describes a new AMPL interface for files in CSV format. It uses the same table statements as AMPL’s other data interfaces, but works directly with .csv files for speed and reliability.

If you are using .csv files with spreadsheet software, you may also be interested in AMPL’s direct spreadsheet interface that reads and writes .xlsx format files.

Learn More

The AMPL book’s chapter 10 Database Access introduces the use of table statements for data transfer. Although the presentation is not specific to CSV files, the examples in that chapter can be adapted to work with the new amplcsv table handler. Thus we recommend reading at least sections 10.1 though 10.4 if you have not used any AMPL data table interface previously.

For more information on installation and documentation, see our CSV file page on AMPL Documentation.

Need more support? Contact us.

Check out all technical documentation here.

Join the conversation on the AMPL forum.