Get started with AMPL's data handlers to read and write data in the external formats of relational databases, spreadsheets, and CSV files.
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.
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.
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.
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.
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
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
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.
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.