Input from CSV file

[babu9708011 .]

Hi All:

I am trying to run an AMPL model using some parameters available as CSV format. Does AMPL directly handle CSV data or I need to do any other formatting. If it does, then how can I implement this. Any suggestion is highly appreciated. 

Best Regards

Noor

[victor.z...@gmail.com]

Hi Noor,

You can use the Microsoft ODBC Text Driver to read the data from CSV files. Here's a small example.

test.ampl:

set S;

table test 'odbc' 'DSN=test' 'test.csv': [S] IN;

read table test;

display S;

where test is a data source name that uses "Microsoft Text Driver (*.txt, *.csv)" as a driver.

test.csv:

S

a

b

c

> ampl test.ampl

set S := a b c;

Note that unlike some other ODBC drivers, Text Driver uses the table name to identify a file (test.csv in this example) and the directory where to search for the file is specified in the data source name (it was set to "Use Current Directory" in this example).

HTH,

Victor

--
You received this message because you are subscribed to the Google Groups "AMPL Modeling Language" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ampl+uns...@googlegroups.com.
To post to this group, send email to am...@googlegroups.com.
Visit this group at http://groups.google.com/group/ampl.
For more options, visit https://groups.google.com/groups/opt_out.

[babu9708011 .]

Hi Victor:

Thanks a lot. I would like to give you more details about my problem to get more advise from you. Some parameters in my model are linear and available in txt format, but I have 11 more parameters (3 dimensional) for which I have 11 sets of 2 dimensional data are available in CSV format (these are MATLAB output from another process). So far I knew that AMPL can handle only one data file. I do not know how to handle these data sets,in which some are in txt format and some are in CSV format. Could you please shed more lights on it by providing any example.

Again thanks for your help.

Noor

[victor.z...@gmail.com]

Hi Noor,

APML can easily handle multiple data files and databases and you can read part of your data from CSV and part from data files. To read from several CSV files you'll need to create one AMPL table declaration per CSV file and use a read table command to import data for each table.

I am not sure what format your text files are in, but if it is AMPL data you can have multiple data statements as well. If it is in some other format you can use the AMPL read command described in Section 9.5 "Reading unformatted data: the read command" of the AMPL book: http://www.ampl.com/BOOK/CHAPTERS/12-data.pdf#page=21

HTH,

Victor

[babu9708011 .]

Hi Victor:

Thanks very much for your help. To handle my above situation I created a model file and a data file with all sets and parameters except the parameters which are very large. Now my large parameters are saved as a txt documents and they are two-dimensional comma-separated (original parameter is 3 dimensional, but I am using 2 dimensional slices). To incorporate the large data sets I have created a run file where I have given information for model and data files, and wrote following statement (for example for one slice):

read {t in 1..7067,t in 1..7067}S ["sus",t,t]<first.txt;

#datas are saved in first.txt file in the same folder.

However, I am getting syntax error message while I am trying to run it.I am not sure why I am having this message and whether my strategy is correct to deal this issue.

Thanks again for your help.

Best Regards

Noor

[victor.z...@gmail.com]

Hi Noor,

You get an error because of using the same name (t) for different indices in the same scope.

Also note that the AMPL read command reads whitespace-separated values, not comma-separated.

HTH,

Victor

[babu9708011 .]

Hi Victor:

Thanks a lot.I have fixed those issues, but now I having the following error:

Error at _cmdno 7 executing "read" command
(file cp1.run, line 10, offset 195):
        invalid subscript S['sus',1,1]

Do you have any suggestion for this.

Thanks.

Noor

[babu9708011 .]

Hi Victor:

I was able to fix this. Thanks a lot for your help.

Noor

[Noor]

Hi fbahr:

I have fixed this. However, I need a help to covert a big text file from comma separated form to space separated form. Is there any way in ampl to do this.

Thanks.

Noor

Sent from my iPhone

On 2014-01-23, at 4:50 AM, fbahr <floria...@gmail.com> wrote:

How is `param S` defined?

--fbahr

[fbahr]

For data preparation or preprocessing tasks, tools like 'sed' or 'awk' come in handy.

If you're on a Unix/Linux machine, these tools are most likely already installed -- for Windows, binaries can be obtained e. g. here: http://gnuwin32.sourceforge.net/packages.html

Having, for instance, 'sed' installed*1, you can do [from a *nix shell or Windows' cmd.exe]:

  sed "s/,/ /g" <sourcepath>/data.csv > <targetpath>/data.ssv

which takes your file [containing comma-separated values], data.csv, and creates a copy, data.ssv, with space-separated values only.

--fbahr

*For Windows [http://gnuwin32.sourceforge.net/packages/sed.htm], you can choose btw. a version with an installer or a zip'ped archive only containing the executable binary. If you pick the latter, it's probably easiest to unzip the executable right into the directory containing your data file, open cmd.exe, navigate to the respective directory and execute

  sed "s/,/ /g" data.csv > data.ssv