Reading Gigantic columns more than 255 rows in Excel :

[Eric E Sättar]

Dear AMPL supporter,

I am about to read a database with more than 255 columns, the ampl gives me an error that does not make any sense to me (please see below): says column FTD18 does not appear in table, which I know this column is there. I wonder this is the limitation with the ODBC or something else? I would appreciate for any thought. I have post my odbc window as well. P.S. my Excel is 64 bit and my AMPL version is 64 too (option version 'AMPL Version 20171212 (MS VC++ 10.0, 64-bit)\

Error reading table CprcsF with table handler odbc:

Column "FTD18" does not appear in table `CprcsF` of "C:\Users\ampl02\AMPL02018\SecondRunModel\Paper2\Tst\DataIntegratedPaper3.xlsx",

which has 255 columns:

P.S. I have already had this problem with reading rows more than 64,000, and Filipe Brandão gave gave me a tip it was very successful.

Thanks for your prompt answer and sorry if you have received my message twice.

/Eric

[AMPL Google Group]

I do not see ACEODBC.DLL drivers in your screenshots . In case you do not have ACEODBC.DLL drivers installed, you can install Microsoft Access Database Engine 2016 (https://www.microsoft.com/en-us/download/details.aspx?id=54920&751be11f-ede8-5a0c-058c-2ee190a24fa6=True) in order to install recent 64-bit drivers that may fix this issue. Nevertheless, 255 seems to be a limit from Excel (https://www.limesurvey.org/forum/can-i-do-this-with-limesurvey/94932-export-more-than-255-columns-in-excel) that may still be present in recent drivers

In case you have both 32 and 64 bit drivers installed and AMPL is not using the correct one, you can specify the driver name in the table declaration as follows:
table Foods "odbc" "Driver=Microsoft Excel Driver (*.xls, *.xlsx,
*.xlsm, *.xlsb);ReadOnly=0;DBQ=./diet.xls" "verbose": ...
Please note that after "DBQ=" you need to either provide the complete path to the file or start the relative path with "./". The option "verbose" in the table declaration is useful to generate additional feedback about the error.

--
Filipe Brandão
am...@googlegroups.com

{#HS:931176872-52505#}

--
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/7b4b52ca-c5d3-4afb-9981-57e60f20c673%40googlegroups.com.

[Eric E Sättar]

Thanks Filipe,

so this does the mean that, the excel cannot processes data with more than 255 columns?

I am trying to use .tab or txt to read this chunk of the data.

Thanks for other comments too.

/Eric

 

To unsubscribe from this group and stop receiving emails from it, send an email to am...@googlegroups.com.

[AMPL Google Group]

Yes, it looks like the driver is limited to 255 columns and .tab or txt should be better options for that amount of data. The driver seems to follow access's specification in the number of fields (columns) (see, e.g., https://support.office.com/en-us/article/access-specifications-0cf3c66f-9cf2-4e32-9568-98c1025bb47c) that is limited to 255 fields in a table.

--
Filipe Brandão
am...@googlegroups.com

{#HS:931176872-52505#}

On Mon, Aug 19, 2019 at 3:43 PM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:

Thanks Filipe,

so this does the mean that, the excel cannot processes data with more than 255 columns?
I am trying to use .tab or txt to read this chunk of the data.

Thanks for other comments too.

/Eric

On Fri, Aug 16, 2019 at 2:10 PM UTC, AMPL Google Group <am...@googlegroups.com> wrote:

I do not see ACEODBC.DLL drivers in your screenshots . In case you do not have ACEODBC.DLL drivers installed, you can install Microsoft Access Database Engine 2016 (https://www.microsoft.com/en-us/download/details.aspx?id=54920&751be11f-ede8-5a0c-058c-2ee190a24fa6=True) in order to install recent 64-bit drivers that may fix this issue. Nevertheless, 255 seems to be a limit from Excel (https://www.limesurvey.org/forum/can-i-do-this-with-limesurvey/94932-export-more-than-255-columns-in-excel) that may still be present in recent drivers

In case you have both 32 and 64 bit drivers installed and AMPL is not using the correct one, you can specify the driver name in the table declaration as follows:
table Foods "odbc" "Driver=Microsoft Excel Driver (*.xls, *.xlsx,
*.xlsm, *.xlsb);ReadOnly=0;DBQ=./diet.xls" "verbose": ...
Please note that after "DBQ=" you need to either provide the complete path to the file or start the relative path with "./". The option "verbose" in the table declaration is useful to generate additional feedback about the error.

--
Filipe Brandão
am...@googlegroups.com

On Fri, Aug 16, 2019 at 12:58 PM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:

Dear AMPL supporter,

I am about to read a database with more than 255 columns, the ampl gives me an error that does not make any sense to me (please see below): says column FTD18 does not appear in table, which I know this column is there. I wonder this is the limitation with the ODBC or something else? I would appreciate for any thought. I have post my odbc window as well. P.S. my Excel is 64 bit and my AMPL version is 64 too (option version 'AMPL Version 20171212 (MS VC++ 10.0, 64-bit)\

Error reading table CprcsF with table handler odbc:

Column "FTD18" does not appear in table `CprcsF` of "C:\Users\ampl02\AMPL02018\SecondRunModel\Paper2\Tst\DataIntegratedPaper3.xlsx",

which has 255 columns:

P.S. I have already had this problem with reading rows more than 64,000, and Filipe Brandão gave gave me a tip it was very successful.

Thanks for your prompt answer and sorry if you have received my message twice.

/Eric

--
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.

[Eric E Sättar]

Hello Filipe,

I wonder this is the correct format to feed the ampl with csv format? I tried this format, seems doesn't work and return multiple errors such as 

cannot find data, cannot find parameters , etc....

 table xax IN "C:\Users\.....\xax .csv":

[i~proces], {j in Re} <proces [i,j]~(j)>; 

read table xax ;

display proces;

Shall I have to define the name for table xax in csv sheet like excel or does not need it?

 I would appreciate for any thought in the case.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

To unsubscribe from this group and stop receiving emails from it, send an email to am...@googlegroups.com.

[AMPL Google Group]

The table driver for csv does not seem very straightforward. An alternative is to use .tab files as shown in https://ampl.com/NEW/tables.html and in the example attached. The .tab format is very similar to csv but instead of comas between columns we can use spaces and there is a line at the top indicating how many columns belong to the index and how many correspond to the values.

--
Filipe Brandão
am...@googlegroups.com

{#HS:931176872-52505#}

On Thu, Aug 22, 2019 at 1:19 PM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:

Hello Filipe,

I wonder this is the correct format to feed the ampl with csv format? I tried this format, seems doesn't work and return multiple errors such as
cannot find data, cannot find parameters , etc....

table xax IN "C:\Users\.....\xax .csv":
[i~proces], {j in Re} <proces [i,j]~(j)>;
read table xax ;
display proces;

Shall I have to define the name for table xax in csv sheet like excel or does not need it?

I would appreciate for any thought in the case.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

On Tue, Aug 20, 2019 at 2:03 PM UTC, AMPL Google Group <am...@googlegroups.com> wrote:

Yes, it looks like the driver is limited to 255 columns and .tab or txt should be better options for that amount of data. The driver seems to follow access's specification in the number of fields (columns) (see, e.g., https://support.office.com/en-us/article/access-specifications-0cf3c66f-9cf2-4e32-9568-98c1025bb47c) that is limited to 255 fields in a table.

--
Filipe Brandão
am...@googlegroups.com

To unsubscribe from this group and stop receiving emails from it, send an email to ampl+uns...@googlegroups.com.