AMPL handling csv files

[Ra Su]

Hi There,

I am trying to use the attached xls file as an input for the model but I am unsure how to define the range of the "table" COL using ampl. I also failed to find a way to define the "table" using the python script from which I generated it using xlsxwriter.

Is it possible to turn it into a CSV to feed into ampl?

[AMPL Google Group]

See my reply to your previous question. (You need to assign a value to nCOL in OperationalFLMC.run, and also remove the original "read table COL".)

In the new "table COL" statement, since you are using the old .xls format for your spreadsheets, replace ".xlsx" by ".xls".

Also in each of your spreadsheet files, you will need to create a range named "COL" that contains the data.

--
Robert Fourer
am...@googlegroups.com

{#HS:1101335340-69667#}

[Ra Su]

Hi Robert,

Since this is a Monte Carlo Simulation I will have close to 10000 xls files for COL. The way I am generating (using xlsxwriter) I am unable to name the range "COL" using that library. Are you aware of how to do this?

All of my COL files will have the table from range A1:D65...is there a way I can designate this range in AMPL? 

Is there a way I can use instead a CSV instead of xls to forgo this issue???

[AMPL Google Group]

We are working on a new version of the spreadsheet file handler that will not require a range name; if there is no named range, it will automatically find the rows and columns that contain your table. For now, however, there is a way to read the data from CSV files such as those attached. The relevant code is this:

param nCOL = 2;
param nCOLvals = card(DAYD) * card(CROP) * card(QUAL);
param ic; param jc symbolic; param kc;

param sumSD {1..nCOL};
for {c in 1..nCOL}{
   read {1..nCOLvals} (ic,jc,kc,COL[ic,jc,kc]) <("col" & c & ".csv");
   solve;
   let sumSD[c] := 
      sum {h in DAYH, t in DAYD, k in PROD, 
           q in QUAL, d in DC, i in CUST, r in TRANS} SD[h,t,k,q,d,i,r];
}

Note that there is no header line in the CSV file; there are just data lines. Also the param nCOLvals must equal the number of data lines. If you get an "unexpected end of file" message from the read command, then nCOLvals is larger than the number of data lines, and you will need to fix that.

--
Robert Fourer
am...@googlegroups.com

{#HS:1101335340-69667#}

On Sat, Mar 7, 2020 at 3:07 AM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:

Hi Robert,

Since this is a Monte Carlo Simulation I will have close to 10000 xls files for COL. The way I am generating (using xlsxwriter) I am unable to name the range "COL" using that library. Are you aware of how to do this?
All of my COL files will have the table from range A1:D65...is there a way I can designate this range in AMPL?

Is there a way I can use instead a CSV instead of xls to forgo this issue???

On Fri, Mar 6, 2020 at 2:15 PM UTC, AMPL Google Group <am...@googlegroups.com> wrote:

See my reply to your previous question. (You need to assign a value to nCOL in OperationalFLMC.run, and also remove the original "read table COL".)

In the new "table COL" statement, since you are using the old .xls format for your spreadsheets, replace ".xlsx" by ".xls".

Also in each of your spreadsheet files, you will need to create a range named "COL" that contains the data.

--
Robert Fourer
am...@googlegroups.com

[ratnasu...@gmail.com]

Here is how I added the code for the csv:

read table CTPW;

read table CT;

read table CTPD;

read table CTW;

read table CTD;

read table CTWD;

read table CI;

read table CID;

read table DM;

read table DC;

# Options Selected for CPLEX Solver #

param nCOL = 2;

param nCOLvals = card(DAYD) * card(CROP) * card(QUAL);

param ic; param jc symbolic; param kc;

param sumSD {1..nCOL};

for {c in 1..nCOL}{

   read {1..nCOLvals} (ic,jc,kc,COL[ic,jc,kc]) <("COL" & c & ".csv");

   solve;

   let sumSD[c] := 

      sum {h in DAYH, t in DAYD, k in PROD, 

           q in QUAL, d in DC, i in CUST, r in TRANS} SD[h,t,k,q,d,i,r];

}

option solver cplexamp;

option eexit -100; 

option cplex_options 'timing=1 timelimit=3600 mipdisplay=2 lpdisplay=1 mipgap=.005 sensitivity';

Here is the error I get

(file C:\Users\Owner\Documents\amplide.mswin64\models\Opertrialmccsv.run, line 100, offset 3400):

"read" command error: expected a number

[AMPL Google Group]

Assuming COL is indexed over {DAYS,CROP,QUAL} as in your spreadsheet examples, the csv files should have four comma-separated entries on each line, like

1,TA,2,0

The first, third, and fourth entries should be numbers, and the second should be a character string. The error message "expected a number" means that the read command found a character string where there should have been a number. This could be caused by a mistyped number, or a missing or extra comma, or some other error in the formatting of the csv file.

--
Robert Fourer
am...@googlegroups.com

{#HS:1101335340-69667#}

On Mon, Mar 9, 2020 at 6:59 PM UTC, AMPL Google Group <am...@googlegroups.com> wrote:

We are working on a new version of the spreadsheet file handler that will not require a range name; if there is no named range, it will automatically find the rows and columns that contain your table. For now, however, there is a way to read the data from CSV files such as those attached. The relevant code is this:

param nCOL = 2;
param nCOLvals = card(DAYD) * card(CROP) * card(QUAL);
param ic; param jc symbolic; param kc;

param sumSD {1..nCOL};
for {c in 1..nCOL}{
   read {1..nCOLvals} (ic,jc,kc,COL[ic,jc,kc]) <("col" & c & ".csv");
   solve;
   let sumSD[c] := 
      sum {h in DAYH, t in DAYD, k in PROD, 
           q in QUAL, d in DC, i in CUST, r in TRANS} SD[h,t,k,q,d,i,r];
}

Note that there is no header line in the CSV file; there are just data lines. Also the param nCOLvals must equal the number of data lines. If you get an "unexpected end of file" message from the read command, then nCOLvals is larger than the number of data lines, and you will need to fix that.

--
Robert Fourer
am...@googlegroups.com

[ratnasu...@gmail.com]

Yes Attached is an example of what I am running. The csv file looks good and I have just incase "Saved as" csv to make sure. Why do you think I am getting this error?

[AMPL Google Group]

You need to remove the first line "DAYD CROP QUAL COL" from your csv file. (The "read" command is an unformatted data reader that expects to find only the data in the file.)

Also you should take care to set param nCOLvals equal to the number of lines in the csv file, as that is exactly the number of lines that will be read. In my example I computed the number of lines as card(DAYD) * card(CROP) * card(QUAL), but you should explicitly compare this number to the actual number of lines in the csv files, and adjust the computation if necessary.

--
Robert Fourer
am...@googlegroups.com

{#HS:1101335340-69667#}

On Fri, Mar 20, 2020 at 12:20 AM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:

Yes Attached is an example of what I am running. The csv file looks good and I have just incase "Saved as" csv to make sure. Why do you think I am getting this error?

On Thu, Mar 19, 2020 at 7:44 PM UTC, AMPL Google Group <am...@googlegroups.com> wrote:

Assuming COL is indexed over {DAYS,CROP,QUAL} as in your spreadsheet examples, the csv files should have four comma-separated entries on each line, like

1,TA,2,0

The first, third, and fourth entries should be numbers, and the second should be a character string. The error message "expected a number" means that the read command found a character string where there should have been a number. This could be caused by a mistyped number, or a missing or extra comma, or some other error in the formatting of the csv file.

--
Robert Fourer
am...@googlegroups.com

[ratnasu...@gmail.com]

The COL table will always be the same length and go to the 63rd row. I am running the attached files with no header row and it still gives the same exact error.

"read" command error: expected a number, symbol or string

[AMPL Google Group]

Does "param nCOLvals = card(DAYD) * card(CROP) * card(QUAL);" set nCOLvals to 63? This expression worked for my example but it might not be right for your application. If the number of rows is always going to be 63, you could just say "param nCOLvals = 63;".

--
Robert Fourer
am...@googlegroups.com

{#HS:1101335340-69667#}

On Sun, Mar 22, 2020 at 4:51 PM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:

The COL table will always be the same length and go to the 63rd row. I am running the attached files with no header row and it still gives the same exact error.

"read" command error: expected a number, symbol or string

On Fri, Mar 20, 2020 at 6:59 PM UTC, AMPL Google Group <am...@googlegroups.com> wrote:

You need to remove the first line "DAYD CROP QUAL COL" from your csv file. (The "read" command is an unformatted data reader that expects to find only the data in the file.)

Also you should take care to set param nCOLvals equal to the number of lines in the csv file, as that is exactly the number of lines that will be read. In my example I computed the number of lines as card(DAYD) * card(CROP) * card(QUAL), but you should explicitly compare this number to the actual number of lines in the csv files, and adjust the computation if necessary.

--
Robert Fourer
am...@googlegroups.com

[Ra Su]

Ok I did that because this example resembles the rest of my sheets.

I'm still getting this error:

(file C:\Users\Owner\Documents\amplide.mswin64\models\Opertrialmccsv.run, line 108, offset 3559):

"read" command error: expected a number, symbol or string

[AMPL Google Group]

To get more help at this point, I recommend posting *all* of your files again. That will enable a fresh run from the beginning, which may reveal where in the file the error is occurring.

--
Robert Fourer
am...@googlegroups.com

{#HS:1101335340-69667#}

On Tue, Mar 24, 2020 at 12:17 AM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:

Ok I did that because this example resembles the rest of my sheets.

I'm still getting this error:

(file C:\Users\Owner\Documents\amplide.mswin64\models\Opertrialmccsv.run, line 108, offset 3559):

"read" command error: expected a number, symbol or string

On Mon, Mar 23, 2020 at 11:44 PM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:

Ok I did that. I am not sure what card(DAYD) would evaluate to. So if I remove that line and make it 63 then how will it know what columns it is reading in?

On Mon, Mar 23, 2020 at 10:24 PM UTC, AMPL Google Group <am...@googlegroups.com> wrote:

Does "param nCOLvals = card(DAYD) * card(CROP) * card(QUAL);" set nCOLvals to 63? This expression worked for my example but it might not be right for your application. If the number of rows is always going to be 63, you could just say "param nCOLvals = 63;".

--
Robert Fourer
am...@googlegroups.com

[Ra Su]

here they are. Thank you!

[AMPL Google Group]

I don't see "expected a number, symbol or string" when I run the files that you have provided. The error message that I see is:

ampl: include Opertrialmccsv.run
Error at _cmdno 40 executing "read" command
(file Opertrialmccsv.run, line 108, offset 3559):
	invalid subscript COL[1,'TA',5]

To understand why this error occurs, observe that the definition of COL is

param COL {DAYD,CROP,QUAL};

and that the members of the three indexing sets for COL are

ampl: display DAYD, CROP, QUAL;
set DAYD := 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18;
set CROP := TA;
set QUAL := 1 2 3 4;

In particular, the third index of COL must be a member of QUAL: 1, 2, 3, or 4. However, line 37 of COL1.csv is

1,TA,5,5.057073736

which causes the read statement to try to assign 5.057073736 to COL[1,'TA',5]. Since 5 is not a member of QUAL, AMPL rejects this attempted assignment with the "invalid subscript" message.

--
Robert Fourer
am...@googlegroups.com

{#HS:1101335340-69667#}

On Wed, Mar 25, 2020 at 4:25 PM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:

here they are. Thank you!

On Tue, Mar 24, 2020 at 12:46 AM UTC, AMPL Google Group <am...@googlegroups.com> wrote:

To get more help at this point, I recommend posting *all* of your files again. That will enable a fresh run from the beginning, which may reveal where in the file the error is occurring.

--
Robert Fourer
am...@googlegroups.com

[ratnasu...@gmail.com]

Hi Robert,

I removed the 5 index from my csv and it is still giving me a read error....attached are my files. Let me know what I am doing wrong please.

Thank You,

Ratna

[AMPL Google Group]

You are getting "invalid subscript" messages because there is data in your spreadsheet that does not correspond to the indexing of your params. The first such message refers to param CID:

param CID {PROD,DC} >= 0;

AMPL's display command gives the members of the two indexing sets as follows:

ampl: display PROD, DC;
set PROD := TA5X6 TA5X5 TA4X5;
set DC := D1 D2;

So 6 data values are needed, for CID['TA5X6','D1'], CID['TA5X5','D1'], CID['TA4X5','D1'], CID['TA5X6','D2'], CID['TA5X5','D2'], CID['TA4X5','D2']. You are reading these values from spreadsheet file pubinput.xls:

table CID IN "ODBC" "pubinput.xls": [PROD,DC], CID;

But in the spreadsheet, there are 16 data values for CID:



The 10 extra entries have values in the PROD column that don't correspond to members of set PROD in your data. AMPL ignores these entries, but warns you with the "10 invalid subscripts discarded" message. You can remove the extra entries, or you can ignore the error message since the correct values are being read:

ampl: display CID;
CID :=
TA4X5 D1 0.015
TA4X5 D2 0.015
TA5X5 D1 0.015
TA5X5 D2 0.015
TA5X6 D1 0.015
TA5X6 D2 0.015
;

You can follow the same reasoning to understand the "invalid subscripts" messages for param PROB and param SL.

--
Robert Fourer
am...@googlegroups.com

{#HS:1101335340-69667#}

On Sat, Apr 4, 2020 at 2:42 PM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:

Hi Robert,

I removed the 5 index from my csv and it is still giving me a read error....attached are my files. Let me know what I am doing wrong please.

Thank You,
Ratna

On Thu, Mar 26, 2020 at 2:21 PM UTC, AMPL Google Group <am...@googlegroups.com> wrote:

I don't see "expected a number, symbol or string" when I run the files that you have provided. The error message that I see is:

ampl: include Opertrialmccsv.run
Error at _cmdno 40 executing "read" command
(file Opertrialmccsv.run, line 108, offset 3559):
	invalid subscript COL[1,'TA',5]

To understand why this error occurs, observe that the definition of COL is

param COL {DAYD,CROP,QUAL};

and that the members of the three indexing sets for COL are

ampl: display DAYD, CROP, QUAL;
set DAYD := 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18;
set CROP := TA;
set QUAL := 1 2 3 4;

In particular, the third index of COL must be a member of QUAL: 1, 2, 3, or 4. However, line 37 of COL1.csv is

1,TA,5,5.057073736

which causes the read statement to try to assign 5.057073736 to COL[1,'TA',5]. Since 5 is not a member of QUAL, AMPL rejects this attempted assignment with the "invalid subscript" message.

--
Robert Fourer
am...@googlegroups.com

[RG S]

Yes but the read in message only occurs when I use the COL as csv.

When I remove the invalid subscriptions it still gives me the same answer. 

--
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 view this discussion on the web visit https://groups.google.com/d/msgid/ampl/reply-77152-1101335340-3207959997-1586096828-1206731380%40helpscout.net.

[Ra Su]

Here is a new inputfile and run file with those indices removed...I am still getting a read error at this line that reads in the COL1.csv

[AMPL Google Group]

Can you post a new, complete set of files that are still giving an error when reading COL1.csv and COL2.csv? Be sure to check these files carefully before including them, to be sure that all previous causes of error have been removed; when you have many versions around, it is easy to accidentally attach an older one.

--
Robert Fourer
am...@googlegroups.com

{#HS:1101335340-69667#}

On Mon, Apr 6, 2020 at 5:54 PM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:

Here is a new inputfile and run file with those indices removed...I am still getting a read error at this line that reads in the COL1.csv

On Mon, Apr 6, 2020 at 1:52 PM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:

Yes but the read in message only occurs when I use the COL as csv.

When I remove the invalid subscriptions it still gives me the same answer.

On Sun, Apr 5, 2020 at 2:26 PM UTC, AMPL Google Group <am...@googlegroups.com> wrote:

You are getting "invalid subscript" messages because there is data in your spreadsheet that does not correspond to the indexing of your params. The first such message refers to param CID:

param CID {PROD,DC} >= 0;

AMPL's display command gives the members of the two indexing sets as follows:

ampl: display PROD, DC;
set PROD := TA5X6 TA5X5 TA4X5;
set DC := D1 D2;

So 6 data values are needed, for CID['TA5X6','D1'], CID['TA5X5','D1'], CID['TA4X5','D1'], CID['TA5X6','D2'], CID['TA5X5','D2'], CID['TA4X5','D2']. You are reading these values from spreadsheet file pubinput.xls:

table CID IN "ODBC" "pubinput.xls": [PROD,DC], CID;

But in the spreadsheet, there are 16 data values for CID:



The 10 extra entries have values in the PROD column that don't correspond to members of set PROD in your data. AMPL ignores these entries, but warns you with the "10 invalid subscripts discarded" message. You can remove the extra entries, or you can ignore the error message since the correct values are being read:

ampl: display CID;
CID :=
TA4X5 D1 0.015
TA4X5 D2 0.015
TA5X5 D1 0.015
TA5X5 D2 0.015
TA5X6 D1 0.015
TA5X6 D2 0.015
;

You can follow the same reasoning to understand the "invalid subscripts" messages for param PROB and param SL.

--
Robert Fourer
am...@googlegroups.com