Extent fields in excel via ODBC

Posted by Alessandro Spotti on 07-Mar-2016 04:06

Hi,

  I remember once that retrieving OpenEdge extent fields via ODBC return separate columns in access or excel. 
Is it still possible now. What is the best approach: custom sql query? some options in the ODBC configuration?

thanks.

   Alex

OpenEdge General - Forum

All Replies

Posted by Jean Richert on 10-Mar-2016 02:52

Anyone following this forum would be able to assist Alessandro?

Posted by laurent KIEFFER on 10-Mar-2016 03:01

Hi Alessandro

by default an extent column will be presented as a comma separated datas .

If you ant to have any other split information some work should be done probably using some intermediary datastructure and split in other columns .... but not sure how it should be done for Excel (some VBA dev...)

But if you use Open Access for OpenEdge you could define a special structure and expose specific columns to be used with an ODBC driver.  For this you will need to use Appserver and Datadirect OpenAccess .

Perhaps some other idea could be to investigate the views in the OE Database ?

My 2 cents insight

Laurent

Posted by gus on 10-Mar-2016 07:55

> On Mar 10, 2016, at 4:02 AM, laurent KIEFFER wrote:

>

> by default an extent column will be presented as a comma separated datas .

quite some time ago, the sql array support was enhanced to do it in the manner of the sql 1999 specification.

so if you have an array column foo, in OpenEdeg SQL you reference its elements by foo[1], foo[2], etc.

regards,gus

gus@progress.com

"A distributed system is one in which the failure of a computer you didn't

even know existed can render your own computer unusable."

-- Leslie Lamport

Posted by Steve Moore on 10-Mar-2016 08:32

Does the following - works with Excel 2013 and OpenEdge 11.6 - solve the problem?

1) In Excel select "From Microsoft Query" from the "Other Sources" drop-down (Get External Data) in the "Data" tab.

2) Select the OpenEdge database.

3) Add the fields including the extent field.

4) Confirm the various dialogs and for the "Query Wizard - Finish" dialog select "View data or edit query in Microsoft Query" then the Finish button.

5) From the new "Microsoft Query" select "View->SQL...".

6) Edit the SQL code replacing the extent field with the extent number(s).  Instead of Salesrep_0.MonthQuota you have Salesrep_0.MonthQuota[3], Salesrep_0.MonthQuota[7] etc..

7) Press OK. A message will appear saying "SQL Query can't be represented graphically. Continue anyway?", OK this.

8) File->Return Data to Microsoft Excel and only extent fields data should be in the columns.

This thread is closed