GDXXRW Excel read - how to read excel data tables containing MERGED cells

GAMS
1

Hi

We read tables of data directly in from Excel using GDXXRW. The
tables are something like:

apple orange
summer girl 1 2
summer boy 3 4
winter girl 5 6
winter boy 7 8

The data we are now getting has merged cells in the table, done for
formatting reasons because it is easier to read. For example imagine
the table shown above has the 2 summer cells ‘merged’ using Excel’s
merge cells capability (ie cells A2 and A3 merged, and A4 and A5
merged).

Our gdxxrw is failing on this. The error is:
**** There were 1 duplicate entries for symbol myTable
1: ,boy,apple
2: ,boy,apple
etc

GAMS 23.0.2 Win 9396.9411 VIS x86/MS Windows

So clearly it is not picking up a summer/winter entry in the boy row
during the read.


Questions:

  1. Is there an option or something which will allow gdxxrw to read in
    values for rows with merged cells? (and assume the value of the merged
    cell)
  2. Does a different version of GAMS (gdxxrw) work for merged cells?


    I looked through the manuals and didn’t see anything, but wondered if
    there was some info out there.

Cheers
AndyC

\

2

Andy,

try option CMerge as in ‘gdxxrw x.xlsx cmerge=1 par=x rng=a1 dim=3’.
That should do the trick.
BTW, gdxxrw without arguments gives you a list of possible command
line parameters if you don’t want to look into the ‘gdxutils.pdf’.

Hope this helps,
Michael Bussieck - GAMSWorld Coordinator



On Nov 23, 7:45 pm, AC wrote:

Hi

We read tables of data directly in from Excel using GDXXRW. The
tables are something like:

                   apple   orange

summer girl 1 2
summer boy 3 4
winter girl 5 6
winter boy 7 8

The data we are now getting has merged cells in the table, done for
formatting reasons because it is easier to read. For example imagine
the table shown above has the 2 summer cells ‘merged’ using Excel’s
merge cells capability (ie cells A2 and A3 merged, and A4 and A5
merged).

Our gdxxrw is failing on this. The error is:
**** There were 1 duplicate entries for symbol myTable
1: ,boy,apple
2: ,boy,apple
etc

GAMS 23.0.2 Win 9396.9411 VIS x86/MS Windows

So clearly it is not picking up a summer/winter entry in the boy row
during the read.

Questions:

  1. Is there an option or something which will allow gdxxrw to read in
    values for rows with merged cells? (and assume the value of the merged
    cell)
  2. Does a different version of GAMS (gdxxrw) work for merged cells?

I looked through the manuals and didn’t see anything, but wondered if
there was some info out there.

Cheers
AndyC

\