[AMPL 15234] Reading large data-set from Excel to AMPL

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

[AMPL 15234] Reading large data-set from Excel to AMPL

Azadeh Mobtaker
Hello everyone,

I have a large set of 132,341 elements in it. I am able to read this set until 65000 elements but for more than that AMPL gives an irrelevant error "Error reading table setCluster with table handler tableproxy: Table `CLUSTERS` does not appear in "Data.xlsx".".

The given error doesn't make sense to me, as there has been no problem with table handler until the size of my set "CLUSTERS" was less than 65000.

My thesis supervisor has bought the AMPL FOR COURSES'', for which the problem sizes are supposedly unrestricted. 

I was wondering if you could guide me on what could be the problem here.

Thank you.

Azadeh


--
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/ampl.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

RE: [AMPL 15245] Reading large data-set from Excel to AMPL

Robert Fourer-2
Is your limit actually 2^16 = 65536?  This would suggest that you are running up against some built-in limitation of Excel, or the ODBC driver for Excel, or the AMPL table handler.  This limitation would affect all versions of AMPL and would not be specific to the AMPL for Courses version.  The unexpected error message is most likely a side-effect of the underlying limitation.

So that we can investigate further and try to remove this limit, please reply with the following information:  (1) Go to File / Help in Excel, and send a screenshot of the window that appears.  (2) Open your file in Excel and send a screenshot showing the beginning of your data.  (3) Copy into your reply the AMPL "table" statement you are using, and the AMPL definitions of the sets and params that appear in the table statement.

Bob Fourer
[hidden email]

=======

From: [hidden email] [mailto:[hidden email]] On Behalf Of Azadeh Mobtaker
Sent: Tuesday, November 28, 2017 3:11 PM
To: AMPL Modeling Language
Subject: [AMPL 15234] Reading large data-set from Excel to AMPL

I have a large set of 132,341 elements in it. I am able to read this set until 65000 elements but for more than that AMPL gives an irrelevant error "Error reading table setCluster with table handler tableproxy: Table `CLUSTERS` does not appear in "Data.xlsx".".

The given error doesn't make sense to me, as there has been no problem with table handler until the size of my set "CLUSTERS" was less than 65000.

My thesis supervisor has bought the AMPL FOR COURSES'', for which the problem sizes are supposedly unrestricted.

I was wondering if you could guide me on what could be the problem here.


--
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/ampl.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [AMPL 15248] Reading large data-set from Excel to AMPL

Azadeh Mobtaker
Hi,
Thank you for your explanations. Yes, that is exactly the limit. Below are the three elements you asked for:

(1)


(2)

(3)table cluster_data "tableproxy" "ODBC" "Data.xlsx" "ClusterData":

CLUSTERS <- [CLUSTERS], vol, MST, Num; 

read table cluster_data;


set CLUSTERS;

param vol {a in CLUSTERS}; 

param MST {a in CLUSTERS};

param Num {a in CLUSTERS};



On Wednesday, November 29, 2017 at 12:07:12 PM UTC-5, Robert Fourer wrote:
Is your limit actually 2^16 = 65536?  This would suggest that you are running up against some built-in limitation of Excel, or the ODBC driver for Excel, or the AMPL table handler.  This limitation would affect all versions of AMPL and would not be specific to the AMPL for Courses version.  The unexpected error message is most likely a side-effect of the underlying limitation.

So that we can investigate further and try to remove this limit, please reply with the following information:  (1) Go to File / Help in Excel, and send a screenshot of the window that appears.  (2) Open your file in Excel and send a screenshot showing the beginning of your data.  (3) Copy into your reply the AMPL "table" statement you are using, and the AMPL definitions of the sets and params that appear in the table statement.

Bob Fourer
<a href="javascript:" target="_blank" gdf-obfuscated-mailto="43PnAWRpAQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">am...@...

=======

From: <a href="javascript:" target="_blank" gdf-obfuscated-mailto="43PnAWRpAQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">am...@... [mailto:<a href="javascript:" target="_blank" gdf-obfuscated-mailto="43PnAWRpAQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">am...@...] On Behalf Of Azadeh Mobtaker
Sent: Tuesday, November 28, 2017 3:11 PM
To: AMPL Modeling Language
Subject: [AMPL 15234] Reading large data-set from Excel to AMPL

I have a large set of 132,341 elements in it. I am able to read this set until 65000 elements but for more than that AMPL gives an irrelevant error "Error reading table setCluster with table handler tableproxy:        Table `CLUSTERS` does not appear in "Data.xlsx".".

The given error doesn't make sense to me, as there has been no problem with table handler until the size of my set "CLUSTERS" was less than 65000.

My thesis supervisor has bought the AMPL FOR COURSES'', for which the problem sizes are supposedly unrestricted.

I was wondering if you could guide me on what could be the problem here.


--
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/ampl.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

RE: [AMPL 15257] Reading large data-set from Excel to AMPL

Robert Fourer-2

We found another report of this problem, with the same error message when the Excel range exceeded 2^16 rows.  It was caused by limits in an older version of the Microsoft ODBC drivers, and it was fixed by updating the ODBC drivers to the latest version which is available for download from http://www.microsoft.com/en-us/download/confirmation.aspx?id=13255.  (You may need to scroll down to see the installation instructions.)

 

Bob Fourer

[hidden email]

 

 

--
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/ampl.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [AMPL 15266] Reading large data-set from Excel to AMPL

Azadeh Mobtaker
Dear Bob,

Thank you very much. In the instruction line #4, I need to set the Connection String to “Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=path to xls/xlsx/xlsm/xlsb file”; I don't know how to do that. I was wondering if you could guide me on that as well. Thanks.

Best regards,
Azadeh

On Thursday, November 30, 2017 at 7:13:59 PM UTC-5, Robert Fourer wrote:

We found another report of this problem, with the same error message when the Excel range exceeded 2^16 rows.  It was caused by limits in an older version of the Microsoft ODBC drivers, and it was fixed by updating the ODBC drivers to the latest version which is available for download from <a href="http://www.microsoft.com/en-us/download/confirmation.aspx?id=13255" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fdownload%2Fconfirmation.aspx%3Fid%3D13255\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFGy9kDqSzgmprILV_1bpPOm2joyw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fdownload%2Fconfirmation.aspx%3Fid%3D13255\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFGy9kDqSzgmprILV_1bpPOm2joyw&#39;;return true;">http://www.microsoft.com/en-us/download/confirmation.aspx?id=13255.  (You may need to scroll down to see the installation instructions.)

 

Bob Fourer

<a href="javascript:" target="_blank" gdf-obfuscated-mailto="IB-fqULPAQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">am...@...

 

 

--
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/ampl.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

RE: [AMPL 15280] Reading large data-set from Excel to AMPL

Robert Fourer-2
You should follow DBQ= by the name of your Excel file.  For example,

   DBQ=C:\Users\Azadeh\Test_AMPL2017\smallLP\Results2.xls

(Of course this is from another project and your file path will be different.)

Bob Fourer
[hidden email]

=======

From: [hidden email] [mailto:[hidden email]] On Behalf Of Azadeh
Sent: Friday, December 1, 2017 1:03 PM
To: AMPL Modeling Language
Subject: Re: [AMPL 15266] Reading large data-set from Excel to AMPL

Thank you very much. In the instruction line #4, I need to set the Connection String to “Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=path to xls/xlsx/xlsm/xlsb file”; I don't know how to do that. I was wondering if you could guide me on that as well. Thanks.


--
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/ampl.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [AMPL 15322] Reading large data-set from Excel to AMPL

Azadeh Mobtaker
Dear Bob,

Thank you very much for your explanations. 

Best regards,

Azadeh Mobtaker

On Sunday, December 3, 2017 at 12:51:54 PM UTC-5, Robert Fourer wrote:
You should follow DBQ= by the name of your Excel file.  For example,

   DBQ=C:\Users\Azadeh\Test_AMPL2017\smallLP\Results2.xls

(Of course this is from another project and your file path will be different.)

Bob Fourer
<a href="javascript:" target="_blank" gdf-obfuscated-mailto="H-QN3nH6AgAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">am...@...

=======

From: <a href="javascript:" target="_blank" gdf-obfuscated-mailto="H-QN3nH6AgAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">am...@... [mailto:<a href="javascript:" target="_blank" gdf-obfuscated-mailto="H-QN3nH6AgAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">am...@...] On Behalf Of Azadeh
Sent: Friday, December 1, 2017 1:03 PM
To: AMPL Modeling Language
Subject: Re: [AMPL 15266] Reading large data-set from Excel to AMPL

Thank you very much. In the instruction line #4, I need to set the Connection String to “Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=path to xls/xlsx/xlsm/xlsb file”; I don't know how to do that. I was wondering if you could guide me on that as well. Thanks.


--
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/ampl.
For more options, visit https://groups.google.com/d/optout.