If you are using .csv files with spreadsheet software, you may also be interested in AMPL’s AMPL Direct Spreadsheet Interface that reads and writes .xlsx format files.
To confirm that your installation is working, download amplcsv-test.zip. Double-click the zipfile or use an unzip utility to extract the contents, which comprise 5 files:
If you are using command-line AMPL, move the 5 files into the folder (or Linux directory) where you have put
amplcsv.dll. Then start AMPL from that folder.
If you are using the AMPL IDE, move the 5 files into any convenient folder (or Linux directory). Start the AMPL IDE, and use the IDE’s file pane (at the left) to make that folder current.
To run the test, execute the following AMPL commands in your command window or IDE console window:
option solver minos;
If you see the message
Cannot invoke minos: no such program then replace minos in the
option solver command by a solver that is installed on your system.
The AMPL script
diet.run reads the AMPL model from
diet.mod, reads the data from
amounts.csv, and solves the optimization problem. At completion, the command window or IDE console window should display results like the following:
ampl: load amplcsv.dll;
ampl: include diet.run;
MINOS 5.51: optimal solution found.
13 iterations, objective 118.0594032
ampl: display Buy;
Buy [*] :=
If you have specified a different solver, the “optimal solution” message will be different, but the reported result values should be the same to many decimal places.
The AMPL script also writes several columns of data and optimal results to a new csv format file,
Buy.csv. To complete the test, open
Buy.csv as a text file, and confirm that you see the following lines:
If your system is configured to open .csv files in a spreadsheet program, however, then you may instead see a display like this:
Details of this image may differ, depending on your choice of spreadsheet software; in particular, the number of decimal places shown will depend on the width of the columns. If you are unable to get these results, please report the differences that you observed to email@example.com, including the text of any error messages; also if
Buy.csv is different, include it as an attachment.
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.
The amplcsv table handler recognizes the following option strings when they are included in AMPL table statements. (After
load amplcsv.dll; has been executed, you can also display this listing by use of the AMPL command
Instead of writing the data to a specific .csv file it is
possible to define an alias. In the following example the table
handler will search for the file tablealias.csv to write the data.
If the file does not exist it will be created.
table tablename OUT "amplcsv" "tablealias": [A], B;
Specifies the path to the .csv file to be read or written with
the read table and write table commands. If no file is
specified, amplcsv will search for a file with the table name
and the .csv file extension in the current directory. If the
table is to be written and the file does not exist it will be
table tablename OUT "amplcsv" "filename.csv": [keycol], valcol;
Whether or not to read/write the header from the .csv file
(defaults to true). If the option "header=false" is specified,
amplcsv will assume that the external table does not have a
header and that the order of the columns in AMPL and in the .csv
file is the same.
table tablename OUT "amplcsv" "header=false": [keycol], valcol;
(OUT only) If the option is used the external table will be
completely overwritten. Note that, by default, in OUT mode
amplcsv will maintain the initial header.
table tablename OUT "amplcsv" "overwrite": [keycol], valcol;
Whether or not to quote strings when writing data to a file or
unquote strings when reading data from a file. Available
options: "none" (default), "single" for single quotes and
"double" for double quotes.
table tablename IN "amplcsv" "quote=double": keycol <- [keycol], valcol;
Specifies the separator character in the .csv file. Available
comma (default) - "," character separator,
semicolon - ";" character separator,
colon - ":" character separator,
tab - tab character separator,
space - single space separator.
table tablename IN "amplcsv" "sep=tab": keycol <- [keycol], valcol;
Display warnings during the execution of the read table and
write table commands.
table tablename OUT "amplcsv" "verbose": [keycol], valcol;
Display information according to the specified option. Available
0 (default) - display information only on error,
1 - display warnings,
2 - display general information
3 - display debug information.
table tablename OUT "amplcsv" "verbose=2": [keycol], valcol;