Author Topic: Exporting the results of a SQL query  (Read 1105 times)

DavidM

  • EA User
  • **
  • Posts: 64
  • Karma: +0/-0
    • View Profile
Exporting the results of a SQL query
« on: November 18, 2018, 03:35:18 pm »
Hi Everybody,
Have accepted that Sparx Class diagrams doesn't support logical model foreign/primary composite keys, I have had to grin and bear it.
Another question I have, still being a fairly newcomer to Sparxs, is can you output the results of a report in CSV?

I know we have an export csv function, but you cannot apply complex SQL to that (e.g. doing sub queries based upon result etc.).

I can (and am)  doing it in the report with SQL fragments, but the fields I am producing (30+) are just too big for the landscape, besides I do want it as an excel/csv anyway that can be filtered and graphically represented by data owners and stewards.

Any suggestions?
The only thing I can think of is to bolt a Reporting engine on the front of the database Hyperion, SQL Reporting Services etc.. but then that gives us 3 different and inconsistent methods of extracting meta data - reports, export CSV, and reporting.

Thanks in advance
David

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 10172
  • Karma: +329/-30
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Exporting the results of a SQL query
« Reply #1 on: November 19, 2018, 05:08:33 am »
David,

The simplest way is to write an SQL search query and export the results from there.

Another option is to write a small script that automates that.
In such a script you could also directly export to Excel if you wanted to.

Geert

DavidM

  • EA User
  • **
  • Posts: 64
  • Karma: +0/-0
    • View Profile
Re: Exporting the results of a SQL query
« Reply #2 on: November 21, 2018, 08:11:13 pm »
Thanks Geert,
I've never written a SQL search query before (I am assuming it is in EA?) , I shall have to investigate!
Thank you.

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 10172
  • Karma: +329/-30
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Exporting the results of a SQL query
« Reply #3 on: November 21, 2018, 08:16:40 pm »
Thanks Geert,
I've never written a SQL search query before (I am assuming it is in EA?) , I shall have to investigate!
Thank you.

I posted few articles about SQL searches on my website that might be of use: https://bellekens.com/tag/sql/

Geert

DavidM

  • EA User
  • **
  • Posts: 64
  • Karma: +0/-0
    • View Profile
Re: Exporting the results of a SQL query
« Reply #4 on: November 21, 2018, 08:19:32 pm »
Just tried it out - worked pretty well - thanks, I'm not sure how to export however, but I shall check out your website link

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 10172
  • Karma: +329/-30
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Exporting the results of a SQL query
« Reply #5 on: November 21, 2018, 08:56:45 pm »
Export to CSV from the button/menu on the top right of your search results (next to the [?] button for help)
I think you need to first select all results

(or Ctrl-A, Ctrl-C, open notepad, Ctrl-V, save as .csv)

Geert